Microsoft SQL Server management is often a difficult and time-consuming task. To address challenges such as performance adjustment, scalability, and updates, Microsoft introduced the Azure SQL Managed Instance.
Azure SQL Managed Instances takes the hassle out of managing an 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 manage and use.
Providing an Azure SQL managed instance
To get started, navigate to the Azure SQL service offering in the Azure Portal. Click “Create Azure SQL Resource”; to get started.
A couple of options are available for SQL distributions. Although this article focuses on the middle option, SQL-managed instances, there are some other options that may be useful.
- SQL databases: Flexible options for both individual databases and elastic pools, this is a modern way of managing database offerings.
- SQL managed instances: Great for migrations and lift-and-shift operations, an easy-to-manage and powerful SQL environment.
- SQL virtual machines: Traditional SQL Server that can be completely managed by an administrator.
In this case, we will work with SQL-managed instance. Click “Create” under the SQL Management instance option to launch the provisioning wizard.
First we need to enter a name, select region and enter administrator account information.
The administrator account is an SQL login account and can be connected to such.
To change estimated resources, click “Configure Managed Instance.” First we need to choose the type of service level and the calculation hardware. As you can tell, this is not exactly the cheapest option but provides a lot of benefits. Balanced with the time savings with patching and handling, this could very well pay for itself.
Networks, additional settings, and tags will be created with their default settings in this article. If you need to customize collections, time zones, public endpoints, and tags, you’ll want to look at these pages more deeply.
Finally, create SQL-managed instance. Warning, this process can take up to 6 hours according to their documentation. During the creation of this instance, it took about 2 hours to provide completely.
Create a new database
Now that we have our SQL server provided, let’s create a simple database with the name
testdatabase. Although this can be done through SQL Server Management Studio (SSMS), it can also be done through Azure Portal. Click the New Database option to quickly create a database.
Just enter a name and click “Create”. This immediately creates the database and makes it accessible.
Connects 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 which can then connect to this instance, via code using available connection strings, or if you choose to allow the public endpoint to be accessible, by remote installation of SSMS. To facilitate management, we will show you how to allow the remote access point option.
Every time you open a door externally, you risk unauthorized access, make sure you are completely secure and consider whether this is the best option.
With that warning out of the way, first navigate to the network window of your SQL-managed instance. Select “Activate” in the public endpoint (data) and click “Save”. This option does not in itself allow an SSMS installation to access this instance, as the necessary ports are required to open the Network Security Group (NSG).
Find the section Virtual Network / Subnet on the overview page and click on the link to the configuration of the managed instance.
Once under the virtual network resource assigned to this SQL-managed instance, navigate to the subnet window and note the security group. In this case, this is called
The easiest way to navigate to this NSG is to find the link via the search function in the Microsoft Azure Portal. Click on the resource found to navigate to the page.
When you are on NSG linked to SQL managed instance, navigate to Incoming Security Rules and click “Add”.
Enter the following to create a firewall rule to allow public endpoint access to this managed instance and click “Add” to enable the firewall rule.
- Source: A few
- Source port range: *
- Destination: A few
- Destination port areas: 3342
- Protocol: TCP
- Action: Allow
- Priority: 1300 (This must be prioritized over default denying all rules, which 1300 will do by default.)
- Name: Port_3342
- Description: empty (optional)
Once the firewall rule has been added, navigate back to your SQL-managed instance and click “Connection Strings Pane.” In the ADO.NET section, you will see that the second section has the public endpoint. The value you need, as shown below, is as follows.
Please note that we are in the process of doing so
,3342section as this will tell the SSMS which unique port to use to connect to this instance. SQL uses
,instead of the typical
:to denote port.
Take the value you retrieved from the public endpoint ADO.NET 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.
Azure SQL Managed Instances provide a powerful and easy-to-use SQL instance to use. This can be used to migrate local resources quickly and easily or to remove the burden on SQL administrators and allow developers to take full advantage of Microsoft SQL.