Managing Microsoft SQL Server is often a difficult and time-consuming task. Microsoft introduced the Azure SQL Managed Instance to address challenges such as performance optimization, scalability and updates.
Managing an SQL environment is no longer difficult with Azure SQL Managed Instances. Evergreen Microsoft SQL is used, which always uses the latest version and features. SQL upgrades, patches and security are fully managed and automated. High availability, disaster recovery, and automated backups make managed SQL easy to manage and use.
Deploy an Azure SQL managed instance
First, navigate to the Azure SQL service offering in the Azure portal. Click Create Azure SQL Resource to get started.
There are a few options available for SQL deployments. While this article focuses on the middle option, SQL Managed Instances, there are a few other options that can be useful.
- SQL databases: Flexible options for both individual databases and elastic pools. This is a modern take on the managed database offerings.
- Instances managed by SQL: Excellent for migrations and lift-and-shift operations, easy to manage and powerful SQL environment.
- Virtual SQL machines: Traditional SQL Server that can be fully managed by an administrator.
In this case we are working with the instance managed by SQL. Under the SQL Managed Instance option, click Create to start the Deployment 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 as follows.
If you want to change the provisioned computing resources, click Configure Managed Instance. First of all, we need to select the type of service level and the computer hardware. As you can see, this isn’t exactly the cheapest option, but it offers a lot of benefits. Compared to the time saved in patching and administration, this could very easily be amortized.
Network, additional settings, and tags are all created with their default settings in this article. If you need to adjust the sorts, time zones, public endpoints, and tags, be sure to check these pages carefully.
Finally, create the SQL managed instance. Caution, according to the documentation, this process can take up to 6 hours. It took approximately 2 hours to fully deploy while this instance was being created.
Create a new database
Now that we have our SQL Server deployed, let’s create a simple database called
testdatabase. This can be done through SQL Server Management Studio (SSMS), but also through the Azure portal. Click the New Database option to quickly mount a database.
Just enter a name and click “Create”. This creates the database and makes it available immediately.
Establish connection via public endpoint
There are several ways to connect to this instance. You can connect through a virtual machine with SSMS installed, which can then connect to that instance through code using the available connection strings, or, if you allow the public endpoint to be available, through remote SSMS installation. To simplify administration, it shows how to allow the remote endpoint option.
Every time you open a port externally, there is a risk of unauthorized access. Make sure you are fully secured and see if this is the best option.
First, with this restriction, navigate to the network area of your SQL managed instance. Select Enable for the Public Endpoint (Data) option and click Save. This option alone does not allow an SSMS installation to access this instance because the required ports in the Network Security Group (NSG) had to be opened.
On the overview page, find the Virtual Network / Subnet section and click the link to configure the managed instance.
Under the virtual network resource assigned to this managed SQL instance, navigate to the subnet range and make note of the security group. In this case this is named
The easiest way to navigate to this NSG is to find the link using the Microsoft Azure portal search function. Click on the resource found to navigate to the page.
In the NSG attached to the SQL Managed Instance, navigate to the Incoming Security Rules and click Add.
Enter the following to create a firewall rule that will allow public endpoint access to this Managed Instance and click Add to enable the firewall rule.
- Source: Any
- Source port areas: * *
- Target: Any
- Target port areas: 3342
- Protocol: TCP
- Action: enable
- Priority: 1300 (This needs to be prioritized over the standard Deny All rule, which 1300 does by default.)
- Surname: Port_3342
- Description: empty (optional)
After adding the firewall rule, navigate back to your SQL managed instance and click the “Connection Strings” section. In the ADO.NET section, you can see that the second section has the public endpoint. The value you need as shown below is the following.
Notice we’re keeping this
,3342This section tells SSMS which unique port to use to connect to this instance. SQL uses the
,instead of the typical
:To designate port.
Take the value you obtained from the ADO.NET public endpoint configuration and connect to SSMS and your previously configured administrator login and password.
You can see the ones created earlier
test-database is available for administration like a typical SQL Server.
Azure SQL Managed Instances provide a powerful and easy-to-use SQL instance. This can be used to migrate local resources quickly and easily or to relieve SQL administrators and give developers the opportunity to use the full power of Microsoft SQL.