Hochverfügbarkeit beim SQL Server – DBCC 2021 – Teil 1

Jeder meiner Kunden hat sich irgendwann mal die Frage gestellt oder zumindest sollte er sich diese Frage stellen, “brauche ich für meine SQL Server oder einen meiner SQL Server eine gewisse Hochverfügbarkeit”. Leider enden diese Überlegungen immer bei der Frage nach dem Budget… natürlich kosten solche HA-Lösungen mehr Geld, je nach Auswahl der Lösung und der Ausstattung der Umgebung müssen entsprechende Lizenzen zur Verfügung gestellt werden. Aber wenn man die im “worst-case” anfallenden Kosten während oder durch einen Ausfall dagegen rechnet, sollte man ganz klar zur Einsicht kommen, dass diese Kosten vertretbar sind.

Ich durfte heute auf der Data Blaster Community Conference 2021 einen Vortrag dazu halten (Vielen Dank liebe SQLPASS Deutschland), hier kommt eine kleine Zusammenfassung aus meinem Vortrag.

Ich habe direkt am Anfang dargestellt, wie hoch solche Kosten sein können, hierzu habe ich mit Hilfe eines Online-Rechners von Percona eine beispielhafte Rechnung aufgemacht. Der SQL Server fällt für 1-2 Stunden aus, 5 Mitarbeiter sind an dem Restore beschöftigt, jeder von ihnen verdient 100.000 pro Jahr, 100 weitere Mitarbeiter können nicht richtig arbeiten, diese verdienen durchschnittlich 50.000 pro Jahr. Im folgenden Screenshot könnt ihr die Zahlen lesen, die unter Umständen während des Ausfalls, bei der Behebung und deren Nachwirkungen entstehen können.

Warum SQLServer Hochverfuegbarkeit sich rechnet - Percona Calculator

Bei Gesamtkosten von rund 8.000.000 Euro für einen Ausfall eines SQL Servers von rund 2 Stunden, würde ich mir schon vorher überlegen, ob ich z.B. 250.000 Euro im Rahmen der Installation mehr ausgebe, um einen zweiten Server hinzustellen und mit den jeweiligen Lizenzen auszustatten. Aber wie können solche Hochverfügbarkeiten im SQL Server Umfeld aussehen, dazu komme ich in den nächsten Abschnitten, aber erst einmal sollten wir die Überschriften klären… 😉

Hochverfügbarkeit vs Desaster Recovery

Die Definition von Hochverfügbarkeit auf wikipedia.de lautet wie folgt :

Hochverfügbarkeit (englisch high availabilityHA) bezeichnet die Fähigkeit eines Systems, trotz Ausfalls einer seiner Komponenten mit einer hohen Wahrscheinlichkeit (oft 99,99 % oder besser) den Betrieb zu gewährleisten. In Abgrenzung zur Fehlertoleranz kann es bei dem Betrieb im Fehlerfall zu einer Unterbrechung kommen.

https://de.wikipedia.org/wiki/Hochverf%C3%BCgbarkeit

Im Vergleich dazu die Definition von Desaster Recovery

Der englische Begriff Disaster Recovery (im Deutschen auch Katastrophenwiederherstellung oder Notfallwiederherstellung genannt) bezeichnet Maßnahmen, die nach einem Ausfall von Komponenten in der Informationstechnik eingeleitet werden. Dazu zählt sowohl die Datenwiederherstellung als auch das Ersetzen nicht mehr benutzbarer InfrastrukturHardware und Organisation. Umfassender als Disaster Recovery ist der Begriff Business Continuity, der nicht die Wiederherstellung der IT-Dienste, sondern unterbrechungsfreie Geschäftsabläufe in den Vordergrund stellt.

https://de.wikipedia.org/wiki/Disaster_Recovery

Bei der Hochverfügbarkeit geht es eben mehr darum, die eintretende Zeit des Ausfalls, also die Downtime möglichst gering zu halten, so dass alle angeschlossenen Systeme möglichst ohne Datenverlust und manuellen Eingriff schnellstmöglich weiterlaufen. Hierzu sollte man sich vor der Installation natürlich Gedanken zur Prozessgestaltung, den zu erreichenden Zielen und der Notwendigkeit zu machen.

  • Was braucht das Business?
  • Was kostet es das Business wenn sie nicht mehr arbeiten können?
  • Gibt es ggfs einen Kompromiss zwischen Technik und Business? Müssen es wirklich die minimalsten Ausfallzeiten (< 10 Sekunden) sein oder reichen vielleicht auch maximale Ausfallzeiten von kleiner einer Minute?
  • Was kann die Technik überhaupt abbilden? Wissen, Prozesse u.ä.
  • Welceh rechtlichen Rahmen müssen ggfs eingehalten werden?

Und erst wenn man diese Fragen vorher geklärt hat, kann man sich Gedanken über die eigentliche Hochverfügbarkeitslösung machen und wie diese implementiert werden kann/soll.

Das AlwaysOn Failover-Cluster (FCI)

Hochverfuegbarkeit - SQLServer - AlwaysOn Failover Cluster

Kommen wir zu einer der möglichen Lösungen zur Realisierung der Hochverfügbarkeit des SQL Servers innerhalb des eigenen Rechenzentrums, das Windows Failover Cluster (im Grunde identisch zu einem Linux Failover Cluster, hier sollte man sich an die Angaben/Dokumentation der jeweiligen Distribution und eingesetzten Cluster-Software halten).

Initial erstellt man aus zwei Windows Servern mit dem zusätzlich installierten Failover-Cluster Feature ein Windows Failover Cluster, grob gesagt, man verbindet beide Server logisch (und im AD) miteinander. Beide kennen sich und wissen nun, dass sie zusammengehören und tauschen mehrere entsprechenden Health-Status Informationen untereinander aus. Dieses Windows Failover Cluster erhält eine eigene IP und einen eigenen DNS Namen, sowie ein Cluster-Named-Object im AD, dieses CNO “steuert” später das Cluster im AD.

Zu den notwendigen Storage-Requirements kann ich hier wenig sagen, da dies von den jeweiligen Umgebungen abhängt… SAN, NAS, NFS, HCI oder ähnliches, auf jeden Fall muss es sich um einen Storage handeln, der in der Lage ist beiden Servern Zugriff zu gewähren. Microsoft schreibt dazu folgendes:

Storage

Im Gegensatz zur Verfügbarkeitsgruppe muss eine FCI freigegebenen Speicher zwischen allen Knoten der FCI für Datenbank und Protokolle verwenden. Der freigegebene Speicher kann die Form von WSFC-Clusterdatenträgern, direkten Speicherplätzen (Storage Spaces Direct, S2D), Datenträgern auf einem SAN oder Dateifreigaben auf einem SMB aufweisen. Auf diese Weise verfügen alle Knoten in der FCI immer dann über die gleiche Sicht der Instanzdaten, wenn ein Failover auftritt. Dies bedeutet jedoch, dass der freigegebene Speicher das Potenzial hat, die einzelne Fehlerquelle zu sein. Die FCI hängt zudem von der zugrunde liegenden Speicherlösung ab, um Datenschutz sicherzustellen.

https://docs.microsoft.com/de-de/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sql-server?view=sql-server-ver15&WT.mc_id=DP-MVP-5002576#Recommendations

Nun kann man mit der eigentlichen Installation des SQL Servers beginnen, auf dem Node 1 wird die Basis-Installation für das SQL Server Failover Cluster geschaffen “Erstellung eines Failover-Clusters”, während auf dem Node 2 “nur” ein Knoten zu einem bestehenden SQL Server Cluster hinzugefügt wird.

Je nach Ausgestaltung dieses Cluster – nur eine Instanz oder mehrere Instanzen – müssen SQL Server Lizenzen beschafft werden. Hierbei kann man zwischen zwei Betriebsmodis unterscheiden, aktiv/passiv oder aktiv/aktiv. Bei aktiv/passiv dürfen die SQL Server Instanzen immer nur auf einem Knoten des Clusters laufen, im Fehlerfall für einen begrenzten Zeitraum auch auf beiden Knoten. Bei aaktiv/aktiv können beide Knoten gleichermaßen und zeitlich unbegrenzt voll genutzt werden, was gerade bei mehreren Instanzen unter Umständen Sinn machen kann, da man so eine gewisse Last-Aufteilung vornehmen kann.

Sollte nun ein Knoten im Cluster ausfallen, so kann der zweite Knoten vereinfacht gesagt nicht mehr der anderen Seite kommunizieren und versucht schnellstmöglich die ausgefallenen Services auf seine Seite zu holen und dort zu starten.

Ein hybrides Szenario ist hierbei leider nicht möglich, da es keine Möglichkeit der gesharten Ressourcen zwischen on-premise und Cloud gibt. Aber man kann alternativ auch ein Failover-Cluster in der Azure Cloud aufbauen. Hierzu benötigt man eine Proximity Placement Group, entsprechende Managed Disks mit aktiviertem “Shared Storage” Feature und die beschriebenen zwei virtuellen Maschinen, die eigentliche Installation und Betrieb ist im Grunde identisch.

Hochverfügbarkeit Azure SQLServer Failover Cluster - DBCC2021

Mehr zu Hochverfügbarkeiten des SQL Servers kommt demnächst in einem zweiten Beitrag.

TempDB-Performance-Tuning out of the box – T-SQL Tuesday #87

TempDB-Performance-Tuning out of the box - T-SQL Tuesday #87

T-SQL Tuesday ist eine wiederkehrende Blog-Serie, die von Adam Machanic (b | t) gestartet wurde, jeden Monat ist ein Blogger Gastgeber für ein Thema rund um den SQL Server und jeder kann einen Blogbeitrag zu diesem bestimmten Thema schreiben.

Diesen Monat ist Matt Gordon (b | t) unser Host und das Thema geht um neue Features im SQL Server (ab der Version 2014) welche alte Problemstellungen beheben, bekannt gegeben in seinem Announcement-Post.

Mein Thema für diesen TSQL2sDay ist die neue Möglickeit zur Konfiguration der TempDB im Rahmen der SQL Server Installation.

Umsetzung der Best-Practise für die TempDB vereinfacht

Bisher musste man nach erfolgter Installation des SQL Server die Best-Practise Empfehlungen von Microsoft mehr oder weniger aufwändig konfigurieren. Entweder man ist auf herkömmliche Art und Weise an das Thema heran gegangen und hat die Werte manuell über das SQL Servermanagement Studio eingestellt oder man hatte es sich einfacher gemacht, dieses entweder per T-SQL oder Powershell Skript automatisiert. Aber dennoch musste man jedesmal diese Konfigurationsanpassungen nachträglich hinzufügen, um eine optimierte Performance der TempDB zu erreichen.

Die grundsätzliche Performance erhält die TempDB im Grunde zwar über die Hardware-Ausstattung, hier die physikalische Trennung zwischen User-Datenbanken und TempDB und die Verlagerung der TempDB auf hochperformanten Storage. Microsoft empfiehlt die strikte Trennung von UserDB und TempDB nicht nur durch getrennte Ordner-Strukturen oder getrennte Partitionen, es sollen eigene (schnelle) Platten sein. Hier muss auch keine großartige RAID-Konfiguration vorliegen, ein RAID 1 oder RAID 10 wären toll, aber keine wirkliche Notwendigkeit, denn die TempDB wird beim Neustarten des SQL Servers immer wieder “neu” erstellt, somit wäre es kein “Drama” wenn sie korrput wäre oder die drunterliegenden Platten crashen würden. (Ok, mal von dem Verlust von Daten und Zeit abgesehen).

Überblick über die Best-Practise Änderungen

Soviele Anpassungen sind gar nicht notwendig um eine bessere Performance der TempDB zu erreichen, diese möchte ich hier kurz zusammenfassen:

  • physikalische Trennung von User-Datenbanken und TempDB
  • Aufsplittung der TempDB in einzelne Datenfiles gem. Prozessor-Anzahl
  • Autogrowth-Parameter Anpassungen
  • Trace-Flag je nach Verwendung/Bedarf

Wenn man nun diese physikalische Optimierung im Storage-Bereich realsiert hat, kann man sich an die logischen Anpassungen in der Konfiguration machen. Je nach Prozessor-/Kernzahl empfiehlt Microsoft anstatt eines großen Datenfiles die Aufsplittung in mehrere getrennte Datenfiles, um eine Optimierung der IO-Streams umzusetzen (Parallelisierung). Ebenso kann man – je nach Anwendungsgebiet bzw Bedarfs – nun die Datenfiles initial auf eine feste Größe festlegen, damit der interne Aufbau der Datendateien optimiert wird und alle Dateien gleich groß sind. (ähnl. Traceflag 1117/1118). Anderweitig sollte man den Autogrowth-Parameter auf 10% setzen, so dass die Datendateien in Verbindung mit den Traceflag 1117 und 1118 zu einem optimierten, gleichmäßigen Aufbau der TempDB beitragen.

SQLServer2016 - Configure TempDB during Installation

SQL Server 2016 bringt integrierte Vereinfachungen

Der SQL Server 2016 bringt nun diese ganzen logischen Optimierung gleich im Rahmen der Installation mit… man kann also ohne Umschweife dem SQL Server mitteilen, wie man die Anzahl und Aufteilung der TempDB-Datenfiles sowie deren Autogrowth-Parameter setzen möchte. Microsoft hat endlich die lange publizierten Best-Practise Empfehlungen in den Installationsprozess integriert. Somit muss man eigentlich nur noch die Hardware-Vorraussetzungen für die TempDB schaffen und kann anfangen zu installieren.
Ok, auch hier muss man sich natürlich Gedanken machen, welche Werte man wie setzt, aber zumindest muss man sich über diese Konfigurationen hinterher keine Gedanken machen!

VIELEN DANK an Microsoft für diese Verbesserung.

Man kann man während des Installationsprozesses einstellen, wieviele Datenfiles man anlegen möchte, dazu die initiale Größe und auch den Autogrowth-Parameter festlegen. Theoretisch könnte man sogar für jedes einzelne Datenfile (Aufteilung anhand der Anzahl der Kerne) einen eigenen Datenpfad oder sogar eine eigene (SSD-)Platte spendieren. Alles in einem übersichtlichen Screen dargestellt und auf für den unerfahrenen DBA einsetzbar.

Durch dieses neue “Toy” konnten einige “Problems” reduziert werden… 😉

ServicePack 1 für SQL Server 2016 released – Enterprise meets Standard

Gestern Vormittag (Ortszeit New York) bzw Nachmittag bei uns wurden viele neue Themen im Rahmen der Keynote vom Microsoft Event “Connect(); // 2016” vorgestellt, der wichtigste Punkt aus meiner Sicht sind die Neuerungen aus dem Servicepack 1 für den SQL Server 2016. Bisher waren einige Features nur in der Enterprise Edition des SQL Servers verfügbar, jetzt eröffnet Microsoft seinen Kunden die Möglichkeiten eben diese Features auch in der Standard Edition zu nutzen.

Features in SQL Server 2016 RTM

Das ServicePack 1 des SQL Server 2016 beinhaltet alle Fixes bis zum Cumulative Update 3 und dem Security Update MS16–136.

Folgende Features wurden (zusätzlich) im ServicePack 1 als Verbesserungen eingeführt:

  • Row-level Security
  • Dynamic Data Masking
  • Change Data Capture
  • Database Snapshot
  • Columnstore
  • Partitioning
  • Compression
  • InMemory OLTP
  • Always Encrypted
  • PolyBase
  • Fine grained auditing
  • Multiple filestream containers
  • Database Cloning
  • CREATE OR ALTER
  • USE HINT – als zusätzliche Abfrage Option
  • TempDB – Verbesserung in der Erkennbarkeit im Errorlog
  • und zahlreiche Verbesserungen in DMVs und XEs

All the newly introduced Trace flags with SQL Server 2016 SP1 are documented and can be found at http://aka.ms/traceflags.

Features in SQL Server 2016 ServicePack 1

Einige Einschränkungen gibt es noch in den einzelnen Features in den kleineren Editionen, wie zum Beispiel die Speichergrenzen für InMemory OLTP – Details zu den teilweise recht geringen Größen finden Sie hier.

Fazit:
Nun lohnt sich der Wechsel auf den SQL Server 2016 erst recht, wenn auch erst ab dem ServicePack 1… UND man braucht nicht mehr unbedingt eine Enterprise-Edition um zum Beispiel Compression und Partitioning einsetzen zu können.

VIELEN DANK an Microsoft für diesen großartigen Schritt!

#3.1 Update zu meinem Powershell Skript “Öffnen von Firewall Port”

Nachdem ich meinen Beitrag zum Thema “Öffnen der Firewall Ports mit Powershell” Ende Oktober veröffentlicht hatte, habe ich mein Skript nochmals überarbeitet. Der SQL Server und seine Features wie z.B. die Analysis Services haben eigene Ports, welche auch nur geöffnet werden müssen, wenn die jeweiligen Features installiert sind.

Grundlage für meine weiteren Versuche ist der MSDN-Beitrag zu diesem Thema und der eigene Wunsch nach mehr Flexibilität beim Erstellen von Regeln.

Flexibilität durch eigene Powershell-Funktion “GET SQLServices for Firewall”

Ich habe also meine bisherige Funktion aufgesplittet, so dass ich nicht mehr wie bisher stur (per Copy&Paste) die Regel erstelle. Jetzt habe ich eine Funktion gebaut, welche alle relevanten SQL Server Dienste ermittelt, um diese dann individuell freizuschalten. Also mein erster Schritt ist die Analyse der SQL Server Installation:

function GET_SQLServices_for_Firewall($SQLServerTCPPort) {
    # TCP = 6, UDP = 17

    Write-Host "Opening Firewall ports for this Instance"
    # General Ports
    OpenFirewallPorts 4022 6 "SQL Service Broker"
    OpenFirewallPorts 1434 17 "SQL Browser"

    $Services=get-wmiobject -class win32_service | where {$_.DisplayName -like '*SQL*'} | select-object DisplayName 
    foreach ( $service in $Services ) {
        
        # DB-Engine Ports
        if ($Service.DisplayName -like '*SQL Server (*') { 
            OpenFirewallPorts $SQLServerTCPPort 6 "SQL Server"
        }

        # SSAS Ports
        if ($Service.DisplayName -like '*Analysis Services (*') {
            OpenFirewallPorts 2383 6 "SQL - Analysis Services"
            OpenFirewallPorts 2382 6 "SQL - Analysis Services Browserservice"
        }
    }
}

Wie man nun erkennen kann, rufe ich nun eine weitere Funktion auf mit der ich die Verwaltung der Windows Firewall übernehme. Die eigentlichen Teile meines bisherigen Skriptes habe ich somit relativ unverändert gelassen, nur “eingedampft” und variabler gestaltet.
Ich rufe also die eigentliche Arbeitsfunktion mit entsprechenden Parametern auf, um den jeweiligen Port, das Protokoll und eine sprechende Beschreibung zu übermitteln.

function OpenFirewallPorts ([int]$Port, [int]$Protocol, [string]$FirewallRuleDescsription) {
    
    if ($Protocol -eq 6) {$Protocol_String = "TCP"}
    if ($Protocol -eq 17) {$Protocol_String = "UDP"}

    Try {
	Write-Host "Opening Firewall on $Protocol_String Port $Port" 
	$port1 = New-Object -ComObject HNetCfg.FWOpenPort
	$port1.Port = $SQLServerTCPPort
	$port1.Name = $FirewallRuleDescsription + "(" + $Protocol_String + " " + $Port + ") " + $InstanceName 
	$port1.Enabled = $true
	$port1.Protocol = $Protocol
	$fwMgr = New-Object -ComObject HNetCfg.FwMgr
	$profiledomain=$fwMgr.LocalPolicy.GetProfileByType(0)
	$profiledomain.GloballyOpenPorts.Add($port1)
        Write-Host "[INFO] Successfully opened Firewall on $Protocol_String Port $Port." -ForegroundColor Green
	} 
    Catch { 
        Write-Host "[ERROR] Opening Firewall on $Protocol_String Port $Port failed." -ForegroundColor Red 
    }
}

Für mich und meine Zwecke funktioniert das soweit ganz gut und ist natürlich bei Bedarf individuell anpassbar. Wenn man als Beispiel einen weiteren Listener im SQL Server konfiguriert, dann könnte man dies ebenfalls ermitteln und in der Windows Firewall freischalten. Grob => Invoke-SQLcmd “Get Listener Port” => OpenFirewallPorts newListenerPortNumber 6 “Additional SQL Server Listener”

Für mich bedeutet das Niederschreiben und Erläutern der einzelnen Schritte und Veränderungen auch einen Lernprozess. Wenn jemand Anmerkungen oder Verbesserungen für mich hat, freue ich mich sehr darüber. “Please share your knowledge” 😉

Installing old SQL Server Versions – T-SQL Tuesday #83

T-SQL Tuesday #82 – To the cloud… And beyond!!!

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (b | t). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month Andy Mallon is our TSQL2sDay host and the subject is “We’re still dealing with the same problems”.

Yes, of cause I know such problems too! Let me tell you my story about customers, 3rd party companies and their solutions.

Their solution vs. my recommendation vs the real world

Due to the contracts between “us” and the customer we’re responsible for their SQL Servers… very old ones, middle-aged ones and newer ones. So if there is any problem with any database, user or what else they’ll contact “me”. So I’ve got a big interest in building up stable and long running SQL Servers according to best practices.

Some of might know that I’m working for a big outsourcing company and many of our customer don’t have a IT team anymore. So what do they do if they wanna have a new application for their business cases… correct they asking a third party company to help them… ok, better than doing it themself. But what happens next ???

Due the internal structure of our company the customer doesn’t get directly in contact with me or my team, they asking their Service Manager for assistance… what do they do? There are four options

  1. asking a solution architect for a structured solution
  2. calling me or my team for assistance in building a stable solution
  3. doing it themself
  4. asking the customer or his 3rd party to send a recommendation for SQL Server setup

our prepared solution architects

They’re doing a great job and have very much knowledge and trying to get the best result to fulfill everyone needs. Nearly all solution architects at my company are knowing how to setup a stable basic SQL Server, if they’re not sure at all they will ask one of the DBAs. There is also a regular communication round where we talk to each other about new features, new ideas and so on… So I can rely on what they do and how they build solutions.

the lonesome rider

Several Service Manager of my company think they are able to do such solution design on their own… mostly they’re on the easiest path and accept every requested solution from the customer or 3rd party. But with that solution we’re not going live… 😉 So we’ll have to take several rounds of discussions… very annoying.

calling the specialists team

After several annoying years with lots of solution discussions we’ve build up a “shopping cart” where everyone can click through the most relevant features and informations needed for a stable SQL Server Installation. All these items and recommendations are based on the general available standards and best-practices. So I can guarantee that this sql server will fit nearly all application requirements and provides a stable, performant SQL Server.

warenkorb

The most annoying to me are always the requirements from third-party service providers and our own service managers that we should still install SQL Server 2008R2. That version is very aged and the newer version had so much improvements according to performance and stability.

So why shouldn’t my or their customers benefit from these improvements ???