Last week in Ignite Microsoft announced the general availability of Azure SQL Database Managed Instance. This service allows you run SQL Server with all the features of Microsoft SQL Server Enterprise in the cloud without needing to manage Windows virtual machines (VMs).

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 ^

  1. Navigate to Azure Portal and click on All Services. Then search for SQL managed instances.
  2. Click on Add, and a new blade will pop up where you have to specify the following:
    1. Subscription: The subscription to charge for using this service
    2. Managed instance name: Specify a descriptive name for the instance
    3. Managed instance admin login: Specify a SQL admin user name
    4. Password: Specify a strong password based on the criteria that will pop up when you start typing the password
    5. Resource group: The resource group where this service will exist as a resource
    6. 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.
      1. Other cloud services than SQL managed instances do not use the subnet
      2. The subnet is not a gateway subnet
      3. No service endpoints are attached
      4. The subnet must have at least 16 IP addresses.
      5. There's a user-defined route table for the subnet with 0.0.0.0/24 next-hop internet
      6. There's a network security group with the following rules:
        Inbound security rules

        NamePortProtocolSourceDestinationAction
        Management9000, 9003, 1438, 1440, 1452TCPAnyAnyAllow
        Mi_subnetAnyAnyMI SubnetAnyAllow
        Health_probeAnyAnyAzure Load BalancerAnyAllow

        Outbound security rules

        NamePortProtocolSourceDestinationAction
        Management80, 443, 12000TCPAnyAnyAllow
        Mi_subnetAnyAnyAnyMI SubnetAllow
    7. 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:
      1. 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
      2. 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
      3. After you finish your configuration, click on Create.

Note:

  • 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!

  1. Navigate back to the managed instance we created earlier
  2. On the overview page, copy the host name indicated in the following figure

    Managed server name

    Managed server name

  3. Open SSMS and input the following:
    1. Server name: The host name you copied
    2. Authentication: Select SQL Server Authentication—this is the default method to connect to a SQL managed instance
    3. Login: Enter the managed instance admin login you specified when creating the managed instance
    4. Password: Enter the password you specified for the managed instance admin login

      Connect to the managed instance

      Connect to the managed instance

Conclusion ^

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.

avatar
© 4sysops 2006 - 2022

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account