Always On Availability Group einrichten

Letzte Aktualisierung am 11.03.2023, 22:03:36 Uhr

Mit Always On Availability Groups (AOAG) hat Microsoft im SQL-Server eine Technik bereitgestellt, um Datenbanken redundant auszulegen. Damit können (un)gewollte Ausfälle oder Wartungsarbeiten am SQL-Server miniert werden. So können problemlos Microsoft Updates auf einem Server in der AOAG durchgeführt werden und die bereitgestellten Datenbanken sind weiterhin für die Anwender nutzbar. Die Technik basiert auf der Windows Server Feature „Failover-Clustering“ und dem SQL-Server Feature „Datenspiegelung“. Die AOAG ist Bestandteil der SQL-Server Enterprise Edition.

Quelle: Differences between availability modes for an Always On availability group

Ab SQL-Server 2016 hat Microsoft Basic Availability Groups (BAG) in das Produkt implementiert. Dabei handelt es sich sozusagen um die Light Variante der AOAG. Das Besondere daran ist, dass dies bereits Bestandteil der SQL-Server Standard Edition ist. Somit ist es für viele Kunden eine interessante und vor allem bezahlbare Option. Es gibt natürlich gegenüber der AOAG einige technische Einschränken, die Microsoft hier dokumentiert hat.

Technisch gesehen speichert jeder SQL-Server, in einer AOAG/BAG eine vollständige Kopie der Datenbank. Das hat den großen Vorteil, dass kein Shared Storage notwendig ist welches redundant ausgelegt ist. Anderenfalls wird der Single Point of Failture von der Anwendung (SQL-Server) auf das Shared Storage verschoben. Beim Erstellen einer AOAG/BAG wird ein Computerkonto im Active Directory angelegt, eine dedizierte Listener (IP-Adresse/Port) erstellt und ein DNS-Namen gegeben. Dieser routet die Datenpakete an den jeweiligen aktiven SQL-Server weiter. Kommt es zu einem Ausfall des primären SQL Servers, übernimmt nach 1-3 Sekunden der bisherige sekundär SQL-Server. Dadurch wird dieser zum primären SQL-Server.

Nachfolgend beschreibe ich die Einrichtung einer BAG unter SQL-Server 2019 Standard.

Voraussetzungen

Es ist später für jeden Cluster unter Windows Server und jede BAG im SQL-Server eine weitere IP-Adresse notwendig. Dies ist evtl. für die Planungen relevant, falls die IP-Adressen für den jeweiligen Zweck zusammenhängend sein sollen.

Vorbereitungen auf dem Domain Controller

Active Directory

In meiner Testumgebung gibt es bereits eine Domäne mit dem Namen lab03.wydler.eu (NetBIOS: lab03) und besteht aus einem Domain Controller (DC) mit dem Namen dc01. Die beiden Windows Servern sind Mitglied dieser Domäne.

Für den SQL-Server Cluster ist es empfehlenswert entsprechende Strukturen im „Active Directory-Benutzer und -Computer“ anzulegen. So können später die notwendigen Berechtigungen als auch Gruppenrichtlinien spezifisch problemlos zugeordnet werden.

New-ADOrganizationalUnit -Name "Gruppen" -Path "dc=lab03,dc=wydler,dc=eu"
New-ADOrganizationalUnit -Name "Managed Service Accounts" -Path "ou=Gruppen,dc=lab03,dc=wydler,dc=eu"
New-ADOrganizationalUnit -Name "Fileshares" -Path "ou=Gruppen,dc=lab03,dc=wydler,dc=eu"

New-ADOrganizationalUnit -Name "Server 2019" -Path "dc=lab03,dc=wydler,dc=eu"
New-ADOrganizationalUnit -Name "SQL Server" -Path "ou=Server 2019,dc=lab03,dc=wydler,dc=eu"
New-ADOrganizationalUnit -Name "sqlcl01" -Path "ou=SQL Server,ou=Server 2019,dc=lab03,dc=wydler,dc=eu"

Das Ergebnis der Befehle erzeugt folgende Struktur:

Die Computerkonten sql01a und sql01b habe ich anschließend in die OU sqlcl01 verschoben.

Nach der Installation des SQL-Servers läuft der Windows Dienst der Instanz unter einem Systemkonto. Dies ist schon alleine aus Aspekten der Sicherheit heute nicht mehr zeitgemäß. Bei dedizierten SQL-Server (Standalone) greift man auf Managed Service Accounts (MSA). MSAs können allerdings nicht für Windows Server Failover Cluster bzw. SQL Server BAGs genutzt werden. In diesem Fall muss auf group Managed Service Accounts (gMSA) zurückgegriffen werden.

Überprüfen, ob es bereits einen Key im Key Distribution Center (KDC) existiert:

Get-KdsRootKey

Erscheint keine Ausgabe, existiert in der Domäne noch keinen Key (=Schlüssel).

Dieser Key kann wie folgt angelegt werden:

Add-KdsRootKey -EffectiveImmediately

Nachdem ausführen des Befehls kann es bis zu 10 Stunden dauern, bis dieser aktiv ist. Erst dann können auch gMSAs im Active Directory angelegt werden. Dies entsprechend bei der Ablaufplanung berücksichtigten. Vor den weiteren Schritten nochmals prüfen, ob der Key existiert:

PS C:\Users\Administrator> Get-KdsRootKey

AttributeOfWrongFormat :
KeyValue               : {167, 223, 144, 21...}
EffectiveTime          : 13.04.2020 03:10:43
CreationTime           : 13.04.2020 13:10:43
IsFormatValid          : True
DomainController       : CN=DC01,OU=Domain Controllers,DC=lab03,DC=wydler,DC=eu
ServerConfiguration    : Microsoft.KeyDistributionService.Cmdlets.KdsServerConfiguration
KeyId                  : cd5c2bc2-a4da-2689-a985-d87433953a7a
VersionNumber          : 1

Gruppenrichtlinien

Die Kommunikation der SQL Server für die BAG erfolgt standardmäßig über den Port 5022 (Endpoint). Die dafür notwendigen Regeln in der Windows Defender Firewall werden nicht automatisch angelegt. Daher bietet sich an eine Gruppenrichtlinie (GPO) dafür zu konfigurieren. Diese kann problemlos mit der OU verknüpft werden.

Die Gruppenrichtlinie heißt in meinen Fall „gpo-server-win2019-sqlcl01-computer-windows-defender-firewall“ und ist mit der OU „OU=sqlcl01,OU=SQL Server,OU=Server 2019,DC=lab03,DC=wydler,DC=eu“ verknüpft.

Folgende eingehenden Regeln habe ich in der genannten GPO hinzugefügt:

Nachstehend habe ich die einzelnen Schritte der beiden Regeln dokumentiert.

Microsoft SQL-Server – Standardinstanz (TCP)

Microsoft SQL-Server – Datenbankspiegelung (TCP)

Wer es auf die Spitze treiben möchte, kann innerhalb der Regel noch die IP-Adressen angegeben:

Somit ist eine Kommunikation ausschließlich von den definierten IP-Adressen möglich.

Erstellen des Managed Service Accounts

Zuerst wird die notwendige Sicherheitsgruppe angelegt:

New-ADGroup -Name gg-gmsa-sqlcl01 -Description "Sicherheitsgruppe für Computer des gmsa-sqlcl01" -GroupCategory Security -GroupScope Global -Path "ou=Managed Service Accounts,ou=Gruppen,dc=lab03,dc=wydler,dc=eu"

Anschließend die Computerobjekte sql01a und sql01b als Mitglied in die Gruppe aufnehmen:

Add-ADGroupMember -Identity gg-gmsa-sqlcl01 -Members sql01a$, sql01b$

Danach die Mitgliedschaft überprüfen:

PS C:\Users\Administrator> Get-ADGroupMember -Identity gg-gmsa-sqlcl01

distinguishedName : CN=sql01a,OU=sqlcl01,OU=SQL Server,OU=Server 2019,DC=lab03,DC=wydler,DC=eu
name              : sql01a
objectClass       : computer
objectGUID        : f07fd571-62e2-465f-b8a6-f73a3b8fd5fa
SamAccountName    : SQL01A$
SID               : S-1-5-21-1751175101-161647773-2013570167-1104

distinguishedName : CN=sql01b,OU=sqlcl01,OU=SQL Server,OU=Server 2019,DC=lab03,DC=wydler,DC=eu
name              : sql01b
objectClass       : computer
objectGUID        : a03acb15-0733-4bb0-8b2b-b80d879dad71
SamAccountName    : SQL01B$
SID               : S-1-5-21-1751175101-161647773-2013570167-1105

In diesen Fall ist alles in Ordnung.

Nun kann der gMSA angelegt werden:

New-ADServiceAccount -Name gmsa-sqlcl01 -PrincipalsAllowedToRetrieveManagedPassword gg-gmsa-sqlcl01 -Enabled:$true -DNSHostName gmsa-sqlcl01.lab03.wydler.eu -SamAccountName gmsa-sqlcl01 -ManagedPasswordIntervalInDays 30 -Description "MSA für die Computer sql01a und sql01b"

Im Active Directory-Benutzer und -Computer sollte der gMSA auch sichtbar sein:

Vorbereitungen auf den SQL Servern

Installieren des gMSA

Für die Installation des gMSA ist es leider unumgänglich (temporär) das Modul PowerShell für Active Directory zu installieren.

Add-WindowsFeature RSAT-AD-PowerShell -Restart

Nach dem Neustart kann endlich der gMSA installiert werden.

Install-ADServiceAccount -Identity gmsa-sqlcl01

Natürlich nicht vergessen die ausgeführte Operation zu überprüfen.

Test-ADServiceAccount -Identity gmsa-sqlcl01

Einrichten des Failover Cluster

Zuerst wird das Feature „Failover-Clustering“ inkl. den dazugehörigen Management installiert.

Install-WindowsFeature -Name Failover-Clustering –IncludeManagementTools -Restart

Es ist egal auf welchem SQL Server die Failover Cluster mit Hilfe des Assistenten eingerichtet wird.

In Active Directory-Benutzer und -Computer sollte unter „OU=sqlcl01,OU=SQL Server,OU=Server 2019,DC=lab03,DC=wydler,DC=eu“ ein neues Computerobjekt mit dem Namen „sqlcl01“ auftauchen.

Im DNS Server wird automatisch ein A- und PTR-Eintrag für den Computernamen und IP-Adresse erzeugt.

Das Computerkonto des erstellten Failover Clusters „sqlcl01“ benötigt weitere Rechte im Active Directory. Grund dafür ist, dass beim Erstellen von BAGs jeweils ein eigenes Computerkonto generiert wird. Dieses wird in derselben OU erstellt, in dem sich auch das Computerkonto „sqlcl01“ befindet.

und DNS Server. Anderenfalls könne für die BAGs im SQL Server keine weiteren Computerkonten und DNS-Einträge erstellt werden. Und war benötigt

FileShare Witness – Konfiguration

Bekanntlich ist für ein Windows Server Failover Clustering (WSFC) ein Windows File Share Witness (FSW) sinnvoll. Es handelt sich bei FSW um eine Datei,  welche bei einem Ausfall eines Knoten im WSFC eine weitere Quorum Stimme darstellt. Damit ist der Weiterbetrieb  des WSFC grundsätzlich sichergestellt.

In Produktivumgebungen bietet es sich an, die erweitere Freigabe auf einem Dateiserver abzulegen, welcher hochverfügbar ist. Denn so sind die Ausfallzeiten miniert. Im Labor lege ich das Verzeichnis/Freigabe auf dem Server „dc01“ ab.

Natürlich werden auch hier die Konzepte der Berechtigungen beachtet. D.h. bedeutet, es werden entsprechende Gruppen im Active Directory angelegt und konfiguriert. Somit kann die Berechtigungen jederzeit flexibel und einfach angepasst werden.

New-ADGroup -Name "lg-fs-sqlcl01-fa" -Description "Sicherheitsgruppe für den Zugriff auf dc01, C:\Quorum\sqlcl01." -GroupCategory Security -GroupScope DomainLocal -Path "ou=Fileshares,ou=Gruppen,dc=lab03,dc=wydler,dc=eu"
New-ADGroup -Name "gg-fs-sqlcl01-fa" -Description "Sicherheitsgruppe für den Zugriff auf dc01, C:\Quorum\sqlcl01." -GroupCategory Security -GroupScope Global -Path "ou=Fileshares,ou=Gruppen,dc=lab03,dc=wydler,dc=eu"

Add-ADGroupMember -Identity "lg-fs-sqlcl01-fa" -Members "gg-fs-sqlcl01-fa"
Add-ADGroupMember -Identity "gg-fs-sqlcl01-fa" -Members "sqlcl01$"

Nun wird das notwendige Verzeichnis erstellt, die NTFS-Berechtigungen angepasst und freigegeben.

New-Item -Path "C:\" -Name "Quorum" -Type Directory
New-Item -Path "C:\Quorum" -Name "sqlcl01" -Type Directory

$Acl = Get-Acl "C:\Quorum\sqlcl01"
$Ar = New-Object system.Security.AccessControl.FileSystemAccessRule("lg-fs-sqlcl01-fa", "Modify", "ContainerInherit, ObjectInherit", "None", "Allow")
$Acl.Setaccessrule($Ar)
Set-Acl "C:\Quorum\sqlcl01" $Acl

New-SmbShare –Name quorum-sqlcl01 –Path "C:\Quorum\sqlcl01" -FullAccess Everyone

Cluster Quorum konfigurieren

Installation des SQL Servers

Nachstehende Schritte müssen für jeden SQL Server im BAG wiederholt werden. Nach dem Start der setup.exe auf dem Installationsmedium geht’s los.

Entsprechende Empfehlungen für den Betrieb eines SQL Servers sind auch in diesen Fall zu beachten. Dazu gehört unter anderem bei Einsatz der Virtualisierung die Datenbanken, Protokolle, Sicherung, etc… auf separate Laufwerke abzulegen.	Die Installation kann je nach Leistung des (virtuellen) Servers einige Minuten in Anspruch nehmen. Daher ist es Zeit für einen Tee. 😉

Vorbereitungen für Basic Availability Groups

Zum einen muss der gMSA für den Windows Dienst „SQL (MSSQLSERVER)“ hinterlegt werden. Zum anderen muss AOAG aktiviert werden. Dazu das SQL Server Konfigurations-Manger über das Startmenü aufrufen.

Basic Availability Group erstellen

Hierfür ist ein Microsoft SQL Server Management Studio (SSMS) erforderlich. Die aktuellste Version der Anwendung kann bei Microsoft kostenlos heruntergeladen werden. Ich habe die Software aus Bequemlichkeit auf beiden SQL Servern installiert. Gerade bei einem Ausfall oder Wartungsarbeiten sicherlich ein Plus Punkt. Nachstehende Interaktionen führe ich auf dem Server „sql01a“ aus.

Zuerst lege ich eine neue Datenbank mit dem Namen „test1“ an.

Im Anschluss erstelle ich eine Sicherung der (leeren) Datenbank. Dies ist leider notwendig, damit für die Datenbank in ein BAG erstellt werden kann.

Nun richten wir eine BAG für die Datenbank „test01“ ein.

Ich nehme als Namen der Name der Datenbank. Somit ist eine eindeutige Zuordnung zwischen BAG und Datenbank problemlos möglich, ohne eine Doku anzulegen.	Wer die Sicherung der Datenbank vorher nicht durchgeführt hat, erhält an der Stelle anderenfalls eine Fehlermeldung. Die Datenbank kann dann auch nicht ausgewählt werden.
Durch das Setzen des Hakens wird automatisch der Verfügbarkeitsmodus auf „Synchroner Commit“ geändert. Lesender Zugriff ist bei BAGs nicht möglich.	Wer die Portnummer an dieser Stelle muss dies natürlich in der Regel für Windows Defender Firewall ebenfalls abändern. Anderenfalls ist eine Replizierung zwischen den Servern möglich. An dieser Stelle sieht man auch nochmals in der letzten Spalte ob meine SQL Server dasselbe Dienstkonto verwenden.
Der DNS-Name muss eindeutig sein. Denn zum einen wird ein Computerkonto dafür im Active Directory angelegt. Zum anderen kann darüber auch zukünftig die Anwendung ihre Verbindungen aufbauen. Falls der Port geändert wird, so ist eine weitere, zusätzliche eigehende Regel für die Windows Defender Firewall notwendig. Dies entsprechend in der obigen Gruppenrichtlinie ergänzen.

Abschließend darf die Qualitätssicherung nicht fehlen. Zuerst verbindet man sich via SSMS auf beide SQL-Server um das Resultat des Assistenten zu überprüfen.

Das ist auf Ebene des SQL Servers sehr gut aus. In diesem Fall ist der Abgleich in wenigen Sekunden erledigt. Was daran liegt, dass die Datenbank leer ist. Führt man dies mit bestehenden Datenbanken mit einer Größe von 10, 30 oder 100GB durch, wird der Vorgang deutlich länger nötigen. Das hängt von den Leistungsfaktoren von Servern, Storage, Netzwerk, etc… ab.

Ein Blick in das Active Directory offenbart, dass auch das Computerkonto „sqlcl01test1“ erfolgreich erstellt wurde.

Zu guter letzt wird noch die Namensauflösung, Ping und eine Verbindungsaufbau mit dem Namen „sqlcl01test1“ getestet. Für letzteres habe ich Telnet nachinstalliert und folgenden Befehl ausgeführt.

telnet sqlcl01test1 1433

(Un)geplanter Failover durchführen

Soweit so gut, aber ein praktischer Test des SQL Clusters darf nicht fehlen. Um einen ungeplanten Ausfall zu testen, reicht es den Windows Server neu starten, der in der BAG als primär Server gekennzeichnet ist. Schaut man nach dem vermeidlichen Neustart wieder ins SSMS sieht man folgende Informationen.

Die beiden Server haben die Rollen getauscht. Sprich der Knoten der ausgefallen ist, wird automatisch zum sekundären Replikat in der BAG. Das heißt, es erfolgt kein automatisch Fallback. Und das ist nach meiner Meinung nach auch so in Ordnung. Wenn ein Server zurück ist, bedeutet dies nicht automatisch, dass dieser wieder voll funktionstüchtig und einsatzbereit ist.

Ein geplanter Failover (z.B. für Wartungsarbeiten) ist natürlich ebenfalls möglich. Dazu im SSMS die entsprechende BAG markieren und den Eintrag Failover auswählen.

Für den eigentlichen Vorgang kommt wieder ein Assistent zum Vorschein, der Schritt-für-Schritt die Daten abfragt.

Aktualisiert man die Ansicht im SSMS im Bereich der BAGs haben die beiden Server den Replikationsstatus wieder getauscht.

Benutzerverwaltung

Windows Authentifizierung

In Arbeit.

SQL Authentifizierung

Nicht immer ist es möglich die Authentifizierung über Windows Anmeldeinformationen (z.B. Windows (Service) Benutzerkonto, (Group) Managed Server Accounts) zu realisieren. In vielen Fällen scheitert es an der eingesetzten Anwendung. Die Entwickler verfolgen oft das Motto „keep it simple. In all diesen Fällen bleibt als letzte Möglichkeit die Verwendung eines SQL Benutzers.

In SQL Server Availability Groups (AGs) müssen alle SQL Benutzerkonten auf allen Knoten, auf denen die BAG Datenbanken verwendet werden, dieselbe Security Identifier (SID) haben.

Ein SQL Benutzerkonto hat einen Datensatz in der Tabelle „sys.database_principals“ in der Datenbank „master“. Diese Tabelle enthält die Sicherheitskonfiguration (Eigentümer, Lesen, Schreiben, Ausführen, etc…) für diese Anmeldung. Die Verknüpfung zwischen diesen beiden Tabellen ist die spalte „SID“.

Wird der Benutzer mit dem SQL Server Management Studio (SSMS) auf allen SQL Servern der AG über den Assistenten erstellt, wir jedes Konto auf jedem Server eine andere SID haben.

USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'!Test1234!', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

SELECT name, sid FROM syslogins WHERE name = 'test';
GO

Ausgabe auf dem sql01a:

Ausgabe auf dem sql01b:

Nach einem Failover der Datenbank von Server sql01a auf sql01b kann/ist die Anwendung nicht mehr entsprechenden Datenbank verbunden. Grund dafür ist, dass Microsoft Produkte nie mit dem Namen des Objekts arbeitet, sondern mit dessen SID. Im Worst Case stürzt die Anwendung einfach ab, weil dieser Fall nicht bei der Entwicklung berücksichtigt wurde.

Nachstehend eine exemplarische Beschreibung für das Anlegen eines SQL Benutzerkontos in einer AG. Zuerst mit dem SQL Server Management Studio (SSMS) auf den primären SQL Node verbinden.

USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'!Test1234!', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

SELECT name, sid FROM syslogins WHERE name = 'test';
GO

Ausgabe auf dem sql01a:

Ich kopiere die SID in ein leeres Notepad. Diese benötigen wir für das Anlegen der Anmeldeinformationen auf allen weiteren SQL Nodes. Dazu die SID mit der linken Maustaste anklicken, so dass das Feld blau markiert wird. Anschließend rechte Maustaste -> Kopieren.

Danach mit dem SSMS mit dem sekundären SQL Note verbinden, um dort ebenfalls den SQL Benutzer anzulegen.

USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'!Test1234!', sid = 0x4CDA8F252A341642AD9675974A7B1264, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

SELECT name, sid FROM syslogins WHERE name = 'test';
GO

Beim Parameter sid die SID des Benutzers test einfügen. Nun kann der Benutzer auf sql01b angelegt werden.

Wie man sieht hat der Benutzer nun dieselbe SID wie auf dem sql01a.

Viel Spaß beim Ausprobieren. 🙂