Change Many Server Configuration Options in One Fell Swoop

In SQL Server 2005 and earlier, you can use several tools to change a server’s configuration options. For example, you can use GUIs such as SQL Server 2005’s SQL Server Management Studio (SSMS) or SQL Server 2000’s Enterprise Manager. However, GUIs are interactive, so you can’t use them to programmatically change a server’s configuration options.

Another tool you can use is the sp_configure system stored procedure. When you use sp_configure with its show advanced options parameter set to 1, you can change more than 60 configuration options in SQL Server 2005 and more than 30 configuration options in earlier versions of SQL Server. However, you can change only one configuration option each time you execute sp_configure. Plus, after each sp_configure execution, you have to run the RECONFIGURE WITH OVERRIDE statement to make the change take effect.

Because I wanted to programmatically change configuration options without having to execute sp_configure numerous times, I developed an alternative method. This approach involves creating a configuration (.config) file that lists all the configuration options to be changed and using the sp_ConfigureFromFile stored procedure to apply those changes.

The .config file is a text file that acts similar to an INI file. In the .config file, you need to put each configuration option on a separate line, following the format

OptionName=OptionValue

where OptionName is the name of the configuration option and OptionValue is the value you want to assign to the configuration option. You must specify the configuration option name that you’d use if you were to manually execute sp_configure with the show advanced options parameter. You can find a list of the names in the SQL Server 2005 Books Online (BOL) documentation “Setting Server Configuration Options” at msdn.microsoft.com/en-us/library/ ms189631(SQL.90).aspx. After you’ve created the .config file, save it in a directory on the SQL Server machine on which you want to change the configuration options.

Next, you use sp_ConfigureFromFile to apply the settings in the .config file. Suppose you created a .config file named SQLopts.config, which Figure 1 shows, and saved it in D:DATA. To apply the file’s settings on your local server, you need to use a sysAdmin server role login to log on to the server and execute sp_ConfigureFromFile with the code

USE masterGOEXEC sp_ConfigureFromFile  @configFileName =  'D:DATASQLopts.config'

After running the stored procedure, make sure that all the listed configuration options have been changed accordingly.

The sp_ConfigureFromFile stored procedure uses dynamic T-SQL. After the usual declarations, it enables the sp_Configure system stored procedure’s show advanced options parameter and creates a temporary table. Next, it uses the BULK INSERT statement to load the .config file’s configuration options to the table and adds an IDENTITY column. The stored procedure then loops through all the configuration options in the table, as Listing 1 shows. For each option, it first executes sp_Configure to apply the new setting, then executes the RECONFIGURE WITH OVERRIDE statement to make the change take effect.

You can download sp_ConfigureFromFile by clicking the 100516.zip hotlink at the top of this page. With sp_ConfigureFromFile, you can easily and quickly change configuration options. This stored procedure can be of great help to any SQL Server DBA who needs to change many configuration options at once with as little effort as possible.

—Eli Leiba, senior application DBA, Israel Electric