QL Server is a great RDMS application, but its installation and configuration options leave the seasoned SQL user crying out for more. Upon install, SQL Server 2000’s options are limited in a number of ways. For example, it provides no features for separating the transaction logs from the data files or dropping the sample databases. Some may say so what?, but the ability to configure these options easily?especially in SQL’s .iss (unattended) file?could save you a lot of server reconfiguration effort after an install.

In this article, I demonstrate how to configure your server once you’ve installed SQL Server?without even having to open Enterprise Manager! The following procedures (call them “fudges” or work-arounds) simplify the SQL Server configuration process by using scripts, which enable you to quickly configure not just one but multiple servers!

In the Beginning, There Was Script
Much of the instruction in this article involves code?yep, good ol’ scripts that allow you to configure SQL Server. I assume you have some experience writing command files, and I am not going to discuss the code within the scripts in any great depth. You’ll get the picture pretty easily, and I’ve embedded comments in the code to explain anything that’s not quite straightforward. If you need help with any aspect of the code, the Web has a huge amount of resources.

You also have to excuse my fondness for command scripts. While all the code in this article could easily be ported to Windows Script Host (WSH), I prefer batch and command files. They just make getting to the command environment variables, log command calls, and nests inside other scripts so easy.

Each script has an intro section that provides information such as who wrote the script, what purpose it serves, its current version number, the input and output parameters, etc. These intros make maintenance easy and reduce the overhead required to manage the scripts.

One more thing: I do not like hard-coded values in scripts?especially mapped drives. If you must use a hard-coded value, then please make sure you assign it to a variable (well, a constant really) at or near the top of the script and reference the variable/constant further down. This way, if anyone needs to port your script into their environment, they will only have to modify the value in one place.

Now that I have had my rant, let’s look at what the scripts in this article allow you to do:

• Script 1 (DropDatabases.cmd) removes the sample databases that come with SQL Server, which are “pubs” and “Northwind.” These are not required in production environments, and they very rarely provide value to the deployment of SQL Server.
• Script(s) 2 (MoveMasterDB.cmd, MoveDBFiles.cmd, and AlterDatabases.cmd) moves the system databases. This is not something you need to do everyday, but when you install SQL Server, the transaction logs and database files end up on the same partition. This script enables you to either separate the transaction logs from the data files or move both the logs and data files to a completely new partition all together.
• Script 3 (SQLReconfigure.cmd) makes some of the more basic configuration option settings (e.g., sets the maximum memory size, minimum memory size, etc.)

Now, don’t assume this is all you can do with SQL Server. It’s just the tip of the iceberg. Microsoft.com has hundreds of useful knowledge articles?and just about everything you read there about manipulating SQL Server can be done in script (e.g., installing SQL Server 2000 SP3a).

All the scripts are designed (written) for Windows Server 2003, but they should run in Windows 2000 and XP. However, don’t try them in NT 4.0! You might find that some of the newer functions in the command shell don’t port backwards in the older OS.

Clean Out the Unwanted Databases
The first thing I do once I’ve installed SQL Server is get rid of the sample databases. After all, how often do you actually use them? Wouldn’t it be great if the SQL Server install had the option to not install the sample databases? Of course, but it doesn’t yet! Maybe if Microsoft gets enough demand, Yukon will offer this option.

This first script (DropDatabases.cmd) drops unwanted databases from the server. While very simple, it demonstrates the concept and the structure of all the scripts (see Listing 1).

It takes in a space-delimited list of database names (like “pubs Northwind MyDB”), iterates through the list and creates a .qry file to pass to OSQL, and then drops the databases. Simple, huh? As I specified earlier, this script has been designed for Windows Server 2003, though it should run in Windows 2000 without any issues. However, don’t attempt to run it in NT 4.0. The old “for” command may just be the script’s downfall in that OS.

Move the System Databases
By default, SQL Server gives you the option to separate the data and transaction log files from the SQL Server program files when you install it. However, the best practice documentation for SQL Server recommends separating the log files onto a separate set of spindles (drive array) from the data files. Why? Because the log files require a lot of writing and the data files a lot reading. So two drive heads reading and writing to two drives ostensibly is faster than one.

Now take this “best practice” recommendation with a grain of salt. Given the performance that modern hard drives provide, the performance gain comes into play only on systems that have very heavy disk-usage scenarios.

The databases that I have written these scripts for are MSDB, Master, tempDB, and Model. Feel free to modify the script to move user databases. I am not going to list all of the scripts, because it would just take up room and provide little value. Instead, I cover the key areas within the scripts (download the scripts here).

Script for Modifying the Master DB
All the first script (MoveMasterDB.cmd) needs to do is stop the SQL Server service, modify the registry, copy the files to the new location, and restart the SQL Server service. Pretty simple, isn’t it? To accomplish its task, the script uses two utilities from the Windows 2000 Resource Kit, Reg.exe (a command-line registry editor tool) and RoboCopy.exe (a command-line, file-copying tool). You can modify the script to use tools that you prefer.

The script first checks the old path and the new path, as shown in Listing 2.

If it detects that the old path (OldDataFilePath) and the new path (NewDataFilePath) are different (i.e., they do not match), then it updates the registry with the new values. The script repeats this same concept for all the variables passed in to it.

Next, it copies the files from one location to another, just after it finishes the registry modifications. Notice the use of %UtilsDir% (shown in bold), which is where the RoboCopy.exe and Reg.exe files are on the disk.

Finally, once the registry has been updated for all values and all files have been copied, you can restart the SQL Server service:

***** Begin Code Insert *****	REM Ok now restart the SQL Service...	(echo Restarting the SQL Server service.) >> %LogFile%	(net start MSSQLServer) >> %LogFile% 2>>&1***** End Code Insert *****

And that is basically it! Bad-dah bing, bad-dah boom, you have moved the master database to a new location.

Script for Modifying the Model and MSDB
The next script (MoveDBFiles.cmd) moves the Model and MSDB databases simply by using the “detach” and “reattach” commands. Once again, I have stripped the script down to show only its key elements (see Listing 3).

The first thing I do here is stop the SQL Server service. Then I add a special flag with the value “-T3608” to the registry and restart the SQL Server service. This flag tells SQL Server not to recover any databases except the Master database. This allows for the MSDB and Model databases to be moved, since they will not be in use.

Next, I build a .qry file in the script, which I pass to OSQL to execute the detach command. You can connect to OSQL with either a trusted connection (Windows Authentication) or SQL Authentication, as shown in Listing 4.

Then once again, I use RoboCopy.exe to copy the transaction log and data files (if their paths are different) from the old location to the new location, as shown in Listing 5.

Then I just reverse what I already have done: attach the databases, stop SQL Server, remove the trace flag “-T3608”, and then restart the SQL Server. I’m sure by now you can pretty much guess what that code looks like.

Script for Modifying the tempDB DB
The last system database I need to modify is good ol’ tempDB. So once again, I look at the key parts of the script (AlterDatabases.cmd), rather than the whole thing (see Listing 6).

I apply a similar principle to this script as I did to the previous scripts (i.e., write out a .sql file with my parameters in it and pass it to OSQL). Then I simply stop and start the SQL Server service so that if I run sp_helpdb it reports the correct paths for the data and transaction log files. I also delete the old data and transaction log files, as shown in Listing 7.

And there you have it: all the scripts you need to successfully move your transaction logs and data files for your system databases.

Before moving on to general configuration for SQL Server, make sure that you move the databases in this order: MSDB, Model, tempDB, and finally Master. This ensures that Master is up to date with all of the other system database configurations before it moves. And in the event of a catastrophe, it allows you to at least recover the databases to the point at which they last were.

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 masterEXEC 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.