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/

Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.

#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

Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.

Pause / Resume / Backup einer Azure SQL Database mit Powershell – Teil 3

Viele Services in Azure erlauben durch Automatisierung eine gewisse Kostenersparnis, wie man das mit dem Platform-as-a-Service „Azure SQL Database“ erreichen kann, darum geht es in diesem Blogbeitrag. Ganz so einfach wie z.B. beim Azure Analysis Service ist es hier leider nicht, denn es gibt eigentlich keine Pause-Resume Funktionalität – wie hier das Backup mitspielt, dazu kommen wir als erstes.

Azure SQL Database und das Thema Backup

Bevor wir einsteigen in das Thema Azure SQL Database und dessen Pause-Resume-Funktionalität müssen wir vorher kurz einen Blick auf das Thema Backup werfen, welches in diesem Zusammenhang nicht ganz unwichtig ist. Einen großen Vorteil gegenüber einem SQL Server (onpremise oder IaaS) hat die Azure SQL Database auf jeden Fall… man muss sich nicht explizit um das Backup kümmern, da dieses automatisch erstellt wird. Soll heißen, je nach Datenbank-Größe und dem Aufkommen von Datenveränderungen sichert Microsoft automatisch die Datenbanken in regelmäßigen Abständen! Was heißen soll, die Kosten für eine extra Backup-Software oder gar die Entwicklung eigener Services fallen schon einmal weg. Je nach PerformanceLevel gibt es unterschiedliche Backup Retention Zeiten:

  • beim Basis Level beträgt die Aufbewahrungszeit 7 Tage.
  • beim Standard Level beträgt die Aufbewahrungszeit 35 Tage.
  • beim Premium Level beträgt die Aufbewahrungszeit 35 Tage.

Wann wird meine Datenbank denn aber nun gesichert? Auch darauf gibt es eine Anwort…
Das Full-Backup erfolgt jede Woche (leider) zu nicht fest definierten Zeiten, differentielle Backups werden generell alle paar Stunden erstellt und eben je nach Datenaufkommen erfolgt die Sicherung der TransaktionsProtokolle alle 5-10 Minuten. Ein erste Voll-Sicherung wird innerhalb der ersten 30 Minuten nach Erstellung der Datenbank erstellt. Diese Sicherungen werden entsprechend des Service-Tiers (siehe oben) aufbewahrt (Kostenersparnis : man braucht keinen extra Storage-Account, da das Backup bereits im Preis inkludiert ist). Möchte man sein Backup aber länger als 35 Tage aufbewahren, so hat man die Möglichkeit ein „Long-Time-Retention-Backup“ zu aktivieren, hierzu ist ein weitere Storage-Account notwendig auf dem dann die Backups parallel abgelegt werden und eben dauerhaft abgelegt werden können.

Backup Azure SQL Database

Pause und Resume zwecks Kostenersparnis

Diese Funktionalität gibt es leider bei Azure SQL Database nicht… Wie kann ich trotzdem eine Kostenersparnis erwirken, wenn ich diesen Platform-as-a-Service verwenden möchte… natürlich kann man – wie bereits in einem anderen Blogpost erläutert – die Datenbank Performance verändern, um die auftretenden Lastspitzen abzufangen. Aber wir möchten doch eigentlich mit der Migration in die Cloud auch eine gewisse Kostenersparnis erreichen… wenn die Fachabteilung nur tagsüber arbeitet (8-20 Uhr), dann brauche ich diese Datenbank(n) doch während der Nacht nicht… kann man die dann nicht einfach stoppen, da man doch nur zahlt, wenn die Datenbank online ist?

Für genau dieses Szenario – die Fachabteilung braucht die Datenbank nur tagsüber – gibt es eigentlich keine Lösung, aber einen Workaround, denn hier hilft abends nur ein „Drop Database“ und am nächsten Morgen  ein „Create Database from Backup“. Aber dieses Vorgehen hat Microsoft ausgesprochen angenehm gelöst und bedeutet keinen großen Aufwand.

# Dropping DB to stop costs
Get-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename -ev notPresent -ea 0
if ($notPresent) {
    Write-Host $databasename "already deleted" 
} else {
    Remove-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename
}

Hierzu sollte man beachten, dass man nur die Datenbank löscht/entfernt und nicht den logischen Server, denn die Backup-Historie hängt am Server. Man benötigt also das Datum des letzten Backups um diese Datenbank wieder herzustellen. Beim Neu-Erstellen der Datenbank am folgenden Morgen nutzt man dann diesen Backup-Zeitpunkt, um damit direkt einen Restore durchzuführen. In Powershell kann man diese Tätigkeiten sehr einfach kombinieren.

$deleteddatabase = Get-AzureRmSqlDeletedDatabaseBackup -ResourceGroupName $resourcegroupname -ServerName $servername #-DatabaseName $databasename
$deleteddatabase
# Do not continue until the cmdlet returns information about the deleted database.

Restore-AzureRmSqlDatabase -FromDeletedDatabaseBackup `
    -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -TargetDatabaseName $databasename `
    -ResourceId $deleteddatabase.ResourceID `
    -DeletionDate $deleteddatabase.DeletionDate `
    -Edition "Standard" `
    -ServiceObjectiveName "S0"

Weitere Beispiele-Skripte zum Thema Backup/Restore findet ihr hier => https://docs.microsoft.com/de-de/azure/sql-database/scripts/sql-database-restore-database-powershell

Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.

Resize einer Azure SQL Database mit Powershell – Teil 2

Nachdem ich im ersten Teil meiner Azure SQL Database Powershell Automation Reihe gezeigt habe, wie man sich die Ressource-Gruppe, den logischen SQL Server und die entsprechende Datenbank anlegt, möchte ich euch nun zeigen wie man ein Resize des Performance-Level dieser Datenbank umsetzen kann.
Warum soll man eine Datenbank resizen wollen? Nach oben – also mehr Leistung kann man vielleicht noch verstehen, aber warum auch wieder ein Downsizing machen? Also starte ich erst einmal mit den jeweiligen Gründen für eine Anpassung der gewählten Leistungsklasse.

Welche Leistung brauche ich wann?

Nehmen wir am besten ein Beispiel… eine größere Firma die Arbeitsplätze (Workspace) vermietet und nur in Deutschland aktiv ist, hat eine Applikation zur Verwaltung/Buchung ihrer Meetingräume bzw Arbeitsplätze. Diese Applikation wird von den Damen am Empfang nur während der „Öffnungszeiten“ intensiv genutzt und außerhalb dieser Zeiten gelegentlich durch Mitarbeiter. Im Grunde benötigen die Empfangsdamen ihre Applikation und somit die Datenbank nur zwischen bestimmten Zeitpunkten, beispielsweise 7 – 20 Uhr, den Rest des Tages bleibt die Datenbank nahezu ungenutzt…
Was liegt also näher diese Datenbank tagsüber „schneller“ zu machen? On-prem ist dies leider nicht möglich, da man einer einzelnen Datenbank nicht so ohne weiteres weitere Ressourcen zuweisen kann.

Ein weiteres Beispiel sind Auswertungen oder Verarbeitungen, hier können die betrieblichen Belange stark variieren, wenn am Monatsende der Abschluss ansteht wird mehr Rechenleistung in der Azure SQL Database benötigt, damit die Daten ausreichend schnell verarbeitet und bereitgestellt werden können.

  • je nach Applikation-Nutzung-Verhalten
  • je nach betrieblichen Anforderungen
    • nächtliche Verarbeitungen
    • Monatsabschluss
    • Jahres-End-Rally

Azure SQL Database

Was benötigen wir alles für einen Resize der Azure SQL Database

  • eine Resource Gruppe bzw deren Namen
  • einen Namen für den logischen SQL Server (der unique sein muss)
  • einen Datenbank Namen
  • das neue Performance-Level (DTU)

Die Anmeldung an Azure sowie die Auswahl der zu nutzenden Subscription lasse ich hier aussen vor und beginne mit dem eigentlichen Script.
Auch hier starte ich mit der Definition der notwendigen Variablen (siehe oben):

# Set the resource group name for your server
$resourcegroupname = "RG-AzureSQLDatabase-Demo"
# Set logical server name
$servername = "server-sqldbdemo"
# The sample database name
$databasename = "db-sqldbdemo"
# Set new performance-level
$newdbDTUsize = "S3"

Nun können wir die Azure SQL Database resizen

Dies ist wesentlich einfacher als die Neu-Anlage einer Datenbank, da wir weniger Variabeln (siehe oben) und nur eine Kommandozeile zur Ausführung benötigen. Aber auch frage ich vorsichtshalber das Vorhandensein der Datenbank ab, um sicherzustellen, dass das Script keinen „Blödsinn“ macht.

# Resize Azure SQL Database to new performance-level
Get-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename -ev notPresent -ea 0
if ($notPresent) {
    Write-Host $databasename "doesn't exist" 
} else {
    Set-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename -Edition "Standard" -RequestedServiceObjectiveName $newdbDTUsize
}

Wie der Vorher-Nachher-Vergleich zeigt, ist ein Resize ohne Probleme möglich und dauert nur wenige Augenblicke.

Vorher-Nachher-Resize Azure SQL Database

Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.

Einstieg in Azure SQL Database mit Powershell – Teil 1

Neben der Möglichkeit in Azure sich einen virtuellen Server zu deployen auf dem ein SQL Server läuft, gibt es auch die „einfachere“ Methode nur eine Datenbank on Demand zu deployen => die Azure SQL Database. Diese Database-as-a-Service kann man auch sehr gut und einfach für eine Vielzahl von Applikationen nutzen, wobei es auch eine Vielzahl an Optionen gibt über die man sich vorher Gedanken machen sollte.

  • reicht eine einfache Datenbank
  • lieber doch einen Elastic Pool
  • welche Region
  • oder doch lieber Geo-Redundant auslegen
  • reicht das „mitgelieferte“ Backup
  • oder muss das Backup eine „Long Time Retention“ erhalten
  • Wer greift von wo aus auf die Datenbank zu?
  • Welche Leistungsstufe brauche ich für die Datenbank bzw den Elastic Pool?

Eine Übersicht über die einzelnen Möglichkeiten und deren Verwendung für die jeweilige Solution, sowie eine großere Anzahl an Antworten auf viele Fragen findet man in der Dokumentation von Microsoft => https://docs.microsoft.com/en-us/azure/sql-database/

Beginn mit einer einfachen Azure SQL Database

Ein wichtiger Punkt in der Bereitstellung einer Azure SQL Database ist, dass man nicht nur eine Datenbank braucht sondern auch einen logischen SQL Server (=> Listener Endpunkt), ohne diese „Hülle“ kann man keine Datenbanken hosten.

In meinem folgenden Beispiel legen wir mit Powershell eine Standard-Datenbank an, natürlich als Script, damit wir den Server und seine Datenbank oder nur die Datenbank immer wieder gleich (als Standard) anlegen können.

Was benötigen wir alles um eine Azure SQL Database anzulegen

  • eine Resource Gruppe bzw deren Namen
  • eine Location für die Resource Gruppe
  • Admin-Usernamen und Passwort
  • einen Namen für den logischen SQL Server (der unique sein muss)
  • einen Datenbank Namen
  • idealerweise auch die IP-Adressen/Ranges, die darauf zugreifen dürfen (die eigene IP reicht für den ersten Zugriff)

Die Anmeldung an Azure sowie die Auswahl der zu nutzenden Subscription lasse ich hier aussen vor und beginne mit dem eigentlichen Script.
Mein erstes Script startet also mit der Definition diverser Variablen (siehe oben):

# Set the resource group name and location for your server
$resourcegroupname = "RG-AzureSQLDatabase-Demo"
$location = "west europe"
# Set an admin login and password for your server
$adminlogin = "dbadmin"
$password = "DemoPwd@2017"
# Set server name - the logical server name has to be unique in the system
$servername = "server-sqldbdemo"
# The sample database name
$databasename = "db-sqldbdemo"
# The ip address range that you want to allow to access your server
$clientIP = (Invoke-WebRequest ifconfig.me/ip).Content
$startip = $clientIP
$endip = $clientIP

Nun das Erstellen des logischen Servers und der Azure SQL Database mit Beispiel-Daten

Nun kommt – wie in fast allen meinen Scripten – erst einmal die Abfrage ob die Resourcegruppe bereits exisitiert, wenn nicht wird sie angelegt. Nach der Resourcegruppe kommt als nächstes der logische Server in den wir zum Schluss unsere Azure SQL Database einbinden können. Dem logischen Server weisen wir noch die Credentials aus adminlogin und password zu, damit der Server als auch die Datenbanken geschützt sind. Apropos geschützt, auch die Firewall des Servers müssen wir natürlich für einen externen Zugriff öffnen, hierzu ermittel ich über eine zusätzliche Funktion und einen externen Dienst meine eigene Public-IP-Adresse. Mit dieser IP-Adresse konfigurieren wir nun den logischen SQL Server und zu guter Letzt prüfen wir ob die gewünschte Datenbank vielleicht schon existiert, wenn nicht wird diese mit den gewünschten Parametern erstellt.

# Create a resource group
Get-AzureRmResourceGroup -Name $resourcegroupname -ev notPresent -ea 0
if ($notPresent) {
    $resourcegroup = New-AzureRmResourceGroup -Name $resourcegroupname -Location $location
} else {
    Write-Host $resourcegroupname "already exists"
}

# Create a server with a system wide unique server name
Get-AzureRmSqlServer -ResourceGroupName $resourcegroupname -ServerName $servername -ev notPresent -ea 0
if ($notPresent) {
    $server = New-AzureRmSqlServer -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -Location $location `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
} else {
    Write-Host $servername "already exists"
}

# Create a server firewall rule that allows access from the specified IP range
Get-AzureRmSqlServerFirewallRule -ResourceGroupName $resourcegroupname -ServerName $servername -FirewallRuleName "AllowedIPs" -ev notPresent -ea 0
if ($notPresent) {
    $serverfirewallrule = New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -FirewallRuleName "AllowedIPs" -StartIpAddress $startip -EndIpAddress $endip
} else {
    Write-Host "FirewallRule already exists"
}

# Create a blank database with an S0 performance level
Get-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename -ev notPresent -ea 0
if ($notPresent) {
    $database = New-AzureRmSqlDatabase  -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -DatabaseName $databasename `
    -RequestedServiceObjectiveName "S0"
} else {
    Write-Host "Database" $databasename "already exists"
}

Nun können wir von unserer Workstation mittels SQL Server Tools – z.b. dem Management Studio – auf diese Datenbank zugreifen und die nutzende Applikation anbinden sowie testen.

Beispielzugriff - SSMS auf Azure SQL Database

Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.