Azure SQL Managed Instance – An Introduction
IMPORTANT: According to this post from Microsoft, Managed Instance creation is currently supported for Enterprise Agreement, Pay-As-You-Go, and Cloud Service Provider subscriptions. I will update this post when additional subscription types are added.
Historically there have been two offerings available for running SQL Server in Azure. The first is Azure SQL Database, released in 2010, which is Platform as a Service (PaaS). The second is SQL Server on an Azure VM, which is Infrastructure as a Service (IaaS), also released in 2010. The benefits and drawbacks of each offering is listed below.
Benefits | Drawbacks | Use Cases | |
---|---|---|---|
Azure SQL Database | Infrastructure is fully managed | Limited to a single database | New development in Azure |
Cross-database communication is difficult | Multi-tenant applications | ||
No SQL Server Agent | |||
Cannot be powered down | |||
SQL Server on an Azure VM | Full feature set of SQL Server | Client is responsible for managing infrastructure | Migration of existing applications |
Can be powered down to reduce cost | Creation of test environments | ||
Multiple Databases | |||
Cross-database communication | |||
SQL Server Agent |
The drawbacks of Azure SQL Database make it difficult to migrate existing applications, because of the number of application changes required. Azure SQL Database is designed to be used for new development in Azure and for multi-tenant environments, where each tenant requires their own copy of a database.
The benefits of SQL Server on an Azure VM make it much easier to migrate an existing application to Azure. However, the VMs underlying the application still have to be managed by the client. This fails to take advantage of the management of resources in Azure, and uses Azure as a VM host.
A third option, Azure SQL Managed Instance, was released at the beginning of October 2018. Managed Instance combines the best of the previous options. With Managed Instance, the infrastructure is fully managed and the majority of the SQL Server feature set is available. The full list of differences between a traditional install of SQL Server and Managed Instance can be found here. A number of the most dramatic differences are listed below.
- Filestream is not supported
- Backups must be run to Azure Blob Storage
- No control over file placement
In the following series of blog posts, I will detail the steps required for the following tasks:
- Provisioning the prerequisite resources for Azure SQL Managed Instance
- Provisioning an Azure SQL Managed Instance
- Methods for migrating existing SQL Server instances to Managed Instance
- Maintaining databases running on an Azure SQL Managed Instance
Additionally, I will delve into the internals of an Azure SQL Managed Instance and describe where they differ from a regular SQL Server instance.
I am really excited about the options Azure SQL Managed Instance provides for migrating existing SQL applications to Azure and am looking forward to blogging about it.
SQL Server Availability Groups and Automatic Seeding
Microsoft released Availability Groups (AG) as a feature in SQL Server 2012. Prior to SQL Server 2016, there were two methods of adding a database to a new AG replica.
- You could provide the Add Database to Availability Group wizard a file share accessible by the primary and secondary replicas. SQL Server would run FULL and LOG backups of each database to the share and use them to restore the database(s) to each replica.
- You could manually run a FULL and LOG backup of each database, copy the backup files to each replica, and restore the databases WITH NORECOVERY.
With SQL Server 2016. Microsoft has provided a third option, Automatic Seeding. With Automatic Seeding, you specify the databases and the replicas and SQL Server will begin transferring data to each replica. The duration of the seeding process depends on the size of the database and the network bandwidth available between primary and secondary replica.
There are a few considerations to keep in mind when considering Automatic Seeding. Virtual log files in the primary database cannot be freed while the automatic seeding process is running. For a large, active database this can lead to excessive log growth. Automatic seeding is a single-threaded process and is limited to 5 databases. Additionally, Automatic Seeding will not work if Change Data Capture is enabled. Finally, compression is disabled for Automatic Seeding by default, but can be enabled by turning on trace flag 9567. This can improve performance of the seeding process but will increase CPU overhead.
The status of the Automatic Seeding process can be monitored using the following query: (download)
USE master; SELECT local_database_name, internal_state_desc, remote_machine_name, database_size_bytes / 1045876 AS [Database Size (mb)], transferred_size_bytes / 1045876 AS [Transferred Size (mb)], transfer_rate_bytes_per_second / 1045876 AS [Transfer Rate/Sec (mb)], (database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second AS [Time Remaining (sec)], CASE WHEN ((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) < 360000 THEN '0' ELSE '' END + RTRIM(((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) / 3600) + ':' + RIGHT('0' + RTRIM(((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) % 3600 / 60), 2) + ':' + RIGHT('0' + RTRIM(((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) % 60), 2) AS [Time Remaining] FROM sys.dm_hadr_physical_seeding_stats WHERE internal_state_desc = 'ReadingAndSendingData' ORDER BY remote_machine_name;
The query returns the following result set:
Note that one row will be returned per replica being seeded.
Automatic Seeding makes initializing AG replicas simple. I hope you can put the status query to good use.
RIP @sqlsoldier
Today I learned that Robert Davis, aka @sqlsoldier, passed away. Among other things, Robert literally wrote the book on SQL Server mirroring. I met him briefly at SQL Saturday Oregon, but interacted with him frequently via the #sqlhelp hashtag. He was often one of the first people to reply to my many questions about the transaction log. One of my proudest moments in IT came when I answered a question about reading the transaction log. Robert asked what I meant, and when I explained myself, he wrote, "In that case, I'd do it Frank's way." I don't think my feet touched the ground for the rest of the day.
Robert was a prime example of the strength of the SQL Server community. That someone with his depth and breadth of knowledge would take time to help others leaves me awed and humbled. While his death leaves me shocked and saddened, I will try to keep his memory alive by learning and sharing knowledge with the community.