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.

pstraceflag

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

   

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

2 Comments on “Setting SQL Server Start-up Parameters across Multiple Instances”

  1. Why not just use an instance startup procedure with the global trace flags in there? It’s auditable, contained within the database, and much simpler than this solution…

    1. Hi David,

      You are right. I agree with you. This is just another way of doing it. In my opinion, the trace flags like 3226 doesn’t actually require auditing. I had a client previously who had a trigger to not create objects inside master database. This can be a better way of implementing the startup parameters for such scenarios.

Leave a Reply

Your email address will not be published.