[Edit: Nov 2024] NEW! GA of DR for MI link between SQL MI and SQL Server 2022
[Edit: Nov 2024] NEW! See in action DR between SQL Server 2022 and SQL MI
Link feature for Managed Instance is a new feature reimagining the connection between SQL Server hosted anywhere and the fully managed PaaS service Azure SQL Managed Instance, providing unprecedented hybrid flexibility and database mobility. With an approach that uses near real-time data replication to Azure using Always On technology, you can offload workloads to read-only secondaries on Azure to take advantage of a fully managed database platform, performance, and scale. The link can be operated for as long as you need it – months and years at a time, empowering you to get all the modern benefits of Azure today without migrating to the cloud. On your modernization journey, when and if you are ready to migrate to the cloud, the link de-risks your migration experience allowing you to validate your workloads in Azure prior to migrating with a seamless and instant experience, and at your own pace. This article provides deeper insights into this new feature.
The link feature enables near real-time data replication from SQL Server to Azure SQL Managed Instance using the underlying technology of Distributed Availability Groups. This technology is part of the well-known and proven Always On availability groups technology stack. Think of it as extending SQL Server’s availability group to SQL Managed Instance in Azure in a safe and secure manner. Primary database on the SQL Server can be used for R/W access, whereas replicated database on Managed Instance can be used for R/O access. Changes to the primary database in SQL Server are transferred near real-time to the secondary Managed Instance in Azure.
Each link is database scoped, meaning that one link connects a single database. You can use multiple links to connect multiple databases. Through making the link database scoped, it is now possible to consolidate and deconsolidate your workloads in many-to-many relationships between SQL Server and Managed Instance. This allows you to replicate data from multiple SQL Servers to a single Managed Instance in Azure, or replicate from a single SQL Server to multiple Managed Instances to any of Azure’s 60+ regions worldwide. The latter empowers you to quickly bring your SQL Server workloads closer to your customers in any Azure region worldwide.
Hands-on with the link
[Updated March 2022] The feature has been released in the public preview on March 11, 2022 for SQL Server 2019 enterprise and developer edition with CU 15 and above, and SQL Server 2022 CTP. Scenarios supported at this time are scaling-out R/O workloads, and seamless minimum downtime migration to Azure SQL Managed Instance, as described further in this article. Please note that DR scenario for SQL Server 2022 is not part of this preview.
To start using the link hands-on with SQL Server 2019 enterprise or developer edition, or SQL Server 2022 CTP, follow our tutorials on Azure docs, see Using the link feature for Managed Instance.
[Updated Feb 2022] To learn more and see a demo of the link in action, register for Azure webinar Connect Your SQL Server to Azure SQL Managed Instance. After the registration, you will receive an email with a link to recorded webinar.
Roadmap
Our future product development strategy will involve enabling SQL Server versions 2016-2022 to support scaling out R/O workloads, and seamless and instant migration to Azure. Please note that support for earlier SQL Server versions (2008, 2012 and 2014) is not possible as engines of these products lack required Always On capability for our feature.
In addition to these, our aim is to also build bi-directional disaster recovery (DR) on Azure SQL Managed Instance exclusively for SQL Server 2022. This article will not address details of the DR scenario with SQL Server 2022 at this time, as this will be a subject of a separate article as we near the release of the new SQL Server. To get placed on the wait list for SQL Server 2022 DR scenario, join the SQL Server 2022 EAP program.
If you're interested in using Link feature for Azure SQL Managed Instance with SQL Server versions and editions that are currently not supported (2016, 2017, standard editions, DR for 2022), sign-up for EAP (Early Access Program) here:
Customer testimonials
Some of our early preview customers already had a chance to experience the link hands-on. County of Los Angeles Fire Department is an US government organization relying on a critical SQL Server database for handling all fire department operations serving all of Los Angeles County. An incident can be one of these: COVID-19, fire Incident, Emergency Medical Service involving 911, automobile accident incident, police rescue incident, and others.
County of Los Angeles Fire Department has a business need for near real-time reporting on the data operationally and historically – data trending via data warehouse. To achieve this, SQL Server 2019 on premises is used as the primary database storage, with replication of required data to SQL Managed Instance in Azure to offload all of reporting from the primary database. Express route is used to establish a secure network connectivity between on-premises network and Azure. Earlier, County of Los Angeles Fire Department has used an ETL process and a customized pipeline to replicate data from SQL Server to Managed Instance. This solution had its challenges - contention issues, updating 1900+ tables continuously with frequent schema changes, and a constant need to update the pipeline. In turn, operating the solution introduced significant maintenance costs.
All these problems were solved with the link feature for Managed Instance. With the new solution data replication is almost instantaneous, there no longer exists a need to customize the pipeline, manually update any processes, nor invest time to maintain the link -- it all works automatically out of the box. Identical copy of the database running on SQL Server on premises is being synchronized to Managed Instance in Azure. Even if the link is temporarily down, such is for example when performing a regular maintenance, or rebooting the SQL Server, as soon as the issue has been resolved, the link starts working automatically again continuing where it left off.
“The near real-time data replication in Azure SQL Managed Instance’s link feature provides the redundancy we need and an uninterruptible, consistent flow of data. I no longer have to stop my work to fix a broken data flow or make manual adjustments when a schema changes. I have the peace of mind that the data is safe, reliable, and secured in Azure.” – Alan Choy, Senior Database Administrator, Los Angeles County Fire Department.
The modernization journey has allowed County of Los Angeles Fire Department to considerably reduce maintenance and operational cost with Managed Instance and the link feature. By choosing Managed Instance as a fully managed PaaS service in Azure with at least 99.99% uptime, operational costs were saved by offloading the server maintenance and infrastructure management to Microsoft. Choosing the link feature for Managed Instance has resulted in savings on maintaining data replication from on-premises SQL Server to Azure.
What can you do with Managed Instance link?
In this section we will discuss some of the scenarios how you can take advantage of link feature for managed instance. The main scenarios supported in our limited public preview release are:
(1) Scaling out R/O workloads to Azure
- Scaling out to, or bursting to Azure for R/O workloads
- Offloading operation management of on-premises instance
(2) Seamless and instant database migration to Azure
- Minimum downtime migrations
- Consolidation of workloads in the cloud
Scaling out R/O workloads to Azure
Replicated databases from SQL Server on Managed Instance are available for R/O access. This allows for scaling out to, or bursting to Azure for R/O workloads, while remaining to run on SQL Server as the primary database. Your application can use the primary SQL Server database for R/W access, while offloading the R/O workload to secondary Managed Instance. In the example below, SQL Server database is replicated using link feature to Managed Instance (1). Application hosted anywhere takes advantage of this scenario by offloading R/O workload to Managed Instance (2).
Expanding on this, it is also possible to use the link for geo-replication and take advantage of being able to quickly expand R/O capacity to multiple Managed Instances to any of Azure’s 60+ regions worldwide. In this scenario, it is possible to replicate databases from a single SQL Server to Managed Instances located in different regions using multiple links. In the example below we show two databases from a single SQL Server geo-replicated to Azure’s US and Europe regions bringing workloads closer to your customers in different geographical regions.
Replicated SQL Server data can also be used with a vast number of Azure services, such is for example offloading reporting, analytics, Machine Learning, and others without migrating to Azure.
Seamless and instant database migration to Azure
Our migration strategy is not to leave ANY SQL database behind. While DMS and LRS services offer migration path for any SQL version 2008 and up, the link feature for managed instance provides an optimized experience for Azure-enabled SQL versions starting from SQL Server 2016 and up.
The link could be operated as long as you need it, for months and years at a time. On your modernization journey, when and if you are ready to migrate to Azure, the link enables a seamless and instant migration experience to Managed Instance, at your own pace. The link provides a considerably more performant minimum downtime experience, which might especially benefit critical production workloads with little to no downtime tolerance. As replicated primary SQL Server database is accessible on secondary Managed Instance, it is now possible to test that all your data is up to date, perform schema and integrity checks prior to deciding to cutover to Azure. These options are providing new choices and considerably minimize risk of migrating SQL Server workloads to Azure.
The migration using link feature for Managed Instance is orchestrated at your own pace with the following steps:
- Replicate user databases near real-time from SQL Server to SQL Managed Instance.
- Test data replicated to Azure, test your application workloads and functionalities. Run the link for as long as you need – all new commits made on the SQL Server will automatically be committed to Managed Instance.
- When ready to migrate, initiate failover (cutover) to Azure. Repoint the application connection string from SQL Server to SQL Managed Instance.
As the link is database scoped, consolidating and deconsolidating migration is also possible in many-to-many relationships. For example, it is possible to migrate databases from different SQL Servers to a single Managed Instance, and also to migrate databases from a single SQL Server to multiple Managed Instances.
|
Migration consolidation |
Migration deconsolidation |
This configuration is possible as long as physical limitations of a single Managed Instance are taken into consideration in terms of storage space and number of databases it can contain. As each Managed Instance today can host up to 100 databases, it is possible to establish up to 100 database links from SQL Server to a single Managed Instance.
How does the link feature work?
Managed Instance link works through leveraging Distributed Availability Groups technology to replicate user databases near real-time from SQL Server to SQL Managed Instance. This technology bridges the Availability Groups on SQL Server with highly available Managed Instance for near real-time data replication, as shown in the figure below.
Availability groups are one of the best and proven data replication technologies for SQL Server. We are taking advantage of this, by extending such replication mechanism to SQL Managed Instance bridging the two worlds with Distributed Availability Groups. Think of it as a bridge that connects two availability groups – one on SQL Server, and the other one on Managed Instance in Azure.
To use the link feature for Managed Instance, you are not required to have an existing Availability Group on the primary SQL Server, although we also support existing AGs. Single node (single server) systems are supported, and there is no need to have Windows Server Failover Cluster on the single node. Built-in SSMS wizard, currently in the preview, will help you create a local AG for the single node systems, and enable database replication to Managed Instance. In case that you already have existing Availability Groups with multiple nodes, and\or with Windows Server Failover Cluster, the existing AG configuration can be used as well, with adjustments.
More specifically, the figure below discloses in more technical details how the link feature for Managed Instance works.
To secure the link between the SQL Server and SQL Managed Instance, as one of the first steps of the initial setup, public keys of security certificates are exchanged between the servers participating in the exchange, ensuring the trust and encryption of data transmissions.
Having one link per database provides flexibility to connect user databases from different SQL Servers, even of different versions, to the same Managed Instance, or user databases from the same SQL Server to different Managed Instances. It is possible to establish up to 100 links from SQL Server(s) to a single Managed Instance. This limit is governed by the number of databases that could be hosted on a single Managed Instance today.
Replicated user databases (shown in the figure above as DB1 and DB2) are available for R/O access on the Managed Instance. There could also exist additional independent user databases on the Managed Instance used for R/W access (shown in the figure above as DB3).
Data replication by default is asynchronous taking into consideration that SQL Server and Managed Instance might be geographically dispersed, which might result in a significant network latency. Although the synchronous data replication could be enabled on demand, it is not recommended for the default operation of the link as significant network latency could slow down the SQL Server primary.
In the case of migration scenarios to Azure, at the time of initiating the failover, the replication is intentionally switched from asynchronous to synchronous. This is to ensure completion of the data catchup prior to the cutover, and to prevent potential data loss on the cutover to Azure.
Next steps
Hands on with Managed Instance link
To start using the link hands-on with SQL Server 2019 enterprise or developer edition, or SQL Server 2022 CTP, follow our tutorials on Azure docs, see Using the link feature for Managed Instance.
If you're interested in using Link feature for Azure SQL Managed Instance with SQL Server versions and editions that are currently not supported, sign-up for EAP (Early Access Program) here:
Closing remarks
Please note that products and options presented in this article are subject to change. This article reflects the state of the link feature options available for Managed Instance in March, 2022.
If you find this article useful, please like it on this page and share through social media. To share this article, you can use the Share button below, or this short link: https://aka.ms/mi-link-techblog.
Please feel free to post your questions and comments in the section below.
Microsoft
