Automating the setup of Azure SQL Managed Instance Link

Posted on by — No Comments ↓

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

Introduction

 

In this post we will be discussing how to automate the setup of the Managed Instance Link feature which enables near real-time data replication from SQL Server to Azure SQL Managed Instance. 

This feature is an extremely powerful migration tool for moving your databases from SQL Server running on-premises, in Azure VMs or on other cloud providers to Azure SQL Managed Instance and can also be used to provide a readable copy of your database(s) on Azure SQL Managed Instance for offloading specific real only workloads.

The scripts we have created and referenced here in this post are a joint effort between the SQL CSE Engineering team and the Azure SQL Managed Instance team at Microsoft.

 

MI Link Scenarios and Requirements

 

Possible Scenarios

MI Link can be used in the following scenarios:

  • Migration to Azure SQL Managed Instance where there is little tolerance for downtime during the migration cutover.
    • MI Link does not initiate the seeding process across MI replicas in Business Critical tier on cutover and therefore brings the databases online much faster upon the final cutover action.
  • Azure based disaster recovery replica / database copy.
  • Offsite Azure based replica to offload read-only workloads.

DesFitz_0-1671006815018.png

 

DesFitz_1-1671006830777.png

 

Requirements

  • Administrative access to both the source SQL Server instance as well as the target Azure SQL Managed Instance using TSQL endpoint.
  • Secure connectivity, such as VPN or Express Route, between an on-premises network and Azure is required. If SQL Server is hosted on an Azure VM, the internal Azure network can be used between the VM and managed instance – such as, for example, global VNet peering.
  • Trust between the two systems is established using certificate-based authentication, in which SQL Server and Azure SQL Managed Instance exchange their public keys.
  • Site to Site Connectivity between the source SQL Server instance network and Azure SQL Managed Instance subnet and visa-versa is mandatory. MI Link can only be established using private connectivity.
    • Inbound traffic on port 5022, 1433 and port range 11000-11999 from the network hosting SQL Server must be enabled on the network security group (NSG) on the Azure SQL Managed Instance subnet.
    • Inbound traffic on the database mirroring endpoint port (default TCP: 5022) must be enabled on the firewall so that the Azure SQL Managed Instance can establish a mirroring session. Additionally, the Azure SQL Managed Instance subnet range must be permitted outbound on the on-premises firewall as the IP address as well as the port (range 11000-11999) of the Managed Instance can change.

DesFitz_0-1674050206760.png

 

  • Access to the target Azure SQL Managed Instance through PowerShell.
  • PowerShell Az.Sql module version 3.9.0 or newer.(install)
  • PowerShell Az.Accounts module version 2.8.0 or newer. (install)
  • SqlServer module version 21.1.18256 or newer. (install)
  • Trace Flags (recommended):
    • -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. If both primary and secondary replicas have a disk sector size of 4K, this trace flag isn't required. To learn more, review KB3009974.
    • -T9567: This trace flag enables compression of the data stream for availability groups during automatic seeding. The compression increases the load on CPU but can significantly reduce transfer times during seeding.

Limitations

For the full list of MI Link limitations please refer to the public document:  https://learn.microsoft.com/azure/azure-sql/managed-instance/managed-instance-link-feature-overview?source=recommendations&view=azuresql#limitations

 

  • Only user databases can be included in MI Link. Inclusion of system databases is not supported.
  • The solution does not replicate server level objects, agent jobs or user logins from SQL Server to Azure SQL Managed Instance.
  • Only one database can be placed into each Availability Group and used for a single MI Link.
  • File tables and file streams aren't supported with MI Link as Azure SQL Managed Instance does not support these features.
  • Replicating Databases which are using In-Memory OLTP objects are not supported on Azure SQL Managed Instance General Purpose service tier. Such In-Memory objects are only supported on Azure SQL Managed Instance Business Critical service tier.
  • If Change data capture (CDC), log shipping, or service broker are being used with databases and these databases are migrated to Azure SQL Managed Instance, during failover to Azure SQL Managed Instance, clients will need to connect using the instance name of the current global primary replica. These settings should be manually reconfigured.
  • If transactional replication is used with a database on SQL Server then in the case of a migration scenario, during failover to Azure, transactional replication on SQL Managed Instance will fail and should be manually reconfigured.
  • In case distributed transactions are used with databases to be linked to Azure SQL Managed Instance, and in case of a migration scenario, on the cutover to the Azure SQL Managed Instance, the MS-DTC capabilities won't be transferred as currently Azure SQL Managed Instance does not support MS-DTC.
  • Managed Instance link can replicate database of any size so long as the total database size does not exceed the chosen storage size of target Azure SQL Managed Instance.
  • Client Operating Systems such as Windows 10 and 11 cannot be used to host your SQL Server, as it will not be permitted to enable the Always On feature required for using MI Link. The source SQL Server must be hosted on Windows Server 2012 or higher.
  • SQL Server 2008, 2012 and 2014 cannot be used for the MI Link feature, as these SQL Server versions do not  support Distributed Availability Groups, which are required for MI Link. Only the following SQL Server versions are currently supported:
    • SQL Server 2016 SP3 (KB 5003279) and SQL Server 2016 Azure Connect pack (KB 5014242) or newer
    • SQL Server 2019 CU15 or newer
    • SQL Server 2022
  • Replication of databases from SQL Server to Azure SQL Managed Instance is currently one-way. Databases hosted on Azure SQL Managed Instance can't be linked back to SQL Server.
  • MI Link authentication between the SQL Server instance and Azure SQL Managed Instance is certificate-based and available only through the exchange of certificates. Windows authentication between SQL Server and Azure SQL Managed Instance is not supported.
  • MI Link R/O databases hosted on the Azure SQL Managed Instance are not part of the auto-backup process within the Azure SQL Managed Instance.

 

MILinkSetup Module

 

The following sections refer to PowerShell Modules/Scripts we have created.
In order to follow the next sections you will need to download the PS Script/modules from the Microsoft Download Center here.

 

The MI Link module helps in simplifying and automating the end-to-end MI Link creation. It validates and prepares the local instance(s) to be ready to participate in a distributed availability group (DAG) between the local instance and Azure SQL Managed Instance. If the local instance is a member of an existing availability group topology each of the replicas (primary and secondaries) are validated.

The module also validates if the desired database(s) are valid and meet MI Link requirements.

 

Preparing and validating the SQL Server instance (ValidateAndPrepareMiLinkSetup cmdlet)

 

This section describes all steps and actions that are invoked in the ValidateAndPrepareMiLinkSetup cmdlet. Additionally, all cmdlet parameters are described, and example execution commands are provided for reference.

The main purpose is to execute the cmdlet in order to ensure the environment is properly prepared and all the prerequisites at the instance and/or host level are met for MI Link. 

There are 11 logical blocks that are executed by the cmdlet. 
Each block starts with a message Step X out of 11 [started] and completes with Step X out of 11 [completed]

 

The following outlines the coloring used to show statuses of execution:

DesFitz_3-1671007247004.png

 

Execution Steps:

 

  • Step 1: Assesses if the source SQL Server instance is a member of an existing Always On Availability Group (AOAG) topology or if it is a standalone SQL Server instance. FCI instances are considered as standalone instance unless they participate in AOAG topology.

 

In AOAG topology all replicas are assessed and prepared for the MI Link creation. This ensures that the MI Link will continue to work if a local database that is a member of a local AOAG fails over between local replicas.

 

 

 

Additionally, the step verifies if all replicas have the same instance collation and if the collation matches the Azure SQL Managed Instance collation. If a collation conflict between replicas is identified then a warning is displayed and the user is prompted if they wish to continue. The collation difference is not a hard blocker but creating a local AOAG or MI Link across replicas with different collations is discouraged.

 

  • Step 3: The Database master key (DMK) in the master databases is checked. If the key does not exist, and a new DMKis created.

 

  • Step 4: The HADR / AlwaysOn feature must be enabled. , an exception is thrown.

Once the HADR / AG feature has been enabled a restart of the cmdlet is required. The cmdlet does not enable the feature nor does it restart the instance post enablement.

 

  • Step 5: Import public root certificate keys of Microsoft and DigiCert certificate authorities (CA) to SQL Server. It’s required for the SQL Server to trust certificates issued by Azure for database.windows.net domains.

 

  • Step 6 & 7: Verifies if the Database Mirroring Endpoint (DBM) is available and whether it uses certificate authentication. If the endpoint (DBM) does not exist

In AOAG topology if a DBM endpoint does not exist or if the DBM endpoint exists but uses Windows authentication ONLY a new DBM endpoint certificate will be created. The certificate with the same keys and thumbprint will be recreated on every local AOAG replica. Additionally, a new SQL login will be created that will own the certificate. The user (caller) is asked for a login and certificate names. The login password is randomly generated..

 

In the AOAG topology a common DBM port must be used. This ensures that the MI Link continues working even after a local AOAG failover between replicas.

 

  • Step 8: The user is prompted to log in in Azure. Once the token has been obtained all the subscriptions will be iterated to find the proper subscription that hosts the target Azure SQL Managed Instance. The Azure SQL Managed Instance is validated to ensure that the max database count limit hasn’t been reached (100 Databases per Azure SQL Managed Instance) and that the instance does not participate in an Auto Failover Group (AOFG) setup.

 

  • Step 9: The Managed Instance DBM certificate (public key) is imported into the SQL Server instance.

 

  • Step 10: SQL Server DBM certificate (public key) is imported into the Azure SQL Managed Instance.

 

In the AOAG topology if the existing DBM endpoints had used the certificate authentication before the cmdlet was executed all the certificates will be imported into Azure SQL Managed Instance.

 

  • Step 11: . between Azure SQL Managed Instance and local instance(s) . The following ports are being verified for connectivity:
    • Local DBM endpoint port (default: 5022)
    • Azure SQL Managed Instance DBM endpoint port (5022) and T-SQL endpoints (1433 and/or 11000 - 11999) 
      • IMPORTANT: The script verifies the current in use port from the range 11000 – 11999 – this may change if the Managed instance restarts or fails over.
        Therefore it is important to ensure the entire range is open on the Managed Instance NSG.
        Reverse connectivity from Azure SQL Managed Instance to SQL Server instance(s) on DBM endpoint port (default: 5022)

 

Please note: The forward and reverse connectivity requires the above-mentioned ports to be opened on the firewall as well as on the Network Security Group (NSG) on the Azure SQL Managed Instance subnet.

 

The reverse connectivity test attempts to establish the connection on the DBM endpoint port (default 5022) from Azure SQL Managed Instance to SQL Server instance(s) iterating all IP addresses bound to HOST A records of the replica / instance host(s).

Additionally, all Virtual IPs (VIPs) are validated to find if any listeners exist on the instance(s).

 

 

All the connectivity checks may report the following connectivity statuses:

status 1: tcp connection successful.
status 0: tcp timeout. Connection is not possible. Check firewall, NSG rules and if the network connectivity exists between the on-premise network and MI subnet
status: -1: MI SQL Agent job timeout. The reverse connectivity job might have failed to start. Please investigate the MI SQL Agent logs for more information.
status: -2: MI SQL Agent job creation timeout or exception. Please investigate the MI SQL Agent logs for more information.

 

Parameters and Execution:

The following parameters are mandatory to execute the cmdlet:

  • SqlInstance (mandatory): Sql Server local instance name.
  • ManagedInstanceName (mandatory): short name of the Azure SQL Managed Instance name. 
    • Do not use FQDN.

 

To execute the cmdlet follow the steps:

  • Import the module

 

Import-Module 'C:\{pathtoscript}\MiLinkSetup.ps1'

 

 

You may need to change the Execution policy before importing the module.

Set-ExecutionPolicy Unrestricted -Scope Process

 

  • Execute the cmdlet. Below is an example execution – sqlinst1 is the local replica / instance and azuresqlmi01 is the target managed instance name.

 

ValidateAndPrepareMiLinkSetup -SqlInstance sqlinst1 -ManagedInstanceName azuresqlmi01

 

Creating the MI Link between the local instance and Azure SQL Managed Instance (CreateMiLink cmdlet)

 

This section describes all steps and actions that are invoked in the CreateMiLink cmdlet. Additionally, all cmdlet parameters are described, and example execution commands are provided for reference.

The main purpose of this cmdlet is to validate the desired databases and assess if they can be used to participate in a link between the local instance and Azure SQL Managed Instance.

Once the database(s) are validated and meet all prerequisites the distributed availability group (DAG) and MI Link are created and the database(s) are seeded to the Azure SQL Managed Instance.

There are 8 logical blocks that are executed by this cmdlet. Each block starts with a message Step X out of 8 [started] and completes with Step X out of 8 [completed]

 

The following outlines the coloring used to show statuses of execution:

DesFitz_3-1671007247004.png

 

Execution Steps:

  • Step 1: Assesses if the source SQL Server instance is a member of an existing Always On Availability Group (AOAG) topology or is a standalone instance. FCI instances are considered as standalone unless they participate in AG topology.

 

In AOAG topology all replicas are assessed and prepared for the MI Link creation. This ensures that the MI Link will continue to work if a local database that is a member of a local AOAG fails over between local replicas.

 

 

 

Additionally, the step verifies if all replicas have the same instance collation and if the collation matches the Azure SQL Managed Instance collation. If a collation conflict between replicas is identified then a warning is displayed and the user is prompted if they wish to continue. The collation difference is not a hard blocker but creating a local AOAG or MI Link across replicas with different collations is discouraged.

 

  • Step 3: The user is prompted to log in in Azure. The user is prompted to log in in Azure. Once the token has been obtained all the subscriptions will be iterated to find the proper subscription that hosts the target Azure SQL Managed Instance. The Azure SQL Managed Instance is validated to ensure the max database count limit (100 Databases per Azure SQL Managed Instance) has not been reached.

 

  • Step 4: The Pre-Link validation examines each database (in the provided database list) and validates that each database:
    • exists and is in the ONLINE state.
    • does not already exist on the Azure SQL Managed Instance (database name).
    • does not have any FILESTREAM container.
    • does not have any In-Memory container (unless the Azure SQL Managed Instance service tier is Business Critical).
    • will not exceed the maximum available storage (the database size must fit under the storage free space on the Azure SQL Managed Instance. The cmdlet deliberately assumes there must be at least 10GB free space left after restoring the assessed database.).
    • will not exceed the max database count on the Azure SQL Managed Instance.
      • The max database count on Azure SQL Managed Instance is 100.
    • will not exceed the max database file count on the Azure SQL Managed Instance.
      • The max database file count (all databases) on Azure SQL Managed Instance General Purpose is 280
    • does not have any single database file greater than 8TB in size (General Purpose only).
      • The max allowed database file size on Azure SQL Managed Instance General Purpose is 8TB

 

For databases that are members of an existing local Always On Availability Group (AOAG) the following additional tests are executed to validate if:

  • the local Availability Group (AG) has more than 1 database.
  • Currently the MI Link is limited to one database per AG per Link.
  • the local Availability Group (AG) is in a health state.
  • the cmdlet is not being executed on the secondary replica.
  • The cmdlet should be executed against the primary replica.
  • the database is not a member of any ML Link or Distributed Availability Group (DAG).
  • the AOAG has a listener created (if the replica count is greater than 1).
  • The MI Link must be established between the listener’s name (VNN) or listener’s virtual IP (VIP).
  • the listener IP count is not greater than 1.
  • If the listener IP count is greater than 1 the cmdlet must be started with UseDnsName flag to establish the link with the listener FQDN. It may require Azure custom DNS for the Azure SQL Managed Instance subnet.
  • the listener IP states are ONLINE

If the database is a member of an existing local AOAG, the availability group will be altered to change the seeding mode to AUTOMATIC:
Automatic seeding for secondary replicas - SQL Server Always On | Microsoft Learn

 

  • Step 5: Each database is validated to ensure that the recovery model is FULL. Simple and Bulk Logged recovery models are not supported.

If the database is in non-FULL recovery model or the database has not transitioned yet into the FULL recovery model the user will be asked if the model can be changed. After changing the recovery model a FULL database backup will be executed – if the cmdlet was started without the BackupPathLocation parameter the user will be prompted to provide a valid location (local disk or SMB share) where the FULL database backup can be stored.  If the recovery model is not changed the database will be omitted.

 

  • Step 6: In this step the encryption state of each database (in the provided database list) is examined. If the database is TDE protected the user will be asked if the encryption certificate can be backed up and uploaded to the Azure SQL Managed Instance. If not, the database is omitted. If it is permitted, the following steps are taken:
    • the TDE certificate is backed up to the location specified in the BackupPathLocation parameter. If a parameter is not provided the user is prompted to provide a valid path (local or SMB) where the backup can be placed. Both public (.cer) and private (.pvk) keys are included in the backup set.
    • the certificate backup is converted into pfx file format using the Pvk2pfx.exe utility. The path to the Pvk2pfx.exe must be specified in the Pvk2pfxPathLocation parameter
    • the pfx file is converted and uploaded into Azure SQL Managed Instance.

If the TDE encryption scan is in progress or the encryption state is being changed a warning will be displayed and the database will be omitted.

 

The Pvk2pfx.exe tool can be downloaded form
https://docs.microsoft.com/en-us/windows-hardware/drivers/devtest/pvk2pfx

 

To successfully back up the certificate and convert it into a pfx file the cmdlet may need to be executed with admin privileges. 
  • Step 7: Summary validation is displayed which includes the omitted database count as well as the databases that have been successfully validated. The user is asked whether the process should proceed with the MI Link creation for the successfully validated databases.

 

  • Step 8: MI link creation sets up the MI Link between the local instance and Azure SQL Managed Instance. The following steps are executed here:
    • Connectivity check between Azure SQL Managed Instance and local instance (or listener)
    • local Availability Group (AG) creation
      • If a database is NOT a member of a local Availability Group (AG) a new local AG with the name databaseName_LINK_AG is created.
        • The local AG is created on a single instance and without cluster (WSFC) integration (CLUSTER_TYPE=NONE)
        • On SQL Server 2016 the local single instance AG is created with WSFC integration and a new WSFC group is provisioned
      • If a database is already a member of a local Availability Group (AG) it will be used in the MI Link creation.
    • Distributed Availability Group (DAG) creation.
      • The DAG is created to link the local AG and the Managed Instance. The DAG name used here is databaseName_LINK_DAG
    • MI Link creation and database seeding.
      • Each database is seeded to the Azure SQL Managed Instance which creates a secondary copy of the local database on the Azure SQL Managed Instance.
      • By default one database at a time is seeded unless the ParallelSeededDatabaseCount parameter is specified
      • The max number of parallel seeded databases permitted is 5 (ParallelSeededDatabaseCount =5)
      • The seeding progress is reported at 30 second intervals.

 

Parameters and Execution:

The following parameters are can be used to execute the cmdlet:

  • SqlInstance (mandatory): Sql Server local instance name.
  • ManagedInstanceName (mandatory): short name of the Azure SQL Managed Instance name. Do not use FQDN.
  • DatabaseNameList (mandatory): Comma separated list of all databases that should be validated and used in creating MI Links. To include all databases on the instance please use *
  • BackupPathLocation (optional): the path to the folder where Full database backups (required for simple and bulk logged recovery model databases only) or TDE certificate backups (required for TDE protected databases only) can be stored.
  • Pvk2pfxPathLocation (optional): the full path where the Pvk2Pfx.exe tool is located (required for TDE protected databases only).
  • UseDnsName (optional): forces the process to use the host or AG listener DNS / FQDN names instead of IP/VIP addresses.  It requires configuration of custom Azure DNS for Azure SQL Managed Instance subnet. Default is false.
  • ParallelSeededDatabaseCount (optional): Number of databases that can be seeded in parallel to Azure SQL Managed Instance. Default is 1.

 

To execute the cmdlet please follow the steps:

  • Import the module 

 

Import-Module 'C:\{pathtoscript}\MiLinkSetup.ps1'

 

 

You may need to change the Execution policy before importing the module.

Set-ExecutionPolicy Unrestricted -Scope Process

 

  • Execute the cmdlet. Below are some execution examples where sqlinst01 is the local replica / instance and azuresqlmi01 is the target Azure SQL Managed Instance name.

 

  • The following sample cmdlet validates AdventureWorksDB1 and AdventureWorksDB2 databases and executes the FULL backup to E:\BACKUP folder ONLY if any of the databases is not in FULL recovery model

 

CreateMiLink -SqlInstance sqlinst01 -ManagedInstanceName azuresqlmi01 -DatabaseNameList “AdventureWorksDB1”, “AdventureWorksDB2” -BackupPathLocation “E:\BACKUP”

 

 

  • The following cmdlet validates all databases on the sqlinst01 instance and executes the FULL backup to E:\BACKUP folder ONLY if any of the databases are not in FULL recovery model. For the TDE protected databases pvk2pfx.exe located in E:\TOOLS\Pvk2Pfx will be used to convert the TDE certificate backup into a .pfx file and this will in turn be uploaded to the Azure SQL Managed Instance to allow restoring of the database backup.

 

CreateMiLink -SqlInstance sqlinst01 -ManagedInstanceName azuresqlmi01 -DatabaseNameList "*" -BackupPathLocation "E:\BACKUP" -Pvk2pfxPathLocation "E:\TOOLS\Pvk2Pfx\pvk2pfx.exe"

 

 

  • The following sample cmdlet validates the AdventureWorksDB1 database and uses the instance host FQDN name (instead of IP) to establish the MI Link. If the instance is FCI the FCI virtual name (VCO) FQDN will be used. If AdventureWorksDB1 is part of a local availability group (AG) the AG listener (VNN) will be used.

 

CreateMiLink -SqlInstance sqlinst01 -ManagedInstanceName azuresqlmi01 -DatabaseNameList “AdventureWorksDB1" -UseDnsName $true

 

 

  • The following sample cmdlet validates the AdventureWorksDB1, AdventureWorksDB2 and AdventureWorksDB3 databases. Max 2 databases will be seeded to the Managed Instance at the same time

 

CreateMiLink -SqlInstance sqlinst01 -ManagedInstanceName azuresqlmi01 -DatabaseNameList " AdventureWorksDB1", “AdventureWorksDB2”, “AdventureWorksDB3” -ParallelSeededDatabaseCount 2

 

Summary

Managed Instance Link is a powerful feature allowing near zero downtime migrations to Azure SQL Managed Instance and we hope that this PowerShell solution will allow you to easier automate and codify the end-to-end use of this powerful feature to facilitate both migrations to Azure SQL Managed Instance or a synced copy of the database in Azure SQL Managed Instance for read-only workloads.

Feedback and suggestions

If you have feedback or suggestions for improving this data migration asset, please contact the Database Platform Engineering Team. Thanks for your support!

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.