Scripted Configuration for SQL Server 2000

Scripted Configuration for SQL Server 2000

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



Share the Post:
Global Layoffs

Tech Layoffs Are Getting Worse Globally

Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019

Cybersecurity Banking Revolution

Digital Banking Needs Cybersecurity

The banking, financial, and insurance (BFSI) sectors are pioneers in digital transformation, using web applications and application programming interfaces (APIs) to provide seamless services to

FinTech Leadership

Terry Clune’s Fintech Empire

Over the past 30 years, Terry Clune has built a remarkable business empire, with CluneTech at the helm. The CEO and Founder has successfully created

The Role Of AI Within A Web Design Agency?

In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used

Global Layoffs

Tech Layoffs Are Getting Worse Globally

Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019 tech firms, as per data

Huawei Electric Dazzle

Huawei Dazzles with Electric Vehicles and Wireless Earbuds

During a prominent unveiling event, Huawei, the Chinese telecommunications powerhouse, kept quiet about its enigmatic new 5G phone and alleged cutting-edge chip development. Instead, Huawei astounded the audience by presenting

Cybersecurity Banking Revolution

Digital Banking Needs Cybersecurity

The banking, financial, and insurance (BFSI) sectors are pioneers in digital transformation, using web applications and application programming interfaces (APIs) to provide seamless services to customers around the world. Rising

FinTech Leadership

Terry Clune’s Fintech Empire

Over the past 30 years, Terry Clune has built a remarkable business empire, with CluneTech at the helm. The CEO and Founder has successfully created eight fintech firms, attracting renowned

The Role Of AI Within A Web Design Agency?

In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used in design, coding, content writing

Generative AI Revolution

Is Generative AI the Next Internet?

The increasing demand for Generative AI models has led to a surge in its adoption across diverse sectors, with healthcare, automotive, and financial services being among the top beneficiaries. These

Microsoft Laptop

The New Surface Laptop Studio 2 Is Nuts

The Surface Laptop Studio 2 is a dynamic and robust all-in-one laptop designed for creators and professionals alike. It features a 14.4″ touchscreen and a cutting-edge design that is over

5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created

Cisco Splunk Deal

Cisco Splunk Deal Sparks Tech Acquisition Frenzy

Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the

Iran Drone Expansion

Iran’s Jet-Propelled Drone Reshapes Power Balance

Iran has recently unveiled a jet-propelled variant of its Shahed series drone, marking a significant advancement in the nation’s drone technology. The new drone is poised to reshape the regional

Solar Geoengineering

Did the Overshoot Commission Shoot Down Geoengineering?

The Overshoot Commission has recently released a comprehensive report that discusses the controversial topic of Solar Geoengineering, also known as Solar Radiation Modification (SRM). The Commission’s primary objective is to

Remote Learning

Revolutionizing Remote Learning for Success

School districts are preparing to reveal a substantial technological upgrade designed to significantly improve remote learning experiences for both educators and students amid the ongoing pandemic. This major investment, which

Revolutionary SABERS Transforming

SABERS Batteries Transforming Industries

Scientists John Connell and Yi Lin from NASA’s Solid-state Architecture Batteries for Enhanced Rechargeability and Safety (SABERS) project are working on experimental solid-state battery packs that could dramatically change the

Build a Website

How Much Does It Cost to Build a Website?

Are you wondering how much it costs to build a website? The approximated cost is based on several factors, including which add-ons and platforms you choose. For example, a self-hosted

Battery Investments

Battery Startups Attract Billion-Dollar Investments

In recent times, battery startups have experienced a significant boost in investments, with three businesses obtaining over $1 billion in funding within the last month. French company Verkor amassed $2.1

Copilot Revolution

Microsoft Copilot: A Suit of AI Features

Microsoft’s latest offering, Microsoft Copilot, aims to revolutionize the way we interact with technology. By integrating various AI capabilities, this all-in-one tool provides users with an improved experience that not

AI Girlfriend Craze

AI Girlfriend Craze Threatens Relationships

The surge in virtual AI girlfriends’ popularity is playing a role in the escalating issue of loneliness among young males, and this could have serious repercussions for America’s future. A

AIOps Innovations

Senser is Changing AIOps

Senser, an AIOps platform based in Tel Aviv, has introduced its groundbreaking AI-powered observability solution to support developers and operations teams in promptly pinpointing the root causes of service disruptions

Bebop Charging Stations

Check Out The New Bebob Battery Charging Stations

Bebob has introduced new 4- and 8-channel battery charging stations primarily aimed at rental companies, providing a convenient solution for clients with a large quantity of batteries. These wall-mountable and

Malyasian Networks

Malaysia’s Dual 5G Network Growth

On Wednesday, Malaysia’s Prime Minister Anwar Ibrahim announced the country’s plan to implement a dual 5G network strategy. This move is designed to achieve a more equitable incorporation of both

Advanced Drones Race

Pentagon’s Bold Race for Advanced Drones

The Pentagon has recently unveiled its ambitious strategy to acquire thousands of sophisticated drones within the next two years. This decision comes in response to Russia’s rapid utilization of airborne

Important Updates

You Need to See the New Microsoft Updates

Microsoft has recently announced a series of new features and updates across their applications, including Outlook, Microsoft Teams, and SharePoint. These new developments are centered around improving user experience, streamlining