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
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 doesnt allow you to specify all configuration options. For example, it doesnt 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. Lets 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 didnt 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
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 cant 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.