Link Feature for Azure SQL Managed Instance

The Link feature for Azure SQL Managed Instance enables near real-time data replication from SQL Server to Azure SQL MI, providing a seamless path for cloud migration and hybrid scenarios.

Understanding the Link Feature

The Link creates a distributed availability group between your on-premises SQL Server and Azure SQL MI, enabling:

• Real-time data synchronization
• Read-scale workloads on Azure
• Disaster recovery to the cloud
• Staged migration approach

Prerequisites

-- Check SQL Server version (2019 or later required)
SELECT @@VERSION;

-- Enable availability groups
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'hadr enabled', 1;
RECONFIGURE;

-- Verify certificate for endpoint authentication
SELECT name, certificate_id, pvt_key_encryption_type_desc
FROM sys.certificates
WHERE name LIKE 'AG%';

Setting Up the Link

Step 1: Prepare SQL Server

-- Create database mirroring endpoint
CREATE ENDPOINT Hadr_endpoint
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = CERTIFICATE AG_Cert,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );

-- Create the database for replication
CREATE DATABASE SalesDB;
ALTER DATABASE SalesDB SET RECOVERY FULL;
BACKUP DATABASE SalesDB TO DISK = 'C:\Backups\SalesDB.bak';
BACKUP LOG SalesDB TO DISK = 'C:\Backups\SalesDB.trn';

Step 2: Configure Azure SQL MI

# Get SQL MI endpoint info
az sql mi show \
    --name mysqlmi \
    --resource-group myResourceGroup \
    --query "{endpoint:fullyQualifiedDomainName,state:state}"

Step 3: Create the Distributed AG

-- On SQL Server
CREATE AVAILABILITY GROUP [DAG_Link]
    WITH (DISTRIBUTED)
    AVAILABILITY GROUP ON
        'OnPremAG' WITH (
            LISTENER_URL = 'TCP://sqlserver.domain.com:5022',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC
        ),
        'MI_AG' WITH (
            LISTENER_URL = 'TCP://mysqlmi.12345.database.windows.net:5022',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC
        );

Using PowerShell for Link Setup

# Install the SQL MI Link module
Install-Module -Name Az.Sql -Force

# Connect to Azure
Connect-AzAccount

# Create the link
$linkParams = @{
    ResourceGroupName = "myResourceGroup"
    InstanceName = "mysqlmi"
    Name = "SalesDBLink"
    PrimaryAvailabilityGroupName = "OnPremAG"
    SecondaryAvailabilityGroupName = "MI_AG"
    TargetDatabase = "SalesDB"
    SourceEndpoint = "TCP://sqlserver.domain.com:5022"
}

New-AzSqlInstanceLink @linkParams

Monitoring Link Status

-- On SQL Server: Check distributed AG status
SELECT
    ag.name AS distributed_ag_name,
    ar.replica_server_name,
    ars.role_desc,
    ars.synchronization_health_desc,
    ars.connected_state_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id
WHERE ag.is_distributed = 1;

-- Check database synchronization
SELECT
    db_name(database_id) AS database_name,
    synchronization_state_desc,
    synchronization_health_desc,
    log_send_queue_size,
    redo_queue_size
FROM sys.dm_hadr_database_replica_states;

Python Application for Read-Scale

import pyodbc

class HybridSQLConnection:
    def __init__(self):
        self.write_conn_str = (
            "Driver={ODBC Driver 18 for SQL Server};"
            "Server=sqlserver.domain.com;"
            "Database=SalesDB;"
            "Trusted_Connection=yes;"
        )

        self.read_conn_str = (
            "Driver={ODBC Driver 18 for SQL Server};"
            "Server=mysqlmi.database.windows.net;"
            "Database=SalesDB;"
            "Authentication=ActiveDirectoryInteractive;"
        )

    def execute_write(self, query, params=None):
        """Execute write operations on-premises"""
        with pyodbc.connect(self.write_conn_str) as conn:
            cursor = conn.cursor()
            cursor.execute(query, params or [])
            conn.commit()
            return cursor.rowcount

    def execute_read(self, query, params=None):
        """Execute read operations on Azure SQL MI"""
        with pyodbc.connect(self.read_conn_str) as conn:
            cursor = conn.cursor()
            cursor.execute(query, params or [])
            return cursor.fetchall()

# Usage
db = HybridSQLConnection()

# Writes go to on-premises
db.execute_write("INSERT INTO Orders (CustomerID, Total) VALUES (?, ?)", [1, 99.99])

# Reads go to Azure SQL MI
orders = db.execute_read("SELECT * FROM Orders WHERE CustomerID = ?", [1])

Failover to Azure SQL MI

-- Break the link and promote Azure SQL MI
ALTER AVAILABILITY GROUP [DAG_Link]
    FAILOVER;

-- After failover, MI becomes read-write
-- Update connection strings in your applications

The Link feature provides a low-risk path to cloud migration while enabling hybrid scenarios during the transition.