Managed Instance Link - SQL Server mit Azure SQL verbinden

Einleitung

Die neue Link-Funktion in Azure SQL Managed Instance verbindet unsere lokalen SQL Server mit einer SQL Managed Instance und bietet hybride Flexibilität und Ausfallsicherheit. Wir nutzen hier die Datenreplikation in die Cloud in nahezu Echtzeit und verlagern somit Arbeitslasten auf eine sekundäre Azure-Instanz mit Lesezugriff. Dies erlaubt uns Azure-typische Funktionen, Leistung und Skalierung zu nutzen.

Vorbereitung

SQL Server Version und Availability Group.

Wir prüfen als erstes die installierte Version des SQL-Servers. Wir benötigen mindestens SQL Server 2019 CU15.

SELECT @@Version

Mainzer Datenfabrik - Managed Instance Link - SQL Server mit Azure SQL verbinden

In unserem Fall haben wir das zum Zeitpunkt der Artikel-Erstellung aktuellste CU für SQL Server 2019 installiert, CU16.

Wie eingangs erwähnt ist eine Grundvoraussetzung zur Erstellung des Links, dass die Instanz Teil einer Availability Group ist. Der Vollständigkeit halber überprüfen wir das auch.

declare @IsHadrEnabled sql_variant = (select SERVERPROPERTY('IsHadrEnabled'))
select
@IsHadrEnabled as IsHadrEnabled,
case @IsHadrEnabled
when 0 then 'The Always On availability groups is disabled.'
when 1 then 'The Always On availability groups is enabled.'
else 'Unknown status.'
end as 'HadrStatus'

Database Master Key

Als nächstes erstellen wir einen Database Master Key in der master database. Dieser wird später für ein Zertifikat benötigt, welches der Endpoint benutzen muss.

USE MASTER
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<PASSWORT>'
GO

Mit dem nachfolgenden Code überprüfen wir, ob dies erfolgreich war.

SELECT * FROM sys.symmetric_keys WHERE name LIKE '%DatabaseMasterKey%'

Einschalten der Start Trace Flags

Aus performanceseitigen Erwägungen setzen für die Instanz Start up Trace Flags:

-T1800: Ein Trace Flag, das die Performance optimiert, wenn Transaktionslogdateien der primären und sekundären Replikate innerhalb der Availability Group auf Platten mit unterschiedlichen Sektorengrößen gespeichert werden. Wenn die Platten alle auf eine Sektorengröße von 4K konfiguriert sind (was zu empfehlen ist), kann dieser Parameter wegfallen.

-T9567: Wichtiger Parameter beim automatischen Seeding in der Availability Group, wenn wir mit großen Datenbanken arbeiten. Dieser Parameter aktiviert die Datenkompression beim Seeding. Belastet zusätzlich die CPUs, kann aber die Zeit für die Datenübertragung signifikant verkürzen.

Bitte beachten, dass dies auf allen Knoten durchgeführt werden muss. Ebenso muss der Service der Database Engine neu gestartet werden.

Danach können wir dies noch mit folgenden Statement prüfen.

DBCC TRACESTATUS

Netzwerkkonnektivität

Um unsere Availability Group mit der Managed Instance verbinden zu können muss natürlich die Netzwerkkonnektivität gegeben sein. Bei der Erstellung einer Managed Instance per Assistenten wird in einem geeigneten Subnetz bereits eine Network Security Group erstellt und bei den entsprechenden Regeln (incoming/outgoing) der Port 5022 freigeschaltet. Hier ist dann zu gewährleisten, dass die richtigen Quell- und Zieladressen/-netze hinterlegt sind.

Auf den Knoten der Availability Group ist mindestens die Windows Firewall entsprechend mit folgendem Code zu konfigurieren.

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

Jedes Szenario können wir hier natürlich nicht abdecken - die Kommunikation zwischen Availability Group und Managed Instance findet über den TCP Port 5022 statt. Die Firewalls zwischen den Servern der Availability Group und der Managed Instance in Azure sollten dies bei Freischaltungen widerspiegeln.
In unserem Fallen liegen auch die VMs der Teststellung in Azure, sodass wir lediglich darauf achten müssen, dass die Network Security Groups entsprechend konfiguriert sind.

Test der Konnektivität vom SQL Server zur SQL Managed Instance

Prüfen können wir die Konnektivität mit folgenden PowerShell-Befehl:

tnc <ManagedInstanceFQDN> -port 5022

('tnc' ist übrigens ein Alias für das cmdlet 'Test-NetConnection')

In unserem Fall sieht das so aus (und ist auch erfolgreich).

Test der Konnektivität von der SQL Managed Instance zum SQL Server:

Nun müssen wir auch den umgekehrten Weg testen. Da wir keinen direkten Zugriff auf das OS der SQL Managed Instance haben, werden wir das Powershell-Cmdlet per SQL Server Agent der Managed Instance ausführen lassen.

Da wir in unserer Teststellung eine Availability Group benutzen, besteht auch auf unseren SQL Servern bereits ein dedizierter Endpoint gegen den wir testen können.

Wir testen noch schnell mit Test-NetConnection ob die lokale Erreichbarkeit auch gegeben ist.

Nun erzeugen wir auf der SQL Managed Instance einen SQL Server Agent Job mit der Bezeichnung “NetHelper”.

Hier ist darauf zu achten, dass in der Variable “@SQLServerIpAddress” die IP-Adresse des SQL Servers, gegen den wir testen wollen, als Wert mitgegeben wird. In der Variable “@owner_login_name” muss das SA-Konto unserer Managed Instance stehen.

-- Run on managed instance
-- Create NetHelper Job

BEGIN TRAN
DECLARE @SQLServerIpAddress NVARCHAR(MAX) = '10.5.0.6'
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'mi_sa', @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)'
COMMIT TRAN

Danach starten wir den neu erzeugten Job.

-- Run on managed instance
-- Starts NetHelper Job
EXEC msdb.dbo.sp_start_job @job_name = N'NetHelper'

oder per GUI:

Das Ergebnis können wir dann kurz danach prüfen.

-- Run on managed instance
-- Check result
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'

Und es ist auch positiv.

Recovery Model der Datenbank

Letztlich prüfen wir nun ob die Datenbanken, die repliziert werden sollen, als Recovery Model "Full" gesetzt haben. Da wir ja mit einer Availability Group arbeiten, ist dies sowieso Vorraussetzung. Auch ein Vollbackup der Datenbank sollte angefertigt worden sein.

Nun sind wir endlich mit allen Vorbereitungen und Prüfungen fertig und können uns der eigentlichen Konfiguration des Links widmen.

Etablierung des Managed Instance Link

Erzeugung eines Zertifikats für den HADR Endpoint und Konfiguration des Endpoints auf jeden Knoten der Availability Group

Wir müssen nun die gesamte Kommunikation der Availability Group auf Zertifikatsauthentifizierung umstellen, d.h. die Endpunkte der einzelnen Knoten müssen ausgehende Zertifikate verwenden und auch eingehend die Zertifikate der anderen Knoten akzeptieren.

Dies ist mit einiger Arbeit verbunden. Wir orientieren uns hier an dieser offiziellen Dokumentation: Use certificates for a database mirroring endpoint - SQL Server Database Mirroring

Exemplarisch führen wir das nur für einen Knoten aus - es muss allerdings für jeden Knoten in der Availability Group ausgeführt werden (vor allem bei der eingehenden Kommunikation muss dann darauf geachtet werden, dass die Zertifikate der anderen Knoten akzeptiert werden).

Als erstes erzeugen wir ein Zertifikat auf Basis des vorher erzeugten Master Keys.

-- Run on SQL Server

USE master;
CREATE CERTIFICATE HOST_A_cert
WITH SUBJECT = 'HOST_A certificate for database mirroring',
EXPIRY_DATE = '11/30/2031';
GO

Wir prüfen auch, ob das erfolgreich war.

Als nächstes konfigurieren wir unseren HADR Endpoint so, dass er zur Authentifizierung das Zertifikat verwendet. Dies betrifft die ausgehende Kommunikation.

-- outgoing
-- Configures HADR endpoint for certificate
USE master;
ALTER ENDPOINT Hadr_endpoint
STATE = STARTED
AS TCP (
LISTENER_PORT=5022
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_A_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO

Nun sichern wir das Zertifikat zur Verwendung auf den anderen Knoten der Availability Group.

-- Backup certificate for other nodes
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\install\HOST_A_cert.cer';
GO

Diese Datei wird dann auf die anderen Knoten kopiert. Für die weiteren Schritte gehen wir davon aus, dass die Zertifikate der anderen Knoten auch auf unseren ersten Knoten kopiert wurden.

Nun konfigurieren wir den Knoten so, dass er auch die eingehenden Verbindungen mit Zertifikat der anderen Knoten akzeptiert.

Zuerst erstellen wir einen Login und einen User.

-- incoming
USE master;
--On HOST_A, create a login for HOST_B.
CREATE LOGIN HOST_B_login WITH PASSWORD = 'AStrongPassword!@#';
GO
--Create a user, HOST_A_user, for that login.
CREATE USER HOST_B_user FOR LOGIN HOST_B_login
GO

Danach erzeugen wir ein lokales Zertifikat auf Basis der vorher auf einen anderen Knoten erstellten Sicherung und assoziieren dieses mit dem gerade erstellen User.

--Obtain HOST_B certificate.
--Asscociate this certificate with the user, HOST_B_user.
CREATE CERTIFICATE HOST_B_cert
AUTHORIZATION HOST_B_user
FROM FILE = 'C:\install\HOST_B_cert.cer';
GO

Nun erteilen wir dem Login noch “Connect”-Berechtigung auf den HADR Endpoint des Knotens.

--Grant CONNECT permission for the server instance on HOST_A.
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO HOST_B_login
GO

Anschließend akzeptiert der Knoten eingehende Verbindungen auf dem Endpoint, die sich mit dem entsprechenden Zertifikat authentifizieren.

Dies wiederholen wir nochmal für den zweiten Knoten.

USE master;
--On HOST_A, create a login for HOST_C.
CREATE LOGIN HOST_C_login WITH PASSWORD = 'AStrongPassword!@#';
GO
--Create a user, HOST_C_user, for that login.
CREATE USER HOST_C_user FOR LOGIN HOST_C_login
GO
--Obtain HOST_C certificate. 
--Asscociate this certificate with the user, HOST_A_user.
CREATE CERTIFICATE HOST_C_cert
AUTHORIZATION HOST_C_user
FROM FILE = 'C:\install\HOST_C_cert.cer';
GO
--Grant CONNECT permission for the server instance on HOST_A.
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO HOST_C_login
GO

Wir wiederholen das ganze auf jeden weiteren Knoten der Availability Group - achten Sie darauf, dass sie die richtigen Zertifikate verwenden!

Wenn wir diese Arbeit nicht erledigen und nur auf einen Knoten der Availability Group den Endpoint ausgehend konfigurieren (wie das einige Anleitungen empfehlen), dann hat das zur Folge, dass die interne Replikation der Availability Group nicht mehr funktioniert.

Replikation einer Datenbank / Assistent zur Erstellung eines neuen Managed Instance Link

Über die Option “Azure SQL Managed Instance Link | Replicate Database” im Kontextmenü der Datenbank starten wir den Assistenten zur Erstellung eines neuen Managed Instance Link.

Der Assistent checkt nochmal die Vorraussetzungen für den Link.

Einmal auf Serverseite - hier finden wir einige Einstellungen aus der Vorbereitung wieder.

Und einmal auf Seite der Availability Group. Und hier findet sich die Konfiguration der Zertifikate wieder.

Wir wählen die zu replizierende Datenbank aus.

Auch hier wird vom Assistenten nochmal geprüft, ob alle Vorraussetzungen erfüllt sind (Recovery Model: Full, Vollbackup durchgeführt).

Danach wählen wir unsere SQL Managed Instance.

Wir authentifizieren uns und wählen unsere SQL Managed Instance aus.

Abschließend kommen wir zur Konfiguration der Zertifikate für den Endpoint und der Benennung der DAG.

Bei der Einstellung “SQL Server Certificate” ist beim Wert “Certificate name:” darauf zu achten, dass das verwendete Zertifikat eingetragen wird, sonst bekommen wir eine Fehlermeldung und es geht nicht weiter.

Abschließend bekommen wir eine Zusammenfassung.

Nun ist auch die DAG im SSMS sichtbar.

Und bei einer Verbindung zur SQL Managed Instance können wir sehen, dass die Datenbank repliziert wurde.

Im Dashboard der DAG können wir auch nochmal den Status der Replikate überprüfen.

Fazit

Die Anbindung einer Availability Group an eine SQL Managed Instance per Azure SQL Managed Instance link ist eine neue Möglichkeit, den Workload in die Cloud zu transferieren. Die Konfiguration ist allerdings mit so manchen Fallstrick versehen und man muss zweimal hinschauen, wenn man nicht will, dass man mit Etablierung des Links gleichzeitig die Replikation in der Availability Group verliert.

Wenn Sie Experten suchen, die auch lieber zweimal hinschauen, dann kontaktieren sie uns gerne. Unsere Spezialisten stehen ihnen gerne mit Rat und Tat zur Seite.