SQL Managed Instance enabled by Azure Arc has near 100% compatibility with the latest SQL Server database engine, and enables existing SQL Server customers to lift and shift their applications to Azure Arc data services with minimal application and database changes while maintaining data sovereignty. At the same time, SQL Managed Instance includes built-in management capabilities that drastically reduce management overhead.
In Part 1 we created the required componenet for creating managed instance enabled by Azure Arc
In the Basics section, select the Subscription and Resource group to install SQL to. This should be identical to where the data controller was created.
Under Managed Instance details, select an instance name for the SQL cluster, as well as setting the custom location.
Click Configure compute + storage to set up the virtual hardware and service tiers
Two service tiers are available for compute + storage.
The General Purpose tier is designed for most general workloads with common performance and availability features and can be compared to SQL Standard Edition. There is only one replica, and the high availability is achieved via Kubernetes built-in orchestration.
For example, if a pod or node containing the SQL Managed Instance container fails, Kubernetes will attempt to spin up another pod and attach the same persistent storage to it. During this time, the SQL Managed Instance will be unavailable to the applications. Applications will need to reconnect and retry the transaction after the new pod is running.
If the LoadBalancer service type was used, applications can reconnect to the same primary endpoint IP and Kubernetes will redirect the connection to the new pod. If NodePort was used, applications will need to reconnect to the new IP.
The Business Critical tier is designed for performance and latency-sensitive workloads with robust high availability features and can be compared to SQL Enterprise Edition. In addition to built-in Kubernetes orchestration, SQL availability groups are now used for high availability.
SQL Managed Instances using the Business Critical tier can be deployed with two or three replicas that are always in sync with each other. With availability groups, any pod or node failures are transparent to the application, as at least one pod is always available to take on connections. This example was set up using the General Purpose tier and development use licensing.
Selecting the For development use only is perfect for testing and incurs no Azure costs, but cannot be used for any production workloads. This is one benefit of on-premises Azure Arc-enabled SQL Managed Instances, as development licensing is not available for Azure cloud-based SQL Managed Instances.
Default compute uses up to 4 CPU vCores (which is what billing is based on) and 8 GB of RAM. Be sure that your Kubernetes cluster nodes have enough memory and CPU to support the compute settings you select, or the pods will not have enough resources to start.
Click the Review + create button. This will take you to the final screen. If the information is correct, click Create to deploy the Azure Arc enabled SQL Managed Instance.
Connect from Azure Data Studio
- In Azure Data Studio, under Connections tab, select the New Connection on the Servers view
- Under Connection>Server, paste the ServerEndpoint
- Select SQL Login as the Authentication type
- Enter sa as the user name
- Enter the password for the sa account
- Optionally, enter the specific database name to connect to
- Optionally, select/Add New Server Group as appropriate
- Select Connect to connect to the Azure SQL Managed Instance – Azure Arc
From SSMS
In conclusion, SQL Managed Instance on Azure Arc represents a transformative approach to database management by extending Azure’s cloud capabilities to on-premises and multi-cloud environments. This hybrid solution combines the scalability, flexibility, and advanced features of Azure SQL Database with the control, compliance, and cost-efficiency of on-premises infrastructure.
Key benefits include seamless integration with existing infrastructure, enhanced security features, automated updates, and consistent performance. These advantages enable organizations to modernize their data estates, optimize operational efficiency, and ensure robust data governance while leveraging their existing investments.
By deploying SQL Managed Instance on Azure Arc, businesses can achieve greater agility in managing their data landscape, support diverse workloads, and future-proof their IT strategies against evolving technological demands. This hybrid solution is ideal for enterprises looking to innovate and drive growth without compromising on security, compliance, or performance.
I hope this was informative for you, please let me know your comments.
See you soon
Karim Hamdy
Infrastructure Architect
