Azure SQL Managed Instance link feature overview

In one of my last blog-posts I spoke about Striim which is a data replication platform that can be used for migrations but also for offloaded reporting or even real-time analytics.
A new Azure feature, currently on preview, named Link feature for Azure SQL Managed Instance is available. It gives the possibility to connect a SQL Server hosted on-premise or in the cloud to a SQL Managed instance.
This feature keeps the replicate up-to-date with near real-time data replication to the cloud.
It gives the opportunity to have a read-only secondary in the cloud to offload the workload and take advantages of the Azure environment like built-in security, scalability. performance…

This new feature is based on Distributed Availability Groups. There is no prerequisite to already have an Availability Group or multiple nodes, a single node is working but multiple ones with multiples AGs are also working.

The link can be kept ever or remove after a transition period for a migration with near to zero downtime.

Before to configure a Manage Instance Link as I’m working with SQL Server 2019 we need:

• SQL Server 2019 Enterprise or Developer Edition with CU15 or above
• An Azure SQL Manage Instance

And also prepare our SQL Server instance with some prerequisites:

• Check CU15
• Create a database master key in the master database
• Enable Availability group feature
• Enable trace flags to optimize performance (not required):
  • -T1800: This trace flag optimizes performance when the log files for the primary and secondary replicas in an availability group are hosted on disks with different sector sizes, such as 512 bytes and 4K otherwise it’s not required
  • -T9567: This trace flag enables compression of the data stream for availability groups during automatic seeding. The compression increases the load on the processor but can significantly reduce transfer time during seeding.

Don’t forget to restart the SQL engine if you have just enable Always On Availability Groups feature for your SQL service.

Let’s start to replicate the AdventureWorks database from our on-premise instance to an Azure SQL Managed Instance. The source database must be in Full recovery model and have at least one full backup.
Go to the source database, right click on it and select “Azure SQL Managed Instance link” and “Replicate database”:

An introduction slide explains us the goal, some scenarios and the requirements of this new feature:

Requirements are now checked and they are all met here, for the server but also for Availability Group where the database master key is available:

We can now select one or more databases to replicate to the Azure SQL Managed instance via the link feature. We select here the AdventureWorks2019 database which meets the requirements, it’s means Full recovery model and a Full backup executed:

It’s time to sign in to our Azure Subscription:

Once done we need to select the Managed Instance information which will be our target:

On the following screen we have lots of information:

• The name and the port of the endpoint which will be created for the database mirroring
• the certificate which will be created with an expiry date
• the names for the Availability group and for the Distributed Availability group

A last check to the choices made and click Finish to start the process:

All steps succeeded:

I have now my AdventureWorks database replicated on my Managed instance and ready for read-only workload.
If I check my Distributed Availability group dashboard, I can see that my first replica is my Availability group and my second is the Managed Instance. My AdventureWorks database is synchronized and the last hardened time occurs during my first synchronization.

If I update the column JobTitle on my Employee table on my Source instance, the update will be replicated asynchronously to my secondary database.
My replica database before the update:

I run my script on my primary database:

After some seconds my secondary is again synchronized:

And if I look on my Distributed Availability group, I can see the details with my last hardened time:

We cannot see that the database on the Managed Instance is not a “Standard” one, there is no information written after the database name like Synchronized as it is the case with a secondary database in an Availability group. Nevertheless if we try to run an update statement again this database we receive an error message:

This new Azure Managed Instance link feature is really interesting to off-load analytics and reporting to Azure but can also be used in a migration scenario with near to zero downtime.
It’s also a good way to create a first hybrid scenario before moving to the Cloud.