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


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'
            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)+"'
            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

dbatools – Migration of SQL Agent Backup Jobs

You can think of what you want, but sometimes you have to jump over your own shadow… usually I’m an advocate of SQL Server’s own backup, whether it’s done to disk or to URL or even to network share, but here I had to give in and set up the backup using 3rd party tools. Said and done…

At this customer, we had already switched everything to 3rd party backup, but not initiated by the central server, but SQL Server initiated. The SQL Server Agent starts the backup as a command line call from within an Agent Job. For this, I had to copy the backup jobs from an existing server, as well as all related SQL Server objects. I started using “Create Objects to NewQuery” but received the error message that the mail component or receiver is not existing or not configured on the destination side. So I had to first migrate the complete SQL-Mail configuration, take over the operators and if we are already on the mailing configuration, then I can also migrate the SQL Agent Alerts recommended by Brent Ozar

doing backup with dbatools - THE powershell modules for all DBAs

used powershell modules from dbatools for backup

Just think about how to migrate the configuration of SQL mail, operators and custom alerts from one SQL Server to the other without much effort, so I mean with SQL Server builtin methods… This is not really much effort, but still a little more than 5 minutes. So what is closer than to deal more intensively with the dbatools …
With following commands “Copy-SqlDatabaseMail” you can migrate the SQLMail configuration, with “Copy-SqlOperator” you’ll take over all operators, continuing with “Copy-SqlAlert” and finally copy the jobs. It is not that much work 😉

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

So only 4 lines of Powershell code to ease the work, also thanks to the unification of the parameters within dbatools which also eases the parameterization of the individual commands in a very simple way – to find a quick and uncomplicated way to do any migration. I have to recommend the use of dbatools to every DBA!

Another blogpost (German) can be found here => https://www.sql-aus-hamburg.de/sql-server-backup-erstaunliche-grundlagen/