Link feature for Managed Instance (MI link) was launched to the public preview back in March of 2022, as a solution that reimagines the hybrid connection between SQL Server hosted anywhere and Azure SQL Managed Instance. Today we are excited to announce general availability (GA) of the link feature for managed instance for SQL Server 2022. We are also excited to announce a limited public preview of the managed disaster recovery (DR) between SQL Server 2022 and Azure SQL Managed Instance.
About MI link
With an approach that uses near real-time data synchronization to Azure using Always On technology, you can scale workloads, or analytics to read-only secondaries in 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.
Databases synchronized through the link feature from SQL Server 2022 to Azure SQL Managed Instance can be used with several scenarios, such are:
- Integrate with Microsoft cloud services without migrating database to Azure.
- Migrate to Azure
- Disaster recovery with SQL Server 2022 (in preview with sign-up)
The link feature offers the following functionality:
- One-way synchronization (SQL Server 2016-2022): Use the link feature to synchronize databases one way from supported releases of SQL Server to Azure SQL Managed Instance. While manual failover to SQL MI is available in the event of a disaster, doing so breaks the link, and online fail back is not supported. For SQL Server 2022 only, you can take backup on Managed Instance and restore it manually back to SQL Server 2022.
- Disaster recovery (SQL Server 2022 only): Use the link feature to continuously synchronize database from SQL Server 2022 to Azure SQL Managed Instance, manually failover to SQL MI in the event of a disaster and fail back to SQL Server once the disaster is mitigated. This functionality is currently in limited public preview.
What’s new – improvements made
Since our announcement of the public preview of the MI link, we have made a number of improvements to the link, as follows:
- SSSM 19 - Improvements to usability and bug fixes to connectivity and failover wizards
- Implementation of Azure PowerShell tooling to establish and failover the link
- Replicated R/O databases from SQL Server to SQL MI are now automatically backed up to Azure Backups, and can be restored to any SQL MI using the built-in point-in-time restore capability.
- Support for link authentication using certificates issued by trusted certified authorities (CA)
- Automatic update of certificates rotated on SQL MI back to SQL Server
- Improvements to validating SQL Server database compatibility prior to creating the link
- Improvements in return responses for successful/unsuccessful connection
- Improvements towards lossless failover solution in introducing Required Synchronized Secondaries to Commit (RSStC) for Distributed Availability Groups on SQL Server 2022
- Automated timeout of links that can’t be established due to technical reasons (e.g. ports and firewalls misconfiguration) within the first 15 minutes, releasing the link name for the next attempt.
Disaster Recovery with SQL Server 2022
There are two types of disaster recovery for SQL Server 2022 possible:
- Offline DR: This option involves manually taking COPY_ONLY backup on Managed Instance using backup to URL and specifying Azure Blob Storage container. Using backup from URL on SQL Server 2022, backups from Azure Blob Storage can be restored back as an offline operation. This option is available in GA today for the entire Managed Instance fleet in production, all public regions worldwide and national clouds.
- Online DR: This option involves the ability to failover online from SQL Server 2022 to Managed Instance reversing the primary and secondary roles of the link, making the link on SQL MI primary and the link SQL Server 2022 secondary in case of a disaster. Once the issue has been resolved, an online failback of the link from SQL MI to SQL Server 2022 can be executed, reversing the link roles again, and making back the link on SQL Server 2022 primary, and the link on SQL MI secondary. This option is available in a limited public preview through sign-up and will require onboarding by the product group (see hands-on with the link section below for more details).
Online DR Technology
The tech behind the two-way failover is based on Distributed Availability Groups allowing near real-time synchronization of data between SQL Server and Managed Instance. The solution supports single-node systems without existing availability groups, or multiple node systems with existing availability groups. The link communication mode is asynchronous. Secure connectivity, such as VPN or Express Route is used between an on-premises network and Azure. If SQL Server is hosted on an Azure VM, the internal Azure backbone can be used between the VM and managed instance for connectivity.
The base scenario supported is forced failover between SQL Server and Managed Instance using the built-in capability in the engine to reverse the roles of Distributed Availability Groups (one link). The failover on the SQL Server side is initiated by reversing the role using the T-SQL and then initiating API call to Managed Instance to reverse the link role on the Azure side. The same process is applied for forced failover in both directions. As with the existing Always On technology solutions, forced failover could allow a data loss on the failover.
Planned failover whereas data loss on failover is controlled, is possible through either careful execution of manual steps, or through help of SSMS wizard. The steps involve changing the link communication mode from asynchronous to synchronous for the duration of the failover. Further, we have implemented Required Synchronized Secondaries to Commit (RSStC) for Distributed Availability Groups on SQL Server 2022 in the engine which when enabled ensures that transaction on the SQL Server in the role of primary, is not considered as committed unless secondary, in this case SQL MI, confirms it has committed the transaction. This ensures that transactions could not be considered as committed on SQL Server 2022 unless they are also committed on SQL MI. Upon the failover execution, the link communication mode is switched back to asynchronous for the performance purposes. Reversing the roles back again takes similar steps, executing first the link role change on SQL MI from primary to secondary using an API call, and then reversing the role through T-SQL on SQL Server 2022.
It needs to be noted that primary\secondary relationship is per each link. This means that there exists a capability to choose which databases to failover from SQL Server 2022 to MI, which is especially useful if MI is a secondary DR site for multiple SQL Servers. In this respect, links could be established from multiple SQL Servers to a single Managed Instance, and DR managed for each link individually.
Hands-on with the link
One-way synchronization: To start using the link for one-way synchronization from SQL Server 2016-2022, go to MI link page on Azure docs, and follow the instructions provided. You will be able to create one-way synchronization using SSMS 19.0, or manually through scripts. One-way synchronization from SQL Server 2022 is now in GA.
Offline DR: To restore database from SQL MI back to SQL Server, make a COPY ONLY backup of SQL MI database and store it to an Azure Blob Storage account. On SQL Server 2022, restore backup files from the Azure Blob Storage account. This functionality is also generally available.
Online DR: The online two-way failover between SQL Server and Managed Instance is available in a limited public preview. This means that customers need to sign up first requesting access to this feature. The product group will evaluate each request and will need to enable the functionality on customer’s subscription. Private build of SSMS will be available to enrolled customers to evaluate this feature and provide feedback to the product group. The purpose of this phase is to closely with our customers perfecting the feature before the open public preview. As a limited number of seats is available, acceptance in the limited public preview cannot be guaranteed for all customers showing interest. The open public preview and general availability of this feature will be announced at a future date.
Figure 1: Sneak peek at SSMS private build experience for participants of the limited public preview.
Providing feedback to the product group
We hope that our improvements in this space have further improved our services to you. For any comments or feedback, use the comments section below, or post your feedback directly to the product group.
Closing remarks
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-gadr-blogpost.