Solutions present themselves when a challenge arises... usually!
I had a challenge of adding trace flag 1222 for capturing deadlocks to the SQL startup parameters, on over 1000 instances of SQL. All the instances were SQL Server 2008R2 SP2 and higher. It is worth mentioning that this solution would only work on SQL server 2008 R2 SP2 and higher as the sys.dm_server_registry DMV which is been used in the script is not available in lower versions.
The SQL service startup parameters can be changed using the SQL Server Configuration Manager. A remote desktop session to the relevant machine is required in order to launch the SQL Server Configuration Manager. This process can be time consuming when dealing with large number of scattered SQL instances, also it increase the chance or making mistake as new parameters will be entered manually.
The following screenshot display the SQL Server parameters in SQL Server Configuration Manager.
Information regarding the SQL service startup parameters for each instance is stored in registry. Therefore any changes to SQL service parameters using SQL Server Configuration Manager will be directly reflected in Registry.
As shown in the following snapshot, each startup parameter is stored as a separate entry (string value). Also worth noting that each parameter has a name starting with SQLARG0 and the number in the name increments for each additional parameter.
Going back to our challenge, in order to add a new startup parameter (in this instance trace 1222), -T1222 should be added as SQL startup parameter. This can be achieved by adding the parameter directly using SQL Server Configuration Manager as show below.
So the idea to automate this task can be achieved by directly modifying the Registry. And with having a Script in place it can be used in conjunction with CMS, so change can be rolled out to 1000 instance with a click of a button....MAGIC
Let's dive into the script, which has three sections. First, defining the startup parameter that should be add by setting the value for @Parameters variable (highlighted section)
The next section will do a clean up for the specified startup parameter in case it already does exist (the purpose of this section is to make re-runable, so the code will not break if the parameter already does exist) This section checks the registry hive for the SQL instance using the sys.dm_server_registry DMV. As the Registry hive that holds the startup parameters can be different for different instances (default and named instance), using this DMV can simply the process as it always will return the correct hive path for startup parameters.
The last section of the code adds the new parameter by using xp_regwrite.
Note: For the script to run successfully SQL service account should have full access to the registry hive or be a member of local admin
After successfully testing the script against a SQL server instance, functionality can be extended by using Central Management Server(CMS). In order to find out more how to use CMS to Manage your environment, I would refer you to a fantastic article by Ryan Adams
