MI Link is a new feature connecting SQL Server hosted locally or on Azure VM with Azure SQL Managed Instance. MI Link leverages Distributed
Availability Groups to replicate
databases in near real-time to Azure
SQL Managed Instance. MI Link is in
its current implementation database scoped, so you need to create an individual
link for each database you want to replicate. In turn it is both possible to
replicate a single database to multiple managed instances as well as replicate
databases from one or more instances into a single managed instance. MI Link is
not a DR feature as such and I rather see it as either of the following, a) a
mechanism for cloud migrations, b) a step in an ETL process or c) a tool to
offload read-only workloads, possibly even to multiple regions world-wide. However,
I hear that with SQL Server 2022 this is supposed to get upgraded to a DR
solution, so I will be very interested to see this.
The script below
covers the setup of MI Link, in its current state (this might change if
additional features require additional/different steps), provided you have
connectivity through either VPN or ExpressRoute if your instance is on-prem or network
peering or a VPN
gateway if your instance
is on Azure. The full list of requirements can be found here.
At first, I’m defining the configuration, then create the managed instance
(this step takes a long time, in my case a little over 4 hours, make sure you
don’t get disconnected) and configure the local instance, including the
exchange of certificates between the instances before finally creating the MI
Link and initiating the replication of the database.
#Set configuration
#1. Azure
# The SubscriptionId in which to create these objects
$SubscriptionId = "11111111-2222-3333-4444-555555555555"
# Set the
resource group name and location for your managed instance
$resourceGroupName = "myResourceGroup-$(Get-Random)"
$location
= "westeurope"
# Set the
networking values for your managed instance
$vNetName = "myVnet-$(Get-Random)"
$vNetAddressPrefix = "10.0.0.0/16"
$miSubnetName = "myMISubnet-$(Get-Random)"
$miSubnetAddressPrefix = "10.0.0.0/24"
$miLinkName = "MIlink"
# Set the
managed instance name for the new managed instance
$instanceName = "myMIName-$(Get-Random)"
# Set the admin
login and password for your managed instance
$miAdminSqlLogin = "SqlAdmin"
$miAdminSqlPassword = "5tr0ngP@55w0rd"
# Set the
managed instance service tier, compute level, and license mode
$edition
= "General Purpose"
$vCores
= 4
$maxStorage = 32
$computeGeneration = "Gen5"
$license
= "LicenseIncluded" #"BasePrice" or LicenseIncluded
if you have don't have SQL Server license that can be used for AHB discount
#2. local SQL
instance
$Server
= $env:COMPUTERNAME
$AGname = "SQLAG"
$DAGname = "$($AGname)_DAG"
$SQLServerIP = "10.10.0.4"
$SourceIP = "TCP://" + $SQLServerIP + ":5022"
$MasterKey = "R@nd0mK€y"
$DatabaseName = "AdventureWorks"
$BackupPath = "D:\Backup.bak"
$cred
= Get-Credential
#Create resource
group
## Connect to
Azure
Connect-AzAccount
# Set subscription
context
Set-AzContext -SubscriptionId $SubscriptionId
# Create a
resource group
$resourceGroup = New-AzResourceGroup -Name $resourceGroupName -Location $location -Tag @{Owner="SQLDB-Samples"}
#Configure
networking
# Configure
virtual network, subnets, network security group, and routing table
$virtualNetwork = New-AzVirtualNetwork -ResourceGroupName $resourceGroupName -Location $location -Name $vNetName -AddressPrefix
$vNetAddressPrefix
Add-AzVirtualNetworkSubnetConfig -Name $miSubnetName -VirtualNetwork $virtualNetwork -AddressPrefix
$miSubnetAddressPrefix | Set-AzVirtualNetwork
$scriptUrlBase = 'https://raw.githubusercontent.com/Microsoft/sql-server-samples/master/samples/manage/azure-sql-db-managed-instance/delegate-subnet'
$parameters = @{
subscriptionId = $SubscriptionId
resourceGroupName = $resourceGroupName
virtualNetworkName = $vNetName
subnetName =
$miSubnetName
}
Invoke-Command -ScriptBlock
([Scriptblock]::Create((Invoke-WebRequest ($scriptUrlBase+'/delegateSubnet.ps1?t='+ [DateTime]::Now.Ticks)).Content)) -ArgumentList
$parameters
$virtualNetwork = Get-AzVirtualNetwork -Name $vNetName -ResourceGroupName $resourceGroupName
$miSubnet = Get-AzVirtualNetworkSubnetConfig -Name $miSubnetName -VirtualNetwork $virtualNetwork
$miSubnetConfigId = $miSubnet.Id
#Create managed
instance
# Create
credentials
$secpassword = ConvertTo-SecureString $miAdminSqlPassword -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential
($miAdminSqlLogin, $secpassword)
# Create managed
instance
$miInstance = New-AzSqlInstance -Name $instanceName -ResourceGroupName $resourceGroupName -Location $location -SubnetId
$miSubnetConfigId -AdministratorCredential $credential -StorageSizeInGB
$maxStorage -VCore $vCores -Edition $edition -ComputeGeneration
$computeGeneration -LicenseType $license
# verify
connectivity
Test-NetConnection $miInstance.FullyQualifiedDomainName -Port 5022
Get-AzSqlInstanceLink -ResourceGroupName $resourceGroupName -InstanceName
$instanceName
New-NetFirewallRule -DisplayName "Allow TCP port 5022 inbound" -Direction inbound -Profile
Any -Action Allow -LocalPort 5022 -Protocol TCP
New-NetFirewallRule -DisplayName "Allow TCP port 5022 outbound" -Direction outbound -Profile
Any -Action Allow -LocalPort 5022 -Protocol TCP
#prepare local
SQL instance
#Enable AlwaysOn Feature
Enable-SqlAlwaysOn -Path "SQLSERVER:\SQL\$Server\DEFAULT" -Credential $cred
#Enable startup
trace flags
Enable-DbaTraceFlag -SqlInstance $Server -TraceFlag 1800, 9567
#restart the
service
Get-Service -Name MSSQL* -ComputerName $Server | Restart-Service
#create master
key
$q = @"
USE master;
GO
CREATE MASTER
KEY ENCRYPTION BY PASSWORD = '$MasterKey';
"@
Invoke-Sqlcmd -Query $q -ServerInstance
$Server
$q = @"
ALTER DATABASE [$DatabaseName] SET RECOVERY FULL
GO
-- Execute
backup for all databases you want to replicate.
BACKUP DATABASE
[$DatabaseName] TO DISK = N'$BackupPath'
GO
"@
Invoke-Sqlcmd -Query $q -ServerInstance
$Server
#create
certificate and endpoint required to connect to local SQL instance
$q = @"
USE master
GO
CREATE
CERTIFICATE $($Server)_Cert
WITH SUBJECT = '$($Server)',
START_DATE = '$((get-date).tostring("yyyyMMdd"))'
GO
CREATE ENDPOINT $($AGname)_Endpoint
STATE = STARTED
AS TCP
(LISTENER_PORT = 5022)
FOR
DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE $($Server)_Cert,
ROLE = ALL,
ENCRYPTION = REQUIRED ALGORITHM AES
)
GO
DECLARE
@sqlserver_certificate_name NVARCHAR(MAX) = N'$($Server)_Cert'
DECLARE
@PUBLICKEYENC VARBINARY(MAX) =
CERTENCODED(CERT_ID(@sqlserver_certificate_name));
SELECT
CONVERT(varchar(max), @PUBLICKEYENC, 1) AS SQLServerPublicKey;
"@
$localCert = Invoke-Sqlcmd -Query $q -ServerInstance
$Server
New-AzSqlInstanceServerTrustCertificate -ResourceGroupName $resourceGroupName -InstanceName
$instanceName.ToLower() -Name "$($Server)_Cert" -PublicKey
$localCert.SQLServerPublicKey
#get the public
key of the authentication certificate from Managed Instance
$miCert = Get-AzSqlInstanceEndpointCertificate -ResourceGroupName $resourceGroupName -InstanceName
$instanceName.ToLower() -EndpointType
"DATABASE_MIRRORING"
#create
certificate and endpoint required to connect to local SQL instance
$q = @"
CREATE
CERTIFICATE [$($instanceName)_Cert]
FROM BINARY = $($miCert.PublicKey)
"@
Invoke-Sqlcmd -Query $q -ServerInstance
$Server
#Import
Azure-trusted root certificate authority keys to SQL Server
$q = @"
CREATE CERTIFICATE [MicrosoftPKI] FROM BINARY = 0x308205A830820390A00302010202101ED397095FD8B4B347701EAABE7F45B3300D06092A864886F70D01010C05003065310B3009060355040613025553311E301C060355040A13154D6963726F736F667420436F72706F726174696F6E313630340603550403132D4D6963726F736F66742052534120526F6F7420436572746966696361746520417574686F726974792032303137301E170D3139313231383232353132325A170D3432303731383233303032335A3065310B3009060355040613025553311E301C060355040A13154D6963726F736F667420436F72706F726174696F6E313630340603550403132D4D6963726F736F66742052534120526F6F7420436572746966696361746520417574686F72697479203230313730820222300D06092A864886F70D01010105000382020F003082020A0282020100CA5BBE94338C299591160A95BD4762C189F39936DF4690C9A5ED786A6F479168F8276750331DA1A6FBE0E543A3840257015D9C4840825310BCBFC73B6890B6822DE5F465D0CC6D19CC95F97BAC4A94AD0EDE4B431D8707921390808364353904FCE5E96CB3B61F50943865505C1746B9B685B51CB517E8D6459DD8B226B0CAC4704AAE60A4DDB3D9ECFC3BD55772BC3FC8C9B2DE4B6BF8236C03C005BD95C7CD733B668064E31AAC2EF94705F206B69B73F578335BC7A1FB272AA1B49A918C91D33A823E7640B4CD52615170283FC5C55AF2C98C49BB145B4DC8FF674D4C1296ADF5FE78A89787D7FD5E2080DCA14B22FBD489ADBACE479747557B8F45C8672884951C6830EFEF49E0357B64E798B094DA4D853B3E55C428AF57F39E13DB46279F1EA25E4483A4A5CAD513B34B3FC4E3C2E68661A45230B97A204F6F0F3853CB330C132B8FD69ABD2AC82DB11C7D4B51CA47D14827725D87EBD545E648659DAF5290BA5BA2186557129F68B9D4156B94C4692298F433E0EDF9518E4150C9344F7690ACFC38C1D8E17BB9E3E394E14669CB0E0A506B13BAAC0F375AB712B590811E56AE572286D9C9D2D1D751E3AB3BC655FD1E0ED3740AD1DAAAEA69B897288F48C407F852433AF4CA55352CB0A66AC09CF9F281E1126AC045D967B3CEFF23A2890A54D414B92AA8D7ECF9ABCD255832798F905B9839C40806C1AC7F0E3D00A50203010001A3543052300E0603551D0F0101FF040403020186300F0603551D130101FF040530030101FF301D0603551D0E0416041409CB597F86B2708F1AC339E3C0D9E9BFBB4DB223301006092B06010401823715010403020100300D06092A864886F70D01010C05000382020100ACAF3E5DC21196898EA3E792D69715B813A2A6422E02CD16055927CA20E8BAB8E81AEC4DA89756AE6543B18F009B52CD55CD53396D624C8B0D5B7C2E44BF83108FF3538280C34F3AC76E113FE6E3169184FB6D847F3474AD89A7CEB9D7D79F846492BE95A1AD095333DDEE0AEA4A518E6F55ABBAB59446AE8C7FD8A2502565608046DB3304AE6CB598745425DC93E4F8E355153DB86DC30AA412C169856EDF64F15399E14A75209D950FE4D6DC03F15918E84789B2575A94B6A9D8172B1749E576CBC156993A37B1FF692C919193E1DF4CA337764DA19FF86D1E1DD3FAECFBF4451D136DCFF759E52227722B86F357BB30ED244DDC7D56BBA3B3F8347989C1E0F20261F7A6FC0FBB1C170BAE41D97CBD27A3FD2E3AD19394B1731D248BAF5B2089ADB7676679F53AC6A69633FE5392C846B11191C6997F8FC9D66631204110872D0CD6C1AF3498CA6483FB1357D1C1F03C7A8CA5C1FD9521A071C193677112EA8F880A691964992356FBAC2A2E70BE66C40C84EFE58BF39301F86A9093674BB268A3B5628FE93F8C7A3B5E0FE78CB8C67CEF37FD74E2C84F3372E194396DBD12AFBE0C4E707C1B6F8DB332937344166DE8F4F7E095808F965D38A4F4ABDE0A308793D84D00716245274B3A42845B7F65B76734522D9C166BAAA8D87BA3424C71C70CCA3E83E4A6EFB701305E51A379F57069A641440F86B02C91C63DEAAE0F84
--Trust certificates issued by Microsoft PKI root authority for Azure
database.windows.net domains
DECLARE @CERTID int
SELECT @CERTID = CERT_ID('MicrosoftPKI')
EXEC sp_certificate_add_issuer @CERTID,
N'*.database.windows.net'
GO
CREATE CERTIFICATE [DigiCertPKI] FROM BINARY =
0x3082038E30820276A0030201020210033AF1E6A711A9A0BB2864B11D09FAE5300D06092A864886F70D01010B05003061310B300906035504061302555331153013060355040A130C446967694365727420496E6331193017060355040B13107777772E64696769636572742E636F6D3120301E06035504031317446967694365727420476C6F62616C20526F6F74204732301E170D3133303830313132303030305A170D3338303131353132303030305A3061310B300906035504061302555331153013060355040A130C446967694365727420496E6331193017060355040B13107777772E64696769636572742E636F6D3120301E06035504031317446967694365727420476C6F62616C20526F6F7420473230820122300D06092A864886F70D01010105000382010F003082010A0282010100BB37CD34DC7B6BC9B26890AD4A75FF46BA210A088DF51954C9FB88DBF3AEF23A89913C7AE6AB061A6BCFAC2DE85E092444BA629A7ED6A3A87EE054752005AC50B79C631A6C30DCDA1F19B1D71EDEFDD7E0CB948337AEEC1F434EDD7B2CD2BD2EA52FE4A9B8AD3AD499A4B625E99B6B00609260FF4F214918F76790AB61069C8FF2BAE9B4E992326BB5F357E85D1BCD8C1DAB95049549F3352D96E3496DDD77E3FB494BB4AC5507A98F95B3B423BB4C6D45F0F6A9B29530B4FD4C558C274A57147C829DCD7392D3164A060C8C50D18F1E09BE17A1E621CAFD83E510BC83A50AC46728F67314143D4676C387148921344DAF0F450CA649A1BABB9CC5B1338329850203010001A3423040300F0603551D130101FF040530030101FF300E0603551D0F0101FF040403020186301D0603551D0E041604144E2254201895E6E36EE60FFAFAB912ED06178F39300D06092A864886F70D01010B05000382010100606728946F0E4863EB31DDEA6718D5897D3CC58B4A7FE9BEDB2B17DFB05F73772A3213398167428423F2456735EC88BFF88FB0610C34A4AE204C84C6DBF835E176D9DFA642BBC74408867F3674245ADA6C0D145935BDF249DDB61FC9B30D472A3D992FBB5CBBB5D420E1995F534615DB689BF0F330D53E31E28D849EE38ADADA963E3513A55FF0F970507047411157194EC08FAE06C49513172F1B259F75F2B18E99A16F13B14171FE882AC84F102055D7F31445E5E044F4EA879532930EFE5346FA2C9DFF8B22B94BD90945A4DEA4B89A58DD1B7D529F8E59438881A49E26D56FADDD0DC6377DED03921BE5775F76EE3C8DC45D565BA2D9666EB33537E532B6
--Trust certificates issued by DigiCert PKI root authority for Azure
database.windows.net domains
DECLARE @CERTID int
SELECT @CERTID = CERT_ID('DigiCertPKI')
EXEC sp_certificate_add_issuer @CERTID,
N'*.database.windows.net'
"@
Invoke-Sqlcmd -Query $q -ServerInstance
$Server
#create AG
$q = @"
USE [master]
GO
CREATE
AVAILABILITY GROUP [$($AGname)]
WITH
(AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER =
OFF,
DTC_SUPPORT =
NONE,
CLUSTER_TYPE =
NONE,
REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT
= 0)
FOR
REPLICA ON
N'SQL' WITH (ENDPOINT_URL = N'$($EndPoint)', FAILOVER_MODE
= MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 10,
BACKUP_PRIORITY = 50, SEEDING_MODE = MANUAL, PRIMARY_ROLE(ALLOW_CONNECTIONS =
ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
GO
"@
Invoke-Sqlcmd -Query $q -ServerInstance
$Server
#create MIlink
$ResourceGroup = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName
New-AzSqlInstanceLink -ResourceGroupName $ResourceGroup -InstanceName
$ManagedInstanceName -Name $DAGName -PrimaryAvailabilityGroupName
$AGName -SecondaryAvailabilityGroupName $ManagedInstanceName -TargetDatabase
$DatabaseName -SourceEndpoint $SourceIP