In the previous post, we covered the case for Managed Instance. In this post, I’ll show you how to create a Managed Instance and connect to it from SQL Server Management Studio.
Creating an Azure SQL Database Managed Instance
Login to your Azure Portal, and create a new resource of type “Azure SQL Managed Instance”. If you need one, you can get a free Azure account here, which includes 12 months of free services such as Windows and Linux VMs, Azure Storage and SQL Databases. For other services, you get $200 credit to explore any Azure Service, including Managed Instance.
Per below, enter the appropriate details for the Managed Instance. There’s a couple of key things to point out, starting with the Virtual Network.
Managed Instance Networking
You’ll note in the image above the option to “Prepare subnet for Managed Instance” below the Virtual Network setting. This step automatically configures the network security group (NSG) and routes (UDR) to enable the instance to be managed by Microsoft while still living inside a vNet with a private IP address. It also creates a dedicated subnet for the instance. Detailed coverage on Managed Instance networking is covered here.
High Availability & Disaster Recovery
You’ll also note the option to use this Managed Instance as a Failover Group secondary. This option enables the equivalent of what we have with SQL Server Always On Availability Groups to implement a DR strategy.
Managed Instance automatically provides High Availability with up to 3 secondary replicas (with one readable copy) in the Business Critical edition. To provide Disaster Recover protection to a different geographical region, we can tick this box, which will enable a pair of Managed Instances to share an availability group of databases, just like AlwaysOn in SQL Server.
Finally, select a performance level. Managed Instance is available in both General Purpose and Business Critical tiers. As documented here, Business Critical offers a series of benefits including readable secondary copies of the databases, super-fast local SSD storage for minimal latency and the highest IO throughput, and support for In-Memory OLTP.
At this stage, Manage Instance supports from 8 to 80 cores, and up to 8TB storage. The recently announced Hyperscale public preview extends the storage capacity up to 100TB.
Once you’ve selected the desired performance tier, click create.
Given that Managed Instance lives inside a vNet, we can’t simply open SSMS on our desktop and connect to a public endpoint, like we can with other PaaS services. When you open your Managed Instance in the Azure Portal, you’ll notice a “Get Started” link, which provides 2 ways to connect – Via a Virtual Machine with SSMS running in the same vNet as our Managed Instance, or through a VPN connection, which allows connectivity from your desktop on your home or work computer.
Connecting through a Point-to-Site VPN
Let’s go ahead and setup a Point-to-Site VPN. The best way to do this is to follow the guidance in this article from step 2 onwards. Step 1 covers the creation of the vNet, which was automatically done in the creation of the Managed Instance. Steps 2 onwards cover;
- Creating a Gateway Subnet
- Creating a vNet Gateway
- Generating Root and Client certificates
- Generating a pool of client IPs. The clients that connect over a Point-to-Site VPN dynamically receive an IP address from this range
- Configuring the VPN tunnel type e.g.; SSL (SSTP) and IKEv2
- Installing the Root certificate in the Gateway
- Generating and installing the VPN client configuration
Note that in the image above, you’ll see the code to use Powershell to create and configure the VPN Gateway, and attach it to the vNet. This can be used to automate deployments, rather than doing it manually in the Azure Portal.
At the conclusion of these steps, you’ll have something like this, whereby clients (with installed certificates and VPN client) will be able to connect over a secure VPN into the vNet containing your Managed Instance.
Note that this setup makes sense for situations with a small number of clients connecting. For larger enterprise deployments, you can use a Site-to-Site VPN, or ExpressRoute.
SQL Server Management Studio
Now that the Managed Instance is created, and we’ve setup our VPN, let’s connect to it with SQL Server Management Studio (SSMS) from our desktop. Ensure that you’ve installed the latest version of SSMS, available here.
If you haven’t already, make sure your VPN client is connected. If you’ve installed it per the above steps, you’ll see it in your network settings, as below.
In SSMS, connect to a new server, and enter the server name and login details. The server name is shown in the getting started page. See the above image under the Network Connectivity section (step 3).
And you’re done. Having said that, your instance is empty with no user databases! In the next post, I’ll show you how to use Azure Database Migration Service to migrate an on-prem database to Managed Instance.