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 easilyespecially in SQL's .iss (unattended) filecould 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 Serverwithout 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 codeyep, 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 scriptsespecially 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 articlesand 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.