Scripting the Installation of SQL Server 2005

April 26, 2007

Having a consistent SQL Server environment is important. Knowing that all your SQL Server machines are setup the same helps to minimizing confusion when managing multiple machines. If you have lots of machines, it is absolutely imperative that all your SQL Server setups be the same. In order to make sure all of your SQL Server 2005 machines are the same you need to develop a method where you can script your installations. This article will discuss one method of scripting your installation to ensure all your machines are set up the same.

Preparing for an Installation

Before you consider setting up SQL Server via a script, you need to identify what components you want to install. You will also need to determine how you are going to configure SQL Server. The kind of things you will need to consider: are you just installing the database engine or are you also installing other components like, Analysis Services, Reporting Services, Client tools, etc. Also, you need to determine where you want to install the data and log files, what accounts are you going to use to run each service, what collation settings do you want, etc. The following document will help you understand how to install SQL Server from the command prompt, as well as all the different options for identifying what components you want to install: http://msdn2.microsoft.com/en-us/library/ms144259.aspx

Setup.exe

The Setup executable is used to script an installation of SQL Server. This executable needs to be run from a batch file or be executed directly from the command prompt. You pass different parameters to this program to identify what SQL Server components will be installed. Parameters can be passed directly on the Setup.exe command, or you can place the parameters in an INI file. Below I will show you how I used the Setup.exe via a batch file to script my installations.

Limitation of the Setup Executable

The Setup.exe process does have some limitation. It does a great job of installing SQL Server components, but it doesn’t allow you to specify all configuration options. For example, it doesn’t allow you to set up SQL Server to listens on a port other than 1433. Therefore, if you want to build a process that not only installs SQL Server but also sets up the installation to meet your configuration requirements then you will need to incorporate other methods to configure SQL Server after the Setup.exe process completes.

My Generic Installation and Configuration Process

In my environment, I wanted to build a generic scripting process. This process was to be used for each one of my SQL Server machine installations. This process would use Setup.exe to install the SQL Server components. However, to set additional SQL Server configuration options I used a combination of T-SQL commands, registry hacks, and execution of the “sac” utility.

I had the following requirements in mind when I built my installation and configuration process:

  • No passwords would be stored in a script file
  • A single command needed to be used to start the installation process
  • The process needed to work on any SQL Server box
  • Each SQL Server box needed the same drive configuration
  • The process had to support using the 2 CD SQL Server software set (required because some of our machines only have CD drives).

My generic installation and configuration process consists of a single batch script, with a number of supporting scripts and files. The batch file first executes the Setup.exe twice, once for each CD. The first execution installs the server components and then the second execution installs the client components. The batch file then executes the “sac” utility to perform some surface area configuration changes. Lastly, I used the “sqlcmd” utility to execute a T-SQL script to complete the configuration of my SQL Server machine. Let’s review each one of these components.

Here is the batch file (This script has line breaks for display purposes. Please download install.txt):

echo off
echo "Insert CD1 (SQLSRVRS) and press enter"
pause
echo "change directory to the CD drive"
e:
echo "starting installation of server components on CD1, please wait....."
Start /wait setup.exe /qb USERNAME="UserName" COMPANYNAME="MyCompany"  
 INSTALLSQLDIR="D:\Program Files\Microsoft SQL Server\" 
 INSTALLSQLSHAREDDIR="D:\Program Files\Microsoft SQL Server\" 
 INSTALLSQLDATADIR=F:\ ADDLOCAL=SQL_Engine,SQL_Data_Files,SQL_Replication,
  SQL_FullText,SQL_DTS INSTANCENAME=MSSQLSERVER SQLACCOUNT=%1 
   SQLPASSWORD=%2 AGTACCOUNT=%1 AGTPASSWORD=%2 SQLBROWSERACCOUNT=%1 
   SQLBROWSERPASSWORD=%2 SQLAUTOSTART=1 AGTAUTOSTART=1 SECURITYMODE=SQL 
   SAPWD=%2 DISABLENETWORKPROTOCOLS=2 
echo "completed installation of components on CD1"
copy "c:\Program Files\Microsoft SQL Server\90\Setup BootStrap\Log\Summary.txt" 
 "c:\Program Files\Microsoft SQL Server\90\Setup BootStrap\Log\CD1_Summary.txt"
echo "Insert CD2 (SQL Tools) and press enter"
pause
echo "starting installation of Client components on CD2, please wait....."
Start /wait setup.exe /qb USERNAME="UserName" COMPANYNAME="MyCompany"  
 INSTALLSQLDIR="D:\Program Files\Microsoft SQL Server\" 
  INSTALLSQLSHAREDDIR="D:\Program Files\Microsoft SQL Server\" 
  INSTALLSQLDATADIR=F:\ 
  ADDLOCAL=Client_Components,Connectivity,SQL_Tools90,SQL_Documentation,SQL_BooksOnline 
  INSTANCENAME=MSSQLSERVER SQLACCOUNT=%1 
  SQLPASSWORD=%2 
  AGTACCOUNT=%1 
  AGTPASSWORD=%2 
  SQLBROWSERACCOUNT=%1 
  SQLBROWSERPASSWORD=%2 
  SQLAUTOSTART=1 
  AGTAUTOASTART=1 
  SECURITYMODE=SQL 
  SAPWD=%2 
  DISABLENETWORKPROTOCOLS=2
echo "completed installation of components on CD2"
copy "c:\Program Files\Microsoft SQL Server\90\Setup BootStrap\Log\Summary.txt" 
 "c:\Program Files\Microsoft SQL Server\90\Setup BootStrap\Log\CD2_Summary.txt"
echo "configuring SQL Server installation
"c:\Program Files\Microsoft SQL Server\90\Shared\sac" in c:\SQL2005_install\sac_input.txt
rem setting up sql server defaults
"D:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd -USA -P%2 -S. -i c:\install_SQL.sql

To execute this script I would enter something like the following at the command prompt:

c:\SQL2005_install\sql_install.bat <service account name> <password> 

Here you can see, I stored my batch file in directory “c:\SQL2005_install\” on the C drive. In addition, I stored all my supporting scripts and files in this directory as well. Execution of the “sql_install.bat” file required two input parameters, a “service account name” and the “password” for the service account.

The script assumes that the CD-ROM drive is configured to be the “e” drive. The running of the Setup.exe is executed using the “Start” command with the “/wait” option. This allows my batch script to wait until the Setup.exe has completed execution before proceeding to the next command. As stated earlier the Setup.exe command installs all the SQL Server 2005 components that are identified along with the exe command. As you can see the parameters passed on the first exe identifies server components, where as the parameters on the second execution of Setup.exe only identifies client components. After each one of these executions, I copy the summary.txt file to an alternate location. I do this so I can view the summary file after my process runs, so I can make sure my installation was successful and didn’t have any errors.

After the Setup.exe executions have completed, SQL Server 2005 is installed. The next couple of steps configure the SQL Server installation. To perform the configuration I used the “sac” and “sqlcmd” utilities.

The sac utility specifies the surface area configuration for my server, much like you would do using the SQL Server Surface Area Configuration GUI tool. I use this utility to enable the features I want, like “Ad Hoc Remote Queries”, “CLR Integration”. “Database Mail”, etc. The sac utility requires an XML input file to identify how your surface area is to be configured. To create this XML file I first used the sac utility to output this file. In order to do this I first had to manually install and configure a test SQL Server machine the way I wanted to all my machines to be installed. Once my test machine was configured, I used the “out” option of the “sac” utility to create the “c:\SQL2005_install\sac_input.txt” XML file used in the above script. You can use Books Online to find out more information about the “sac” utility.

Lastly, I used a T-SQL script to set my final configuration options. My last few configuration options require some registry hacks using T-SQL. As you can see in my batch file above, I used the “sqlcmd” utility to execute my T-SQL script. Here is a copy of the T-SQL input file used by “sqlcmd” to perform my registry hacks:

-- set the default locations for data and log files
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
 N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', 
REG_SZ, N'F:\MSSQL\Data'
go
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
 N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', 
REG_SZ, N'F:\MSSQL\Data'
-- Set TcpIP ports
DECLARE @VALUE VARCHAR(200)
              
SET @VALUE = '8787'                
EXECUTE master..xp_regwrite
 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER\SuperSocketNetLib\Tcp\IP1\',
'TcpPort',
'REG_SZ',
@VALUE
SET @VALUE = '8787'
EXECUTE master..xp_regwrite
 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER\SuperSocketNetLib\Tcp\IP2\',
'TcpPort',
'REG_SZ',
@VALUE
SET @VALUE = '8787'
EXECUTE master..xp_regwrite
 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER\SuperSocketNetLib\Tcp\IPAll\',
'TcpPort',
'REG_SZ',
@VALUE
               
-- set Audit level so both failed and successful logins are audited
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
 N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', 
REG_DWORD, 3 

Here you can see I change the default location for the data and transaction logs files, changed to TCP port number to 8787, and lastly changed the audit level for successful and failed login attempts.

Other Things You Can Script

Installing SQL Server is not the only thing you can script. You can perform a number of other tasks using the Setup.exe executable. Here is a partial list of the other things you might consider using the Setup.exe for:

Rebuild the system databases
Specify a new system collation
Rebuild the SQL Server 2005 Registry
Replace all shortcuts
Uninstall SQL Server

Conclusion

As you can see, there are quite a few options in how you can install your SQL Server 2005 environment using the Setup.exe. If you have a large installation with lots of SQL Server machines, then scripting your installation is a must. I can’t cover all the different aspects of how to script a SQL Server 2005 installation in a single article. Using the information in the article, should provide you with some building blocks to help design a process where you can script your SQL Server 2005 installation.

» See All Articles by Columnist Gregory A. Larsen








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers