In diesem Intensivkurs werden für SQL Server 2022 Administratoren nach der Installation die wichtigsten Komponenten behandelt. Dazu gehört die Konfiguration des SQL Servers – über das SQL Server Management Studio sowie den SQL Server Configuration Manager. Die Teilnehmer lernen dabei den sicheren administrativen Umgang mit dem SQL Server, nicht nur per GUI sondern auch per T-SQL. Ein weiteres wichtiges Thema ist neben Administration und Performance Tuning auch die Security des SQL Servers die an Praxisbeispielen erläutert wird.

Als erstes werden die einzelnen Komponenten des Microsoft SQL Servers und ihr Zusammenhang erläutert. Dazu gehört nicht nur die Datenbank-Engine, sondern auch die SQL Server Integration Services, die SQL Server Reporting Services und die SQL Server Analysis Services. Zentral für den Kurs ist jedoch die Datenbank-Engine selbst und deren Administration. Die neuen Features wie Query Store, In-Memory Storage Engine (Hekaton, XTP), Database Scoped Configurations, Delayed Durability so wie Indirect Checkpoints werden behandelt. SQL Server bietet zudem noch weitere neue Features wie Automatic Tuning und Resumable Index Rebuilds. Mittlerweile lassen sich Resumable Index Rebuilds jetzt auch per Database Scoped Configuration festlegen.
Mit Ledger Tables, die auf Blockchain Technoloigie in SQL Server 2022 basieren, ist es möglich sicherzustellen, dass Daten nicht mehr manipuliert werden können.
Vor allem die XTP Engine hat in den letzten Versionen große Änderungen erfahren. Es wird erklärt, was es mit den einzelnen Versionen (Express, Standard, Enterprise, Developer Edition) auf sich hat und wo die Unterschiede liegen.

Im Kurs wird weiterhin gezeigt, wie sich der On-Premise SQL Server mit Azure über Azure Arc verbinden lässt, um die Authentifizierung über Azure und Microsoft Entra am Server durchzuführen. Auf diese Weise ist erstmals eine Authentifizierung an einem SQL Server 2022 per OAuth2 Bearer Token / OpenID Connect möglich, welche auch Multi-Faktor unterstützt. Ebenfalls wird die Authentifizierung aber auch per On-Premise AD über Kerberos durchgeführt, sowie per SQL Login praktiziert. Die Unterschiede, sowie Vor- und Nachteile der verschiedenen Authentifizierungsmethoden werden erläutert.

Mit SQL Server 2022 gibt es einige Optimierungen, welche die Performance des SQL Servers deutlich verbessern. Mit Memory Feedback und MaxDOP Feedback kann der SQL Server mit dem Query Store aus alten Ausführungsplänen lernen und so die Performance des SQL Servers entscheidend verbessern.

Im Kurs wird der SQL Server 2022 unter Windows Server eingesetzt, allerdings lassen sich fast alle Features auch entsprechend unter Linux nutzen. Das Management Studio lässt sich zur Administration der Windows sowie Linux Variante des SQL Servers nutzen.

Um hier die Zusammenhänge zu verstehen, werden die einzelnen Objekte, aus denen sich eine Datenbank zusammensetzt, erklärt. Dabei wird vermittelt, was Tabellen und Views sind, welche Datentypen in Tabellen möglich sind, wozu Schemas dienen, wie Indizes funktionieren und was die Unterschiede zwischen Clustered und Non-Clustered Indizes sind. Hier wird im Detail erklärt wie und wann Indizes per REORGANIZE oder REBUILD defragmentiert werden können und auch der Fill Factor neu eingestellt wird. Seit SQL Server 2017 ist es auch möglich Index Rebuilds zu unterbrechen und zu einem späteren Zeitpunkt fortzusetzen.

Weiterhin werden Stored Procedures und Stored Functions (Table-Valued Stored Functions sowie skalare Stored Functions) behandelt: Einerseits wie diese selbst von einem Administrator geschrieben werden können, aber auch wie System Stored Procedures und Dynamic Management Functions zu verwenden sind, um sich über den Zustand des Systems zu informieren und den Server zu konfigurieren.

Wichtig ist dabei auch, den Zusammenhang zwischen Tabellen über Primary Keys und Foreign Keys zu verstehen. In diesem Zusammenhang wird auch der Sinn von Constraints erklärt. Wichtig ist für Administratoren auch das Wissen über DDL und DML Trigger. All diese Objekte werden mit Blick auf den Administrator erläutert und wie sich derartige Objekte auch für administrative Aufgaben einsetzen lassen. Beispielhaft wird dabei auch jeweils gezeigt, wie T-SQL für die einzelnen Aufgaben verwendet werden kann. In dem Zusammenhang wird erklärt wie JOIN und CROSS APPLY in T-SQL funktionieren und für administrative Zwecke eingesetzt werden. In praxisorientierten Beispielen wird gezeigt wie man mit T-SQL direkt ermitteln kann, ob eingehende Logins für die Authentifizierung das alte NTLM Protokoll oder Kerberos verwenden und wie man eventuelle Probleme durch das Setzen der richtigen Service Principal Names im Active Directory löst.

Danach wird das ACID Modell und Transaktionen speziell für den SQL Server betrachtet. Dabei wird hier auf Details für den administrativen Betrieb eingegangen, z.B. in welchen Situationen SNAPSHOT ISOLATION eingesetzt wird und welche Konsequenzen dies für den SQL Server hat. Auch wird in diesem Zusammenhang die Deadlock Problematik beim pessimistischen Locking Modell betrachtet sowie das Monitoring von möglichen Deadlocks (auch mit XEvents). Administratoren lernen Performanceprobleme durch Deadlocks von anderen Performanceproblemen zu unterscheiden. SQL Server bietet mittlerweile die Möglichkeit auch die Durability mit Delayed Durability anzupassen und so in manchen Situationen deutlich mehr Performance zu erhalten. Gerade mit der neuen XTP Engine des SQL Servers ändert sich hier einiges in Bezug auf die Isolations-Modelle von Transaktionen.

Ein weiterer wichtiger Bestandteil des Kurses ist die Migration von Datenbanken, welche Automatic Checkpoints verwenden auf die neue Methode Indirect Checkpoint. Diese bietet bessere I/O Performance und ist erst seit SQL 2016 standardmäßig in Verwendung. Mit dem SQL Server 2017 wurde das I/O Verhalten von Indirect Checkpoints noch einmal deutlich verbessert und mit SQL Server 2019 sind Indirect Checkpoints jetzt hochskalierbar.

Mit SQL Server 2022 wurde Accelerated Database Recovery (ADR) entscheidend verbessert und unterstützt jetzt mehrere parallel laufende Redo-Threads.

Filegroups stellen eine wichtige administrative Gruppierung für Dateien dar und werden im Detail behandelt. Dabei wird auch auf die neuen Möglichkeiten des SQL Servers rund um Filegroups eingegangen. So gibt es jetzt die Möglichkeit mit AUTOGROW_ALL_FILES auf Filegroup Ebene das Round Robin Prinzip für Files innerhalb einer Filegroup zu deaktivieren und so mehr Performance beim Write-I/O zu erreichen. Im Vergleich dazu werden die möglichen Konfigurationsänderungen per Trace-Flag demonstriert. Im Kurs wird ebenfalls behandelt, wie einer Filegroup weitere Dateien hinzugefügt oder auch entfernt werden. Beim Entfernen von Dateien aus einer Filegroup wird dabei eine Migration aller Daten in andere Dateien der gleichen Filegroup vorgenommen. Weiterhin wird auch demonstriert wie weitere Filegroups angelegt werden und bestehende Indizies in die neue Filegroup migriert werden. Bei der XTP Engine wird es dann wichtig spezielle Memory Optimized Filegroups zu erstellen und für alle Objekte innerhalb der XTP Engine zu verwenden.

Die physikalische Struktur der Datendateien des SQL Servers wird betrachtet, um genau zu verstehen wieso Latches auf PFS (Page Free Space) Datapages dazu führen können, dass die Performance beim SQL Server einbricht und wie sich dieses Problem lösen lässt. Dabei wird auch auf den Zusammenhang von Datapages und Extents eingegangen (sowie den Datenstrukturen IAM, GAM und SGAM) und das Wählen der richtigen Allocation Unit Size im NTFS.
Mit SQL Server 2022 kamen deutliche Verbesserungen hinzu, was die interne Verwaltung von Dateien anbelangt. So wird hier die Performance teilweise deutlich gesteigert und es kommt nicht mehr zu PFS Latch Contention Problemen.

Wichtig aus administrativer Sicht sind die vier Systemdatenbanken master, model, tempdb und msdb. Dabei wird im Kurs betrachtet, was es in Bezug auf diese Systemdatenbanken zu beachten gibt und welche Objekte die einzelnen Systemdatenbanken enthalten und wie eine Backupstrategie für die Systemdatenbanken aussieht.

Ein großer Block beschäftigt sich mit den Dynamic Management Views, Dynamic Management Functions, Catalog Views und System Stored Procedures, um sich per T-SQL genauen Einblick in den SQL Server zu verschaffen. Dabei werden auch Methoden gezeigt, wie man beispielsweise die aktuelle Konfiguration des SQL Servers mit einer historischen Konfiguration per JOIN abgleichen kann und so direkt sehen, ob die Konfiguration geändert wurde.

Der SQL Server wird mittels Database Mail mit einem Exchange Server verbunden. Über spezielle Views in der MSDB Datenbank lässt sich hier Troubleshooting betreiben und direkt sehen, ob einzelne Mails nicht versandt worden sind. Auch sollte beim Versand vieler E-Mails über den SQL Server die MSDB Datenbank bereinigt werden, was im Kurs durchgeführt wird.

Um eine große Anzahl an SQL Server Instanzen zu verwalten, wird im Kurs ein Central Management Server installiert, über den auch demonstriert wird, wie sich Queries zentral eingeben und über alle Server ausführen sowie die Ergebnisse zusammenführen lassen. Der Central Management Server kann auch verwendet werden, um Policies direkt auf mehrere SQL Server auszurollen.

Um eine möglichst hohe Sicherheit für die Service Accounts zu garantieren, wird ein bestehender Service Account mit einem Passwort ausgetauscht gegen einen neuen Group Managed Service Account, der in der Lage ist, automatisch sein Passwort zu wechseln, welches auch von dem SQL Administrator nicht eingesehen werden kann. Per Powershell wird dafür auf einem Domain Controller der Key Distribution Service eingerichtet und danach ein Group Managed Service Account erstellt, den die einzelnen SQL Server Instanzen nutzen können.

Im Kurs wird vermittelt wie der SQL Server zu konfigurieren ist und was dabei besonders beachtet werden muss, um die optimale Performance und Sicherheit des Systems zu erreichen. Dabei wird auf die instanzweite Konfiguration eingegangen und auch Details erläutert, wie das richtige Setzen der maximalen Worker Threads. Zusätzlich wird die Datenbankkonfiguration sowie die neue Database Scoped Configuration betrachtet.

Weiterhin werden Sicherheitskonzepte erklärt und umgesetzt. Dabei wird gezeigt wie Principals, Securables und Permissions zusammenhängen. Dabei fließt direkte Projekterfahrung in den Kurs ein, und mögliche Fallstricke bei der Rechteverwaltung werden behandelt. Ebenfalls wird die Feingranulare Rechteverwaltung mit Views durchgeführt. Entsprechende DMVs zur Rechteverwaltung werden verwendet, um herauszufinden welche SIDs auf Instanz-Ebene mit entsprechenden SIDs auf Datenbank Ebene korrelieren. Die Teilnehmer werden danach auch die zugrundeliegenden Prinzipien wie das Database Ownership Chaining (und in der Konfiguration die Auswirkungen von Cross Database Ownership Chaining) verstehen.

Um effizient zu prüfen, ob Rechte sinnvoll eingesetzt werden oder auch den Server zu überwachen, werden Server- und Datenbank-Audits durchgeführt. Damit die Audits schnell und effektiv laufen, müssen mehrere Dinge beachtet werden. Weiterhin gibt es verschiedene Methoden um die Audit Files auszuwerten: Im Kurs wird dabei auch besprochen, wie dies direkt mit einer Table-Valued Stored Function möglich ist, um auch eine effiziente Suche in sehr großen Audit-Files zu ermöglichen.

Auch die Verschlüsselung von Daten wird durchgeführt per Transparent Data Encryption TDE. Dabei werden auch jeweils die benötigten Zertifikate generiert. Ab SQL Server 2019 ist Transparent Data Encryption erstmals ein Standard Feature und benötigt keine Enterprise Lizenz mehr.

Beim Thema Performance Tuning wird vermittelt, wie man Engpässe findet und löst, um die optimale Performance des MS SQL Servers zu gewährleisten. Mit Kompression ist es oft bereits möglich den I/O Durchsatz deutlich zu erhöhen. Im Kurs wird dafür eine Row-Level sowie Page-Level Compression durchgeführt und die Unterschiede erläutert. Um die Performance einzelner Queries zu steigern, werden Ausführungspläne von Queries betrachtet und der Database Tuning Advisor verwendet, um durch das Setzen der richtigen Indizes vorhandene Queries deutlich zu beschleunigen. Dabei wird auch auf die Vorteile von Included Columns bei Indizes eingegangen.

Mit Hilfe der neuen XTP-Engine (eXtreme Transaction Processing), der In-Memory Storage Technologie von Microsoft, lassen sich fast alle Performance Probleme lösen. Die Unterschiede zur klassischen SQL-Engine werden betrachtet und die neue XTP Engine implementiert. Dabei wird eine eigens dafür vorgesehene MEMORY OPTIMIZED Filegroup erstellt, welche die Daten für die XTP Engine erhält. Für die XTP Engine wird dabei betrachtet, wie die Bucket Counts für die neuen Hash Indizes sinnvoll zu setzen sind, und wie man mit entsprechenden DMVs direkt ermitteln kann, ob die Bucket Counts optimal gesetzt sind, um die beste Performance zu gewährleisten. Bei der XTP Engine sind weiterhin Besonderheiten beim Backup zu beachten, da die XTP Objekte selbst per Filestream angesprochen werden.

Beim Monitoring werden die zentralen Performance Counter gezeigt und erklärt wie diese zu deuten sind. So findet man schnell den entsprechenden Flaschenhals und lernt, wie man den Performance Engpass lösen kann. Für sehr genaues Monitoring lässt sich immer noch der SQL Server Profiler einsetzen. Besser ist es in den meisten Fällen jedoch, mittlerweile auf XEvents (eXtended Events) zu setzen, da diese fast ohne Leistungseinbußen aufgezeichnet werden können. Im Kurs wird anhand von Praxisbeispielen erläutert wie sich XEvents nutzen lassen, um auch in komplexen Fehlerfällen die Ursache zu finden. So lassen sich mit zusätzlichen XML Reports innerhalb von XEvents auch Zusatzinformationen gewinnen, die man auf der Oberfläche des Mangement Studios nicht sieht. Im Kurs wird die Funktionsweise von XEvents auch mit dem alten SQL Profiler verglichen.

Ein wichtiger Teil beim Monitoring und Performance Tuning ist der Query Store, der im SQL Server 2016 neu hinzukam und mit SQL Server 2019 erweitert wurde. Mit dem Query Store, welcher für eine Datenbank erst separat aktiviert werden muss, ist es möglich langsam laufende Queries, bzw. Queries, welche die meisten Ressourcen verbrauchen, zu finden. Besonders interessant ist mit dem Query Store Regressed Queries zu finden. So lassen sich hier Queries aufdecken, die beispielsweise nach einem Upgrade langsamer laufen und danach direkt mit dem Query Store optimieren, in dem über den Query Store vorgegeben wird, welcher Ausführungsplan für die entsprechende Query gewählt wird. Mit dem Query Store ist tiefgreifendes, für die Applikation transparentes, Performance Tuning möglich, wie es bisher nur schwer zu erreichen war. Im Kurs wird das neue Feature Automatic Tuning genutzt. Damit erkennt der SQL Server vollautomatisch, wenn Ausführungspläne gewählt werden, die nicht optimal sind und kann hierbei auf schnellere Ausführungspläne zurückgreifen, die sich im Query Store befinden. Die Performance von SQL Servern kann sich dadurch deutlich verbessern.

Im Bereich Policy-Management wird anhand von Übungen erklärt, wie sich eine Policy umsetzen lässt. Ein Blick auf die Facets ermöglicht ein Gefühl davon, welche Policies umsetzbar sind. Hierbei wird auch besprochen, welche Policies für ein Unternehmen sinnvoll sein könnten. Wichtige Best Practice Policies werden importiert und angewandt. Dabei werden auch Policies demonstriert, welche eigenen T-SQL Code enthalten und diesen per ExecuteSql() ausführen. Dies kann verwendet werden, um Policies zu schreiben, welche weit über die Möglichkeiten der vorhandenen Facets hinausgehen. Die verschiedenen Evaluationsmethoden von Policies werden betrachtet. Wichtig ist dabei auch die Technik dahinter anzusehen, da bei On Change Policies ein instanzweiter Trigger erzeugt wird, welcher beispielsweise nicht mit der XTP Engine kompatibel ist.

Besonderes Augenmerk wird auch auf das Thema Backup & Restore gelegt, sowie auf das Thema Automatisierung. Im Kurs wird auch ein komplettes Disaster Recovery durchgeführt inklusive Point-In-Time Restore. Dabei wird auch ein entsprechendes Tail-Log Backup durchgeführt und gezeigt, wie man die Datenbank vor dem Disaster Recovery in den SINGLE_USER Mode schaltet und nach dem Restore wieder den MULTI_USER Mode aktiviert. Wichtig ist auch zu betrachten, was getan werden kann, wenn die Backup-Kette nicht mehr vollständig ist. Mit den richtigen Techniken ist auch hier oft noch ein Restore möglich. Weiterhin wird das Backup (FULL, DIFFERENTIAL und LOG) mit Hilfe von Wartungsplänen voll automatisiert und es werden Alternativen zur Automatisierung mit Wartungsplänen aufgezeigt. Mit speziellen Views in der msdb Datenbank lässt sich später überwachen, wann die Backups liefen und wie lange sie liefen.

Für die allgemeine Automatisierung wird der SQL Server Agent verwendet. Hier lassen sich auch Warnungen konfigurieren, die dazu führen, dass ein Operator per E-Mail benachrichtigt wird. Im Kurs wird dafür ein Exchange Server mit dem SQL Server verbunden, um Benachrichtigungen für allgemeine Warnungen (mit speziellen Severity Leveln) zu erzeugen oder auch einen Performance Condition Alert festzulegen, der beispielsweise anschlägt, falls eine Datenbank eine gewisse Größe überschreitet. Ein weiteres wichtiges Thema ist die Security des SQL Server Agents. So wird gezeigt, wie man einzelnen Benutzern Rechte auf dem SQL Server Agent einräumt, sodass diese eigene Jobs erstellen können, welche aber jeweils nur mit den Rechten des Erstellers laufen. Weiterhin sollen nicht privilegierte Benutzer nicht die Möglichkeit haben, Jobs anderer Benutzer einzusehen.

Um den SQL Server besser zu automatisieren, ist auch die Powershell notwendig, denn nicht alles lässt sich per T-SQL scripten. Dabei wird betrachtet wie die Powershell verwendet werden kann, um Abläufe nach der Installation zu automatisieren und welche Möglichkeiten das Powershell Modul sqlserver (früher sqlps) bietet. Weiterhin werden die SMO (Sql Server Management Objects) betrachtet, welche weit über die bestehenden Cmdlets des SQL Servers hinausgehen und eine erweiterte Automatisierung aller SQL Abläufe auf Basis der Powershell ermöglichen.

Der Resource Governor lässt sich verwenden, um Monitoring auf Session Ebene zu ermöglichen (hierfür werden eigene Performance Counter zur Verfügung gestellt), aber auch um Performance Tuning oder eine Art QoS, bzw. Resourcenkontrolle zu bieten. Dafür wird eine eigene Classification Function programmiert, mit der ankommende Sessions in entsprechende Workload Groups (welche Resource Pools zugewiesen sind) klassifiziert werden. Weitere Features sind dabei I/O Kontrolle und Hard CPU Caps des Resource Governors. Mit dem JOIN entsprechender DMVs kann man direkt sehen, ob die Klassifizierung der Sessions richtig abläuft. Der Resource Governor ist ein optionales Thema im Kurs.

Ein weiteres wichtiges Thema ist die Migration bestehender SQL Server auf den SQL Server 2022. Hierbei wird eine Side-By-Side Migration durchgeführt und der neue Data Migration Assistent eingesetzt. Es wird gezeigt wie eine Datenbank, die noch im Kompatibilitätsmodus von SQL 2000 steht und auf einem SQL Server 2008 R2 läuft, direkt auf einen neuen SQL Server migriert werden kann. Dabei werden mögliche Probleme vor der Migration analysiert. Bei der Migration wird darauf geachtet, dass auch entsprechende Logins an der Instanz auf den neuen Server umgezogen werden.

Beim Troubleshooting wird die DAC (Dedicated Admin Connection) für Remote Connections aktiviert und verwendet, um auf einem SQL Server, der kaum mehr reagiert, noch einzelne Sessions stoppen zu können. Sollte der SQL Server nicht einmal mehr hochfahren, wird gezeigt wie der Minimal Configuration Mode eingesetzt wird und auf was hier zu achten ist, damit die einzig verbleibende Session in diesem Modus nicht blockiert wird. Zum Troubleshooting gehören auch regelmäßige Integritätschecks der Datenbanken. Sollte es dort zu Problemen kommen, wird auch gezeigt wie ein schneller Page Level Restore eine fehlerhafte Datenbank schnell wieder in einen verwendbaren Zustand überführt.

Nach dem Kurs sind die Teilnehmer in der Lage große SQL Server Farmen zu verwalten und zu automatisieren, sowie den SQL Server aus Security Perspektive richtig abzusichern. Mit dem tiefen Verständnis über den SQL Server können dann auch komplexe Probleme effizient gelöst werden.

» Dieser Kurs wird durch den Kurs Alway On SQL Cluster optimal ergänzt.