Powershell – Ermitteln aller installierter SQL Server Services

Manchmal sind es die kleinen Dinge, die einem das Leben erleichtern können… 😉
Vor oder nach der Installation des SQL Servers auf einem Windows Server sollte man auch einen entsprechenden Virenschutz installieren, oder man möchte bestimmte Dienste/Services aus dem Monitoring ausklammern… dann benötigt man für die jeweiligen Ausnahmen die Pfade und Namen der ausführenden Programme. Natürlich kann man sich diese Informationen manuell über die Services-Ansicht holen, aber diesen „Klick-Aufwand“ kann man sich getrotzt sparen, denn es geht viel einfacher.

Beispiel aus dem Leben

Für einige unserer Kunden und deren Anti-Viren-Strategie müssen wir unseren Kollegen immer eine EMail zukommen lassen, welche Dienste (ausführbaren Programme) und deren Verzeichnisse NICHT gescannt werden sollen… wir haben uns also auf ein Format geeinigt, welches wir schnell und einfach liefern können und wo die Kollegen die relevanten Daten auf „einen“ Blick entnehmen können, um diese dann in der Administrations-Oberfläche für den jeweiligen SQL Server zu hinterlegen. Da wir nicht nur einen SQL Server installieren, sondern ein paar mehr… musste eine „universelle“ Lösung her.

Overview SQLServer Services GUI

Wo bekommen wir also diese Daten idealerweise her? Natürlich aus dem Betriebssystem und dem bereits mitgelieferten WMI-Stack… also ran an die Services-Informationen

Get-WmiObject win32_service

Aber so erhalten wir eben eine Vielzahl von Informationen und Services, die wir für unsere Zwecke gar nicht gebrauchen können, denn wir wollen ja nur die SQL Server relevanten Services identifizieren und an die Kollegen melden… also müssen wir diese Informationsflut mit einem Filter eindämmen.


Jetzt erhalten wir nur noch alle Services mit dem Pattern „*sql*“, was für einen SQL Server völlig ausreichend ist, denn glücklicherweise werden aktuell alle SQL Server auf Windows Dienste mit eben diesem Pattern bezeichnet. Hier fehlen aber die relevanten Informationen wie Pfad und Exe oder der Anzeigename… damit wir nun auch diese weiteren Informationen erhalten muss man ein wenig tricksen und die Pfad-Angabe – welche hier ebenfalls (versteckt) enthalten ist – zerlegen.

Get-WmiObject win32_service | ?{$_.Name -like '*sql*'} | select Name, DisplayName, @{Name="Path"; Expression={$_.PathName.split('"')[1]}}


Theoretisch könnte man das nun an die Kollegen schicken, damit aber dort kein Fehler bei Copy&Paste passiert, formatieren wir das Ergebnis noch mit dem Powershell-Ausgabe-Format „Format-List“, dann verrutscht man nicht mehr so leicht in der Zeile und es ist etwas übersichtlicher (auf meinem Laptop ist nicht viel installiert, aber ihr versteht zumindest was ich meine) 😉

Get-WmiObject win32_service | ?{$_.Name -like '*sql*'} | select Name, DisplayName, @{Name="Path"; Expression={$_.PathName.split('"')[1]}} | Format-List

Dieses Ergebnis können wir nun ohne Bedenken in ein Mail-Template einfügen und den Kollegen schicken…

SQLDays 2018 in München – Nachtrag – Pause aka Restore

Im Rahmen meines Vortrages auf den SQLDays in München hatte ich versucht einen Workaround zu „Wie spare ich Geld? Ich droppe einfach die Datenbank!“… hierzu möchte ich gerne ein Update posten.
Aber der Reihe nach, denn erst einmal möchte ich mich recht herzlich bei der ppedv Event bedanken für die Einladung nach München, zum anderen bei den zahlreichen Teilnehmern, die meine Session besucht haben! Ich hoffe ihr habt alle etwas aus meiner Session mitgenommen und könnt diese Ideen und Anregungen in eurem Alltag umsetzen bzw integrieren.

Unsplash - Photo by kevin Xue - THANK YOU

Powershell – Workaround um Kosten zu sparen

In meiner Demo ist das Wiederherstellen nach dem „Drop Database“ leider fehlgeschlagen, so dass ich in meiner Demo nicht zeigen konnte wie man aus dem logischen SQL Server das gespeicherte „Last Deleted Database“ entnehmen kann, um daraus einen Restore zu erzeugen. Ich habe diese Code-Zeilen bisher immer nur einmal ausgeführt, also Demo-Umgebung frisch aufsetzen, die Skripte nur auf Syntax überprüfen und hoffen das alles gut geht 😉 Dieses mal hatte ich meine Skripte eben doch einmal vorher ausgeführt, dadurch entstand schon ein Eintrag für „DeletedDatabases“… und somit gab es eben mehr als einen Eintrag und der Restore wußte nicht auf welches Datum er zurück gehen sollte…

# Do not continue until the cmdlet returns information about the deleted database.
if(![string]::IsNullOrEmpty($deleteddatabase)) {
Write-Host "Now restoring database $databasename_backup ..." -foregroundcolor "DarkYellow"
Restore-AzureRmSqlDatabase -FromDeletedDatabaseBackup `
-ResourceGroupName $resourcegroupname `
-ServerName $servername_backup `
-TargetDatabaseName $databasename_backup `
-ResourceId $deleteddatabase.ResourceID `
-DeletionDate $deleteddatabase.DeletionDate `
-Edition "Standard" `
-ServiceObjectiveName "S0"
}

Bin ich so bisher noch gar nicht drüber gestolpert, aber als ich dann nach der Session das Skript und den Output mir noch einmal angesehen habe…

SQLDays AzureSQLDB DeletedDatabases - Scriptproblem

Ich könnte mir vorstellen, dass es noch eine sichere oder sinnvollere Lösung gibt, aber als Ansatz bzw vorübergehende Lösung kann man das erst einmal so machen 😉
Es gibt zumindest zwei Möglichkeiten…

Möglichkeit 1

Aus meiner Sicht am einfachsten, aber unter Umständen nicht sicher, dass tatsächlich das Datum des letzten Löschvorganges übernommen wird, da das Skript zuvor keinen Einfluss auf die Sortierung der Ergebnisse nimmt.

# Do not continue until the cmdlet returns information about the deleted database.
if(![string]::IsNullOrEmpty($deleteddatabase)) {
Write-Host "Now restoring database $databasename_backup ..." -foregroundcolor "DarkYellow"
Restore-AzureRmSqlDatabase -FromDeletedDatabaseBackup `
-ResourceGroupName $resourcegroupname `
-ServerName $servername_backup `
-TargetDatabaseName $databasename_backup `
-ResourceId $deleteddatabase[0].ResourceID `
-DeletionDate $deleteddatabase[0].DeletionDate `
-Edition "Standard" `
-ServiceObjectiveName "S0"
}

Syntaktisches korrekt, funktioniert auf jeden Fall, ABER eben keine 100%ige Gewissheit, dass auch tatsächlich der gewünschte Zeitpunkt ausgewählt wird.

Möglichkeit 2

Etwas „umständlicher“, aber auf jeden Fall sicherer, denn wir wählen explizit das zu letzt hinzugefügte Datum aus.

# Do not continue until the cmdlet returns information about the deleted database.
if(![string]::IsNullOrEmpty($deleteddatabase)) {
Write-Host "Now restoring database $databasename_backup ..." -foregroundcolor "DarkYellow"
Restore-AzureRmSqlDatabase -FromDeletedDatabaseBackup `
-ResourceGroupName $resourcegroupname `
-ServerName $servername_backup `
-TargetDatabaseName $databasename_backup `
-ResourceId ($deleteddatabase.ResourceID | Select-Object -Last 1) `
-DeletionDate ($deleteddatabase.DeletionDate | Select-Object -Last 1) `
-Edition "Standard" `
-ServiceObjectiveName "S0"
}

Syntaktisches korrekt, funktioniert auf jeden Fall, ABER eben keine 100%ige Gewissheit, dass auch tatsächlich der gewünschte Zeitpunkt ausgewählt wird.

SQLDays-AzureSQLDB-DeletedDatabases-Script-optimized

Nun kann ich auch die SQLDays 2018 in München auf jeden Fall als Erfolg verbuchen, da ich wieder etwas an meinen Demos optimieren konnte und den Teilnehmern doch noch zeigen kann, wie dieses Skript funktioniert. In den nächsten Tage werde ich meine Demo-Skripte auf Github entsprechend aktualisieren, damit jeder davon profitieren kann!

Hier findet Ihr/sie nicht nur meine Skripte sondern auch meine Slides als PDF.

Skripte und Slides zu den SQLDays 2018 von SQL_aus_HH auf Github

Azure SQL Database – Probleme beim Löschen von Recovery Service Vault

Ich möchte euch heute von einer Erfahrung berichten, welche ich in den letzten Tagen mehrfach und wiederholt machen musste. Für meine Sessions bei der Imagine Cloud Conference und dem PASS Camp über Azure SQL Databases hatte ich einen Azure Recovery Service Vault angelegt, um dort die Backups für die Langzeit-Archivierung abzulegen. Nachdem die Sessions beendet waren, wollte ich aufräumen bzw für die nächste Session vorbereiten, hierzu wollte ich die ganze Ressource-Gruppe löschen (samt SQL Server, Azure SQL Databases und dem besagten Recovery Service Vault). Egal was ich versucht habe, der Recovery Service Vault ließ sich nicht löschen, der SQL Server mit seinen SQL Databases konnte erfolgreich gelöscht werden, der ARS Vault aber blieb hartnäckig bestehen… Die Fehlermeldung lautete sinngemäß, dass noch registrierte Server / Items vorhanden sind (Please delete any replicated items, registered servers, Hyper-V sites…), welche erst gelöscht werden müssen. In der Service-Übersicht war allerdings nichts hierzu zu finden, keine registrierten Server, keine registrierten Server oder Replicated Items, keine Größen… es konnte nichts gefunden werden, was darauf hindeutete wo sich noch etwas verbirgt.

fast verzweifelt - verzweifelte Suche - StockSnap_27XHUPB48N - aus dem Newsletter von StockSnap

Verzweiflung breitet sich aus

Wie kann ich also nun diesen Azure Recovery Service Vault wieder löschen? Wenn es sich wenigstens um eine generische Ressourcengruppe handeln würde, aber da ich zur Abgrenzung der einzelnen Veranstaltungen bzw der Einfachheit halber alle Ressourcen einer Demo in einer Ressourcengruppe anlege, damit ich sie schneller löschen kann… hätte ich den ARS Vault entweder gerne verschoben oder eben doch ganz gelöscht… (siehe Beitragsbild ganz oben)

Folgende Fehlermeldung habe ich wiederholt bekommen:

Deletion of ARS Vault failed - Error Message

Vault cannot be deleted as there are existing resources within the vault. Please ensure there are no backup items, protected servers or backup management servers associated with this vault. Unregister the following containers associated with this vault before proceeding for deletion : Unregister all containers from the vault and then retry to delete vault

Bei der Kontrolle des Azure Recovery Services, welche Server oder Backup Items noch in dem ASR-Vault konfiguriert sind, schaute ich natürlich als erste in den entsprechenden Service nach… aber egal welche Ansicht ich auch öffnete, ich konnte keinerlei verbliebenen Backups finden.

Deletion of ARS Vault failed - Backup Details on ARS

 

Also erst einmal eine Runde googlen ob jemand anderes dieses Problem schon einmal gehabt hat… und siehe da, so ganz unbekannt ist das Problem mit dem Löschen des Azure Recovery Services nach der Nutzung mit Azure SQL Databases nicht. Felipe de Assis hatte hierzu bereits einen kleinen Beitrag und ein Skript geschrieben => https://social.msdn.microsoft.com/ Der eigentliche Grund liegt darin, dass weder Azure SQL VM Server noch Azure SQL Databases dort als aktiv gelistet werden und diese explizit mittels Powershell zu löschen sind. Ich habe das Skript nach meinen Anpassungen hier unten angehängt, so dass ihr seht was ich meine…

Login-AureRmAccount

$vault = Get-AzureRmRecoveryServicesVault -Name "WingtipDB-LongTime"
Set-AzureRmRecoveryServicesVaultContext -Vault $vault

$containers = Get-AzureRmRecoveryServicesBackupContainer -ContainerType AzureSQL -FriendlyName $vault.Name
ForEach ($container in $containers) {
 $items = Get-AzureRmRecoveryServicesBackupItem -container $container -WorkloadType AzureSQLDatabase
 ForEach ($item in $items) {
 Disable-AzureRmRecoveryServicesBackupProtection -item $item -RemoveRecoveryPoints -ea SilentlyContinue
 }
 Unregister-AzureRmRecoveryServicesBackupContainer -Container $container
}
Remove-AzureRmRecoveryServicesVault -Vault $vault

Damit ließ sich auch der Recovery Service Vault einwandfrei löschen und dann auch die Ressourcegruppe und ich hatte wieder eine saubere Test- bzw Demo-Umgebung.

TSQL2sday #94 – daily database copy using Powershell – dbatools

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 Rob Sewell is our TSQL2sDay host and his subject is “Let’s get all Posh!”.

As I had written in a former post I was inspired by Andre Kamann to start using Powershell to manage our SQL Server environments, since this year I’m a major contributor of dbatools – a multifunctional Powershell module for DBAs.

I use the functions from the dbatools module day by day more and more. And I try to write about some of those tasks here in my blog like this post 😉

So one of me ServiceManager asked me to write a job which should refresh the test-environment each day – only 3 databases (out of 12). So I just wrote a Powershell-script which copies those databases from production to test environment.

First, we start with the requirements:

  • Currently approx. 280 GB
  • Backup with Copy only
  • Creation of database duplication as an automatic job
  • Every morning at 4:00

My first thoughts about that were creating a SQL Server Agent Job with following steps:

  1. check the availability of Shared-Destination-Folder
  2. delete/clear Destination-Folder-Content
  3. Shrink all Transaction-Logfiles
  4. Backup all Databases from given list
  5. Restore each Backup-File from folder
  6. Check all orphaned user
  7. delete/clear Destination-Folder-Content

A year or two ago, I had built this with a lot of normal T-SQL-Agent-Jobs, now I’m doing this with dbatools which make it very easy and fast (and in one step)

dbatools.io - Logo - Thor

I’m building such scripts in a very simple way, in order to make it easy to understand what a script is doing… so I’m not using any complex one-liner 😉
This time I need a job log and dbatools, so I started with importing those functionalities

. E:\SQL_Admin_Skripte\Function-Write-Log.ps1

$Network_Transfer_Folder = '\\DestinationShare\Backup'
$Local_Transfer_Folder = 'E:\BackupPath\'


if (-not  (Get-Module -Name dbatools)) {
    Import-Module E:\SQL_Admin_Skripte\dbatools-master\dbatools.psd1
}

Claudio Silva (b | t) helped me a little with the following „SHRINK“-command which was in a first stage a normal combination of Powershell „Invoke-Sqlcmd“ and T-SQL, now it is a dbatools-function called „Invoke-DbaDatabaseShrink“ which is a little bit tricky if you only want to shrink log files… but it works.

#Shrink TLogs
Invoke-DbaDatabaseShrink -SqlInstance Src-InstanceName -Database DB1,DB2,DB3 -LogsOnly -ShrinkMethod TruncateOnly

Now I had just to make a Backup and restore those Backups on the destination instance… no real magic 😉

#Backup named databases
Backup-DbaDatabase -SqlInstance Src-InstanceName -Databases DB1,DB2,DB3 -Type Full -FileCount 32 -CopyOnly -BackupDirectory $Network_Transfer_Folder

#Restore all databases in given folder
Restore-DbaDatabase -SqlServer Dest-InstanceName -Path $Local_Transfer_Folder -WithReplace -UseDestinationDefaultDirectories 

Last but not least… I’ll have to check for orphaned user and clean up everything…

#Repair orphaned users
Repair-SqlOrphanUser -SqlServer Dest-InstanceName

#Cleanup after Restoring
Get-ChildItem -Path $Local_Transfer_Folder -Include *.bak -File -Recurse | foreach { $_.Delete() }

Now I’m triggering the script every day with a SQL Server Agent Job what makes it even easier for me as DBA. (but be careful – dbatools run only with a PowerShell version > 3 => SQL Server 2014 if you use a PowerShell step)

The job runs ~12 minutes including importing dbatools module, Backup three databases (~280GB) and restoring them on the test server which I think is a good runtime!

 

At the end, I’m having more time to read any books, tweets or other blog posts 😉

 

My former blog post about another database copy job can be found here: Copy Database with Rename using dbatools

Special Thanks to Jason Wasser @wasserja for his great logging function!
https://gallery.technet.microsoft.com/scriptcenter/Write-Log-PowerShell-999c32d0

and to Derik Hammer for his list of PowerShell version in SQL Server and how to implement PowerShell into Agent Steps…
https://www.sqlhammer.com/running-powershell-in-a-sql-agent-job/

#4 SQL Server Konfiguration – Best Practices umsetzen

Ich habe schon lange nichts mehr für meine Powershell-Serie geschrieben und möchte dies nun nachholen, obwohl sich mittlerweile sehr viel (in meinem Arbeits- & Communityleben) diesbezüglich getan hat… Ich möchte euch heute zweierlei Dinge vorstellen, einmal wie auf herkömmliche Art und Weise mit Powershell den SQL Server konfigurieren konnte (oder ich es in meinem Skript getan habe) und zum anderen die mittlerweile einfachere und schnellere Weise mit dem Powershell Modul von dbatools.io.

Best Practices mit T-SQL

Im Rahmen der SQL Server Installation sollte man gewisse Parameter optimieren, damit einem performanten und stabilen Betrieb nichts im Wege steht. Hierzu gehören eine Vielzahl Konfigurationsparametern auf Instanzebene, wie zum Beispiel „Max. Memory“ oder „Max. Degree of Parallelism“. All diese Instanz-Einstellungen können mit der selben Funktion durchgeführt werden, also habe ich für diese wiederkehrenden T-SQL-Befehle eine eigene „Funktion“ geschrieben, um für spätere Erweiterungen flexibel bleiben zu können.

function ExecuteSQLCmd ([string]$SQLQuery) {
     Invoke-Sqlcmd -ServerInstance $ServerName -Query $SQLQuery -QueryTimeout 65535
}

Mit dieser einfachen Funktion (auch schon vorher, aber so ist es „einfacher“), kann ich die nun folgenden Funktionen entsprechend aufrufen und meinen SQL Server gemäß Best Practices konfigurieren, in dem ich die jeweilige Funktion aufrufe, Werte je nach Systemausstattung berechne um sie dann an ExecuteSQLCmd zu übergeben. Die folgenden Funktionen ermöglichen mir die Anpassungen an das jeweilige Umfeld.

SetMaxMemory
Add_TempDBFiles
SetMaxDOP
SetNetworkPacketSize
SetOptimizeAdhocWorkload
SetBackupCompression
AddLocalSystemToSysadminGroup
enable_XPAgent

Beispiel – Powershell Funktion „SetMaxDOP“

Um den Wert für MaxDOP (max Degree of Parallelism) setzen zu können, muss ich wissen wieviele logische CPU ich habe. Diesen Wert hatte ich mir zur Anfang des Skriptes über Hilfsfunktionen ermittelt, erst mit diesem Wert kann ich entscheiden… Den Threshold für MaxDOP sezte ich auf unseren Systemen in der Regel auf 40, dies passt zumindest bei 90% der Systeme. Sicherlich kann man sich hier noch viel mehr an die Best-Practices halten, wie ihr auch im nächsten Abschnitt lesen könnt, aber mit diesen Werten bin ich die letzten zwei Jahre auf unseren Systemen ganz gut gefahren.

function SetMaxDOP() {
    Try { 
        Write-Host "Setting of MaxDOP / Threshold"
        $sqlquery = "
        EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE;
        EXEC sys.sp_configure N'cost threshold for parallelism', N'40';
        "
        ExecuteSQLCmd $sqlquery

        if ($global:NoLogicalCPUs -le 4) {
            $sqlquery = "
            EXEC sys.sp_configure N'max degree of parallelism', N'0'
            RECONFIGURE WITH OVERRIDE
            "
            Write-Host "[INFO] Set Threshold to 40 and Set MaxDOP to 0."  -ForegroundColor Green
        } else {
            $sqlquery = "
            EXEC sys.sp_configure N'max degree of parallelism', N'"+($global:NoLogicalCPUs/2)+"'
            RECONFIGURE WITH OVERRIDE
            "
            Write-Host "[INFO] Set Threshold to 40 and Set MaxDOP to "($global:NoLogicalCPUs/2) -ForegroundColor Green
        }
        ExecuteSQLCmd $sqlquery
    }
    Catch {
        Write-Host "[ERROR] Failed to set MaxDOP." -ForegroundColor Red
    }
}

dbatools – die Funktion Set-DbaMaxDop

Ich hatte ja schon mehrmals über das Powershell Modul dbatools berichtet – zum Beispiel beim Erstellen einer Datenbank-Kopie – hier möchte ich euch nun den Vergleich zwischen dem herkömmlichen Weg und dem einfacheren Weg mittels dbatools vorstellen. dbatools bietet eine Funktion zum einfachen Setzen des SQL Server Instanz Parameters für Max Degree of Parallelism, auch weitere Instanz-Parameter sind entsprechende Funktionen vorhanden.

Das Kommando „Set-DbaMaxDop“ bietet eine Vielzahl von Möglichkeiten, das einfache Setzen des MaxDop auf Instanzebene (SQL Server 2008 – 2016) sowie das Setzen des MaxDop auf Datenbank-Ebene ab dem SQL Server 2016. Ohne Angabe eines weiteren Parameters ermittelt die Funktion alle Rahmenbedingungen, basierend auf dem Algorythmus aus dem Microsoft KB-Artikel KB2806535, sowie dem MaxDoP-Calculator von Sakthivel Chidambaram werden daraus die notwendigen Werte zum Setzen des MaxDoPs zu errechnet. Wobei man natürlich – wie auch bei meinen Angaben/Hinweisen – auch immer darauf hinweisen muss, dass es sich hierbei um Empfehlungen handelt die nicht zu 100% auf jede Umgebung und Applikation passen, aber einen ersten Anhaltspunkt geben.

Set-DbaMaxDop -SqlServer SQL2016

dbatools - Set-DbaMaxDop - Set to Best Practices

Möchte man nun selber einen Wert vorgeben, ist dies auch möglich… oder das verwenden der internen Test-Funktion „Test-DbaMaxDop

Test-DbaMaxDop -SqlServer SQL2016

dbatools - Test-DbaMaxDop

oder eben das selber entscheiden, welcher Wert für diese Umgebung sinnvoller ist…

Set-DbaMaxDop -SqlServer SQL2016 -MaxDop 6

dbatools - Set-DbaMaxDop - Set to your own Value

Weitere Informationen findet ihr auf folgenden Hilfe-Seiten von dbatools.io : https://dbatools.io/functions/set-dbamaxdop/ und https://dbatools.io/functions/test-dbamaxdop/

dbatools – die Funktion Set-DbaSpConfigure

Nun haben wir oben aber nicht nur den Wert für MaxDop geändert, sondern ebenso auch den Wert für den Threshold für den max Degree of Parallelism. Auch diesen Wert kann man mit den dbatools sehr einfach setzen. Da beides Instanz-Parameter sind könnte man beide Einstellungen individuell mit eigenen Werten und diesem Kommando anpassen, aber die interne Berechnung macht den Einsatz zwei unterschiedlicher Befehle sinnvoll. Um nun also den Wert für den Threshold auf 40 zu setzen, verwende ich „Set-DbaSpConfigure“, was uns ansich nicht unbekannt sein sollte.

Set-DbaSpConfigure -SqlServer SQL2016 -ConfigName CostThresholdForParallelism -Value 40

dbatools - Set-DbaSpConfigure

Gerade mit dem IntelliSense-Feature macht diese Funktion Freude, da die einzeln verfügbaren Parameter schnell einsetzbar sind und man schnell zu ganzen Befehl kommt. Weitere Hilfe und Beispiele findet ihr natürlich auch auf den Hilfe-Seiten der Funktion => https://dbatools.io/functions/set-dbaspconfigure/

So kann man nun – im Gegensatz zu meinem eigenen Skript – beide Instanz-Parameter mit nur 3 Zeilen optimieren.

Import-Module .\dbatools\dbatools.psd1

Set-DbaSpConfigure -SqlServer SQL2016 -ConfigName CostThresholdForParallelism -Value 40
Set-DbaMaxDop -SqlServer SQL2016