SQL Server 2016 で Managed Instance Link を設定してみる at SE の雑記

SQL Server 2016 で Managed Instance Link がサポートされるようになりましたで書きましたが、SQL Server 2016 SP3 + Azure Connect Feature Pack で Managed Instance Link がサポートされるようになったので、実際に設定をしてみました。

今回は SQL Server 2016 SP3 Standard Edition で検証をしています。

Contents

1 前提条件
2 SQL Server 2016 で Managed Instance Link を設定
3 まとめ

前提条件

SQL Server 2016 を使用した場合の前提については、次のドキュメントを確認します。

Link feature for Azure SQL Managed Instance (preview)

SQL Server 2016 は Service Pack 3 と Azure connect feature pack をインストールした環境である必要があります。

ベースとなる SQL Server が前提を満たした後は、Prepare your environment for a link – Azure SQL Managed Instance に従い、環境の準備をします。(2022/5/22 時点では、本ドキュメントは SQL Server 2016 の対応は記載されていないようです)

準備としては次のような作業があり、基本的な作業については、SQL Server 2019 CU15 以降と共通です。

SQL Server でマスターキーを作成
SQL Server 構成マネージャーで Always On 可用性グループを有効化し SQL Server のサービスを再起動
SQL Server のスタートアップオプションに -T1800 / -T9567 を有効にし SQL Server のサービスを再起動
Windows Firewall で TCP : 5022 の送受信を許可

SQL Server 2019 との違い

SQL Server 2016 と 2019 との違いですが、現時点の制約による違いも含めていくつかあるようです。

Always On 可用性グループの環境の違い

SQL Server 2017 データベースエンジン / 読み取りスケール可用性グループ (クラスターなし) で触れられていますが、SQL Server 2017 以降は「クラスターレス可用性グループ (クラスターを使用しない可用性グループ)」が使用することができるようになったため、SQL Server 2019 で Managed Instance Link を使用する場合は、単一の SQL Server を使用することができました。

SQL Server 2016 では、「クラスターレス可用性グループ」が使用できません。そのため 2016 で Always On 可用性グループを有効にするためには WSFC を有効にする必要があります。

Managed Instance Link を使用する目的であれば、「1 ノードのワークグループクラスター」の構成でも問題はないのですが、WSFC を設定については、意識しておく必要があります。

SSMS 18.11.1 は 2016 の Managed Instance Link は GUI 操作をサポートしていない

SQL Server 2019 + SSMS 18.11.1 であれば、データベースを右クリックすると、「Azure SQL Managed Instance link」のメニューが表示されます。

しかし、SQL Server 2016 については、SSM 18.11. では GUI のサポートが行われておらず、メニューが表示されません。

次バージョンの SSMS 19 でサポートされるようですが、現時点では、SQL Server 2016 で可用性グループを有効にするためには、CUI ベースで実施する必要があります。

追記

SSSMS 18.12 以降で SSMS の操作がサポートされているようですので、本機能の検証時には SSMS を最新化していただければと思います。

SQL Server 2016 で Managed Instance Link を設定

前述のように、現時点では SQL Server 2016 に対しては、SSMS の GUI 操作をサポートしていませんので、すべて CUI (T-SQL / Azure PowerShell) で実施する必要があります。

設定の方法については Replicate a database with the link feature via T-SQL and PowerShell scripts – Azure SQL Managed Instance で記載されています。

SQL Server 2019 で設定を行う際には、スクリプト化を行うことができるようになっていますので、一度 2019 で設定をスクリプト化して、それを基に 2016 で実行しても良いかと。

流れとしては次のようになるかと。

MASTER KEY の作成 : SQL Server で実行

USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong Password>'
GO

エンドポイント用の証明書を作成 : SQL Server で実行

USE [master]
GO
CREATE CERTIFICATE [Cert_MSSQL_endpoint]
WITH SUBJECT = N'Certificate for SQL Server endpoint' , EXPIRY_DATE = N'03/13/2025'
GO

エンドポイントの作成と有効化 : SQL Server で実行

USE [master]
GO
CREATE ENDPOINT [MSSQL_endpoint] 
	AS TCP (LISTENER_PORT = 5022)
	FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = CERTIFICATE [Cert_MSSQL_endpoint], ENCRYPTION = REQUIRED ALGORITHM AES)
GO

IF (SELECT state FROM sys.endpoints WHERE name = N'MSSQL_endpoint') <> 0
BEGIN
	ALTER ENDPOINT [MSSQL_endpoint] STATE = STARTED
END
GO

証明書の作成 (SQL Server で実行)

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

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
GO

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

エンドポイント証明書の取得 : Managed Instance で実行

EXEC sp_get_endpoint_certificate 4
GO

実行時に出力されたバイナリデータは次のステップで使用する

エンドポイント証明書を登録 : SQL Server で実行

USE [master]
GO
CEATE CERTIFICATE [Managed Instance FQDN] 
FROM BINARY = 0x[binarycertificate]
GO

認証証明書公開キーの取得 : SQL Server で実行

USE [master]
GO
SELECT CERTENCODED(CERT_ID('Cert_MSSQL_endpoint'))
GO

実行時に出力されたバイナリデータは次のステップで使用する

証明書の登録 : Cloud Shell で実行して Managed Instance に登録

New-AzSqlInstanceServerTrustCertificate -ResourceGroupName '<Resource Group Name>' -InstanceName '<MI Instance Name>' -CertificateName 'SqlServerCert_MSSQL_endpoint' -PublicKey '0x[binarycertificate]'

可用性グループの登録 : SQL Server で実行

USE [master]
GO
CREATE AVAILABILITY GROUP [<Availaiblity Group Name>]
WITH (CLUSTER_TYPE = NONE)
FOR DATABASE [Database Name]
REPLICA ON N'<Local Server Name>' WITH (ENDPOINT_URL = N'tcp://<Local IP Address>:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));
GO

分散型可用性グループの登録 : SQL Server で実行

USE [master]
GO
CREATE AVAILABILITY GROUP [<DAG name>]
WITH (DISTRIBUTED)
AVAILABILITY GROUP ON
	N'<Availability Group Name>' WITH (LISTENER_URL = N'tcp://<Local IP Address>:5022', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC),
	N'<MI Server Name>' WITH (LISTENER_URL = N'tcp://<MI FQDN>:5022;Server=[<MI Server Name>]', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC);

GO

Managed Instance Link の作成 : Cloud Shell で実行して Managed Instance に登録

New-AzSqlInstanceLink -ResourceGroupName '<Resource Group Name>' -InstanceName '<MI Database Name>' -PrimaryAvailabilityGroupName '<AG Name>' -LinkName '<DAG NAme>' -SecondaryAvailabilityGroupName '<MI Server Name>' -TargetDatabase '<DB Name>' -SourceEndpoint 'TCP://<SQL Server Local IP Address>:5022'

適切に設定ができていれば Managed Instance Link の作成を実行したタイミングでデータベースの同期が開始されます。

まとめ

SQL Server 2016 も 2019 と基本的な設定の流れは同じですが、クラスターレス可用性グループが使用できないことは意識しておく必要がありそうですね。

Thank you for writing this article, much appreciate it. I would like to make a correction, that for SQL Server 2016 customers have to use SSMS 18.12, or higher. (in your article you state 18.11 which does not have support for SQL Server 2016). I hope you can correct this in you article. Thank you. -Dani

Dani Ljepava

7 7月 22 at 22:53

Thank you for your comment.
I have added a note about support in SSMS 18.12 and later.

Masayuki.Ozawa

26 7月 22 at 10:35

SE の雑記

SQL Server 2016 で Managed Instance Link を設定してみる