Setting SQL Server Start-up Parameters across Multiple Instances

Hello Geeks,

This is the first blog of the part of blogs set I am going to write for next few months. I have been working on a centralized approach for Database Administration. I know this has been mostly the case for bigger organisations. This blog series will target DBAs who are finding difficult to manage multiple SQL instances without any centralised DBA maintenance and monitoring tools.

As a DBA it is assumed that the setup is always perfect and need not be changed over time. But, I have seen many instances where the standard setup of multiple servers is not following best practices. And it is up to the DBA to make these changes across the environment over time and again. In this blog I will take the example of a start-up trace flag which is essential to cut down the noise in the error log.

When is the last time you have considered to add 3226 as part of start-up parameter when installing a new instance? If you have a standard practice to include it, you have a better process than many other places. Trace flag 3226 is used to supress the success messages for backups. I will use this as an example in this post.

I can use either C# or Powershell to do this. I have seen many of the DBA inclined towards using Powershell. Personally I like C# .NET more. For this post I will use Powershell. First things first, to run the Powershell script on remote computer the powershell remoting should be enable on the remote computer. More details about Enable-PSremoting.

The following code takes two parameters

1. Comma separated list of computers.
2. Start-up parameter value to be set.

The script then gets the list of SQL instances installed on the computer. Then it adds the start-up parameter to each and every instance on the computer. It is intelligent enough to add the parameter as the last value in the parameter list.

AddStartupParam_Remote.ps1

param([string]$computers, [string]$parameter)

$option = [System.StringSplitOptions]::RemoveEmptyEntries
$comps = $computers.Split(",",$option).Trim()
foreach($computer in $comps)
{
     write-host "" 
     write-host "Checking $computer" 
     $path = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"
     
     $objects = invoke-command -computername $computer -scriptblock {param($path) get-itemproperty $path} -Args $path

     $members = invoke-command -computername $computer -scriptblock {param($objects) $objects | Get-Member} -Args $objects
     
     foreach ($member in $members)
     {
        if ($member.Definition -match "MSSQL*")
        {
            $def = $member.Definition.ToString()
            $instance = $def.Substring($def.IndexOf("=") + 1,$def.Length - $def.IndexOf("=") - 1)
            $path1 = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\" + $instance + "\MSSQLServer\Parameters\"
            write-host ""
            write-host "For instance $instance"
            :loop for($i=1;$i -le 10;$i++)
            {
                $sqlarg = "SQLArg$i"
                $params = invoke-command -computername $computer -scriptblock {param($path1, $sqlarg) get-itemproperty $path1 -name $sqlarg -ErrorAction SilentlyContinue} -Args $path1, $sqlarg
                write-host "$sqlarg = "$params.$sqlarg
                if ( $params."$sqlarg".Length -eq 0)
                {            
                    echo "setting property for $instance"
                
                    invoke-command -computername $computer -scriptblock {param($path1, $sqlarg, $parameter) set-itemproperty $path1 -name $sqlarg -value $parameter} -Args $path1, $sqlarg, $parameter
                    break loop
                }
                else
                {
                    if ( $params."$sqlarg" -eq $parameter)
                    {
                        echo "Key value already exists for $instance"
                        break loop
                    }
                }
            }
        }
     }
 }

The execution of the above script on a couple of machines looks like the following.

The script has skipped adding the start-up parameter on COMP1 for default instance and on COMP2 for two named and a default instance. This is as the trace flag is already added.

This can be used for setting various other start-up parameters or by a simple tweak to set other registry parameters. But let me warn you, it is not a recommended way of changing things at registry level unless you are 100% sure about what you are changing.

Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook