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, 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 (consolidation), or replicate from a single SQL Server to multiple Managed Instances to any of Azure’s 60+ regions worldwide (deconsolidation). The latter empowers you to quickly bring your SQL Server workloads closer to your customers in any Azure region worldwide.
In a case of a disaster impacting your SQL Server, you can rely on Managed Instance in Azure as your safe site for business continuity – either for R/O access to your data until the primary node is back online, or for full R/W access in Azure in case you decide to perform a full failover to the cloud.
The feature has been released in a limited public preview, and it is available for SQL Server 2019 with CU 13 and above, and SQL Server 2022 in private preview at this time. Our long-term plan is to enable link feature support for SQL Server 2016 and above.
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 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 Managed Instance Link, you are not required to have an existing Availability Group on the primary SQL Server. Single node (single server) systems are supported. Built-in SSMS wizard will help you create a local single node AG with a database that can be replicated to Managed Instance. In case you already have existing AGs with multiple nodes, the existing AG configuration can be used as well, with adjustments described further in this document.
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.
The links are database scoped, meaning that one link is established per a single user database. This still 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 database links from SQL Server 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 complete the catchup of data movement prior to the cutover. This was designed so to prevent potential data loss on cut-off to Azure.
Next steps
Hands on with Managed Instance link
To use the link hands-on with the limited public preview release, you will need to have the following resources available:
- SQL Server 2019 Enterprise Edition with CU13 (or above), or SQL Server 2022 (in private preview)
- SQL Managed Instance (any service tier, any size)
- Networking connectivity between SQL Server and Managed Instance
Use the below link to sign-up for the limited public preview of link feature for Managed Instance.
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 November, 2021.
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.
Posted at https://sl.advdat.com/3CSLXUp