Microsoft SQL Server management is often a difficult and time-consuming task. To address challenges such as performance tuning, scalability, and updates, Microsoft introduced the Azure SQL Managed Instance.
Azure SQL Managed Instances take the difficulty out of managing a SQL environment. It uses an evergreen Microsoft SQL, which always uses the latest version and features. SQL upgrades, patching, and security are fully managed and automated. High availability, disaster recovery, and automated backups make Managed SQL easy to administrator and use.
Provisioning an Azure SQL Managed Instance
To get started, navigate to the Azure SQL service offering within the Azure Portal. Click on “Create Azure SQL resource” to get started.
A couple of options are available for SQL deployments. Though this article focuses on the middle option, SQL managed instances, there are a few other options that may be useful.
- SQL Databases: Flexible options for both single databases and elastic pools, this is a modern take on managed database offerings.
- SQL Managed Instances: Excellent for migrations and “lift-and-shift” operations, an easy to administrator and powerful SQL environment.
- SQL Virtual Machines: Traditional SQL Server that can be fully managed by an administrator.
In this case, we are going to work with the SQL managed instance. Click on “Create” under the SQL managed instance option to start the provisioning wizard.
First, we need to enter a name, select the region, and provide the administrator account details.
The administrator account is a SQL login type account, and can be connected to like so.
If you would like to change the compute resources provided, click on “Configure Managed Instance.” First, we need to choose the type of service tier and the compute hardware. As you can tell, this is not exactly the cheapest of options but does provide a lot of utility. Balanced against the time savings with patching and management, this very well could easily pay for itself.
Networking, additional settings, and tags will all be created with their defaults in this article. If you need customization of the collations, time zones, public endpoints, and tagging, you will want to review those pages in depth.
Finally, create the SQL managed instance. Word of caution, this process can take up to 6 hours, per their documentation. During the creation of this instance, it took about 2 hours to fully provision.
Create a New Database
Now that we have our SQL server provisioned, let’s create a simple database named testdatabase. Though this can be done through SQL Server Management Studio (SSMS), it can also be done via the Azure Portal. Click on the New Database option to quickly provision a database.
Simply provide a name and click on “Create.” This will immediately create the database and make it available.
Connecting via Public Endpoint
There are a number of ways to connect to this instance. You can choose to connect via a virtual machine with SSMS installed that can then connect to this instance, via code using the available connection strings, or if you choose to allow the public endpoint to be available, by remote SSMS installation. For ease of management, we will demonstrate how to allow the remote endpoint option.
Any time you open a port externally, you run the risk of unauthorized access, make sure to fully secure, and consider if this is the best option.
With that caveat out of the way, first, navigate to the Networking pane on your SQL managed instance. Choose “Enable” on the public endpoint (data) option and click on “Save.” This option by itself won’t allow an SSMS installation to access this instance, as the necessary ports needed to be opened in the Network Security Group (NSG).
On the overview page, locate the Virtual network/subnet section, and click on the link to the managed instance configuration.
Once under the virtual network resource assigned to this SQL managed instance, navigate to the subnet pane and take note of the Security group. In this case, this is named nsg-lc-test-sql-instance.
The easiest way to navigate to this NSG is to locate the link via the Microsoft Azure Portal search functionality. Click on the found resource to navigate to the page.
Once on the NSG attached to the SQL managed instance, navigate to Inbound security rules and click on “Add.”
Enter the following to create a firewall rule to allow public endpoint access to this managed instance and click on “Add” to enable the firewall rule.
- Source: Any
- Source port ranges: *
- Destination: Any
- Destination port ranges: 3342
- Protocol: TCP
- Action: Allow
- Priority: 1300 (This needs to be prioritized above the default deny all rule, which 1300 will do so by default.)
- Name: Port_3342
- Description: blank (optional)
Once the firewall rule has been added, navigate back to your SQL managed instance, and click on the “Connection Strings Pane.” In the ADO.NET section, you will see that the second section has the public endpoint. The value that you will need, as shown below, is the following.
lc-test-sql-instance.public.1994d7e4f669.database.windows.net,3342
Note that we are keeping the ,3342 section as this will tell SSMS what unique port to use to connect to this instance. SQL uses the , instead of the typical : to denote port.
Take the value that you retrieved from the ADO.NET public endpoint configuration, and connect using SSMS and your previously configured Administrator login and password.
You can see the previously created test-database is available for administration, just like a typical SQL server.
Conclusion
Azure SQL Managed Instances provide a powerful and easy-to-use SQL instance to use. This can be leveraged to migrate on-premise resources quickly and easily, or to take the burden off of SQL administrators and empower developers to leverage the full power of Microsoft SQL.