Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Scripted Configuration for SQL Server 2000 : Page 3

SQL Server's limited installation and configuration options force database developers to take on a lot of server reconfiguration work. Use scripts to quickly configure not just one but multiple servers, and simplify your SQL Server configuration process.

Basic Configuration Script for SQL Server
The script in this section (SQLReconfigure.cmd) is as simple as the others. It builds a .qry file and passes it to SQL Server via OSQL. This time, however, I use just a single script for all the configuration options.

But before I begin, let's take a look at the configuration options you can set for SQL Server by changing it with the following command:

USE master EXEC sp_configure 'show advanced option', '1' RECONFIGURE

This allows you to see all of the advanced options that SQL Server supports (just run sp_configure), as well as the real flexibility that SQL Server offers. For example, by simply running a SQL statement, you can set the initial memory that SQL Server will use, as well as the maximum amount of memory it will use.

The script takes in a list of options that you want to set for SQL Server (but the advanced option is always set), parses through the list, writes out a .qry file, and then binds to OSQL and executes the .qry file. For example, if you want to set the recovery interval for the server, as well as the maximum and minimum server memory, you would pass a string value like the following:

set OptionsToSet='recovery interval', '3';'max server memory', '2900000';
'min server memory', '2900000'

When the string is passed into the script, the script looks for the delimiter (in this case, a semi-colon) and separates the string into different commands to insert into the .qry file. This is all due to the new command-line functionality in Windows (2000, XP, 2003), which has extended the IF and FOR statements, allowing for greater flexibility in string parsing.

The SQL Server service then restarts to ensure that the options have been re-configured correctly. While this is not mandatory for all options, some options do require a server restart. So restarting the service is simpler than rebooting the server.

Let's examine the main body of Listing 8. This snippet always turns on the advanced options (by writing out a small .qry file and binding to OSQL), because some of the options you may want to configure are not available without enabling this option.

The next section of the script iterates through the list of options and generates a .qry file (see Listing 9). The really interesting line in this snippet is the FOR loop line. This is where the script expands and then loops through all the environment variables.

Finally, I take the .qry file and pass it to SQL Server, and then start and stop the service, as per the other scripts (see see Listing 10).

And there you have it! I now have set all of the general configuration options for SQL Server. You can expand/modify this script so that you can easily set database-level parameters, change service accounts, etc.

The Possibilities Are Practically Endless
What you can do with "hands-off" configuration of SQL Server is almost unlimited. By using unattended installations and configuration scripts, you can quickly recover your server in the event of a failure, or just simply reduce the shear overhead in building multiple servers—especially in development environments where changes happen often!

A word to the wise, however: don't use REM in your scripts. They actually get parsed and take the command interpreter time to work through. A good friend of mine at Microsoft warned me about this, and you will need to make this small modification.

Rob Hawthorne is a senior consultant for Microsoft Consulting Services (MSFT) in Wellington, New Zealand. Rob works extensively with Microsoft's tools and technologies, and has implemented a wide and varying range of solutions for his customers using MS tools. .
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date