How to create SQL managed instance in azure

In this azure tutorial, we will discuss How to create an Azure SQL database. Apart from this, we will also discuss on the below topics

• What is Azure SQL Database managed instance
• Key features and benefits of Azure SQL Database managed instance
• Retrieve connection details to SQL Managed Instance
• Azure SQL managed instance vs Azure SQL database
• Azure SQL managed instance limitations
• Azure SQL Managed Instance Monitoring
• Azure SQL Managed Instance Cross Database Query

What is Azure SQL Database managed instance

Azure SQL Managed Instance is a cloud database service that combines the best features of Azure SQL Database and the SQL Server database engine.

• How to create an Azure SQL database
• Azure SQL Managed Instance Backup

This is a platform as a service and it is 100% compatible with the latest SQL Server database engine.

This service gives you a way to migrate database from on-premises environments to the cloud with lowers risk. 

This service allows the existing SQL Server customers to migrate their on-premises applications to the cloud with very less application and database changes

Azure SQL Managed Instance is designed for customers who are looking to migrate a large number of applications from an on-premises to the cloud environment.

Key features and benefits of Azure SQL Database managed instance

• It is a platform as a service with fully loaded Paas features.
• Provides an isolated environment with VNET integration, single-tenant service, and high-security features like Private IP address, Azure AD authentication, Single sign-on support, SQL auditing, Advanced threat protection, etc.
• It is an excellent business model for the business users.
• Provides automated backups, automatic software patching feature.
• It provides all the latest database engine features.
• Provides built-in instance and database monitoring and metrics features.

How to create SQL managed instance in azure

Let’s discuss here, how to create a database in Azure SQL managed instance. Follow the below steps to create the database.

• How to create an Azure SQL database
• What is Azure web app?

Step-1: Login to the Azure portal (https://portal.azure.com/)

Step-2: Search for the Azure SQL and select the Azure SQL option.

Step-3: Click on the + Add button on the Azure SQL window.

Step-4: On the select SQL deployment option window, click on the Create button from the SQL managed instances section.

Step-5: Now the Create Azure SQL Database Managed Instance window will open. Fill out mandatory information required on the Basics tab.

• Subscription: Choose your correct subscription.
• Resource group: You can choose your existing resource group or can create a new one by clicking on the Create new link.
• Managed Instance name: Provide a name for the managed instance.
• Region: Select the region you belong to.
• Compute + storage: Select the default one or you can configure a new one by clicking on the Configure Manage Instance link as per your business need.
• Managed Instance admin login: Provide a username for the login.
• Password: Provide a password as per the password policy
• Confirm Password: Confirm the same Password.

Now click on the Next : Networking > button to go to the Networking tab.

Step-6: On the Networking tab, Fill the optional options as per the business need.

• Virtual network: Select a valid virtual network either or you can  Create new virtual network or the default one.
• Connection type: You can choose a proxy(Default) option or a redirect connection type.
• Public end point(data): Select Enable option here.

Click on the Next : Additional settings > button to navigate to the Additional settings tab.

Step-7: On the Additional settings tab, Fill the optional options as per the business need.

• Collation: Select the collation that you want to use for your managed instance. You can click on the Find a collation to choose a new one or else you can keep the default one as well.
• Time zone: Select the time zone you belong to.
• Use as failover secondary: Choose Yes for this option.

Now click on the Next: Tags button to go to the Tags tab.

Step-8: On the tags tab, you can keep the default option as it is. Click on the Next : Review + Create > button.

Step-9: Finally, click on the create button.

if you can see now, the deployment is succeeded.

Now to verify if the Azure SQL managed instance is created, you can open your resource group

Retrieve connection details to SQL Managed Instance

We can retrieve the Host name and FQDN to connect to the SQL managed instance

• From the resource group window, select your managed instance.

• From the Overview tab, locate the Host property and copy it you can use it to quickly connect to the Azure SQL managed instance.

Azure SQL managed instance vs Azure SQL database

Well, let’s discuss a few key difference between Azure SQL managed instance and Azure SQL database.

Azure SQL managed instance

• The Azure SQL Managed Instance is a Platform-as-a-Service (PaaS).
• This is 100% compatible with the latest SQL Server database engine.
• This service gives you a way to migrate the database from on-premises environments to the cloud with lowers risk. 
• Azure SQL Managed Instance is designed for customers who are looking to migrate a large number of applications from an on-premises to the cloud environment.
• There are some minimal number of SQL Server features that are not available with Azure SQL managed instance.
• The size-wise Databases of up to 8 TB supported.
• Supports Azure AD authentication. Along with this it also supports server-level Azure AD logins.
• SQL Server Profiler tool is supported in the case of the Azure SQL managed instance.
• The active geo-replication platform feature is not supported here.
• Azure Resource Health check feature is not supported here.
• VNet Service endpoint feature is not supported here.

Azure SQL database

• The Azure SQL Database is a category of Platform-as-a-Service (PaaS) and is a relational database-as-a-service hosted in Azure.
• This is a fully-managed SQL Server database engine and it is based on the latest version of SQL Server
• Azure SQL Database provides different deployment options like a single database and an elastic pool.
• The size-wise Databases of up to 100 TB supported.
• Supports Azure AD authentication and only Azure AD user in this case.
• SQL Server Profiler tool is not supported in the case of the Azure SQL database.
• The active geo-replication platform feature is supported here.
• Azure Resource Health check feature is supported here.
• VNet Service endpoint feature is supported here.

Azure SQL managed instance limitations

There are few limitations in terms of file size

• The maximum number of database files per instance is up to 280, unless the instance storage size or Azure Premium limit has been reached in case of general-purpose.
• Maximum number of database files per instance is 32,767 files per database, unless the instance storage size limit has been reached in case of business-critical.
• The maximum data file size is (max 2 TB – 8 TB) in case of general-purpose.
• In the case of business-critical, the maximum data file size is (up to 1 TB – 4 TB).
• Max log file size is limited to 2 TB in case of general-purpose and business-critical.
• Max number of databases per instance is 100 in both the case of general-purpose and business-critical scenarios.

Azure SQL Managed Instance Monitoring

Monitoring your Azure SQL managed instance is quite important to make it run smoothly in production. So that in case you will come to know about any critical issue, you can act on that immediately to make sure it won’t affect your business.

There are a number of tools available that can be used for monitoring your Azure SQL managed instance.

But, before considering the tool to monitor your Azure SQL managed instance, you should consider a few factors as mentioned below.

• Cost of the Tool
• What is the skill set required to run that tool
• How frequently it can run?
• How much capable the tool is with respect to alerting? etc.

Below is the list of a few key tools that you can consider to monitor your Azure SQL managed instance based on your requirement

• Azure Monitor
• Azure Insights (Preview)
• Azure SQL Analytics (Preview)
• Query Store

Azure SQL Managed Instance Cross Database Query

Using Linked servers, you can able to use the cross Database query.

You may like following Azure tutorials

• The ‘New-AzSqlServer’ command was found in the module ‘Az.Sql’, but the module could not be loaded
• Microsoft Azure naming conventions
• How To Change User Id And Password For Azure SQL Server Database
• Azure SQL Database Vs. SQL Server
• How To Upload SQL Database To Azure

Conclusion

In this Azure tutorial, We discussed

• How to create SQL managed instance in azure
• what is azure SQL database managed instance
• Key features and benefits of Azure SQL Database managed instance
• Retrieve connection details to SQL Managed Instance
• Azure SQL managed instance vs Azure SQL database
• Azure SQL managed instance limitations
• Azure SQL Managed Instance Monitoring
• Azure SQL Managed Instance Cross Database Query

Hope you have enjoyed this article !!!