icrosoft’s free server tool, ADS (Automated Deployment Services), provides system administrators with a consistent way to deploy the Windows OS, but what about RDBMS deployment? Does setting up SQL Server on a number of servers mean you have to run setup.exe again and again, specifying the same parameters each time? No, it doesn’t.

This article demonstrates how to configure ADS to install SQL Server 2000?without having to run setup.exe! The best part is, with the ADS Controller, you can deploy SQL Server to multiple servers at once, without having to visit each machine individually.

Before following this tutorial, you should have ADS installed and working to deploy OS images (“snapshots” of an operating system, not GIFs or JPEGs), and your image should contain the ADS Administration Agent (the agent that resides at the OS level). The ADS Quick Start Guide (bundled with the product) is an excellent reference for installing and configuring ADS. Optionally, installing the ADS Administration Agent under a domain-level account would be easier than using LocalSystem. This isn’t mandatory, but the article is tailored to using a domain account rather than a system account to enable the ADS Administration Agent to use UNC paths without having to map drives.

Guidelines for the Environment
Much of the instruction in this article involves code?mainly, command statements and XML?that enables ADS to run jobs and install SQL Server. It assumes you have some experience writing XML and understand general command-line syntax. It won’t discuss the code in any great depth; the code merely serves as a reference point to get you started. Embedded comments in the code explain anything that may not be quite clear.

You also have to excuse my fondness for command-line code. While the batch files this article uses could easily be ported to Windows Script Host (WSH), I prefer command files. They’re just a little cleaner, and they generally require only a couple of simple commands rather than a lot of extraneous checking and error handling. Luckily, ADS handles errors well.

Each XML file (called job sequences) has an intro section that provides information such as who wrote the job, what purpose it serves, its current version number, the changes made, etc. These details make maintenance easier and reduce the overhead required to manage the jobs in the future.

The article begins with a job sequence that has simple syntax, and contains hard-coded values. However, to demonstrate the capabilities and flexibility of ADS, the job sequences will get progressively complex. Since I’m not a fan of hard-coded values in the job sequences anyway, I use ADS’s native functionality to replace parameters where these values are required. Once again though, this is just the framework you need to get your scripts up and running, so don’t feel constrained in any way from customizing the process.

All in all, the environment has the following requirements:

• Install SQL Server 2000 (and SP3a) on a Windows Server 2003 machine, which can be the Web, Standard, Enterprise, or Datacenter edition. This allows you to use the standard features of the ADS Administration Agent. Some of the agent’s features are reduced for Windows 2000 Server, and the ADS Agent is not supported in the Windows desktop operating environments.
• Have the ADS Administration Agent running under a domain-level account (however, this does not have to be a domain administrator account), which allows easier access to network resources. If you choose not to use a domain account, be sure to map your drive first whenever you have to access a network resource. You can use the following command:

  net use : \ /u: 

  Where:

  • is the letter of the mapped drive (e.g., s:).
  • is the name of the server to which you are mapping a drive.
  • is the name of the share to which you are mapping a drive.
  • is the domain and username of an account that has access to the shared resource.
  • is the password for the username that has access to the shared resource.
• Have the SQL Server Media available on a network share. You can have this share on the ADS Controller or any other server in the environment. However, you must make sure that the user that the ADS Administration Agent is running under has at least read access to the share.

Additionally, the following are the job sequences you will create, from the simplest to the most complex:

• Job 1 (InstallSQL1.xml) installs SQL Server and SP3a by specifying a basic .iss file (the one included on the SQL Server media) for SQL Server 2000. This demonstrates the ability to quickly deploy SQL Server to your registered devices.
• Job 2 (InstallSQL2.xml) is an extension of InstallSQL1.xml. It shows you how to install SQL Server 2000 by using a more dynamic approach and the built-in tools of ADS. This example swaps values for specific variables that your devices have registered.
• Job 3 (SQLReconfigure.xml) provides the framework you need to incorporate the command files that I wrote about in the article “Scripted Configuration for SQL Server“.

Installing SQL Server
The first thing to do is install SQL Server using ADS and the default .iss files included with the SQL Server media. Although you don’t need to be an expert in unattended installations of SQL Server to follow along, understanding .iss files would be a benefit to any SQL Server administrator.

The first job, InstallSQL1.xml (contained in the accompanying code download), does not do any parameter substitution. You simply use the ADS native tools to copy the file (in this case, the /bmonitor/bmfilexfer.exe utility) and provide the path to the .iss file’s location. Then, you just launch the installation. It’s too easy!

Note: When installing SQL Server 2000 on a Windows Server 2003 machine, you normally would get a warning pop-up specifying that SQL Server SP2 and below is not supported, which would break most unattended installation methods. You can suppress this warning using the information in Microsoft Knowledge Base Article “How to Suppress the Appshelp Message in an Unattended Installation“. You will notice the Appshelp Message is the first command (known as a task) launched using the native Windows REG utility.

Job 1: InstallSQL1.xml
 xmlns="http://schemas.microsoft.com/ads/2003/sequence">      /BMONITOR/BmFileXfer.exe          -d      "C:Build EnvSrcTreeSQL2KEESQLINS.ISS"      "deviceharddisk0partition1WindowsTempSQLINS.ISS"            .cmd.exe          /c REG ADD "HKLMSOFTWAREMicrosoftWindows NTCurrentVersionAppCompatFlags"
       /v {ff25f0b5-c894-45f4-a29d-1bdd0c7926cd} /t REG_DWORD /d 1            .cmd.exe          /c start /wait \IDSTEST01SrcTreeSQL2KEEx86SETUPsetupsql.exe
      -s -m -SMS -f1 "C:WindowsTempsqlins.iss"            /BMONITOR/BmFileXfer.exe          -d      "C:Build EnvSrcTreeSQL2KEESQLDEF.ISS"      "deviceharddisk0partition1WindowsTempSQLDEF.ISS"            .cmd.exe          /c start /wait \IDSTEST01SrcTreeSQL2KSP3ax86SETUPsetupsql.exe
       -s -m -SMS -f1 "C:WindowsTempsqldef.iss"            /BMONITOR/reboot   

Note: You will need to change the servername, sharename, and SQL directory paths in the XML to suit your environment.

If you’ve used ADS before, you may have noticed that I used the REG.exe utility for modifying the registry rather than the native ADS /bmonitor/bmsetreg.exe utility. The reason? I found that, rather than allowing me to add a new key, the bmsetreg utility requires that the registry key already be present. Just a little quirk you need to keep in mind.

The simple use of a standard .iss file certainly makes the installation task easier, but it is not always the best method. For example, what if you wanted to change the service account or the code page, or use different paths for the program and data files? And what if you wanted to do this on a machine-by-machine basis? Those scenarios are when you can start to utilize the true power of ADS.

Getting Smart About Installing SQL Server
When you install SQL Server, you have the option by default to separate the data and transaction log files from the SQL Server program (binary) files. The .iss file for SQL Server contains two path variables that enable this option: szDir and szDataDir, where:

• szDir is the path to the binary files, such as OSQL.exe.
• szDataDir is the path to the data and transaction log files.

This is a nifty feature, but what happens if you install SQL Server on a machine that has multiple disks and then attempt to install it on a machine with a single disk? Your unattended installation would fail. You would have to either manually edit the .iss file every time you deploy to a different type of machine or use a string substitution program that swaps specific values in your .iss file with parameters that you specify in ADS. Since ADS has this feature (called /bmonitor/bmstrrep.exe) built in, you don’t even have to write code!

Consider the following sample job sequence, which allows you to use this string substitution functionality:

Job 2: InstallSQL2.xml
 command="" xmlns="http://schemas.microsoft.com/ads/2003/sequence">      /BMONITOR/BmFileXfer.exe          -d      "C:Build EnvSrcTreeSQL2KEESQLINS.ISS"      "deviceharddisk0partition1WindowsTempSQLINS.ISS"           doesReboot="false">    .cmd.exe          /c REG ADD "HKLMSOFTWAREMicrosoftWindows NTCurrentVersionAppCompatFlags"
       /v {ff25f0b5-c894-45f4-a29d-1bdd0c7926cd} /t REG_DWORD /d 1            /BMONITOR/bmstrrep.exe          "deviceharddisk0partition1WindowsTempSQLINS.ISS"      "^szDir^"      "$szDir$"            .cmd.exe          /c start /wait \IDSTEST01SrcTreeSQL2KEEx86SETUPsetupsql.exe
       -s -m -SMS -f1 "C:WindowsTempsqlins.iss"            /BMONITOR/BmFileXfer.exe          -d      "C:Build EnvSrcTreeSQL2KEESQLDEF.ISS"      "deviceharddisk0partition1WindowsTempSQLDEF.ISS"            /BMONITOR/bmstrrep.exe          "deviceharddisk0partition1WindowsTempSQLDEF.ISS"      "^szDir^"      "$szDir$"            .cmd.exe          /c start /wait \IDSTEST01SrcTreeSQL2KSP3ax86SETUPsetupsql.exe
       -s -m -SMS -f1 "C:WindowsTempsqldef.iss"            /BMONITOR/reboot   

Now within ADS you specify the parameters to be associated with a machine, as shown in Figure 1.

Figure 1: Specify the Parameters to Be Associated with a Machine

How does this work? When ADS deploys the file to the machine, it opens and scans the file until it finds the ^^ pattern in the file. It then performs a very simple replace (i.e., replaces the ^^ with the ADS parameter referenced by $$). The utility that ADS uses is /bmonitor/bmstrrep.exe.

This example has changed only the paths for the data and binary files (szDataDir and szDir). You could further modify the .iss file and add additional parameters to ADS to make almost every parameter substitutable (including the path to the .iss file). This would provide a tremendous amount of flexibility when you deploy different types of installations.

As a side note, when you add new ADS parameters, you can define them as type encrypt value, which provides some basic encryption on the ADS parameter stored in the ADS database and keeps snooping eyes from easily finding the sa or the usernamepassword pair from the .iss file.

Wrapping It All Together
My previous DevX article provided the framework for performing some standard SQL style tasks, such as configuring the advanced options in SQL Server. Because I believe in code reuse, I am using the same scripts again! Except this time I wrap them up in an ADS job sequence.

You may be wondering why you would do this when you can simply run the script on the server. Well, my observant reader, you would have to run these scripts one by one against every server you are building. By using the ADS Agent, you can execute the scripts against as many servers as you wish?all at once if you want! Now that sounds like my type of administration.

In this job sequence, you execute two of the scripts: SQLReconfigure.cmd and DropDatabases.cmd. These scripts demonstrate the framework for the job sequence, which in turn gives you the ability to expand and modify them to your needs. In fact, you can extend the sequencing file to include as many additional scripts as you like.

I’ve written this job sequence to be a standalone sequence. However, you could easily incorporate it into the other job sequences so that you get a complete custom installation of SQL Server, right from start to finish!

This job sequence will copy the script files to the local machine, set up the environment variables for the script, and then execute the script. The environment variable and script execution are done in a single task, as the variables are local only to the command being executed (i.e., not system-wide).

Job 3: SQLReconfigure.xml
 xmlns="http://schemas.microsoft.com/ads/2003/sequence">      /BMONITOR/BmFileXfer.exe          -d      "\IDSTEST01SrcTreeScriptsSQLReconfigure.cmd"      "deviceharddisk0partition1WindowsTempSQLReconfigure.cmd"            /BMONITOR/BmFileXfer.exe          -d      "\IDSTEST01SrcTreeScriptsDropDatabases.cmd"      "deviceharddisk0partition1WindowsTempDropDatabases.cmd"            .cmd.exe          /v:on /c set UseTrustedConn=Yes && OptionsToSet='recovery interval',
       '3';'max server memory', '2900000' && start /wait /B 
       %SYSTEMDRIVE%WindowsTempSQLReconfigure.cmd            .cmd.exe          /v:on /c set UseTrustedConn=Yes && DBsToDrop=Pubs;Northwind && start 
      /wait /B %SYSTEMDRIVE%WindowsTempDropDatabases.cmd       

Note: Be careful when using the ADS Sequence Editor against the XML job sequences. Whenever the ADS editor finds an “&&” it turns it into “&&”. This is due to the ampersand being a character that needs escaping in valid XML. Consider using a simpler editor like Notepad to ensure that your XML is as you expect it.

And there you have it! You have now incorporated some core tasks you should achieve before completing your SQL Server install?and all without having to touch the server. Consider which other tasks you wish to achieve and then think about extending the job sequence to include them.

As an additional tip, if you run the ADS Agent as “localsystem” and would like to see the code executing on your server (by default, it executes as a background task), you can modify the ADS Agent service by selecting the action “Allow service to interact with the desktop” on the Log On tab (see Figure 2).

Figure 2: Allow Service to Interact with the Desktop

This allows the LocalSystem account to interact with the desktop, enabling you to see the command window pop-up with the commands you issued from the ADS Controller to the agent. Be cautious though: this can be a security issue (or so I’ve heard!).

The Possibilities Are Practically Endless
What you can do with ADS and a scripted “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 overhead in building multiple servers?especially in development environments where changes happen often!

Using ADS as a free management tool affects a significant change in the way you handle the Windows environment, and it allows administrators a lot more flexibility and control of their servers. So use it!