dbatools – Migration der SQL Agent Backup Jobs

Man kann denken was man will, aber manchmal muss man über seinen Schatten springen… in der Regel bin ich ein Verfechter des SQL Server eigenen Backups, egal ob Backup to Disc, Backup to URL oder Backup to NetworkShare, aber hier musste ich (leider) nachgeben und die Sicherung mittels 3rd-Party-Tools einrichten. Gesagt… getan…

Bei dem Kunden hatten wir sowieso schon alles auf 3rd-Party-Backup umgestellt, aber nicht auf Backup-Server initiertes Sicherungen, sondern SQL Server initiert. Der SQL Server Agent startet also das jeweilige Backup als Kommandozeilen-Aufruf. Hierzu musste ich also die Sicherungsjobs von einem existierenden Server kopieren und anpassen, sowie alle dazugehörigen SQL Server Objekte. Gestartet habe ich mittels „Create Objects to NewQuery“, erhielt aber die Fehlermeldung, dass die Mail-Komponente bzw Empfänger noch existiert bzw konfiguriert ist. Also musste ich erst die SQL-Mail-Konfiguration übernehmen, hierzu auch noch die Operator übernehmen und wenn wir schon beim Mailing sind, dann kann ich die von Brent Ozar empfohlenen SQL Agent Alerts auch gleich mit migrieren…

dbatools - die Powershell Modulsammlung für den DBA

verwendete Powershell Module – dbatools

Macht euch mal kurz Gedanken dazu, wie man die Konfiguration von SQL-Mail, Operatoren und Custom-Alerts ohne großen Aufwand von einem SQL Server auf den anderen migrieren kann, also ich meine mit Bordmitteln… das ist zwar nicht wirklich viel Aufwand, aber doch etwas mehr als 5 Minuten. Also was liegt näher als sich intensiver mit den dbatools zu beschäftigen…
Mittels „Copy-SqlDatabaseMail“ kann man die SQLMail-Konfiguration übernehmen, mit „Copy-SqlOperator“ die eingerichteten Operatoren migrieren, weiter geht es mit „Copy-SqlAlert“ und zum Schluss noch die Jobs kopieren. Ist doch gar nicht so viel 😉

Copy-SqlDatabaseMail -Source SQLServer01 -Destination SQLServer02
Copy-SqlOperator -Source SQLServer01 -Destination SQLServer02
Copy-SqlAlert -Source SQLServer01 -Destination SQLServer02
Copy-SqlJob -Source SQLServer01 -Destination SQLServer02 -Jobs  Full-Backup, TLog-Backup

Also ganze 4 Zeilen Powershell Code um mir die Arbeit zu erleichtern, auch dank der Vereinheitlichung der Parameter innerhalb von dbatools ist auch die Parametrisierung der einzelnen Befehle eine sehr einfache Sache und schnell und unkompliziert zu erledigen. Man kann die Nutzung von dbatools nur jedem empfehlen!

dbatools – Copy-SqlLogin – Wie man Kunden beeindrucken kann

Für einen Kunden habe ich diese Woche einen neuen SQL Server 2016 SP1 + Management Studio installiert und konfiguriert, alle notwendigen Housekeeping-Jobs eingerichtet und mich um meine dokumentarischen Pflichten gekümmert. Über meine Rückmeldung, dass ich den Server bereits fertig hatte, freute sich mein Kunde und bat mich dann per Mail um folgendes:

[…]
Sent: Wednesday, June 21, 2017 3:43 PM

Außerdem sollten die SQL-User vom SQL01 übernommen werden (wenn möglich).
Geht das?
[…]

Vor zwei Jahren hätte ich wahrscheinlich geantwortet: „Ich versuche es, kann aber für nichts garantieren. Ich melde mich dann in 2 Tagen wenn ich fertig bin…“
Dann hätte ich mich daran gemacht und alle vorhanden Logins und deren Rechte auf Instanz- und Datenbankebene heraus geskriptet, auf dem neuen SQL Server eingespielt und den Kunden gebeten zu testen… (natürlich in der Hoffnung, dass alle Passwörter und Rechte passen) Mein Gott war das immer ein Aufwand, bei zwei oder drei Accounts war das ja gar nicht so das Problem, aber was wenn es plötzlich 100 Accounts sind 😮

Aber da gab es ja die Powershell-Modul-Sammlung von dbatools noch nicht, mit der ich ja bereits großartige Erfahrungen gemacht, schon in einigen Talks darauf hingewiesen und in meinem Blog darauf hingewiesen habe.

In dbatools gibt es eine Funktion „Copy-SqlLogin“, welcher laut Beschreibung alle meine gewünschten Aufgaben in einer Powershell Zeile vereint!

This command migrates logins from source to destination SQL Servers. Supports SQL Server versions 2000 and above. Migrates logins with SIDs, passwords, defaultdb, server roles & securables, database permissions & securables, login attributes (enforce password policy, expiration, etc).
By default, all logins with the exception of system (####Example Login## and local (SERVERNAME\administrators) logins are copied. The -Logins parameter is autopopulated for command-line completion and can be used to copy only specific logins.
If the login already exists on the destination, it will be skipped unless -Force is specified. Force drops and recreates the login.

Da die Anforderung des Kunden lautete „die SQL-User vom SQL01 übernommen werden“, brauchte ich nicht vorher zu analysieren, sondern einfach alle vorhandenen User des alten Servers auf den neuen Server migrieren. Dazu einfach die aktuelle Modul-Sammlung herunterladen, sicherstellen dass auf dem neuen Server auch mindestens Powershell 3 installiert ist. Wenn alles vorhanden ist, müssen die Module nur import werden und schon kann man mit den dbatools loslegen und alle Kommandos auf der Commandline ausführen oder in Scripten verwenden.

Um die Kundenanforderung zu erfüllen, nutze ich also die ganz einfache Kommandozeile:

Import-Module .\dbatools-master\dbatools.psd1
Copy-SqlLogin -Source SQL01 -Destination SQL02

Die Ausführung des Scriptes hat kaum 2 Minuten gedauert und alle User waren migriert!

Durch diese sehr starke Vereinfachung der Account Migration von einem SQL Server zum anderen, konnte ich dem Kunden sehr schnell eine Abschluss-Meldung schicken.

Gesendet: Mittwoch, 21. Juni 2017 15:56
Bitte sehen Sie diesen Task als erledigt an.

Die Antwort meines Kunden kam umgehend 😉

Sent: Wednesday, June 21, 2017 3:59 PM
Danke. Respekt!

(Man muss dazu wissen, dass diese Worte von diesem Kunden(-Mitarbeiter) schon „Gewicht“ haben, da dieser sonst nicht so leicht zu beeindrucken ist, daher freuen mich diese Worte sehr!)

Vielen Dank an Chrissy LeMaire und die dbatools-Community für diese hervoragende Arbeit!

Azure Analysis Services – Powershell Automation im Tagesgeschäft

Im Rahmen des SQLGrillen 2017 hatte ich das Vergnügen mit Gabi Münster einen Vortrag über Azure Analysis Services und deren Automation halten zu dürfen. Diese Inhalte möchte ich euch nicht vorenthalten und im Folgenden näher erläutern, wobei es nicht um die Prozessautomatisierung der Datenbereitstellung geht sondern um die Automatisierung des Deployments bzw des eigentlichen Services, also Möglichkeiten wie man die Services dem Tagesgeschäft entsprechend optimal einsetzen kann. Die hierbei zum Einsatz kommenden Powershell Skripte habe ich versucht so einfach wie möglich zu halten um allen ein Verständnis zu ermöglichen.

Ausgangslage für die Powershell Automation

Sicherlich gibt es zahlreiche Möglichkeiten um die unterschiedlichen Funktionen, Services und VMs in Azure zu deployen oder bearbeiten, ob nun Azure Automation grafisch, als Runbook oder mit der Azure Command Line… ich habe mich als Ausgangslage dafür entschieden, dass die meisten im normalen Tagesgeschäft (meist on-prem) einen Server habe, der die Job-Steuerung übernimmt, dieser dient mir in meinen Beispielen als Ausgangspunkt für alle Skripte. Als weitere Voraussetzung nutze ich eine Active Directory (AD) Applikation und einen Service Principal um mich bzw meine Skripte an Azure anzumelden. Hierbei habe ich mich an die Beschreibung von Christos Matskas gehalten, die recht gut erläutert wie man sich einen Azure Automation Login erstellt.

Azure Analysis Services Automation programmieren

Diesen Login verwende ich nun entweder als Klartext oder als „INCLUDE“ innerhalb meiner Skripte, um das Skript bei jeder Ausführung an Azure anzumelden.

$azureAccountName = "1234567-1234-1234-1234-012345678912" 
$azurePassword = ConvertTo-SecureString "Passwort@2017" -AsPlainText -Force 
$psCred = New-Object System.Management.Automation.PSCredential($azureAccountName, $azurePassword) 
Add-AzureRmAccount -Credential $psCred -ServicePrincipal -TenantId "1234567-1234-1234-1234-012345678912"

Was kann man nun automatisieren?

Mit Powershell kann man natürlich auch das Prozessieren der Datenbanken/Cubes automatisieren, aber darum soll es hier nicht gehen, sondern mehr darum was man als DBA oder DevOps „regelmäßig“ machen kann/muss. Zum Beispiel benötigt man im Laufe des Tages nicht unbedingt die gleiche Leistung wie in der Nacht während der Verarbeitung. On-premise bleibt einem nichts anderes übrig als eine Hardware zu kaufen, die alle möglichen Lastzustände abdecken kann, in Azure ist das ein wenig anders, hier kann man entsprechend skalieren. Was unter Berücksichtgung eines gewissen Kostendrucks zu einem positiven Eindruck im Management führen kann, wenn man diesem erklären kann, dass die Kosten für eben diesen Azure Analysis Service durch Automatisierung variabl gestaltet werden kann. Aber fangen wir einmal vorne an…

Entsprechend der vorhandenen Umgebung oder der Konzeptionierung der Datenaufbereitung kann man sich zwischen horizontaler und vertikaler Skalierung entscheiden, bei der vertikalen Skalierung benötigen wir nur einen Azure Analysis Service, den wir nach Bedarf „größer“ oder „kleiner“ machen können. Bei horizontaler Skalierung könnte man sich zum Beispiel zu bestimmten Zeitpunkten weitere Azure Analysis Services hinzu deployen. Oder wenn man eine Testumgebung nur in bestimmten Zyklen braucht, dann kann man diese eben genauso automatisiert erstellen.

Deployment von Azure Analysis Services

Um den Azure Analysis Service neu oder weitere zu deployen benötigen wir den Powershell Befehl „New-AzureRmAnalysisServicesServer„, mit Anmeldung und weiterer Parametrisierung ergibt sich folgender Ablauf bzw folgendes Script:

  • Anmeldung an Azure
  • Variablen deklarieren (SubscriptionID, ResourceGroupName, AAS-Name, etc)
  • Prüfen ob die Ressource-Gruppe existiert, ggfs erstellen.
  • Prüfen ob der anzulegende Azure Analysis Service bereits existiert, ggfs erstellen
    • Angabe von ResourceGroupName, AAS-Name, Performance-Klasse und der EMail des Administrators
$myResourceGroupName = 'SQLGrillen2017'
$mySubscriptionID = '1234567-1234-1234-1234-012345678912'
$myLocation = 'West Europe'
$myAAServerName = 'asbeer01'

Set-AzureRmContext -SubscriptionId $mySubscriptionID

Get-AzureRmResourceGroup -Name $myResourceGroupName -ev notPresent -ea 0
if ($notPresent) {
    New-AzureRmResourceGroup -Name $myResourceGroupName -Location $myLocation
} else {
    write-host "ResourceGroup already exists"
}

Get-AzureRmAnalysisServicesServer -ResourceGroupName $myResourceGroupName -Name $myAAServerName -ev notPresent -ea 0
if ($notPresent) {
    New-AzureRmAnalysisServicesServer -ResourceGroupName $myResourceGroupName -Name $myAAServerName -Location $myLocation -Sku "S0" -Administrator "your-admin-mailadress@test-url.de"
} else {
    write-host "AAS Server already exists"
}

Die Ausführung dauert nicht wirklich lange, aber ein wenig Geduld muss schon aufbringen. (2-3 Minuten)

Geduld

Wieviel Leistung wann in Azure Analysis Services

Nun gibt es im Tagesverlauf oder Verlauf eines Monats unterschiedliche Last-Situationen, in der Nacht während der Verarbeitung wird mehr Leistung benötigt, als tagsüber wenn die Mitarbeiter nur ihre Reports ziehen oder Analysen machen. Weitere Möglichkeit sind der Monatsende, das Quartalsende oder der Jahresabschluss, hier wird wesentlich mehr Performance gebraucht als an anderen Tagen, wobei man on-Premise diese Kapazität von Anfang an bedenken muss und die Maschine entsprechend im voraus darauf auslegen, damit alle erdenklichen Last-Verhalten abgedeckt werden. Als Azure Analysis Services ist dies nun wesentlich einfacher, denn Azure bietet die Möglichkeit auf relativ einfache Art und Weise die Leistung des Service an die auftretende Last anzupassen. Auch hierzu benötigt man nur den Powershell Befehl „Set-AzureRmAnalysisServicesServer„, mit Anmeldung und weiterer Parametrisierung ergibt sich auch hier folgendes Script:

  • Anmeldung an Azure
  • Variablen deklarieren (SubscriptionID, ResourceGroupName, AAS-Name, etc)
  • Prüfen ob die Ressource-Gruppe existiert, ggfs erstellen.
  • Prüfen ob der anzulegende Azure Analysis Service existiert, um diese hoch oder runter zu skalieren
    • Angabe von ResourceGroupName, AAS-Name, neuer Performance-Klasse
$myResourceGroupName = 'SQLGrillen2017'
$mySubscriptionID = '1234567-1234-1234-1234-012345678912'
$myLocation = 'West Europe'
$myAAServerName = 'asbeer01'

Set-AzureRmContext -SubscriptionId $mySubscriptionID

# Upscale Azure Analysis Services
Get-AzureRmAnalysisServicesServer -ResourceGroupName $myResourceGroupName -Name $myAAServerName -ev notPresent -ea 0
if ($notPresent) {
    write-host "AAS Server does not exists"
} else {
    Set-AzureRmAnalysisServicesServer -Name $myAAServerName -ResourceGroupName $myResourceGroupName -SKU "S4"
}

# Downscale Azure Analysis Services
Get-AzureRmAnalysisServicesServer -ResourceGroupName $myResourceGroupName -Name $myAAServerName -ev notPresent -ea 0
if ($notPresent) {
    write-host "AAS Server does not exists"
} else {
    Set-AzureRmAnalysisServicesServer -Name $myAAServerName -ResourceGroupName $myResourceGroupName -SKU "S2"
}

Über das Skalieren des Azure Analysis Services kann man nun auch die Kosten reduzieren, da in der Nutzungsärmeren Zeit die benötigte Leistungsklasse verwendet wird und nur wenn die Verarbeitung läuft auf ein performanteres PerformanceModel skaliert. Dieses Skalieren funktioniert online, so dass man nicht einmal mit einer Unterbrechung des Services rechnen muss.

Resize Azure Analysis Services Demo

Kann man das auch abschalten?

In manchen Umgebungen und Lösungen ergibt sich unter Umständen auch die Notwendigkeit oder Möglichkeit den Azure Analysis Services komplett „abzuschalten“, entweder man hat horizontal skaliert und benötigt unter Tage kein Service oder die Solution gibt es her, dass nach der Verarbeitung kein Azure Analysis Service mehr benötigt wird. Hierzu bietet die Azure Powershell Automation ebenfalls ein Cmdlet (Suspend/Resume), mit dem man den Service pausieren bzw wieder starten kann. Da man dieses Skript nur einsetzt, wenn ein AAS bereits vorhanden ist, verzichte ich hier auf das Überprüfen der Ressourcen-Gruppe und prüfe nur, ob der Analysis Service mit dem gegebenen Namen tatsächlich vorhanden ist.

$myResourceGroupName = 'SQLGrillen2017'
$mySubscriptionID = '1234567-1234-1234-1234-012345678912'
$myLocation = 'West Europe'
$myAAServerName = 'asbeer01'

Set-AzureRmContext -SubscriptionId $mySubscriptionID

# Pause AAS
Get-AzureRmAnalysisServicesServer -ResourceGroupName $myResourceGroupName -Name $myAAServerName -ev notPresent -ea 0
if ($notPresent) {
    write-host "AAS Server does not exists"
} else {
    Suspend-AzureRmAnalysisServicesServer -Name $myAAServerName -ResourceGroupName $myResourceGroupName
}

# Resume AAS
Get-AzureRmAnalysisServicesServer -ResourceGroupName $myResourceGroupName -Name $myAAServerName -ev notPresent -ea 0
if ($notPresent) {
    write-host "AAS Server does not exists"
} else {
    Resume-AzureRmAnalysisServicesServer -Name $myAAServerName -ResourceGroupName $myResourceGroupName
}

neue Session – Powershell Toolbelt für DBAs – PASS UserGroup Hamburg

Am 13.04.2017 habe ich das Vergnügen erneut vor der Regionalgruppe Hamburg der PASS Deutschland e.V. sprechen zu dürfen. Sascha Lorenz und Cornelia Matthesius veranstalten – wie jeden Monat – wieder ein Treffen in der Hamburger Geschäftsstelle von Microsoft, dieses Mal ist es Mittwoch, der 13. April. Anmeldungen am Besten über die Meetup-Plattform =>  SQL Server Hamburg (by PASS Deutschland e.V.)

Automation makes things easier

Wer kennt es nicht? Immer heißt es „du musst effizienter werden“, aber wie… also noch schneller die Arbeit erledigen, aber wo kann man noch Zeit sparen? Natürlich bei immer wieder kehrenden Aufgaben, hier gibt es zweierlei Ansätze…

  • Tasks automatisieren
  • länger dauernde, komplizierte Aufgaben einfacher machen

Wie man das in Bezug auf die DBA-Tätigkeiten umsetzen kann und welche Tools/Produkte/Skripte dabei zum Einsatz kommen (können), darüber werde ich an diesem Abend erzählen.

Beispiele ?!

  • Wie lange braucht man um einen SQL Server von einer älteren Version auf eine neuere Version anzuheben?
    Natürlich inklusiv der Übernahme aller Logins, aller Jobs, aller Linked Server, aller Alerts und aller Datenbanken etc – 1-2 Tage je nach Umfang der einzelnen Unterpunkte.Was würdet ihr aber sagen, wenn man das auch in ~5 Minuten schaffen kann (abhängig von der Datenbank-Größe) und nur einer (!!) Kommandozeile ???
  • Datenbanken von Server zu Server kopieren, Datenfiles und Datenbank umbenennen und Orphaned-Users bereinigen in drei Zeilen Powershell ???

Nicht möglich ???

Lasst euch überraschen, ich habe einige Demos vorbereitet anhand derer ich euch diese Tools vorstellen werde.

Veranstaltungsort

PASS Deutschland e.V. ist die deutsche Microsoft SQL Server Community und ein offizielles Chapter der PASS International. Die Mitgliedschaft bei der PASS ist kostenfrei.

Das Treffen findet am 13. April 2017 um 18:30 in der Microsoft Niederlassung Hamburg in der Gasstraße 6a statt und wird von PASS Deutschland e.V.. ausgerichtet.

#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“ 😉