TSQL Tuesday # 96: Community people who have influenced my way

TSQL Tuesday # 96: Community people who have influenced my way

T-SQL Tuesday is a recurring blog series started by Adam Machanic (b | t), a blogger hosts a SQL Server topic every month, and anyone can write a blog post on this particular topic. This month, Ewald Cress (b | t) is our host and it’s about the people who have influenced us in our lives with data in the community.

For a long time, I had no relation to any community… in my youth, I was a volunteer at the German Red Cross and now on the road again with “you”.

Who influenced me in the last few years?

Actually, everything started with PASSCAMP 2013… Ok, maybe a bit earlier when I signed up for Twitter in mid-2012 and read a lot about a #sqlfamily.

Definitely worth mentioning Brent Ozar, who has inspired me with his knowledge and blog contributions for years and continues to bring in the daily DBA life. Also, his contributions to the topic “Why should I run a blog?” count among my reasons mentioned again and again. Unfortunately, I have not been able to meet him personally yet, but that’s guaranteed to happen. His funny way of explaining complicated things to one person is just great!

Then in 2013, it was my visit to the PASSCamp and thus my first direct contact with the German sqlfamily aka SQLPass. Here you can only list the usual suspects, who are always present at such events. => Oliver Engels, Tillmann Eitelberg, Kostja Klein, Niko Neugebauer and Andreas Wolter, just to name a few… I found the group dynamics awesome and all that love between them… everyone knows each other, everyone laughs at each and everyone talks to everyone, no one is left behind!

I wanted to join in, somehow belong to them… but how?

Henning L. @hlhr_dev Jun 2 thanks to all the speakers and specially to @sql_williamd for this great event #SQLGrillen

So after this experience, I started to get more involved with the PASS and its activities and found among others like Cathrine Wilhelmsen, whose community activities also spurred me and showed me how to start => more participation in local or national activities of the PASS. Then it came to SQLGrillen from William Durkin and the session of Andre Kamann about “PoSh meets Ola Hallengren” and another meeting with Andre Essing, which in total motivated me to jump over my own shadow and act as a spokesman in the PASS. During the following two SQLSaturdays (Rhineland and Munich 2016) I was on the road for my first time as a volunteer and was able to get a taste of the national activities of PASS Germany. As luck would have kicked in or the data network rolled the balls, the SQLSaturday in Munich was the next step in my “community career” and brought me the Azure Meetup Hamburg.

In 2017, I had my first public talks, some in-house, some in the PASS user group Hamburg, at SQLGrillen 2017 (Thanks to William) and twice at SQLSaturday Rheinland 2017 (thanks to Olli, Tillmann and Kostja)… all this in conjunction with my blog and Twitter activities helped me to be awarded by Microsoft with the MVP Award.

I would like to say thank you to following people of the German #sqlfamily:

Oliver Engels
Tillmann Eitelberg
Kostja Klein
Andre Essing
William Durkin
Andre Kamann

A special thanks go to Gabi Münster for the support during my first public appearance, in the end, it’s all about a “kick my ass” to jump over my shadow. Of course, many conversations and Twitter contacts with numerous other community members (Chrissy, Claudio, Rob, Dirk, Volker and, above all, Conny!) helped me to feel as a member (at least of the German) SQL family! THANK YOU SO MUCH! Further goals are planned for 2018 😉

A very special thanks go to my team lead Thorsten Moeller, who supports me again and again in all my activities and a much bigger “Thank you” goes to my wife, who also supports these activities and always “kept my back”!

Björn works as a database administrator and Head of Competence for MS SQL and mySQL in Hamburg (Germany). He regularly participates in the PASS regional group meetings, events of the PASS such as SQLSaturday and SQLGrillen and he organizes the Azure Meetup group in Hamburg. He is interested in topics like SQL Server, Powershell and Azure for science fiction, snowboarding, baking and cycling.

#4 SQL Server Configuration – implement Best Practices

I hadn’t been writing this Powershell series for a long time, and I’d like to make up for it, even though a lot of things have changed (in my work & community life) … I’d like to introduce you to two things today, once in a conventional way and how Powershell could configure SQL Server (or how I did it in my script), and a simpler and faster way with the Powershell module from dbatools.io.

Best Practices with T-SQ

As part of the SQL Server installation, you should optimize certain parameters to guarantee high-performance and stable operation. This includes a variety of configuration parameters at the instance level, such as “Max. Memory” or “Max. degree of Parallelism”. All these instance settings can be set with the same SQL Server function, so I have written a “function” for these recurring T-SQL commands to stay flexible for later extensions.

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

With this simple function (even before, but now it is “easier”), I can call the following functions accordingly and configure my SQL Server according to Best Practices, in which I call the respective function, calculate values according to system equipment then execute it with ExecuteSQLCmd. The following function enables me to adapt it to the respective environment.

SetMaxMemory
Add_TempDBFiles
SetMaxDOP
SetNetworkPacketSize
SetOptimizeAdhocWorkload
SetBackupCompression
AddLocalSystemToSysadminGroup
enable_XPAgent

Example – Powershell Function “SetMaxDOP”

In order to set the value for MaxDOP (max degree of parallelism), I have to know how many logical CPUs the server has. I determined this value I had at the beginning of the script through auxiliary functions, only with this value I can decide… Usually, I set the Cost Threshold for Parallelism on our systems to 40, this fits at least 90% of the systems. Of course, you can still have a lot more to stick to the best practices, as you can read in the next section, but with these values, I was going quite well on our systems for the last two years.

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 – the function Set-DbaMaxDop

I had already written several times about the Powershell module dbatools – for example when copying an agent job – here I would like to introduce you now the comparison between the conventional way and the easier way with dbatools. Dbatools provides a function for simply setting the SQL Server instance parameter for Max Degree of Parallelism, as well as other instance parameters.

The “Set-DbaMaxDop” command offers a variety of options, the simple setting of the MaxDop at the instance level (SQL Server 2008 – 2016) and the setting of the MaxDop at the database level starting with the SQL Server 2016. The function determines all the basic conditions, based on the algorithm from the KB2806535, as well as the MaxDoP Calculator from Sakthivel Chidambaram, are used to calculate the necessary values for setting the MaxDoP. Where, of course, – as with my data/notes – it must always be pointed out that these are recommendations that do not fit 100% to every environment and application, but give the first clue.

Set-DbaMaxDop -SqlServer SQL2016

dbatools - Set-DbaMaxDop - Set to Best Practices

If you want to specify a value yourself, this is also possible … or use the internal test function “Test-DbaMaxDop” Test-DbaMaxDop -SqlServer SQL2016

dbatools - Test-DbaMaxDop

Or even decide for yourself what value is more appropriate for this environment …Set-DbaMaxDop -SqlServer SQL2016 -MaxDop 6

dbatools - Set-DbaMaxDop - Set to your own Value

Further information can be found on the following pages dbatools.io: https://dbatools.io/functions/set-dbamaxdop/ and https://dbatools.io/functions/test-dbamaxdop/

dbatools – the function Set-DbaSpConfigure

Now we have changed not only the value for MaxDop but also the value for the Threshold for the Max Degree of Parallelism. Also, this value can be set with the dbatools very easy. Since both are instance parameters, you can customize both settings individually with your own values and this command, but the internal calculation makes the use of two different commands useful. So to set the value for the threshold to 40, I use “Set-DbaSpConfigure”, which should not be unknown to us.

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

dbatools - Set-DbaSpConfigure

Especially with the IntelliSense feature, this function is a joy since the individually available parameters can be used quickly and you can quickly get the whole command. You will also find further help and examples on the help pages of the function => https://dbatools.io/functions/set-dbaspconfigure/

Thus, as opposed to my own script, you can now optimize both instance parameters with only 3 lines of code.

Import-Module .\dbatools\dbatools.psd1

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

Björn works as a database administrator and Head of Competence for MS SQL and mySQL in Hamburg (Germany). He regularly participates in the PASS regional group meetings, events of the PASS such as SQLSaturday and SQLGrillen and he organizes the Azure Meetup group in Hamburg. He is interested in topics like SQL Server, Powershell and Azure for science fiction, snowboarding, baking and cycling.

Pause / Resume / Backup an Azure SQL Database with Powershell – Part 3

Many services in Azure allow certain cost savings through automation, this can also be achieved with the Platform-as-a-Service “Azure SQL Database”, this is what this blog post is all about. Quite as simple as e.g. with the Azure Analysis Service it is not because actually there is no pause-resume functionality – here comes the backup into place, that’s what I’ll talk about first.

Azure SQL Database and the Backup topic

Before we dive deeper into the Azure SQL Database and its pause-resume functionality, we first have to take a brief look at the topic of backup, which is not quite unimportant in this context. A big advantage of Azure SQL Database against a SQL Server (on premise or Iaas) in any case is … you do not have to explicitly worry about the backup since this is created automatically. According to the database size and number of data changes, Microsoft automatically backs up the databases at regular intervals! That means that the costs for an extra backup software or even the development of own services are already gone. Depending on Performance-Level, there are different backup retention times:

  • at the basic level the retention time is 7 days.
  • at the standard level the retention time is 35 days.
  • at the premium level the retention time is 35 days.

When will my database be backed up? Also there is an answer …
The full backup takes place every week (unfortunately) at non-fixed times, differential backups are generally created every few hours and depending on the data volume, the transaction logs are saved every 5-10 minutes. A first full backup is created within the first 30 minutes after the database is created. These backups are kept according to the service tier(see above) (cost savings: you do not need an extra storage account because the backup is already included in the price). If you want to keep your backup for more than 35 days, you have the option of activating a “Long-Time Retention-Backup”. This requires a further storage account, where the backups are stored in parallel and permanently.

Backup Azure SQL Database

Pause and Resume for cost savings

This functionality is unfortunately not available with Azure SQL Database … How can I still benefit with cost savings if I would like to use this platform-as-a-service… Of course, as already explained in another blog post changing the database performance to prevent occurring load peaks. But we would really like to achieve certain cost savings with the migration to the cloud … if a department only works during the day (8am-8pm), then I need this database(s) only during the night… So why you cannot stop those services, since you only pay when the database is online?

For this scenario, the department needs the database only during the day, there is actually no solution, but a workaround, dropping the database in the evening and restoring the next morning via “Create Database from Backup” helps. This procedure has been implemented by Microsoft extremely pleasantly and does not mean a lot of effort.

# 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
}

Please note that you only delete/remove the database and not the logical server, because the backup history is stored on that logical SQL server. In order to restore this database, you’ll need the date of the last backup database. When you restore the database on the following morning, you’ll directly use this backup time to perform a restore. In Powershell, you can combine these activities very easily.

$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"

You can find more sample scripts on backup / restore here => https://docs.microsoft.com/de-de/azure/sql-database/scripts/sql-database-restore-database-powershell

Björn works as a database administrator and Head of Competence for MS SQL and mySQL in Hamburg (Germany). He regularly participates in the PASS regional group meetings, events of the PASS such as SQLSaturday and SQLGrillen and he organizes the Azure Meetup group in Hamburg. He is interested in topics like SQL Server, Powershell and Azure for science fiction, snowboarding, baking and cycling.

Resize an Azure SQL Database with Powershell – Part 2

In the first part of my Azure SQL Database Powershell Automation series I showed how to create the resource group, the logical SQL Server and the corresponding database, I would like to show you how to implement a resize of the performance level of this database.
Why should I want to resize a database? Upwards – so more power can be understood, but why again downsizing? So I start with the respective reasons for an adjustment of the selected performance class.

What power do I need when?

Let’s take an example… A larger company rents the workspace and is only active in Germany, has an application for the management / booking their meeting rooms or workplaces. This application is used by the ladies at the reception only during the “opening hours” intensively and outside these times occasionally by coworkers. Basically, the receptionists need their application and thus the database only between specific times, for example, 7 – 20 o’clock, the rest of the day the database remains almost unused …
So what is close to making this database “faster” during the day? On-prem is unfortunately not possible since you can not assign a single database so easily further resources.

Another example is evaluations or processing, where operational concerns can vary greatly when the end of the month ends with more computing power in the Azure SQL Database so that the data can be processed and deployed as fast as possible.

  • Depending on application usage
  • Depending on operational requirements
    • nightly processing
    • monthly financial statements
    • Year-end rally

Azure SQL Database

What do we need for a resize of the Azure SQL Database

  • A resource group or its name
  • A name for the logical SQL Server (which must be unique)
  • A database name
  • The new performance level (DTU)

The login to Azure and the selection of to-use Subscription I leave here outside forwards and start the actual script.
Again, I start with the definition of the necessary variables (see above):

# 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"

Now we can resize the Azure SQL Database

This is much easier than rebuilding a database because we need fewer variables (see above) and only one command line for execution. But as I’m careful I also ask if the database exist to ensure that the script does not make any “nonsense”.

# 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
}

As the before-and-after comparison shows, a resize is possible without problems and lasts only a few moments.

Vorher-Nachher-Resize Azure SQL Database

Björn works as a database administrator and Head of Competence for MS SQL and mySQL in Hamburg (Germany). He regularly participates in the PASS regional group meetings, events of the PASS such as SQLSaturday and SQLGrillen and he organizes the Azure Meetup group in Hamburg. He is interested in topics like SQL Server, Powershell and Azure for science fiction, snowboarding, baking and cycling.

First steps with Azure SQL Database and Powershell – Part 1

In addition to the ability to deploy a virtual server on a SQL server in Azure, there is also the “simpler” method to deploy only one database on demand => the Azure SQL Database. This database-as-a-service is also very good and easy to use for a variety of applications, although there are a lot of options you should think about before.

  • is a simple database enough
  • rather an elastic pool
  • In which region to deploy
  • or rather geo-redundant
  • is the “supplied” backup is enough
  • or must have to have a “Long Time Retention” backup
  • Who accesses the database from where?
  • What level of performance do I need for the database or the Elastic Pool?

An overview of the individual possibilities and their use for the respective solution, as well as a larger number of answers to many questions, can be found in the documentation of Microsoft => https://docs.microsoft.com/en-us/azure/sql-database/

Start with a simple Azure SQL Database

An important point in providing an Azure SQL Database is that you not only need a database but also a logical SQL Server (=> listener endpoint), without this “shell” you can not host databases.

In my example, we use Powershell to create a default database, of course, using a script, so that we can create the server and its database, or just the database, the same default way as needed.

What do we need to create an Azure SQL Database

  • a resource group or its name
  • a location/region for the resource group
  • admin-username and password
  • a name for the logical SQL Server (which must be unique)
  • a database name
  • typically also the IP addresses/ranges, which can access it (the own IP suffices for the first access)

The login to Azure and the selection of to-use Subscription I leave here outside forwards and start the actual script. My first script starts with the definition of various variables (see above):

# 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

Create the logical server and the Azure SQL Database using sample data

Now – as in almost all my scripts – first the query whether the resource group is already exisitiert, if not it is created. After the resource group of logical servers coming next in which we can integrate in the end our Azure SQL Database. We assign the credentials from adminlogin and password to the logical server so that the server and the databases are protected. Speaking protected, the firewall of the server we need to open external access, this I ermittel an additional function and an external service my own public IP address. With this IP address, we are now configuring the logical SQL server, and finally, we check if the desired database already exists, if not, with the desired parameters.

# 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"
}

Now we can work from our workstation to use SQL Server Tools – e.g. SQL Server Management Studio – connecting to this database and try to link the application and run some tests.

Sample Access - SSMS to Azure SQL Database

Björn works as a database administrator and Head of Competence for MS SQL and mySQL in Hamburg (Germany). He regularly participates in the PASS regional group meetings, events of the PASS such as SQLSaturday and SQLGrillen and he organizes the Azure Meetup group in Hamburg. He is interested in topics like SQL Server, Powershell and Azure for science fiction, snowboarding, baking and cycling.