SQL Backup auf einer Azure Virtual Machine

Als Database Administrator kennt man das Thema nur zu gut, wenn man sich nicht gleich zu Anfang voll umfänglich mit der Backup bzw Restore-Strategie beschäftigt, fällt es einem irgendwann vor die Füße… aber wie ist das Ganze jetzt in der Cloud, wie kann man da sein Datenbanken richtig sichern? Darum ging es in meinem Vortrag auf dem Global Azure Bootcamp 2019 in Hamburg mit dem Ziel, die neue Möglichkeit mittels Azure Backup auch direkt SQL Server Datenbanken auf einer virtuellen Maschine in Azure zu sichern.

Serverausfall – was nun?

Man stelle sich vor – in der onprem Welt – ein Server fällt aus, die Datenbanken nicht mehr erreichbar… PANIK (zumindest im ersten Moment)… die Fragen, die man sich dann im ersten Moment stellt:

  • Haben wir eine Sicherung?
  • Wann ist die letzte Sicherung erfolgreich gelaufen?
  • Wie groß ist mein Datenverlust?
  • Wie komme ich an die Daten?
  • Wie lange dauert es, bis das richtige Backup für den Restore gefunden wurde?
  • Wie lange dauert der Restore?
  • Klappt der Restore auf Anhieb?
  • Können dann alle wieder auf die Daten zugreifen?

Gehen wir erst einmal von dem Fall aus, dass der Server neu aufgesetzt wird und die Datenbanken dann aus einer Sicherung wieder hergestellt werden müssen. Nun sucht man (vielleicht) über die GUI der Backup-Software nach einer Filesicherung, dem Tape oder einer Datenbank-Sicherung. Spätestens hier wird es interessant 😉

Wie wurde der Datenbank-Server überhaupt gesichert, in welchem Rhythmus, wieviele Files müssen zurück gespielt werden und so weiter… Im schlimmsten Fall gibt es nur eine tägliche Vollsicherung (Full-Backup) auf die lokale Platte mit nur wöchentlicher Filesystemsicherung… 😮
Backup-Lösungen und -Strategien sind aber nicht das Thema dieses Beitrages, denn hier geht es um die Cloud.

Downtime Minimierung durch rechtzeitiges integrieren in die Solution

Azure SQL Databases und das Backup

In der Cloud ist vieles einfacher (oder zumindest anders), denn wie auch on-prem sollte man sich zumindest Gedanken über die Backup-Strategie machen. Aber zumindest hat sich Microsoft sehr viele Gedanken zu diesem Thema für uns gemacht, so dass in vielen Datenbank-Services wie zum Beispiel der Azure SQL Database das Backup schon automatisch inkludiert ist.

Also alle Azure SQL Database Services (SingleDB, Elastic Pool und Managed Instance) haben bereits ein Backup inkludiert, so dass man sich hier nicht wirklich mehr Gedanken zu einer Backup-Strategie oder „Welche Software wollen wir einsetzen?“ machen muss. Beispielsweise Azure SQL Single-Database hat in den unterschiedlichen Performance-Klassen (Basic, Standard, Premium) unterschiedliche Aufbewahrungszeiten von 7-35 Tagen… was aber wenn man das Backup, aus welchen Gründen auch immer, länger aufbewahren möchte/muss? Dann bleibt einem – für diesen Fall – nur das Hinzufügen eines sogenannten „Log-Time-Retention“ Backups, welches das Backup(-File) zusätzlich auf einem weiteren Storage-Account ablegt und erst nach X Tagen/Wochen/Monaten/Jahren löscht, was einem selber überlassen wird über eine entsprechende Policy zu definieren.

Selbst für den Fall, dass mal ein komplettes Microsoft Rechenzentrum oder sogar eine ganze Region ausfallen sollte, hat Microsoft vorgesorgt und verschiebt alle Backups über Geo-Replication auch in andere Regionen, so dass im Fall der Fälle immer ein entsprechendes Backup für einen Point-in-Time-Restore innerhalb der Retention-Period vorhanden ist.

Automatisierte Backups – da war doch schon was

Ja, automatische Sicherungen (Automated Backup) konnte der SQL Server schon seit der Version 2014, wenn man ihn entsprechend konfiguriert hatte, dann wurde alle Datenbanken bereits „damals“ auf einen Azure Storage Account gesichert. Auch mit der Version 2016 und 2017 blieb diese Funktion erhalten, wurde aber angepasst bzw optimiert (Automated Backup v2). Alternativ könnte man sich auch ein Backup-to-URL mittels Ola Hallengren Skripten vorstellen, aber das hängt dann wieder mit der gesamten Backup-Strategie zusammen und muss entsprechend geplant werden.

Enable SQL Server Managed Backup to Microsoft Azure

Gemeinsame Nutzung von Azure Backup ?

Viele Kunden haben in Azure so oder so eine Azure Backup Vault oder den Azure Backup Service laufen, so dass sich diese Kunden gefragt haben, warum sie damit nur Virtuelle Maschinen als „Ganzes“ sichern können, aber nicht auch ihre SQL Server. Somit wäre eigentlich eine zentrale Lösung für viele Backups in Cloud gefunden. Man könne auch von dieser zentralen Stelle im Portal aus, alle Backups administrieren und überwachen, bräuchte kein weiteres Tool geschweige denn eine neue Backup-Infrastruktur.

Microsoft hat auf die Wünsche und Bitten seiner Kunden gehört und das Azure Backup entsprechend erweitert, so dass man seit Mitte März 2019 auch endlich reine SQL Server Datenbank-Backups damit ziehen kann.

https://azure.microsoft.com/de-de/blog/azure-backup-for-sql-server-in-azure-virtual-machines-now-generally-available/

Jetzt kann man also die Azure Backup Extension sowohl auf fertige SQL Server Marketplace VM-Images ausrollen, als auch auf selbst erstellte SQL Server, um diese dann als native SQL Server Backups gegen Azure Backup zu sichern. Derzeit gibt es folgende Eckpunkte, die für diesen Service sprechen:

  • Zentrale Verwaltung und Überwachung
  • Schutz für verschlüsselte Datenbanken
  • Automatische Erkennung von (neuen) Datenbanken
  • RPO-Wert (Recovery Point Objective) von 15 Minuten
  • Point-in-Time-Wiederherstellungen
  • Langfristige Aufbewahrung
  • Kosteneffektiv
Einrichten eines SQL Server Backups über das Portal mit Azure Backup in meiner Demo-Umgebung

Wenn man nun also das native SQL Server Backup über die neue Extension ausgerollt hat, dann kann man während der Konfiguration im Portal entweder den ganzen Server (Instanz) oder einzelne Datenbanken, ebenso „Autoprotection“ auswählen. Und „zusehen“ wie nach erfolgreichem Deployment die ersten Sicherungen durchgeführt werden. Das Ergebnis sowie Alarme können dann sehr schön und zentral über das Azure Backup Dashboard im Portal überwacht werden. Zusätzlich dazu bietet Microsoft eine PowerBi Application mit der man auf einen (dann einzurichtenden StorageAccount) zu aktivieren, um noch mehr Details zu seinen Sicherungen zu erhalten.

Ein Restore der Datenbanken ist mit wenigen Klicks über das Portal zu realisieren, hierbei stehen dann auch weitere Optionen zur Verfügung, wie man sie aus dem normalen SQL-Restore kennt (z.B. File-Relocation, Overwrite oder Database-Rename).

Fazit zu Azure Backup für SQL Server VMs

Ich persönlich finde diese neue Extension super, wenn man seine SQL Server in einer Azure VM nativ sichern möchte/muss und eine (!!!) zentrale Anlaufstelle für Backups in Azure mit Monitoring, Alerting und Config-Management haben möchte!!!

Desired State Configuration #4 – SQL Server Installation und Konfiguration

So endlich ist es soweit… ich habe meine SQL Server Installation mit Powershell Desired State Configuration fertig und möchte euch nun meinen abschließenden Blog-Beitrag präsentieren. Auch wenn noch nicht alles 100% fertig ist und der Ablauf leider noch nicht so ganz so ist, wie ich es mir vorstelle, – es müssen eigentlich noch zu viele Dinge manuell gemacht werden und wie ich mit dem Thema Reboot umgehe ist noch ungeklärt – dennoch möchte ich euch hier meinen Code vorstellen.

Einleitung zur Vorgehensweise

Ich schreibe hier aktuell über Desired State Configuration als Push-Modell, ggfs kommt ein Pull-Modell später noch hinzu. Das heißt ich führe meine Skripte von meiner Workstation aus, die eigentlichen Skripte liegen auf dem Domänen-Controller, alle Server und Workstations sind in einer Domäne und ich führe die Skripte als Domänen-Administrator aus. Noch einmal zur Verdeutlichung meiner Umgebung hier ein Bild von meiner Test-Umgebung:

Powershell Desired State Configuration - Overview - New Environment

Die Verzeichnisstruktur liegt auf dem Domänen-Controller in der allgemein zugänglichen SYSVOL-Freigabe, kann aber natürlich auch auf jeder anderen (zentralen) Freigabe liegen. Hier liegen die Ordner für die notwendigen Powershell-Module und SQL Server Installationsmedien.

  • DC01
    • SYSVOL
      • DSC
        • SQL
          • Updates
        • Modules
        • Scripts
        • Configurations

Auf den Ziel-Servern kann man sich ebenfalls eine frei wählbare Struktur ausdenken, erst einmal geht es aber um das Kopieren von Quell- auf Ziel-Server… die Powershell-Module kopiere ich in das Standard-WindowsPowershell-Verzeichnis ( C:\Windows\system32\WindowsPowerShell\v1.0\Modules) und das SQL Server Setup liegt auf dem D-Laufwerk meiner Azure-Maschinen (D:\SQL2017).

Voraussetzungen für Desired State Configuration

Natürlich müssen auch gewisse Voraussetzungen für die Nutzung von DSC gegeben sein, ich beschränke mich hier auf die rudimentären Themen und verweise ansonsten gerne auf den ersten Teil meiner DSC-Blogpost-Serie, in dem ich auf die Details dazu eingehe. Mir ist im Zuge meiner Recherchen bzw Versuche noch ein Problem untergekommen, auf das ich kurz hinweisen möchte (kann sich aber um ein Problem speziell aus meiner Test-Umgebung handeln)… ich konnte erfolgreich die ersten 3 Server mittels DSC ausrollen, beim vierten allerdings bekam ich immer die Fehlermeldung, dass der Server nicht erreicht werden kann… auf ein Löschen/Re-Deploy des Servers (Azure sei Dank) brachte keine Besserung… hier hatte sich der DNS-Server eine falsche lokale IP-Adresse für den Server „gemerkt“ und konnte somit nicht die Namensauflösung vollbringen. Nachdem ich allerdings auf meiner Workstation den DNS-Cache gelöscht hatte, war es mir möglich auch den vierten SQL Server erfolgreich zu installieren und konfigurieren.

Also nachdem ich nun alle Server entsprechend in der Domäne habe, alle Firewalls und Dienste konfiguriert sind, können wir loslegen mit dem eigentlichen SQL Server Rollout mittels DSC. Entgegen meiner ersten Versuche (siehe Beitrag #2 zu DSC) bin ich jetzt dazu übergegangen alles mit Desired State Configuration zu machen und nur noch wenige Schritte mit reinem Powershell. Aber dazu kommen wir gleich…

In meinem aktuellen Projekt geht es um die regelmäßige Bereitstellung einer SQL Server Umgebung mit AlwaysOn AvailibilityGroups für einige Applikationen OHNE Patching. Das heißt es muss jeden Monat eine neue Umgebung ausgerollt, welche immer den letzten Patch-Level hat. Um dies zu erreichen, habe ich diese Skripte geschrieben. Damit meine Skripte mehrfach bzw modular einsetzbar sind, habe ich die Arbeitsschritte aufgeteilt:

  1. Vorbereitung, Installation und Konfiguration eines SQL Servers (2017 Developer)
  2. Migration der vorhandenen Umgebung auf die neue Umgebung
  3. Vorbereitung, Rollout und Aktivierung der AlwaysOn AvailibilityGroups

Meine Powershell Skripte

Desired-State-Configuration - Meine-Powershell-Skripte

Kurz zur Erläuterung:
Meine Hilfsskripte, welche ich im letzten Blogbeitrag schon erläutert habe

  • Connect Network Share => Erstellt eine Verbindung mit meinem Azure-Fileshare, setzt die PSGallery auf Trusted und „Unblocked“ meine Powershell-Skripte
  • Init_new_Machines => Öffnet die Windows-Firewall für die Netzwerk sowie File und Print-Dienste, formatiert alle Laufwerke, Domänen-Join
  • Remove AG and its secDB => Löscht auf beiden Servern die AGs und auf dem Secondary die verbliebene Datenbank
  • Update Modules => wie der Name sagt, es werden die notwendigen Powershell bzw DSC-Module aktualisiert.
  • 1_SQLServer-Rollout-DSC => Es erfolgt eine Standalone-Installation eines SQL Server 2017 mit Konfiguration nach Best-Practices und Installation der Ola Hallengren Maintenance Skripte
  • 2_Migrate_UserDatabases => Migration aller Datenbank-Objekte mit dbatools
  • 3_Rollout-Start-AvailGroup => Es erfolgt die Vorbereitung, Konfiguration und Start der einzurichtenden Availibilty-Groups mittels DSC und dbatools (noch… eventuell baue ich das auch auf DSC um, aber es ist so schön einfach 😉 )
  • SQLServerRollutwithAG => Ist ein Sammelskript bzw soll ein Sammelskript werden, welches alle Skripte / den Workflow entsprechend steuert
  • SQLServerConfiguration => ein JSON-File, was die Parameter für die Umgebung enthält

Part 1 – Installation und Konfiguration eines SQL Servers mit Desired State Configuration

Ich werde nicht im Detail auf das Skript eingehen und auch nicht alle Code-Zeilen zeigen, sondern „nur“ einen groben Überblick geben, wie ich vorgegangen bin und was ich womit realisiert habe…
Einleitung erfolgt mit der benötigten Powershell-Version, DSC-Modulen und Modulversionen… anschließend die Parameter, die man bei der Nutzung per Kommandozeile eingeben kann/muss.

## At the time of this writing, above modules/version were used. Future versions of this module could have breaking changes and will need to be retested
#Requires -Version 5
#Requires -Modules @{ ModuleName="xPSDesiredStateConfiguration"; ModuleVersion="8.4.0.0" }
#Requires -Modules @{ ModuleName="SqlServerDsc"; ModuleVersion="12.1.0.0" }
#Requires -Modules @{ ModuleName="SecurityPolicyDsc"; ModuleVersion="2.6.0.0" }
#Requires -Modules @{ ModuleName="StorageDSC"; ModuleVersion="4.3.0.0" }
## At the time of this writing, above modules/version were used. Future versions of this module could have breaking changes and will need to be retested

param
(
    # Computer name to install SQL Server On
    [Parameter(Mandatory=$true)]
    [String]
    $ComputerName,

    # Plaintext Password of User who has installation permissons on destination server
    [Parameter(Mandatory = $false)]
    [String]
    $InstallerPwd,

    # Plaintext Password of User who should run the sql server service => e.g. DEV2\sqlserv
    [Parameter(Mandatory = $false)]
    [String]
    $SQLServicePwd,

    # Plaintext Password of User who should run the sql Agent service => e.g. DEV2\sqlserv
    [Parameter(Mandatory = $false)]
    [String]
    $SQLAgentPwd,

    # Plaintext Password of the SA User
    [Parameter(Mandatory = $false)]
    [String]
    $SAPwd,

    # Will it be just for preparation?
    [Parameter(Mandatory=$false)]
    [Switch]
    $Prepare,

    # Will it be just for SQL Server configuration?
    [Parameter(Mandatory=$false)]
    [Switch]
    $Configure
)

Es folgen einige einleitende Zeilen, um die Konfigurationdatei einzulesen und aus JSON umzuwandeln, sowie den „OutputPath“ zu definieren. Der OutputPath ist der Ablage-Ordner für die MOF-Files (also die eigentliche Konfigurationsdatei).

Clear-Host
Write-host "Starting DSC process on"$ComputerName.ToUpper()

## Getting ConfigurationValues from JSON-File
$ConfigurationFile = "SQLServerConfiguration.json"
$Configuration = (Get-Content $ConfigurationFile) -join "`n"  | ConvertFrom-Json

## Defining path to MOF-Files
$OutputPath = $Configuration.General.DSCConfigFolder

Die Parameter werden anschließend überprüft und zu Credentials umgewandelt, damit man später sowohl Username und Passwort getrennt und als Credential verwenden kann. Und schon geht es los mit den ersten DSC-Configurations für das Bereitstellen der notwendigen Powershell-Module, der Installationsmedien und Mounten des ISO-Images . Ich habe aus Debug- und Flexibilitätsgründen mehrere Configurations gebaut um später das Skript für weitere Zwecke (wie man in den Parametern erahnen kann) zu nutzen. Man kann grundsätzlich alles in eine Configuration legen, aber um modular zu bleiben…

Vorbereitungen – Kopieren der Powershell Module und Installationsmedien

Configuration Preparation {
    Import-DscResource -ModuleName PSDesiredStateConfiguration
    
    Node $AllNodes.NodeName {
        LocalConfigurationManager {
            DebugMode = "ForceModuleImport"
            RebootNodeIfNeeded = $true
        }

        File PowershellModules {
            Ensure = 'Present'
            Type = 'Directory'
            SourcePath = $Configuration.InstallationMedia.ModuleSourcePath
            DestinationPath = $Configuration.InstallationMedia.ModuleDestinationPath
            Recurse = $true
        }

        File InstallationFolder {
            Ensure = 'Present'
            Type = 'Directory'
            SourcePath = $Configuration.InstallationMedia.InstallationMediaSourcePath
            DestinationPath = $Configuration.InstallationMedia.InstallationMediaDestinationPath
            Recurse = $true
        }

        WindowsFeature NET-Framework-Core {
            Name = "NET-Framework-Core"
            Ensure = "Present"
            IncludeAllSubFeature = $true
        }
    }
}

Configuration ISOMount {
    Import-DscResource -Module PSDesiredStateConfiguration
    Import-DscResource -ModuleName StorageDsc
    
    Node $AllNodes.NodeName {
        MountImage ISO {
            ImagePath   = $Node.SourcePath
            DriveLetter = 'S'
        }

        WaitForVolume WaitForISO {
            DriveLetter      = 'S'
            RetryIntervalSec = 5
            RetryCount       = 10
        }
    }
}

Wenn also das kopierte ISO-Image am Zielserver gemountet wurde, könnte man – wie ich es im ersten Versuch gemacht habe bzw Chris Lumnah in seinem Blogbeitrag beschrieben hat – mittels Script-Ressource realisiert, könnte man die „setup.exe“ mit der „ConfigurationFile.ini“ aufrufen und eine „unattend-Installation“ durchführen. Ich habe nach zahlreichen Versuchen und einigem Lesen mich für das SQLServerDSC-Modul entschieden, das ist „einfacher“ und nicht ganz so Fehler-anfällig.

Ausführung des eigentlichen SQL Server Setup

Configuration SQLServerSetup {
    Import-DscResource -Module PSDesiredStateConfiguration
    Import-DscResource -Module SqlServerDsc
    Import-DscResource -ModuleName StorageDsc

    Node $AllNodes.NodeName {
        LocalConfigurationManager {
            DebugMode               = "ForceModuleImport"
            RebootNodeIfNeeded      = $true
        }

        WindowsFeature NET-Framework-Core {
            Name                    = "NET-Framework-Core"
            Ensure                  = "Present"
            IncludeAllSubFeature    = $true
        }

        SQLSetup InstallDefaultInstance {
            PsDscRunAsCredential    = $InstallerCredentials
            DependsOn               = '[WindowsFeature]NET-Framework-Core'
            Action                  = "Install"
            UpdateEnabled           = $Configuration.InstallSQL.UpdateEnabled
            UpdateSource            = $Configuration.InstallSQL.UpdateSource
            Features                = $Configuration.InstallSQL.Features
            InstanceName            = $Configuration.InstallSQL.InstanceName
            SQLCollation            = $Configuration.InstallSQL.SQLCollation

            SecurityMode            = $Configuration.InstallSQL.SecurityMode
            SAPwd                   = $SQLSACredentials
            
            AgtSvcAccount           = $SQLServiceCredentials
            SQLSvcAccount           = $SQLServiceCredentials
            #ISSvcAccount           = $Node.ISSvcAccount
            #FTSvcAccount           = $Node.FTSvcAccount
            
            InstallSharedDir        = $Configuration.InstallSQL.InstallSharedDir
            InstallSharedWOWDir     = $Configuration.InstallSQL.InstallSharedWOWDir
            InstanceDir             = $Configuration.InstallSQL.InstanceDir
            InstallSQLDataDir       = $Configuration.InstallSQL.InstallSQLDataDir
            SQLUserDBDir            = $Configuration.InstallSQL.SQLUserDBDir
            SQLUserDBLogDir         = $Configuration.InstallSQL.SQLUserDBLogDir
            SQLTempDBDir            = $Configuration.InstallSQL.SQLTempDBDir
            SQLTempDBLogDir         = $Configuration.InstallSQL.SQLTempDBLogDir
            SQLBackupDir            = $Configuration.InstallSQL.SQLBackupDir

            SourcePath              = 'S:\'
        }
    }
}

Viel brauche ich dazu wohl nicht zu schreiben, da es recht selbst erklärend ist! Weiter geht es mit der eigentlichen Konfiguration des SQL Server nachdem die Installation erfolgreich durchgeführt wurde. Hier richte mich nach den gängigen Best-Practice Empfehlungen der Community zu SQL Servern, wie zum Beispiel „MaxDOP“ und den „Cost Threshold For Parallelism“ oder den maximalen Speicherbedarf des SQL Servers zu begrenzen. Auszugsweise 😉

abschließende Konfiguration des SQL Servers

SqlServerNetwork 'ChangeTcpIpOnDefaultInstance'
        {
            ...
        }
        
        SQLServerMemory SetMAXMemory {
            ...
        }

        SQLServerMaxDop SetMAXDOP {
            ...
        }

        SQLServerConfiguration 'CostThreshold4Parallelism' {
            ...
        }

        UserRightsAssignment PerformVolumeMaintenanceTasks {
            ...
        }

        UserRightsAssignment LockPagesInMemory {
            ...
        }

        SqlWindowsFirewall Create_FirewallRules_For_SQL2017 {
            ...
        }

        SqlServiceAccount SetServiceAccount_User {
            ...
        }

        SqlServiceAccount SetAgentAccount_User {
            ...
        }

Wie man die einzelnen DSC-Ressourcen einsetzt und welche Wert benötigt werden oder nur optional sind, kann man sehr gut in der Microsoft-Dokumentation (nicht alle Examples sind 100%, aber man erhält einen Eindruck) nachlesen. => https://github.com/PowerShell/SqlServerDsc
Nun fehlt nur noch das „Unmount“ des ISO-Images, danach die Schritte „Vorbereitung“, „Installation“ und „Konfiguration“ abgeschlossen. Wie in Desired State Configuration mit Powershell üblich, kommen jetzt am Ende des Skriptes noch die Zuweisungen, „Berechnungen“ und Aufrufe. Beispielsweise für MaxDOP und MaxMemory verwende ich folgende Zeilen:

## Getting CPU-Count and calculate MaxDOP
$CpuCount = 0
$Memory = Get-CimInstance Win32_PhysicalMemory -cn $ComputerName | Measure-Object -Property capacity -Sum 
$MaxMemory = [math]::round((($Memory.Sum * 0.8)/1MB),0)
$CPUCount = (Get-CimInstance Win32_Processor -cn $ComputerName -Property NumberOfLogicalProcessors).NumberOfLogicalProcessors
if ($CPUCount -eq 1) {
    $CPUCount = 1
} elseif ($CPUCount -gt 8) {
    $CPUCount = 8
} else {
    $CPUCount = $CPUCount/2
}

Erstellen der MOF-Datei und Starten der DSC-Konfiguration auf dem Zielserver

Um einen Eindruck davon zu vermitteln, wie ich die einzelnen Schritte der DSC-Installation aufrufe bzw einteile, hier noch ein Beispiel-Abschnitt aus meinem Skript:

if ($Prepare) { 
    $ConfigurationData.AllNodes += @{
        NodeName = $ComputerName
    }
    ## Create MOF-Files
    Preparation -ConfigurationData $ConfigurationData -OutputPath "$OutputPath\Preparation\"
    ## Providing all PowerShell Modules
    Start-DscConfiguration -ComputerName $ComputerName -Path "$OutputPath\Preparation" -Verbose -Wait -Force
}

Im „Prepare“-Schritt benötige ich als Parameter nur den Namen des Zielservers, erstelle mit dem Aufruf des Configuration-Teils, der Übergabe der ConfigurationData und dem „OutputPath“ eine MOF-Datei und meta.MOF-Datei. Diese MOF-Datei kann man separat erstellen, um sie dann als späteren Schritt im Skript oder wie hier direkt nach dem Erstellen auszuführen, theoretisch kann man die MOF-Datei auch mit einem eigenen Skript erstellen und mit einem zweiten Powershell-Skript einlesen und dann auf dem Zielserver ausführen. Ich starte hier die DSC-Konfiguration im direkt Anschluss an das Erstellen gegen den Zielserver aus.

SQL Server Datenbank Wartung mit Ola Hallengren

Als abschließenden Schritt in meiner langen Aufführung möchte ich die Grundlage schaffen, dass Backups und Index-Pflege mit den bekannten Skripten von Ola Hallengren erfolgen können. Da ich auch in weiteren Schritten meines Deployments – Migration und Anlage der Availibility-Groups – auf das Powershell-Community-Modul „dbatools“ nutze, greife auch hier auf die Vereinfachung dafür zurück. Wenn also eine Internet-Verbindung besteht, wird das Skript automatisch heruntergeladen und installiert, wenn nicht wird auf die lokale Kopie zurück gegriffen. (natürlich muss die Datei vorher manuell herunterladen und ablegen)

## Check if Internet is available and download Ola Hallengren Database Maintenance otherwise look into given folder
If (Test-Connection -computer "www.google.com" -count 1 -quiet) {
## Internet-Connection is available
    Write-Host -ForegroundColor Green "$(Get-Date): Connection up!"
    Write-Host "Installing OH-MaintenanceScript from the Internet"
    Install-DbaMaintenanceSolution -SqlInstance $ComputerName -BackupLocation $Configuration.InstallSQL.SQLBACKUPDIR -CleanupTime 167 -ReplaceExisting -InstallJobs
} else {
    Invoke-DbaQuery -SqlInstance $ComputerName -File $Configuration.InstallSQL.InstallationMediaSourcePath"\MaintenanceSolution.sql"
}

In den nächsten Tagen werde ich diese Reihe weiterführen und erzählen wie ich die Migration bewerkstellige und wie das Skript zur Vorbereitung und zum Rollout der Availibility-Groups aussieht. Denn auch hier gibt es das ein oder andere zu beachten.
Für Fragen und/oder Diskussionen rund um das Thema stehe ich gerne zur Verfügung!

„Optimize for ad-hoc workloads“ – warum diesen Parameter aktivieren?

Heute wurde mir die Frage gestellt, ob ich den Parameter „Optimize for ad-hoc workloads“ kenne bzw ob ich wüsste wofür dieser da ist.
Ja, ich kenne diesen Parameter und ich weiß auch, dass wir ihn in unseren Best-Practises aktivieren. Also muss ich mich schon einmal damit beschäftigt haben… scheint aber schon lange her zu sein, denn ich brachte die Fakten einfach nicht mehr zusammen. Dieser Parameter ist auch schon recht alt, in den Dokumentation des SQL Server fand ich den Parameter erstmalig für den SQL Server 2008, da kann man mal die Details „vergessen“.

„Optimize for ad-hoc workloads“

Aber vielleicht sollte ich erst einmal klären, was ad-hoc in diesem Zusammenhang bedeutet… Man findet diesen Begriff in Verbindung mit dem SQL Server relativ häufig, aber macht man sich dann Gedanken was im Detail damit gemeint sein könnte, dabei ist es relativ einfach.

Ad-hoc Queries sind einfache Abfragen, also zum Beispiel Abfragen die ein Benutzer „einfach mal so“ in seinem Abfrage-Tool erstellt und ausführt. Also Abfragen die irgendwie immer anders aussehen, auch wenn sich vielleicht nur Werte oder die Sortierung geändert haben, somit kann der Ausführungsplan für diese Query erst bei Ausführung ermittelt bzw erstellt werden.
Als Gegensatz zu ad-hoc Queries sind Stored-Procedures und Prepared-Statements zu nennen, da die Abfrage bereits zum Zeitpunkt der Erstellung bekannt ist und entsprechend frühzeitig in Ausführungsplan erstellt wurde. Man hat hier eine fest definierte Abfrage/Funktion erstellt, welche nur (grob gesagt) einem verallgemeinerten Zweck dient, um zum Beispiel aus dieser Tabelle eine Auswahl erstellt und an welche nur die ID als Variable übergeben wird. Bei Ausführung der Stored Procedure wird dann immer der bereits bekannte Ausführungsplan herangezogen.

Was passiert bei der Ausführung von ad-hoc Queries?

Jedesmal wenn eine ad-hoc Query ausgeführt wird, sucht der SQL Server (die Datenbank Engine – der Optimizer) in seinem Plan Cache ob es zu dieser Query bereits einen Ausführungsplan gibt. Gibt es keinen gespeicherten Ausführungsplan so wird ein neuer für diese Abfrage berechnet und im Plan Cache gespeichert. Wenn nun eine Applikation immer wieder ein und dieselbe Abfrage ausführt, zum Beispiel für jede Kategorie in einem Kategorienbaum alle zugehörigen Produkte sucht:

Select * from Products where category_id = 1 and stock_value > 0
Select * from Products where category_id = 2 and stock_value > 0
Select * from Products where category_id = 3 and stock_value > 0
...

Da die Anfrage immer „anders“ ist, als die vorherige Abfrage, wird für jedes dieser Statements ein eigener Ausführungsplan im Plan Cache erstellt und belegt dort Platz. Im Idealfall kann man die Applikation anpassen und die Verarbeitung anpassen bzw optimieren, was aber tun wenn man dies nicht kann?

Beispiel eines Autoteile-Händlers: Gewisse Produkte gibt es für gewisse Marken => Ermittel alle Teile aller Kategorien für die Marke XYZ.
Jede Ergebniszeile nimmt einen gewissen Bereich im Speicher des SQL Servers ein, bei ~100.000 Kategorien/Teilen pro Automarke…
Irgendwann ist der Plan Cache voll und der SQL Server benötigt neuen Platz. Da der Plan Cache aber begrenzt ist, bleibt nur die Möglichkeit des „Aufräumens“. Aber woran orientiert sich der SQL Server beim Aufräumen im Plan Cache?

Wie optimiert der Parameter die Ausführung der ad-hoc Queries?

Durch die Aktivierung des Parameters „Optimize for ad-hoc workloads“ (von 0 nach 1) ändert sich die Art der Kompilierung eben dieser ad-hoc Queries bzw deren Speicherung im Plan Cache. Während vorher für jede Abfrage ein Ausführungplan kompiliert wurde und anschließend gespeichert wurde, wird jetzt nur noch ein sogenannter Plan Stub gespeichert, dadurch reduziert sich der Speicherbedarf enorm. In Beispielen die ich hierzu gefunden habe, werden Reduzierungen von ~90% erreicht. (Variiert von System/Abfrage – kann mit Statements ermittelt bzw überprüft werden) Diese Einsparung wird dadurch erreicht, dass nur noch ein Hash-Wert für diese Ad-hoc Queries ermittelt / gespeichert wird und kein vollständiger Ausführungsplan. Wird jetzt eine Abfrage ein zweites Mal ausgeführt, so wird auch hier erst einmal nur der Hash-Wert für die Ausführung berechnet und dann im Plan Cache gesucht. Ist ein gesuchter Ausführungsplan bereits vorhanden so wird nun der endgültige Ausführungsplan vollständig kompiliert und gespeichert. Durch diese optimierte Speicherung und Verarbeitung von Abfragen bzw Ausführungsplänen wird a.) Speicher im Plan Cache für relevantere Abfragen frei gehalten und gleichzeitig die Kompellierungslast von den Prozessoren genommen.

Fazit:

Wenn eine (oder mehrere) Applikation immer wieder eine Vielzahl von ad-hoc Queries ausführt, dann sollte man diesen Parameter „Optimize for ad-hoc workloads“ definitiv aktivieren. Im Grunde sollte dieser Parameter bei allen SQL Servern (es gibt auch Ausnahmen 😉 ) aktivieren, da die meisten Applikationen eine Vielzahl von immer wieder minimal andere Abfragen ausführen. Hier unterstützt diese Speicher-Optimierung den SQL Server bzw die Performance erheblich.

Wer sich selber mal ein Bild machen möchte, sollte sich die Abfragen von Kimberly Tripp anschauen, um sich einen Überblick über die jeweilige Auslastung bzw Mengenverhältnisse seines SQL Servers zu machen (Beispiel das Beitragsbild – hier ist der Unterschied zwischen ad-hoc und prepared Statements nicht so sonderlich – ich habe aber auch keinen Vorher/Nachher-Vergleich)

SELECT objtype AS [CacheType],
    COUNT_BIG(*) AS [Total Plans],
    SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs],
    AVG(usecounts) AS [Avg Use Count],
    SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes
        ELSE 0
        END) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs – USE Count 1],
    SUM(CASE WHEN usecounts = 1 THEN 1
        ELSE 0
        END) AS [Total Plans – USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs – USE Count 1] DESC
GO

TSQL – wichtige Informationen über die SQL Server Instanz ermitteln

Wir alle kennen es, wir sollen Informationen zu unseren SQL Servern liefern… entweder der Kunde oder ein Kollege will schnell einen Überblick über die wichtigsten Parameter des SQL Servers haben… Was liegt da näher als mal eben schnell ein TSQL-Statement auf dem angefragten Server auszuführen?

Die letzten solcher Anfragen wollte immer die selben Informationen zusammengefasst bzw ermittelt haben… als habe ich mir mittels TSQL ein „kurzes“ Skript zusammengestellt und nach meinen Bedürfnissen angepasst. Ich bzw wir verwenden dieses Skript nun auch für unsere eigene Dokumentation 😉

  • Server- / Instanznamen
  • letzter SQL Server Engine Start
  • welche SQL Server Edition / Version ist im Einsatz
  • Welche Werte für CPU / Hyperthreads / maxDOP werden genutzt
  • Konfiguration der RAM Nutzung
  • Anzahl / Namen / Größen der User-Datenbanken
SET NOCOUNT ON;
Go
-- Setting value of 'show advanced options' to 1
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

-- DECLARATION
SET NOCOUNT ON
DECLARE @date datetime, @start int, @ver varchar(13), @config_value varchar(20), @run_value varchar(20), @sqlstart datetime, @Mem int, @optimal_maxdop int, @cpu_count int, @scheduler_count int, @hyperthread_ratio int
DECLARE @CM Table ( [Index] int, Name nvarchar(1000) NOT NULL, Internal_Value int, Character_Value nvarchar(1000) )

-- SET Current Date/Time
SELECT @date = getdate()

-- Get SQL Server Version
SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2005',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,29,13))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2008',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,35,12))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2008 R2',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,30,12))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2012',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,29,12))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2014',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,29,12))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2016',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,29,12))

-- Get Informations and Calculation on MaxDOP
CREATE TABLE #MDP (
name nvarchar(1000),
minimun int NOT NULL,
maximun int NOT NULL,
config_value int NOT NULL,
run_value int NOT NULL
)
Insert into #MDP exec sp_configure 'max degree of parallelism'
SELECT @config_value=rtrim(convert(varchar(8),config_value)) ,@run_value=rtrim(convert(varchar(8),run_value)) from #MDP
DROP TABLE #MDP

-- Last SQL Server Start Date/Time
select @sqlstart = create_date from sys.databases where name = 'Tempdb'

-- Get Informations on CPU, Schedulers and Memory
Insert into @CM exec xp_msver select @Mem = Internal_Value from @CM Where Name = 'PhysicalMemory'
select
@scheduler_count=(SELECT count(*) FROM sys.dm_os_schedulers WHERE scheduler_id < 255) ,@cpu_count=cpu_count ,@hyperthread_ratio=hyperthread_ratio ,@optimal_maxdop=case when @scheduler_count >= 8 then 4
when @scheduler_count > 8 then 8
else CEILING(@scheduler_count*.5) end
from sys.dm_os_sys_info;

-- Main Statement
SELECT
CONVERT(CHAR(50), SERVERPROPERTY('MachineName')) AS [Hostname]
,isnull(CONVERT(CHAR(50), SERVERPROPERTY('InstanceName')), 'Default') [InstanceName]
,@@servername as [Servername]
,getdate() as [Current Date/Time]
,@sqlstart as [last SQL Srv Start]
,serverproperty('Edition') as [SQL Edition]
,@ver as [SQL Version]
,case serverproperty('IsClustered') when 0 THEN 'NO' when 1 THEN 'YES' end as [IsCluster]
,@cpu_count/@hyperthread_ratio as [CPU Count]
,@config_value as [MDP cfg]
,@run_value as [MDP run]
,(SELECT count(*) FROM sys.dm_os_schedulers WHERE scheduler_id < 255) as [No of Scheduler] ,@optimal_maxdop as [Optimal MDP] ,@Mem as [Memory (MB)] ,(SELECT value_in_use FROM sys.configurations WHERE name like 'min server memory (MB)') as [actual min memory] ,(SELECT value_in_use FROM sys.configurations WHERE name like 'max server memory (MB)') as [actual max memory] ,(select COUNT(name) AS MB from master..sysdatabases) AS [No Of DBs] ,(select SUM(size*8/1024) AS MB from master..sysaltfiles where fileid = 1 and dbid > 4) AS [Overall Database Size (MB)]
Go

-- Adding Informations for all User Databases
-- Declaration
DECLARE @SumDBSize VARCHAR(2)
DECLARE @temp INT
DECLARE @DBSize INT
DECLARE @Servername varchar(100)

-- Get/Set Servername
Set @Servername = ''
If @Servername = '' Set @Servername = @@SERVERNAME

-- Calculating DBSize
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[master].[dbo].[DatabaseFiles]'))
Set @temp = (SELECT round(SUM(db.size)/1024/1024,0) FROM [master].[dbo].[DatabaseFiles] as db where DATEDIFF(day, db.CreationDate, GETDATE()) = 0)
else Set @temp = (SELECT sum((size*8)/1024/1024 ) FROM sys.master_files)
Set @temp = (SELECT sum((size*8)/1024/1024 ) FROM sys.master_files)

Select
@Servername as 'Servername',
DB_NAME(sys.master_files.database_id) as 'DBName',
sum(sys.master_files.size * 8 / 1024 ) as 'DB-Size/MB',
sum(sys.master_files.size * 8 / 1024 / 1024 ) as 'DB-Size/GB'
from sys.master_files INNER JOIN sys.databases ON DB_NAME(sys.master_files.database_id) = DB_NAME(sys.databases.database_id) where DB_NAME(sys.master_files.database_id) not in ('master','model','msdb','tempdb') group by sys.master_files.database_id;
go

-- Reset value of 'show advanced options' to 0
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

Vielen Dank an  für seinen Beitrag zu „Best Practices Empfehlungen für MaxDOP

Dieses TSQL kann natürlich jederzeit frei genutzt werden, vorab in einem Testsystem getestet werden, Gefahr/Risiko trägt der Ausführende.

SQLBits 2016 – die UK SQL Server Community lädt ein

Mittlerweile zum 15. Mal lädt die britische SQL Server Community zum SQLBits ein!

Alles begann mit einer kleinen Gruppe von Personen, die sich gerne mit dem SQL Server auseinander setzten und andere an ihrem Wissen teilhaben lassen wollten. Was lag da näher als einen Community Event sondergleichen ins Leben zu rufen…
Mittlerweile hat sich herum gesprochen, dass die SQLBits ganz besondere Tage mit ganz besonderen Sprechern und Inhalten sind, Sprecher aus der ganzen Welt kommen nach England, um Ihr Wissen und Ihre Erkenntnisse mit der Community zu teilen.

Es gibt wieder 4 verschiedene Tracks (DEV, DBA, BI und Azure) aus denen man wählen kann, so dass jeder sich genau seinen SQL Server Schwerpunkt vertiefen kann. Innerhalb dieser Tracks gibt es zahlreiche interessante Sessions im Rahmen der SQLBits.

Zum Beispiel :

Advanced Techniques For Super Scaling SQL Server with Chris Adkin

Sie als DBA oder Entwickler arbeiten an Anwendungen mit anspruchsvoll, unternehmenskritischen Leistungsanforderungen, dass sie mindestens ein ein Level 400 – 500 haben und so ein tieferes Verständnis für die Datenbank-Engine, um auch noch das letzte Quäntchen Leistung aus der Datenbank-Engine heraus zu holen. In dieser Session erhalten sie hierzu einen 360-Grad-Einblick in alle wichtigen Themengebiete, was die Datenbank-Engine tut, damit sie in Zukunft alle „schweren“ Probleme welche durch undokumentierte Waits und Spinlock-Aktivitäten auftreten können, in welche man mit den Standard SQL Server Tools kaum oder gar nicht Einsicht erhält. Sie lernen Techniken zur Durchführung tieferer Analysen der CPU-Auslastung kennen, Wait-Stats Analysen auf Thread-Ebene, ebenso die Fehlersuche nach schlechte Gerätetreiber bis hin zu Probleme im Netzwerk und/oder Storage-Bereich, alles über das Windows Performance Toolkit.

Increase Server Performance Step by Step - SQLBits 2016
Erfahren Sie wie man den Leistungsanforderungen von anspruchsvollen und kritischen Applikationen durch Skalierung begegnen kann. Session mit Chris Adkin auf der SQLBits

New features of SQL Server Reporting Services 2016 with Chris Testa-O’Neill

Sie haben Jahre lang auf Verbesserungen im Reporting Services gewartet, um noch bessere Berichte für Ihre Business-Anwender zu erstellen und liefern. Jetzt brauchen Sie nicht mehr zu warten, endlich ist es soweit, endlich kommen die neuesten Updates für Reporting Services im SQL Server 2016. Lassen Sie sich von Chris Testa-O’Neill in die neuen Funktionen des Reporting Services einweisen, in diesem eintägigen Workshop lernen Sie:

  • Microsoft Reporting-Strategie – Verstehen, wo Reporting Services passt in diese Strategie im Jahr 2016
  • Bericht Entwicklung Merkmale – Entdecken Sie die neuen Möglichkeiten für die Erstellung von Berichten
  • Abonnement Verbesserungen – Haben Sie eine bessere Kontrolle über das, was zu Ihren Benutzern
  • POWER Bi-Integration – Nahtlose Integration Ihrer SSRS Berichte mit Power BI-Berichte
  • SSRS und Datazen – Sehen Sie, wie Datazen vervollständigt das Reporting-Strategie

Weitere kleinere neue Features werden ebenfalls in diesem Workshop angesprochen, so dass sie sich ein vollständiges Bild aller neuen Features machen können.

Diese Session richtet sich an Berichtsentwickler, Berichterstattern und Berater, welche großen Berichte rechtzeitig für ihre Nutzer liefern möchten.

New Features in Reporting Services 2016 - SQLBits 2016
Session zu neuen Features in SQL Server Reporting Services 2016 mit Chris Testa-O’Neill auf der SQLBits

Columnstore Indexes – from basics to optimised analytics with Niko Neugebauer

Erfahren sie innerhalb nur eines Tages alles über die Grundlagen der Struktur der Columns Indizes sowie einen Schritt in die internen Details. Weiterhin werden Sie lernen, wie man Daten in eben die Columnstore Tabellen lädt und am Ende dieses Tages bringt ihnen Niko Neugebauer die fortgeschrittenen Konzepte des BatchModes und des Performance-Tuning näher.

Microsoft hatte bereits die Columns Indizes mit Nonclustered Columns im SQL Server 2012 eingeführt und SQL Server 2014 brachte Mircorsoft aktualisierbare Clustered Columns Indizes, nun mit SQL Server 2016 kommen zwei neue Bereiche mit den aktualisierbaren Nonclustered Columnstore Indexen – Operational Analytics und Operational Analytics In Memory hinzu.

Das erste Update bezieht sich dabei auf die traditionelle zeilenbasierte Speicherung, während sich Operational Analytics In Memory auf die Integration der InMemory Technologien des SQL Server (auch als Hekaton bekannt) konzentriert.

Bei diesem Tages-Training dreht sich alles um die Unterschiede in der Implementierungen, deren Vorteile und Grenzen und wie man das Beste aus allen Arten erhält und Ausprägungen der Columns Indizes.

A Complex Table with Foreing Keys and Clustered Columnstore by Niko Neugebauer - SQLBits 2016
A Complex Table with Foreing Keys and Clustered Columnstore by Niko Neugebauer, auf den SQLBits erklärt Niko alles rund um den Columnstore.

So gibt es noch zahlreiche weitere Sessions in den einzelnen Tracks, wo man sich kaum entscheiden kann, welcher Session man folgen möchte.
Ich wünsche allen Teilnehmern der SQLBITS 2016 ganz viel Spaß auf der Veranstaltung und lernt fleißig.