- EC2 Image Builder: Build your golden VM images on AWS - Wed, Jan 19 2022
- Configuring DFS Namespaces for Amazon FSx for Windows file servers - Fri, Jan 7 2022
- AWS Systems Manager Session Manager: Securely connect EC2 instances - Wed, Dec 22 2021
Microsoft has offered database-as-a-service (DBaaS) solutions since Azure's early days. Over time, they've made many enhancements and updates to Azure SQL Database to fulfill the needs of many customers. However, some customers need to have SQL Server features not supported for Azure SQL Database, such as SQL Agent or linked servers. The only way to get the full SQL Server experience was to install it on an Azure VM.
Azure SQL Database Managed Instance offers you the full functionalities of SQL Server Enterprise as a service and comes with the following benefits:
- Platform-as-a-service (PaaS) solution: This relieves the headache of managing, maintaining, and updating SQL Server and Windows.
- Integration with Azure AD: In addition to the traditional SQL logins you're used to, you can also authenticate your SQL databases with Azure AD accounts. This comes with other features like multi-factor authentication.
- Supportability of SQL Server Enterprise Edition features: You can use all the features you used on premises with Azure SQL managed instances.
- Backward compatibility: You can migrate databases for SQL Server 2005/2008, and they will function properly on the managed instances.
- Dedicated instance: Unlike Azure SQL Database, you will not be sharing the same SQL Server that hosts the databases with others, as you will have your own instance.
- High availability: Managed instances reside on premium storage, giving them 99.99% availability and saving the costs of working with always-on availability groups.
Managed instances are available in two flavors:
- General purpose: This flavor hosts common applications that don't require extraordinary performance.
- Business critical: This flavor hosts applications that require low input/output latency and high resiliency against failures.
Create an Azure SQL managed instance ^
- Navigate to Azure Portal and click on All Services. Then search for SQL managed instances.
- Click on Add, and a new blade will pop up where you have to specify the following:
- Subscription: The subscription to charge for using this service
- Managed instance name: Specify a descriptive name for the instance
- Managed instance admin login: Specify a SQL admin user name
- Password: Specify a strong password based on the criteria that will pop up when you start typing the password
- Resource group: The resource group where this service will exist as a resource
- Virtual network: You can either select an existing subnet in a virtual network (VNet) or select Create new virtual network. The latter option will create a new VNet that fulfills the managed SQL instance's requirements. However, if you want to select a VNet, make sure the subnet in the selected VNet meets the following conditions. Otherwise, it will not be deployed.
- Other cloud services than SQL managed instances do not use the subnet
- The subnet is not a gateway subnet
- No service endpoints are attached
- The subnet must have at least 16 IP addresses.
- There's a user-defined route table for the subnet with 0.0.0.0/24 next-hop internet
- There's a network security group with the following rules:
Inbound security rules
Name Port Protocol Source Destination Action Management 9000, 9003, 1438, 1440, 1452 TCP Any Any Allow Mi_subnet Any Any MI Subnet Any Allow Health_probe Any Any Azure Load Balancer Any Allow
Outbound security rules
Name Port Protocol Source Destination Action Management 80, 443, 12000 TCP Any Any Allow Mi_subnet Any Any Any MI Subnet Allow
- Pricing tier: This will be a grayed-out option until you specify your network option. After that, you can select the flavor that fits your application. Also, you have to specify the number of vCores and the size of storage you want to assign to the managed instance. You can choose from the following compute generations:
- Gen 4: Based on Intel E5-2673 v3 (Haswell) 2.4 GHz processors, this generation supports 8/16/24 vCores and storage ranging from 32 GB to 8 TB
- Gen 5: Based on Intel E5-2673 v4 (Broadwell) 2.3 GHz processors, this generation supports 8/16/24/32/40/64/80 vCores and storage ranging from 32 GB to 4 TB
- After you finish your configuration, click on Create.
- When you try to increase the storage, it will round the custom amount to the nearest value divided by 32
- If you already have a SQL Server license, you can reuse it in this case, and it will save you up to 55%
Connect to the SQL Server ^
You can connect to the created SQL Server via SQL Server Management Studio (SSMS) via the following ways:
- You have a VM in the same VNet of the managed instance and have SSMS installed on it
- You have a VM on a VNet peered with the VNet in which the managed instance exists and have SSMS installed on it
- You have a point-to-site or a site-to-site VPN connection with your environment and SSMS installed
Once you get your environment ready for access, you can open SSMS and follow these steps:
Subscribe to 4sysops newsletter!
- Navigate back to the managed instance we created earlier
- On the overview page, copy the host name indicated in the following figure
- Open SSMS and input the following:
- Server name: The host name you copied
- Authentication: Select SQL Server Authentication—this is the default method to connect to a SQL managed instance
- Login: Enter the managed instance admin login you specified when creating the managed instance
- Password: Enter the password you specified for the managed instance admin login
In this article, I introduced Azure SQL managed instances. This new service has made embracing Azure much easier for SQL Server users who need all the features of SQL Server Enterprise Edition and don't want to manage Windows VMs.