Azure SQL Database – Review of T-SQL Tuesday #103

I’m a little bit sad about the outcome of my TSQL2sDay topic „write about all your experiences with Azure SQL Database“… only 2 people had written about what they were doing with that great product from Microsoft. But this will be a positive motivation for me to spread the words across the #SQLFamily even louder!!! 😉

I asked the SQL Server community on Junes‘ 2018 TSQL2sDay to write about all of their experiences (positive and negative), all their issues, all of their plans and/or challenges with the „Azure SQL Database“.  It seems that there are really fewer experiences within the community with those really good databases… ok „Azure SQL Managed Instance“ is an absolutely new product but the normal database as a service is an old service…
Looking forward for more session about Azure SQL Database

We rise by lifting others

At least two people had written a blog post (and commented on my TSQL2sDay invitation post):

There is Rob Farley who wrote about „The fear of the new“ in a more poetic way why are so many out there struggle with Azure SQL Database. And Kenneth Fisher also wrote about „Why not trying it ?“ and how to develop new processes, ideas, products and even opportunities by using Infrastructure-as-a-Service in the public cloud.

Thank you very much you two for participating in this months topic and the series of TSQL2sDay (initialized by @AdamMachanic).

More talks about Azure SQL Database to come

Now I’ll have to increase my efforts in talking about Azure SQL Databases in the future to make more rumour on this awesome product from Microsoft and all those great features like included backup and monitoring or built-in‘ automatic tuning options. You should definitely have a look at that product and give it a chance. It is very easy to deploy and manage it via Azure portal or Powershell Commandlets. You’ll enjoy the freed up time with other interesting and exciting things (or even with your friends and family) 😉

PASSCamp 2017 – mein erstes Mal

Im Oktober 2017 wurde ich von Oliver Engels und Tillmann Eitelberg gefragt, ob ich auf dem PASSCamp was über Azure SQL Database insbesondere die neue Managed Instance (damals noch im Private Preview) erzählen bzw den Teilnehmern etwas zeigen kann… ja, wollte ich gerne… aber konnte und durfte ich dann doch leider nicht, da es kaum Informationen und Dokumentationen gabe und vor allem leider keine wirklich nutzbare Umgebung für die Teilnehmer des PASSCamps. So wurde es „nur“ ein halbtägiger Workshop zu Azure SQL Database.

Bjoern Peters auf dem PASSCamp 2017

Aufgrund dessen, dass dies mein erster Workshop gewesen ist, war ich sehr unsicher wie lange die Zeit für HandsOn und Vortrag tatsächlich ist… natürlich habe ich zuhause alles mehrfach durchgespielt und hatte eine zeitliche Vorstellung von dem Ganzen, aber irgendwie ist es vor Ort dann leider doch etwas anders gelaufen als erwartet… Sorry dafür!

Auf jeden Fall hat mir mein erster Vortrag auf dem PASSCamp sehr viel Spaß gemacht!

PASSCamp – Welch eine Ehre für mich!

Zur Vorgeschichte 😉 Damals – anno 2012 – durfte ich meinen damaligen Kollegen zum PASSCamp schicken und hörte absolut nur Positives, sowohl über Qualität und Inhalte als auch über die Sprecher und die ganze Veranstaltung. Im folgenden Jahr durfte ich dann selber daran teilnehmen und wurde nicht enttäuscht! Es war für uns beide die erste Erfahrung mit der PASS bzw der SQL Server Community sogar mit solch einer Art der Veranstaltung… wir waren restlos begeistert…

Man sagt ja, dass manche Wege verschlungen sind und man sich nicht ohne Grund ein zweites Mal trifft… in meinem Fall war es dann Conny, welche meinen Weg auf dem Rückweg nach Hause mehrfach kreuzte und ich so auch später den „Zugang“ zur Regionalgruppe der PASS in Hamburg fand (Wir reden und wundern uns manchmal immer noch über diese Heimfahrt). Über die Teilnahme an den regelmäßigen Treffen der Regionalgruppe lernte ich dann später auch die SQLSaturdays in Deutschland kennen und somit den Anfang von meinem aktuell Weg in der Community. Ich kann nur soviel sagen, dass ich bisher nicht einen Schritt davon bereue! Aber der Reihe nach…

Oliver Engels kicks off PASSCamp 2017

Für mich – als DBA – war das PASSCamp immer ein Highlight im Kalender aufgrund der Aktualität der Inhalte und Qualität der Sprecher, in Verbindung mit einem großartigen Ambiente in entspannter Umgebung mit vielen interessierten Menschen gemeinsam lernen!

Daher war die Einladung als Sprecher zum PASSCamp auch eine große Überraschung und ich habe mich sehr darüber gefreut, dass ich dort sprechen durfte. In 2013 für mich nicht vorstellbar, einmal selber dort vor der Gruppe zu stehen und mein Lieblingsthema vortragen, aber anscheinend lohnt sich beständiger Einsatz (dadurch der MVP-Award und dadurch einen „Vorteil“) als Grundlage für das Vertrauen und die Einladung. Vielen Dank für Oliver und Tillmann für ihren Einsatz und die ganzen Mühen solch eine Veranstaltung zu organisieren!

DANKE PASSCamp und SQLPASS_de

Bild-Quelle : Dirk Hondong (aka SQLPaparazzo) – Danke Dirk

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