Azure SQL Managed Instance Link

はじめに

Azure SQL Managed Instance Link機能が、世界中のすべてのAzureリージョンのPublic Previewで利用できるようになりました。

既存のAzure SQL Managed Instanceまたは新しいAzure SQL Managed Instance、およびSQL Server 2022 CTP (EAPから利用可能) を含む SQL Server 2019 Enterprise EditionとDeveloper Editionでも使用できます。
また、SSMS v18.11.1 以降、SQL Server Management Studio で使用可能な自動ウィザードの形式でLinkのツールのサポートもリリースされました。

Azure SQL Managed Instanceに最適なハイブリッド ソリューションとしてAzure SQL Managed Instance Link機能を紹介し、実績のある SQL Server AlwaysOnテクノロジを使用して SQL Server からAzure SQL Managed Instanceへのほぼリアルタイムのデータレプリケーションを可能になりました。

Understanding Migration and Modernization

オンプレミスからCloudに移行する、またはシフトしていくニーズが世の中からずっと増えつつある中、マイグレーションするだけではなく、マイグレーションとともに、モダニゼーションしていくこともよくお客様から求められます。
モダナイゼーションのジャーニーで、Azure に移行する準備ができたら、このSQL MI Linkを使用すると、自分のペースでマネージド インスタンスへのシームレスで即時の移行エクスペリエンスが可能になります。
このSQL MI Linkは、はるかにパフォーマンスの高い最小ダウンタイムエクスペリエンスを提供し、ダウンタイムの許容度がほとんどまたはまったくない重要な運用ワークロードに特に役立ちます。
レプリケートされたプライマリSQL ServerデータベースはセカンダリSQL MIでアクセス可能であるため、Azureへのカットオーバーを決定する前に、すべてのデータが最新であることをテストしたり、スキーマと整合性のチェックを実行したりできます。これらのオプションは新しい選択肢を提供し、SQL Serverワークロードを Azure に移行するリスクを大幅に最小限に抑えます。

Modernize SQL Server application on Azure

SQL MIは常に最新Versionで運用していて、Data Serviceを提供しています。
そして、様々なオンプレミスSQL Serverの互換性レベルを対応しています。
SQL MIはフルマネージドサービスであるため、DBAの生産性もアップしてくれるほか、専用のネットワークでデプロイされていることで、セキュアでアクセスできるようになっています。
SQL Serverと一番高い互換性を持っています。

What is link and why use it?

その理由はいくかあります。

• コンセプトとしては、SQL Server と SQL Managed Instance に共通する機能で、両者間のリッチなハイブリッド接続シナリオを実現します。
• 自分のペースでAzureにに出迎えるためにEmpowerを与えます。
• 既存のSQL Serverで動作させながら、Azureでモダナイズすることができます。
• 最小限のダウンタイムで、自分のペースでAzureに移行できます。
• マネージドインスタンスをディザスターリカバリー（DR）サイトとして利用する SQL Server 2022のみですが、SQL Server + 2019でも、最新のSSMS18.11.1以降のVerを使えば、簡単にSQL MIとLinkができるようになります。

Link feature overview

SQL MI Linkを使用すると、分散可用性グループの基盤となるテクノロジを使用して、SQL Server から Azure SQL Managed Instanceへのほぼリアルタイムのデータ レプリケーションが可能になります。

このテクノロジは、よく知られ、実績のある AlwaysOn 可用性グループ テクノロジ スタックの一部です。
SQL Server の可用性グループを Azure の SQL Managed Instanceに安全かつセキュリティで保護された方法で拡張します。
SQL Server 上のプライマリ データベースは R/W アクセスに使用できますが、マネージド インスタンス上のレプリケート データベースは R/O アクセスに使用できます。
SQL Server のプライマリ データベースへの変更は、Azure のセカンダリ マネージド インスタンスにほぼリアルタイムで転送されます。

各SQL MI Linkはデータベース スコープであり、1つのLinkが 1つのデータベースを接続します。
複数のLinkを使用して、複数のデータベースを接続できます。
Linkされたデータベースをスコープにすることで、SQL Server とSQL Managed Instance間の多対多リレーションシップでワークロードを統合および統合解除できるようになりました。

マイグレーションする際に、SQL MIに統合することも考えられます。それは、1つSQL MIでワークロードを統合する意味です。
つまり、複数のSQL Serverから1つのSQL MIにレプリケートします。

一方、逆のパターンもありです。つまり、統合を解除するパターンです。
統合されたSQL Serverから脱離することです。
1台のSQL Serverから複数のSQL MIにレプリケートを行うこともできます。
なぜ、このようなシナリオがあるという理由ですが、複数SQL MIと複数Azureリージョンにワークロードを分散させるためです。
各SQL MIは最大100個のLinkをサポートしています。

これにより、複数の SQL Server から Azure の 1 つのSQL Managed Instanceにデータをレプリケートしたり、単一の SQL Server から複数のマネージド インスタンスに世界中の Azure の 60 以上のリージョンのいずれかにレプリケートしたりできます。
後者を使用すると、SQL Server ワークロードを世界中のどの Azure リージョンの顧客にもすばやく近づけることができます。

Link networking requirements

SQL ServerとSQL MIを分散可用性グループを実現するためのネットワーク要件としてはAzure SQL MIのエンドポイントと通信する必要があります。
SQL ServerはAzure環境外にある場合、VPNやExpress Routeなどの通信回線で、Azure内にあるSQL MIと接続します。
SQL ServerはAzure環境内にある場合、そのSQL Server on VMのネットワークとSQL MIのネットワーク通信する必要があります。ここで、同じVNet内にするか、またはVNet Peeringを行います。

Minimum downtime migrations

多くのお客様は、SQL MIに移行する前に、きっとSQL MIに移行したら、どんな感じになるだろう、R/Oのワークロードをテストするニーズがあります。
SQL MI Linkを使えば、数週間から数ヶ月間、必要なデータベースだけ、Linkしたまま、テストを行うことができます。
テストなどの準備が整えればいつでも、SQL MIへGoサインを行うことができます。
そして、SQL MI Business Criticalを使うと、さらに、SQL MI BC側のReadonlyのレプリカを利用することもできます。

Using SSMS 18.11.1 (or later) to create SQL MI Link

SQL MI Linkを設定する前に、以下の要件が満たされていることを確認してください。

• SQL Server インスタンスと Azure SQL Managed Instanceをリソースをプロビジョニングする。
• SQL Server と Managed Instance の間のネットワーク接続を有効にします。
• Azure SQL Managed Instance LinkをサポートするSSMSプライベートビルドをインストールします。
• SQL Server 2019 CU15以降、またはSQL Server 2022に、AlwaysOn Availability Groups 機能を有効にする。(インスタンスの再起動が必要です)
  
• SQL Serverでスタートアップ時のトレースフラグ機能(-T1800と-T9567)を有効にする。(インスタンスの再起動が必要です)
  
• SQL ServerとSQL MIのネットワーク通信要件としては、TCP 5022ポートを開く必要です。(Windows ServerのFW、VNetのNSGのInboundの設定)

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

ネットワーク要件を満たさないと、このようなエラーが出ます。

• 事前に、SQL ServerのDatabaseのFullバックアップを行う必要です。
• SQL ServerとSQL MIの間では、SSL証明書で通信するために、SQL Server masterには、masterkeyを事前に作成する必要です。そのmasterkeyは証明書を保護します。つまり、SQL Server と Managed Instance の間で公開鍵を交換する目的です。
• Windows Serverの場合、Azureへのログイン時に「ウェブサイトがブロックされています」というSMSエラーを防ぐため、IEの拡張セキュリティ設定を無効にする必要です。

# ===============================================================================
# POWERSHELL SCRIPT TO DISABLE IE ENHANCED SECURITY SETTINGS TO PREVENT SSMS ERROR "WEBSITE BEING BLOCKED" ON LOGIN TO AZURE
# Run in PowerShell on machine where SSMS is used
# ===============================================================================

function Disable-InternetExplorerESC {
    $AdminKey = "HKLM:\SOFTWARE\Microsoft\Active Setup\Installed Components\{A509B1A7-37EF-4b3f-8CFC-4F3A74704073}"
    $UserKey = "HKLM:\SOFTWARE\Microsoft\Active Setup\Installed Components\{A509B1A8-37EF-4b3f-8CFC-4F3A74704073}"
    Set-ItemProperty -Path $AdminKey -Name "IsInstalled" -Value 0 -Force
    Set-ItemProperty -Path $UserKey -Name "IsInstalled" -Value 0 -Force
    Stop-Process -Name Explorer -Force
    Write-Host "IE Enhanced Security Configuration (ESC) has been disabled." -ForegroundColor Green
}
function Enable-InternetExplorerESC {
    $AdminKey = "HKLM:\SOFTWARE\Microsoft\Active Setup\Installed Components\{A509B1A7-37EF-4b3f-8CFC-4F3A74704073}"
    $UserKey = "HKLM:\SOFTWARE\Microsoft\Active Setup\Installed Components\{A509B1A8-37EF-4b3f-8CFC-4F3A74704073}"
    Set-ItemProperty -Path $AdminKey -Name "IsInstalled" -Value 1 -Force
    Set-ItemProperty -Path $UserKey -Name "IsInstalled" -Value 1 -Force
    Stop-Process -Name Explorer
    Write-Host "IE Enhanced Security Configuration (ESC) has been enabled." -ForegroundColor Green
}
function Disable-UserAccessControl {
    Set-ItemProperty "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System" -Name "ConsentPromptBehaviorAdmin" -Value 00000000 -Force
    Write-Host "User Access Control (UAC) has been disabled." -ForegroundColor Green    
}
Disable-UserAccessControl
Disable-InternetExplorerESC

下記はStep by Step手順です。
対象となるDatabaseを選びます。











そこで、AGとDAGが作成され、SQL ServerとSQL MIの間に、Linkできるようになります。

SQL Server側のTableを確認します。

SQL MI側のTableを確認します。

そして、今回のSQL MIはBusiness Critical service tierを使っていますので、SQL MIのレプリカ(R/O)をさらに確認します。

Establishing the link orchestration

SQL MI Linkの裏側はとても複雑です。上記の図のように、SQL Server側とSQL MI側、それぞれ協力しあって、Linkを実現しています。
今までSSMS18.11.1 (or later)を使えば、簡単にできますが、場合によって、SSMS18.11.1がまだ使えないなどの理由で、手動でSQL MI Linkを設定することもあります。その場合、上記の裏仕組みを理解する必要です。

下記はStep by Step手順です。
参考しては、SSMS18.11.1からのStepはこのようになります。下記の手順は多少順番前後します。

SQL Server側の操作

まず、SQL Serverの要件を満たしているかどうか、確認します。

-- Shows the version and CU of the SQL Server
SELECT @@VERSION
-- Shows if AlwaysOn feature is enabled on SQL Server
SELECT SERVERPROPERTY ('IsHadrEnabled')
-- Lists all trace flags enabled on the SQL Server
DBCC TRACESTATUS

次に、masterに対して、master keyを作成します。すでに存在している場合、このStepは不要です。

USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '{yourpassword}'
GO

Database Mirroring TCP 5022通信のEndpointでは、証明書を使って保護するため、その証明書を作成します。証明書名は任意です。

CREATE CERTIFICATE [Cert_SQL2019onVM_endpoint]
WITH SUBJECT = N'Certificate for SQL2019onVM_endpoint', expiry_date = N'3/30/2031'

作ったオレオレ証明書のPublicKeyを取得します。そのPublicKeyは後で使いますので、メモしておきます。

DECLARE @PUBLICKEYENC VARBINARY(MAX) = CERTENCODED(CERT_ID('Cert_SQL2019onVM_endpoint'))
SELECT @PUBLICKEYENC AS PublicKeyEncoded

Azure CLI側の操作

先ほど取得したPublicKeyのバイナリをSQL MIにインポートします。SQL ServerとSQL MIでは、公開キー交換することで、通信するため、SQL MIにインポートする必要があります。SQL MIにインポートするには、PowerShellを使います。

# ====================================================================================
# POWERSHELL SCRIPT TO IMPORT SQL SERVER CERTIFICATE TO MANAGED INSTANCE
# USER CONFIGURABLE VALUES
# (C) 2021 Managed Instance product group
# ====================================================================================
# Enter your Azure Subscription ID
$SubscriptionID = "<YourSubscriptionID>"
# Enter your Managed Instance name - example "sqlmi1"
$ManagedInstanceName = "<YourManagedInstanceName>"
# Insert the cert public key blob you got from the SQL Server
$PublicKeyEncoded = "<PublicKeyEncoded>"

# ====================================================================================
# INVOKING THE API CALL -- THIS PART IS NOT USER CONFIGURABLE
# ====================================================================================
# Login to subscription if needed
if ((Get-AzContext ) -eq $null)
{
    echo "Logging to Azure subscription"
    Login-AzAccount
}
Select-AzSubscription -SubscriptionName $SubscriptionID
# -----------------------------------
# Build URI for the API call
# -----------------------------------
echo "Building API URI"
$miRG = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName
$uriFull = "https://management.azure.com/subscriptions/" + $SubscriptionID + "/resourceGroups/" + $miRG+ "/providers/Microsoft.Sql/managedInstances/" + $ManagedInstanceName + "/hybridCertificate?api-version=2021-08-01-preview"
echo $uriFull

# -----------------------------------
# Build API request body
# -----------------------------------
echo "Building API request body"
$bodyFull = @"
{           
    "properties":{
         "PublicBlob":"$PublicKeyEncoded"
}}
"@
echo $bodyFull 

# -----------------------------------
# Get auth token and build the header
# -----------------------------------
$azProfile = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile
$currentAzureContext = Get-AzContext
$profileClient = New-Object Microsoft.Azure.Commands.ResourceManager.Common.RMProfileClient($azProfile)    
$token = $profileClient.AcquireAccessToken($currentAzureContext.Tenant.TenantId)
$authToken = $token.AccessToken
$headers = @{}
$headers.Add("Authorization", "Bearer "+"$authToken")

# -----------------------------------
# Invoke API call
# -----------------------------------
echo "Invoking API call to import SQL Server certificate to Managed Instance."
Invoke-WebRequest -Method POST -Headers $headers -Uri $uriFull -ContentType "application/json" -Body $bodyFull

上記のSQL MIに証明書のインポートは、Azure CLIのPowerShellでも実行できます。

New-AzSqlInstanceServerTrustCertificate -ResourceGroupName 'AzureDataPlatform_RG' -InstanceName '{your SQL MI name}' -CertificateName 'SqlServerCert_{yourSQLServerName}' -PublicKey '0x[binarycertificate]'

SQL MI側の操作

SQL Serverの公開キーをSQL MIにインポートしたことで、SQL MIの公開キーもSQL Serverにインポートする必要があります。そのため、SQL MIの公開キー(ルート証明)を取得します。得た公開キーのバイナリをメモしておきます。後でSQL Serverにインポートするのに、使います。

USE [master]
GO
exec sp_get_endpoint_certificate 3
--or
exec sp_get_endpoint_certificate 4

SQL Server側の操作

SQL MIの公開キーをSQL Serverにインポートします。ここで、SQL Server側で、SQL MIと同じサーバー名の証明書を作成します。

SQL MIと同じサーバー名にしないといけないことです。

CREATE CERTIFICATE [xxxx.xxxx.database.windows.net]
FROM BINARY = yourSQLMIPublicKey

Microsoft PKI ルート機関証明書 (Azure で信頼されているもの) をインポートします。

USE [master]
GO
CREATE CERTIFICATE [MicrosoftPKI] FROM BINARY = 0x308205A830820390A00302010202101ED397095FD8B4B347701EAABE7F45B3300D06092A864886F70D01010C05003065310B3009060355040613025553311E301C060355040A13154D6963726F736F667420436F72706F726174696F6E313630340603550403132D4D6963726F736F66742052534120526F6F7420436572746966696361746520417574686F726974792032303137301E170D3139313231383232353132325A170D3432303731383233303032335A3065310B3009060355040613025553311E301C060355040A13154D6963726F736F667420436F72706F726174696F6E313630340603550403132D4D6963726F736F66742052534120526F6F7420436572746966696361746520417574686F72697479203230313730820222300D06092A864886F70D01010105000382020F003082020A0282020100CA5BBE94338C299591160A95BD4762C189F39936DF4690C9A5ED786A6F479168F8276750331DA1A6FBE0E543A3840257015D9C4840825310BCBFC73B6890B6822DE5F465D0CC6D19CC95F97BAC4A94AD0EDE4B431D8707921390808364353904FCE5E96CB3B61F50943865505C1746B9B685B51CB517E8D6459DD8B226B0CAC4704AAE60A4DDB3D9ECFC3BD55772BC3FC8C9B2DE4B6BF8236C03C005BD95C7CD733B668064E31AAC2EF94705F206B69B73F578335BC7A1FB272AA1B49A918C91D33A823E7640B4CD52615170283FC5C55AF2C98C49BB145B4DC8FF674D4C1296ADF5FE78A89787D7FD5E2080DCA14B22FBD489ADBACE479747557B8F45C8672884951C6830EFEF49E0357B64E798B094DA4D853B3E55C428AF57F39E13DB46279F1EA25E4483A4A5CAD513B34B3FC4E3C2E68661A45230B97A204F6F0F3853CB330C132B8FD69ABD2AC82DB11C7D4B51CA47D14827725D87EBD545E648659DAF5290BA5BA2186557129F68B9D4156B94C4692298F433E0EDF9518E4150C9344F7690ACFC38C1D8E17BB9E3E394E14669CB0E0A506B13BAAC0F375AB712B590811E56AE572286D9C9D2D1D751E3AB3BC655FD1E0ED3740AD1DAAAEA69B897288F48C407F852433AF4CA55352CB0A66AC09CF9F281E1126AC045D967B3CEFF23A2890A54D414B92AA8D7ECF9ABCD255832798F905B9839C40806C1AC7F0E3D00A50203010001A3543052300E0603551D0F0101FF040403020186300F0603551D130101FF040530030101FF301D0603551D0E0416041409CB597F86B2708F1AC339E3C0D9E9BFBB4DB223301006092B06010401823715010403020100300D06092A864886F70D01010C05000382020100ACAF3E5DC21196898EA3E792D69715B813A2A6422E02CD16055927CA20E8BAB8E81AEC4DA89756AE6543B18F009B52CD55CD53396D624C8B0D5B7C2E44BF83108FF3538280C34F3AC76E113FE6E3169184FB6D847F3474AD89A7CEB9D7D79F846492BE95A1AD095333DDEE0AEA4A518E6F55ABBAB59446AE8C7FD8A2502565608046DB3304AE6CB598745425DC93E4F8E355153DB86DC30AA412C169856EDF64F15399E14A75209D950FE4D6DC03F15918E84789B2575A94B6A9D8172B1749E576CBC156993A37B1FF692C919193E1DF4CA337764DA19FF86D1E1DD3FAECFBF4451D136DCFF759E52227722B86F357BB30ED244DDC7D56BBA3B3F8347989C1E0F20261F7A6FC0FBB1C170BAE41D97CBD27A3FD2E3AD19394B1731D248BAF5B2089ADB7676679F53AC6A69633FE5392C846B11191C6997F8FC9D66631204110872D0CD6C1AF3498CA6483FB1357D1C1F03C7A8CA5C1FD9521A071C193677112EA8F880A691964992356FBAC2A2E70BE66C40C84EFE58BF39301F86A9093674BB268A3B5628FE93F8C7A3B5E0FE78CB8C67CEF37FD74E2C84F3372E194396DBD12AFBE0C4E707C1B6F8DB332937344166DE8F4F7E095808F965D38A4F4ABDE0A308793D84D00716245274B3A42845B7F65B76734522D9C166BAAA8D87BA3424C71C70CCA3E83E4A6EFB701305E51A379F57069A641440F86B02C91C63DEAAE0F84

Azure database.windows.net ドメインの Microsoft PKI ルート機関で発行された証明書を信頼します。

USE [master]
GO
DECLARE @CERTID int
SELECT @CERTID = CERT_ID('MicrosoftPKI')
EXEC sp_certificate_add_issuer @CERTID, N'*.database.windows.net'
GO

DigiCert PKI ルート機関証明書 (Azure で信頼されているもの) をインポートします。

USE [master]
GO
CREATE CERTIFICATE [DigiCertPKI] FROM BINARY = 0x3082038E30820276A0030201020210033AF1E6A711A9A0BB2864B11D09FAE5300D06092A864886F70D01010B05003061310B300906035504061302555331153013060355040A130C446967694365727420496E6331193017060355040B13107777772E64696769636572742E636F6D3120301E06035504031317446967694365727420476C6F62616C20526F6F74204732301E170D3133303830313132303030305A170D3338303131353132303030305A3061310B300906035504061302555331153013060355040A130C446967694365727420496E6331193017060355040B13107777772E64696769636572742E636F6D3120301E06035504031317446967694365727420476C6F62616C20526F6F7420473230820122300D06092A864886F70D01010105000382010F003082010A0282010100BB37CD34DC7B6BC9B26890AD4A75FF46BA210A088DF51954C9FB88DBF3AEF23A89913C7AE6AB061A6BCFAC2DE85E092444BA629A7ED6A3A87EE054752005AC50B79C631A6C30DCDA1F19B1D71EDEFDD7E0CB948337AEEC1F434EDD7B2CD2BD2EA52FE4A9B8AD3AD499A4B625E99B6B00609260FF4F214918F76790AB61069C8FF2BAE9B4E992326BB5F357E85D1BCD8C1DAB95049549F3352D96E3496DDD77E3FB494BB4AC5507A98F95B3B423BB4C6D45F0F6A9B29530B4FD4C558C274A57147C829DCD7392D3164A060C8C50D18F1E09BE17A1E621CAFD83E510BC83A50AC46728F67314143D4676C387148921344DAF0F450CA649A1BABB9CC5B1338329850203010001A3423040300F0603551D130101FF040530030101FF300E0603551D0F0101FF040403020186301D0603551D0E041604144E2254201895E6E36EE60FFAFAB912ED06178F39300D06092A864886F70D01010B05000382010100606728946F0E4863EB31DDEA6718D5897D3CC58B4A7FE9BEDB2B17DFB05F73772A3213398167428423F2456735EC88BFF88FB0610C34A4AE204C84C6DBF835E176D9DFA642BBC74408867F3674245ADA6C0D145935BDF249DDB61FC9B30D472A3D992FBB5CBBB5D420E1995F534615DB689BF0F330D53E31E28D849EE38ADADA963E3513A55FF0F970507047411157194EC08FAE06C49513172F1B259F75F2B18E99A16F13B14171FE882AC84F102055D7F31445E5E044F4EA879532930EFE5346FA2C9DFF8B22B94BD90945A4DEA4B89A58DD1B7D529F8E59438881A49E26D56FADDD0DC6377DED03921BE5775F76EE3C8DC45D565BA2D9666EB33537E532B6

Azure database.windows.net ドメインの DigiCert PKI ルート機関で発行された証明書を信頼します。

USE [master]
GO
DECLARE @CERTID int
SELECT @CERTID = CERT_ID('DigiCertPKI')
EXEC sp_certificate_add_issuer @CERTID, N'*.database.windows.net'
GO

それから、あらかじめに作ったオレオレ証明書を使って、Database MirroringのEndpointを作成します。

USE [master]
GO

CREATE ENDPOINT [SQL2019onVM_endpoint] 
	STATE=STARTED
	AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
	FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = CERTIFICATE [Cert_SQL2019onVM_endpoint]
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

SQL ServerのDatabase MirroringのEndpointを作成しないと、TCP5022がlisteningしてくれません。

Database MirroringのEndpointを作成した後に、状態の確認を行います。

-- View database mirroring endpoints on SQL Server
SELECT * 
FROM sys.database_mirroring_endpoints 
WHERE type_desc = 'DATABASE_MIRRORING'

SELECT
name, type_desc, state_desc, role_desc,
connection_auth_desc, is_encryption_enabled, encryption_algorithm_desc
FROM
sys.database_mirroring_endpoints

ここで、SQL ServerとSQL MIの間に、TCP 5022 Portの通信が通ることを前提しています。場合によって、ネットワークやFW周りの設定を見直す必要があります。

SQL MIからSQL Serverに通信を確認するには、SQL MI Agnetで、PowerShellを流す方法もあります。

tnc {your SQL Server IP} -port 5022 -InformationLevel Quiet

具体的な操作としては、

SQL MI側の操作

ここで、NetHelperというAgent Jobを作成します。

--Create Agent
DECLARE @SQLServerIpAddress NVARCHAR(MAX) = '{your SQL Server IP}
DECLARE @tncCommand NVARCHAR(MAX) = 'tnc ' + @SQLServerIpAddress + ' -port 5022 -InformationLevel Quiet'
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'NetHelper',
@enabled=1,
@description=N'Test Managed Instance to SQL Server network connectivity on port 5022.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'{SQL Server Login}', @job_id = @jobId OUTPUT
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'tnc step',
@step_id=1,
@os_run_priority=0, @subsystem=N'PowerShell',
@command = @tncCommand,
@database_name=N'master',
@flags=40
EXEC msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

NetHelperを実行します。

--Run Agent job
EXEC msdb.dbo.sp_start_job @job_name = N'NetHelper'

その結果を確認します。1であるかどうかの判断です。1=通信OKという意味です。

--Get Agent job results
SELECT
sj.name JobName, sjs.step_id, sjs.step_name, sjsl.log, sjsl.date_modified
FROM
msdb.dbo.sysjobs sj
LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs
ON sj.job_id = sjs.job_id
LEFT OUTER JOIN msdb.dbo.sysjobstepslogs sjsl
ON sjs.step_uid = sjsl.step_uid
WHERE
sj.name = 'NetHelper'

SQL Server側の操作

まずはAvailability Groupを作成します。

-- Create primary AG on SQL Server
USE MASTER
GO
CREATE AVAILABILITY GROUP [AG_SQLMILinkDB]
WITH (CLUSTER_TYPE = NONE) FOR database [SQLMILinkDB]
REPLICA ON '{your SQL Server}' WITH
( ENDPOINT_URL = 'TCP://{your SQL Server IP}:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
)
GO

それから、分散Availability Group(DAG)を作成します。

CREATE AVAILABILITY GROUP [DAG_SQLMILinkDB]
WITH (DISTRIBUTED)
AVAILABILITY GROUP ON 'AG_SQLMILinkDB' WITH
( 
	LISTENER_URL = 'TCP://{your SQL Server IP}:5022',
	AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
	FAILOVER_MODE = MANUAL,
	SEEDING_MODE = AUTOMATIC,
	SESSION_TIMEOUT = 20
), 
'{SQL MI Name}' WITH
( 
	LISTENER_URL = 'tcp://{SQL MI Name}.xxxx.database.windows.net:5022;Server=[{your SQL MI Name}]',
	AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
	FAILOVER_MODE = MANUAL,
	SEEDING_MODE = AUTOMATIC
)
GO

AGとDAGの状態を確認します。

SELECT
name, is_distributed, cluster_type_desc,
sequence_number, is_contained
FROM
sys.availability_groups

Azure CLI側の操作

最後に、PowerShellを使って、SQL MIのLinkを作成していきます。

# ====================================================================================
# POWERSHELL SCRIPT FOR CREATING MANAGED INSTANCE LINK
# USER CONFIGURABLE VALUES
# (C) 2021 Managed Instance product group
# ====================================================================================
# Enter your Azure Subscription ID
$SubscriptionID = "<SubscriptionID>"
# Enter your Managed Instance name - example "sqlmi1"
$ManagedInstanceName = "<ManagedInstanceName>"
# Enter AG name that was created on the SQL Server
$AGName = "<AGName>"
# Enter DAG name that was created on SQL Server
$DAGName = "<DAGName>"
# Enter database name that was placed in AG for replciation
$DatabaseName = "<DatabaseName>"
# Enter SQL Server IP
$ SQLServerIP = "<SQLServerIP>"

# ====================================================================================
# INVOKING THE API CALL -- THIS PART IS NOT USER CONFIGURABLE
# ====================================================================================
# Login to subscription if needed
if ((Get-AzContext ) -eq $null)
{
    echo "Logging to Azure subscription"
    Login-AzAccount
}
Select-AzSubscription -SubscriptionName $SubscriptionID
# -----------------------------------
# Build URI for the API call
# -----------------------------------
echo "Building API URI"
$miRG = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName
$uriFull = "https://management.azure.com/subscriptions/" + $SubscriptionID + "/resourceGroups/" + $miRG+ "/providers/Microsoft.Sql/managedInstances/" + $ManagedInstanceName + "/distributedAvailabilityGroups/" + $DAGName + "?api-version=2021-08-01-preview"
echo $uriFull
# -----------------------------------
# Build API request body
# -----------------------------------
echo "Building API request body"
$bodyFull = @"
{
    "properties":{
        "TargetDatabase":"$DatabaseName",
        "SourceEndpoint":"TCP://$SQLServerIP`:5022",
        "PrimaryAvailabilityGroupName":"$AGName",
        "SecondaryAvailabilityGroupName":"$ManagedInstanceName",
    }
}
"@
echo $bodyFull 

# -----------------------------------
# Get auth token and build the header
# -----------------------------------
$azProfile = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile
$currentAzureContext = Get-AzContext
$profileClient = New-Object Microsoft.Azure.Commands.ResourceManager.Common.RMProfileClient($azProfile)    
$token = $profileClient.AcquireAccessToken($currentAzureContext.Tenant.TenantId)
$authToken = $token.AccessToken
$headers = @{}
$headers.Add("Authorization", "Bearer "+"$authToken")

# -----------------------------------
# Invoke API call
# -----------------------------------
echo "Invoking API call for creating Managed Instance link."
$response = Invoke-WebRequest -Method PUT -Headers $headers -Uri $uriFull -ContentType "application/json" -Body $bodyFull
echo $response

ここでやっと手動でSQL MI Linkを作成できました。SSMSで、SQL MI側にレプリケートされたデータベースを確認できるようになります。

その後は、いくつかの状態の確認を行います。

SQL Server側の操作

まずは、SQL ServerとSQL MIのConnect状態の確認です。ここで、Connectedであることを確認します。

USE [master]
GO
SELECT
r.replica_server_name AS [Replica],
r.endpoint_url AS [Endpoint],
rs.connected_state_desc AS [Connected state],
rs.last_connect_error_description AS [Last connection error],
rs.last_connect_error_number AS [Last connection error No],
rs.last_connect_error_timestamp AS [Last error timestamp]
FROM
sys.dm_hadr_availability_replica_states rs
JOIN sys.availability_replicas r
ON rs.replica_id = r.replica_id

それから、Syncの状態を確認します。Healthyであることを確認します。

USE [master]
GO
SELECT
ag.[name] AS [DAG Name],
ag.is_distributed,
ar.replica_server_name AS [Underlying AG],
ars.role_desc AS [Role],
ars.synchronization_health_desc AS [Sync Status],
ar.endpoint_url as [Endpoint URL],
ar.availability_mode_desc AS [Sync mode],
ar.failover_mode_desc AS [Failover mode],
ar.seeding_mode_desc AS [Seeding mode],
ar.primary_role_allow_connections_desc AS [Primary allow connections],
ar.secondary_role_allow_connections_desc AS [Secondary allow connections]
FROM
sys.availability_groups AS ag
INNER JOIN sys.availability_replicas AS ar
ON ag.group_id = ar.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ars
ON ar.replica_id = ars.replica_id
WHERE
ag.is_distributed = 1

Migration cutover ochestration

最後は、SQL ServerのDatabaseをSQL MIにCutoverすることを説明します。
SQL MI側にすでにレプリケーションされた状態で、SQL ServerからSQL MIに切り替えます。
切り替える際に、SQL Server側の最後のLSNを確認し、そして、SQL MI側の最後のLSNと比較し、SQL MI側でCommintを行います。

Cutover操作はSSMS18.11.1を使います。



ここで、計画されたFailoverかどうかの選択ですが、SQL Server側のトランザクションはない（つまり、業務アプリから接続もなく、いつでも、遮断できる状態）の場合、確認のチェックボックスに☑をいれて、次へ。
そうではないば、強制Failoverもできますが、その場合、データの損失が出る可能性があります。


SQL Server側のAGとDAGを削除します。



そこで、SQL MI側はR/OからR/Wに切り替えます。

最後に

現時点では、Previewであるため、今後の仕様変更の可能性があります。
52th Microsoft Data Platform Day(Online)の勉強会では、SQL MI Linkの詳細セッションを行います。よかったら、ご参加をお待ちしております。

また、SQL MI Linkを使ったビジネスニーズを聞かせてください。Feedbackのご連絡をお待ちしております。
よろしくお願いいたします。