Updated: 11th November 2020 By Arun Sirpal (Microsoft MVP), Editorial Contributor
The purpose of this article is to discuss what Azure SQL Database Managed Instance is. This will then lay a foundation to explore the capabilities of this deployment model. We will discuss some advantages it can bring to your infrastructure with a cross-comparison against on-premises SQL Server. Finally, we look at some important networking configuration that you will need to be aware of when evaluating it.
What is Azure SQL Database Managed Instance?
Azure SQL Database is Microsoft’s relational database service that operates in Microsoft Azure as Platform as a Service (PaaS) where you have a choice of the following deployment options:
Their newest offering, Managed Instances provides near 100% compatibility with the latest SQL Server on-premises (Enterprise Edition) Database Engine. It became available for public preview in March 2018, and it is seen as a very attractive deployment model, but the question is why?
Many customers are benefiting from this because of three main reasons. Firstly, it is easy to lift and shift the database due to its near 100% compatibility with on-premises SQL Server, coupled with the fact that native restore commands work, thus allowing for easy migrations. All this is possible whilst still being a fully managed PaaS offering. Finally, a lot of thinking and design has gone into the connectivity architecture, allowing for an isolated and secure cloud environment for databases hosted within a Managed Instance where the foundations are built using native VNET implementation and the ability to leverage private IP addresses.
Sizing Options
Managed Instance can be deployed on two hardware generations (Gen4 and Gen5). Hardware generations have different characteristics that are described in the following table:
Gen4 hardware is being phased out (end of 2020) and is not available anymore for new deployments. All new instances of SQL Managed Instance must be deployed on Gen5 hardware. Microsoft even suggests that if you have Gen4 based deployments that you should consider moving to Gen5 to leverage a wider range of vCore options, improved storage and networking capabilities.
Managed Instance is available in two service tiers:
- General Purpose: Designed for applications with typical performance and IO latency requirements.
- Business Critical: Designed for applications with low IO latency requirements and minimal impact of underlying maintenance operations on the workload.
Quite simply, if you know that you need low I/O latency and high I/O throughput then Business Critical would make the most sense because this is based on a fast SSD storage I/O subsystem. If your business requirements dictate that you need more than one replica and read scale capability, then again Business Critical will be the most natural fit.
However, be aware of the capacity requirements for this because it will generally consume four times more capacity than a General Purpose tier database instance due to the extra replicas.
Managed Instance Types
Microsoft offers two types of deployment options for Managed Instances. They are the classic single instance and the newer type called instance pools.
The idea behind instance pools is to have pre-provisioned compute where you would typically move multiple smaller on-premises SQL instances to this pool. The benefit of this is maximising cost savings whilst keeping high levels of performance. For example, if you provision a 16 vCore instance pool you can then potentially deploy one 4 vCore and six 2vCore instances as shown below.
These pools rely on virtual clusters which represents a dedicated set of virtual machines that are within your VNET assigned for your workloads.
This is currently in public preview and there are some limitations you need to be aware of such as:
• Can only build instance pools on Generation 5 hardware.
• Only supported on General purpose tiers.
• 8TB pool limit for storage.
• 100 database limit per pool.
• Currently does not allow for Azure AD Authentication.
Native Backup Benefit
As mentioned in the introduction you have the ability to restore a backup of your database from Azure storage into a Managed Instance. This is groundbreaking, where in the past we have had to rely on tools and BACPACs to migrate databases into Azure. To do this obviously requires some configuration and TSQL code but the benefit of this feature makes it worthwhile. At a high level, you will need the latest version of SSMS (SQL Server Management Studio), a credential that uses a shared access signature to where the backup is located (Azure Storage) and some TSQL code for the actual restore.
Below shows the TSQL statements required, please note keys and secrets have been obfuscated for the purpose of this article.
Connect to the source server and issue the commands to create a credential and a full backup into Azure storage.
-- Create the credential CREATE CREDENTIAL [https://mystorage.blob.core.windows.net/backup] WITH IDENTITY = 'SHARED ACCESS SIGNATURE' , SECRET = 'your secret' --Backup BACKUP DATABASE [TestDB] TO URL = 'https://mystorage.blob.core.windows.net/backup/test.bak'
Then connect to the Managed Instance.
— Create the credential
CREATE CREDENTIAL [https://mystorage.blob.core.windows.net/backup]
WITH IDENTITY = ‘SHARED ACCESS SIGNATURE’
, SECRET = ‘your secret’
–Restore the backup
RESTORE DATABASE [TestDB] FROM URL =
‘https://mystorage.blob.core.windows.net/backup/test.bak’
Once you have restored the database do not forget that this is still a fully managed service so Microsoft will take care of all the backups and consistency checks for you.
Please note, backups from a Managed Instance can only be restored to another Managed Instance. They cannot be restored to an on-premises SQL Server or to an Azure SQL Database single server or an elastic pool.
Azure Database Migration Service
If you decide not to use native backup and restore commands to migrate data you may decide to use Azure Database Migration Service (DMS). This is a fully managed service designed to enable seamless migrations. A typical workstream for migration projects uses the following framework.
The assumption here is that you have completed all necessary steps and you are ready to select the method of migration, in this case, DMS. One of the disadvantages of the backup and restore method is the downtime required, with DMS you can leverage an online mode where there is very minimal downtime.
To start leveraging this tool you need to create DMS.
Once you click ADD, for online migrations you will need to select the premium tier.
From a networking perspective, the virtual network provides Database Migration Service with access to the source SQL Servers and target SQL Managed Instance.
Then create the service and let the deployment finish.
The key here now is to create a migration project where the target is SQL Managed Instance leveraging the online capability as shown below.
Once the project is created you set the online capability.
Other Benefits
The administration costs of this solution are very low. Remember we have just restored a SQL Server backup file and still we have key benefits built into the product. These benefits are what makes this technology more advantageous than classic on-premises SQL Servers.
- Automatic software patching
- Managed full, differential and log backups (already mentioned)
- Built-in High Availability with 99.99% uptime SLA
How does Microsoft achieve this 99.99% SLA? They actually have two different architectures depending on the service tier that you select. Let’s assume you provision a database that uses the Business Critical service tier. High availability is implemented using technology similar to SQL Server Availability Groups. Every database is a cluster of database nodes with one primary database that is accessible for customer workload and three secondary processes containing copies of data. The primary node constantly pushes the changes to secondary nodes in order to ensure that the data is available on secondary replicas if the primary node crashes for any reason.
The General Purpose tier uses the idea of a stateless compute layer for the sqlservr.exe and a stateful data layer for data files. With this concept of layers, if there is an issue with (for example) the hardware, the Azure Service Fabric will move the stateless SQL Server process to another stateless compute node and the data/log files are attached to a newly initialized SQL Server process.
Differences with Singleton Azure SQL Database?
Everything mentioned above also applies to the traditional single Azure SQL Databases. You might be asking what are the key differences that Managed Instances offer over the single Azure SQL Database deployment model? The features that you can use within a Managed Instance but not Azure SQL Database are listed below:
- Change data capture
- CLR
- Cross-database transactions
- Distributed partition views
- Filegroups for your database
- Resource governor
- SQL Agent
- Linked Servers
- Global temp tables
- DB Mail
- Service Broker
However please be aware that the following features are still not supported:
- SSRS
- SSIS
- SSAS
- R Services
- Polybase
- Policy-Based Management
Network Configuration – Preparation is Key
An absolute requirement for Managed Instances is that it must be deployed within a virtual network. You must be network savvy to set this up so it is advisable to prepare well for this section or seek help from a cloud administrator, which in an enterprise business will very likely be the case. This step is so important because it enables the following scenarios:
- Connection to a Managed Instance directly from an on-premises network
- Connecting a Managed Instance to a linked server or another on-premises datastore
- Connecting a Managed Instance to other Azure resources
There are further important requirements of a dedicated subnet within the virtual network and mandatory inbound/output ports that you also need to set up. All are well documented in official Microsoft documentation.
All this work is required to allow for security isolation from other tenants within the Azure Cloud. If you decide that you need to connect an on-premises application to the Managed Instance then you will need ExpressRoute or a site to site VPN for this. Obviously, things are the simplest if the application is inside the same virtual network. Study the below image, this image shows the different choices (1-7) available when connecting application and services to a Managed Instance, it is a great way to summarize this section.
Azure AD Authentication
Ultimately, SQL Managed Instance needs permissions to connect to Azure AD to successfully accomplish tasks such as authentication or creation of new users. You will need to go through setup which is out of scope for this blog post. Please see the following guide from Microsoft showing you the steps needed. https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell#provision-azure-ad-admin-sql-managed-instance.
However, it is worth mentioning the high-level steps that are needed. First is the creation and population of an Azure AD instance. Then the association of an Azure subscription to Azure AD and then the provisioning of an Azure AD Admin for the SQL Managed Instance. This should be a key requirement for many enterprises out there, Azure AD authentication should be used rather than just SQL based authentication.
A difference between Azure SQL Database and Managed Instance Azure AD Admin account is that we do not have to leverage the contained user model for Managed Instances because it supports instance-level concepts, so we can create a log in. The TSQL for such an activity is shown below.
— Syntax for Azure SQL Managed Instance
CREATE LOGIN login_name [FROM EXTERNAL PROVIDER]
Trace Flags
Since late 2019 Microsoft now allows the use of specific trace flags for SQL Managed Instance. Though this will be good news for DBAs to hear, one should always use these with caution. The trace flags that are now supported for use are: 460, 2301, 2389, 2390, 2453, 2467, 7471, 8207, 9389, 10316 and 11024. Below is a list-based description of the trace flags as a handy reference manual for those interested in changing database engine behaviours:
• 460 – Replaces data truncation message ID 8152 with message ID 2628.
• 2301 – Enable advanced decision support optimizations that are specific to decision support queries. This option applies to decision support processing of large data sets.
• 2389 – Enable automatically generated quick statistics for ascending keys (histogram amendment). If trace flag 2389 is set, and a leading statistics column is marked as ascending, then the histogram used to estimate cardinality will be adjusted at query compile time.
• 2390 – Enable automatically generated quick statistics for ascending or unknown keys (histogram amendment). If trace flag 2390 is set, and a leading statistics column is marked as ascending or unknown, then the histogram used to estimate cardinality will be adjusted at query compile time
• 2453 – Allows a table variable to trigger recompile when enough number of rows are changed.
• 2467 – Enables an alternate parallel worker thread allocation policy, based on which node has the least allocated threads.
• 7471 – Enables running multiple UPDATE STATISTICS for different statistics on a single table concurrently.
• 8207 – Enables singleton updates for Transactional Replication and CDC. Updates to subscribers can be replicated as a DELETE and INSERT pair.
• 9389 – Enables additional dynamic memory grant for batch mode operators.
• 10316 – Enables creation of additional indexes on internal memory-optimized staging temporal table.
• 11024 – Enables triggering the auto-update of statistics when the modification count of any partition exceeds the local threshold.
Transactional Replication
It is now possible to use transactional replication with SQL Managed Instance and it can be an integral part of your design. The concept of replication is no different than classic on-premises SQL replication. With this technology feature, you replicate data from tables in SQL Managed Instance to Azure SQL Database or on-premises.
This is a typical design pattern and it can morph into a slight variation where you can split the distributor off to its own managed instance.
Common setups are shown below, showing you the flexibility of replication within Managed Instances.
Local distributor setup
Remote distributor setup
With the above architectures, you can see that the Managed Instance acts as the publisher, you can also let a database in Azure SQL Managed Instance be a subscriber.
Subscriber setup
There are many types of replication available, Managed Instance only supports the types shown below within the blue box.
Please Note: To understand the components within a replication model (publisher, subscriber etc), please read https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/replication-publishing-model-overview?view=sql-server-ver15
Conclusion
To conclude you can see what a compelling option Azure SQL Database Managed Instance is. Having near to 100% compatibility with your on-premises SQL Server is enough reason on its own to migrate to it. The migration process of backing up and restoring via Azure Storage will bring smiles to all data professionals across the globe.
From a business perspective, after completing all the networking pre-requisites you will have complete secure isolation from other tenants in Azure whilst having a flexible capacity model where you can scale where you see fit whilst benefiting from all that a PaaS offering gives you.
By Arun Sirpal (Microsoft MVP), Editorial Contributor
