SQL Server 2005 – Unattended Installations

In this tutorial you will learn about SQL Server 2005 – Unattended Installations, How to perform a silent installation of SQL Server, To Create .iss file, Basic Header Information for Our Script Files, Checking for the Parameters within the Script, Sample of the Combination of the .iss File within the Batch File, To set the security modes use the following code.

Unattended installation is an installation of SQL Server that is done entirely using a script embedded in an .ini file. This file can be created using any text editor. This file is composed of a single section containing multiple parameters relating to the different configuration setting. Once this file has been created, the user has to navigate to the command prompt and type the command setup.exe/settings . The qn switch can be specified to perform a silent installation or the qb switch can be used to specify a progress dialog box display.

SQL Server supports the capability to take a configuration file containing components for the purpose of an installation of the server. This file will contain all the details of components to be installed with depth of customization of the components required. In this tutorial we shall examine how to perform a silent installation of SQL Server. We shall script an .iss file.

There are examples of an .iss file on the installation CD of SQL Server. These samples are for the default installation of the SQL Server, OLAP etc.

To Create .iss file

1. Check whether any other .iss file exists in the operating system by navigating to C:\WinNT.
2. If yes, rename the files to some other name. These files are not required once an installation of the SQL Server is complete and may have been created by previous installations.
3. Insert the installation CD into the drive and in the command prompt specify

x:\Setup\SQLSetup.exe –r.

4. The –r parameter records a unattended installation within the Winnt directory and then opens the installation wizard. The installation wizard screen is displayed.
5. Select the Advanced options to choose the options required. Click Next.
6. Select the Record Unattended .ISS File option in the installation wizard.
7. We shall ignore the Registry option which is intended to restore the registry to the pre-SQL installation state. We shall also ignore the Maintain a virtual Server for Failover clustering as this option is to be used if clusters are being installed. Click Next.
8. The standard installation screens appear. The user can navigate through these screens selecting the options required.
9. Once the installation wizard completes the task, navigate to C:\WINNT and open the new file called setup.iss with the current date and time. Look at the script in the file.
10. Enter the following command in the command line

< name of drive >:\Setup\SQLSetup.exe –z –s –f1c:\winnt\setup.iss

11. –z is to ensure that the setup will run on systems that have more than 256 MB RAM. –s forces setup to run without user input. –f1 allows users specify the path of the .iss file. If path is not specified, the setup looks for an .iss file in the default location.
12. The .iss file can now be manually edited to cater to the needs of the administrator.

The above script procedure does not install the SQL Server. It assists the administrator in creating the script automatically.

Now that the script is created, the variables within the script will have to be initialized. The .iss file that is then generated will install the SQL Server.

The script file created above is a DOS or batch file script. Though it can be ported to Windows Scripting Host (WSH), it is very simple to administer in the current format.

Separate script files can be created for each installation or different values can be passed for the installations.

The first step is to define the header information. This is illustrated below.

Basic Header Information for Our Script Files.

@echo off REM *****************************************************************REM Script Name: InstallSQLServer.bat REM Script Purpose: Installs Microsoft SQL Server 2005. REM Version: 1.1 REM Last Updated By: Exforsys on 23/11/05 REM Version Info: Updated the script to take the Port REM details REM REM Notes: This installation can be modified to NOT install REM all of the tools by default. REM Has the capability of separating the data and program files REM from each other. REM SQL Server can be installed to run under a restricted user REM account, i.e. Service Account REM this is a recommended configuration (use of service REM accounts) REM REM Usage: Used to perform a custom install of SQL Server 2005. REM Designed to be used again and again on differing hardware REM and configurations. REM REM Input Values: REM REM SQLPath – The path where the program files for SQL REM Server are installed REM e.g., %PROGRAMFILES%\Microsoft SQL Server REM SQLsaPwd – The password for the sa account. Note REM the .iss file encrypts this REM e.g., password REM CompanyName – The registered name of the company REM e.g., Microsoft REM MixedMode – (Optional) Specifies whether the installation REM will used mixed authentication REM or Windows only authentication REM e.g., Yes or No REM – Default Value = No REM AppPath – Path to SQL Server installation files. REM Should be a mapped drive path rather than UNC REM e.g., Y:\sql2k.std\x86\setup REM SvcActName – (Optional) The service account name that SQL REM server runs under REM e.g., ServiceSQL REM – Default Value = LocalSystem REM SvcActDom – (Optional if SvcAcctName is not supplied). REM The domain name of REM where the server account resides REM e.g., MSFT REM SvcActPwd – (Optional if SvcAcctName is not supplied) The REM password for REM the service account. Note the .iss file encrypts this REM e.g., 097f738fb19d058f27b5 REM SQLDataPath – (Optional) The path where the data and REM transaction files for SQL Server are installed REM e.g., %PROGRAMFILES%\Microsoft SQL Server\MSSQL\Data REM – Default Value = SQLPath REM SQLCollation – (Optional) The coallation settings (for REM compatibility with earlier versions of SQL) REM e.g., SQL_Latin1_General_CP1_CI_AS REM – Default Value = SQL_Latin1_General_CP1_CI_AS REM InstanceName – (Optional) The name of the instance you REM wish to install SQL under. If any other name REM than MSSQLSERVER is specified it is treated as a REM second instance REM e.g., MSSQLSERVER REM – Default Value = MSSQLSERVER REM TCPPort – (Optional) The port you wish SQL server to REM listen for requests on REM e.g., 1433 REM – Default Value = 1433 REM ISSDir – (Optional) Complete path to the directory REM where you want the .iss file created REM e.g., %SYSTEMDRIVE%\Temp REM – Default Value = %TEMP% REM LogFile – (Optional) Full path and file name to log REM output messages to REM e.g., %SYSTEMDRIVE%\SQLInstall.log REM – Default Value = %TEMP%\SQLInstall.log REM LicenseMode – (Optional) The mode for licensing SQL Server REM e.g., PERDEVICE REM – Default Value = PERDEVICE REM LicenseLimit – (Optional) The number of licenses purchased REM for SQL Server REM e.g., 1 REM – Default Value = 1 REM Output Values: REM RetVal – Return value indicating whether the script has REM succeeded (0) or not (<> 0) REM e.g., RETVAL=0 REM *****************************************************************</body></html></body></html></body></html></body></html>

Header information makes the file more maintainable. All information regarding referenced parameters, meanings in the context of the script, sample value of the parameter, notes etc are included in the header file. This section can be modified as and when required.

The next part of the script is parameter checking. The ‘If” clause is used to check the existence of the parameter. If it does not exist, then a message is sent to the console window alerting the user that the parameter contains no value and a log is registered and the batch file is exited.

Checking for the Parameters within the Script.

 

:Main REM Check for the input values. if not "%LogFile%"=="" goto :LogFileOK (echo WARNING: Missing input parameter LogFile. Setting to default "%TEMP%\SQLInstall.log") set LogFile="%TEMP%\SQLInstall.log" :LogFileOK if not "%AppPath%"=="" goto :AppPathOK (echo ERROR: Missing input parameter AppPath.) (echo ERROR: Missing input parameter AppPath.) >> %Logfile% set RETVAL=10 goto :end :AppPathOK if not "%SQLPath%"=="" goto :SQLPathOK (echo ERROR: Missing input parameter SQLPath.) (echo ERROR: Missing input parameter SQLPath.) >> %Logfile% set RETVAL=15 goto :end :SQLPathOK if not "%SvcActName%"=="" goto :SvcActNameOK (echo WARNING: Missing input parameter SvcActName. Setting to default "LocalSystem") (echo WARNING: Missing input parameter SvcActName. Setting to default "LocalSystem") >> %Logfile% set SvcActName=LocalSystem :SvcActNameOK REM If the service account is set to "LocalSystem" then we don’t check for the existence of the other REM of the domain and password parameters (not required if running under localsystem) if "%SvcActName%"=="LocalSystem" goto :SkipCheck if not "%SvcActDom%"=="" goto :SvcActDomOK (echo ERROR: Missing input parameter SvcActDom.) (echo ERROR: Missing input parameter SvcActDom.) >> %Logfile% set RETVAL=20 goto :end :SvcActDomOK if not "%SvcActPwd%"=="" goto :SvcActPwdOK (echo ERROR: Missing input parameter SvcActPwd.) (echo ERROR: Missing input parameter SvcActPwd.) >> %Logfile% set RETVAL=25 goto :end :SvcActPwdOK :SkipCheck if not "%MixedMode%"=="" goto :MixedModeOK (echo WARNING: Missing input parameter MixedMode. Setting to default "No") (echo WARNING: Missing input parameter MixedMode. Setting to default "No") >> %Logfile% set MixedMode=No :MixedModeOK REM Checks to see if we are using Mixed Mode or Windows only authentication. If Mixed Mode is used REM a password MUST be supplied for the "sa" account. if "%MixedMode:~0,1%"=="N" goto :NotMixedMode if not "%SQLsaPwd%"=="" goto :SQLsaPwdOK (echo ERROR: Missing input parameter SQLsaPwd.) (echo ERROR: Missing input parameter SQLsaPwd.) >> %Logfile% set RETVAL=30 goto :end :SQLsaPwdOK :NotMixedMode if not "%CompanyName%"=="" goto :CompanyNameOK (echo ERROR: Missing input parameter CompanyName.) (echo ERROR: Missing input parameter CompanyName.) >> %Logfile% set RETVAL=35 goto :end :CompanyNameOK if not "%ISSDir%"=="" goto :ISSDirOK (echo WARNING: Missing input parameter ISSDir. Setting to default "%TEMP%") (echo WARNING: Missing input parameter ISSDir. Setting to default "%TEMP%") >> %Logfile% set ISSDir=%TEMP% :ISSDirOK if not "%SQLDataPath%"=="" goto :SQLDataPathOK (echo WARNING: Missing input parameter SQLDataPath. Setting to default "%SQLPath%") (echo WARNING: Missing input parameter SQLDataPath. Setting to default "%SQLPath%") >> %Logfile% set SQLDataPath=%SQLPath% :SQLDataPathOK if not "%InstanceName%"=="" goto :InstanceNameOK (echo WARNING: Missing input parameter InstanceName. Setting to default "MSSQLSERVER") (echo WARNING: Missing input parameter InstanceName. Setting to default "MSSQLSERVER") >> %Logfile% set InstanceName=MSSQLSERVER :InstanceNameOK if not "%SQLCollation%"=="" goto :SQLCollationOK (echo WARNING: Missing input parameter SQLCollation. Setting to default "SQL_Latin1_General_CP1_CI_AS") (echo WARNING: Missing input parameter SQLCollation. Setting to default "SQL_Latin1_General_CP1_CI_AS") >> %Logfile% set SQLCollation=SQL_Latin1_General_CP1_CI_AS :SQLCollationOK if not "%TCPPort%"=="" goto :TCPPortOK (echo WARNING: Missing input parameter TCPPort. Setting to default "1433") (echo WARNING: Missing input parameter TCPPort. Setting to default "1433") >> %Logfile% set TCPPort=1433 :TCPPortOK if NOT "%LicenseMode%"=="" goto :LicenseModeOK (echo WARNING: Missing input parameter LicenseMode. Setting to default "PERDEVICE") (echo WARNING: Missing input parameter LicenseMode. Setting to default "PERDEVICE") >> %Logfile% set LicenseMode=PERDEVICE :LicenseModeOK if NOT "%LicenseLimit%"=="" goto :LicenseLimitOK (echo WARNING: Missing input parameter LicenseLimit. Setting to default "1") (echo WARNING: Missing input parameter LicenseLimit. Setting to default "1") >> %Logfile% set LicenseLimit=1 :LicenseLimitOK if "%InstanceName%"=="MSSQLSERVER" goto :DefaultInstance (echo Setting InstanceName to "MSSQLSERVER$%InstanceName%") (echo Setting InstanceName to "MSSQLSERVER$%InstanceName%") >> %Logfile% set InstanceName=MSSQLSERVER$%InstanceName% :DefaultInstance

It must be noted that some of the variables have been supplied with default values and if the user forgets to supply the value the same will be set to default.

The code has to now check for the value of the SycActName variable. If the value is the LocalSystem then SQL Server will be installed as LocalSystem and hence there is no need to check for SycActDom and SycActPwd variables. If the SycActName is not LocalSystem then we need to check for the other two variables and the user must provide the values. The purpose of using this conditional logic is to enable the installation in a number of slightly varying situations with ease.

The next step is to modify and generate the .iss file. Open the .iss file and paste the echo command at the beginning of every line and >>%ISS% at the end of every line other than the first line. Add a > %ISS% sign at the end of the first line. The file will begin to look like the sample below.

Sample of the Combination of the .iss File within the Batch File.

 

(echo [InstallShield Silent]) > %ISSFile% (echo Version=v5.00.000) >> %ISSFile% (echo File=Response File) >> %ISSFile% (echo.) >> %ISSFile% (echo [File Transfer]) >> %ISSFile% (echo OverwriteReadOnly=NoToAll) >> %ISSFile% (echo.) >> %ISSFile% (echo [DlgOrder]) >> %ISSFile% (echo Dlg0=SdWelcome-0) >> %ISSFile% (echo Count=15) >> %ISSFile% (echo Dlg1=DlgMachine-0) >> %ISSFile% (echo Dlg2=DlgInstallMode-0) >> %ISSFile% (echo Dlg3=DlgAdvanced-0) >> %ISSFile% (echo Dlg4=SdRegisterUser-0) >> %ISSFile

Having completed the above process, we now need to place the variables in the place of the values in the .iss file. For instance

(echo szDir=%PROGRAMFILES%\Microsoft SQL Server\MSSQL) >> %ISSFile%

Will become

(echo szDir=%SQLpath%) >> %ISSFile%

After completing the editing, copy the entire file and place the text into the batch file under the parameter checking section as under:

if "%InstanceName%"=="MSSQLSERVER" goto :DefaultInstance (echo Setting InstanceName to "MSSQLSERVER$%InstanceName%") (echo Setting InstanceName to "MSSQLSERVER$%InstanceName%") >> %Logfile% set InstanceName=MSSQLSERVER$%InstanceName% :DefaultInstance set ISSFile=%ISSDir%\SQL2K.iss REM Build the auto-answer file for the setup program. (echo Building auto-answer file.) (echo Building auto-answer file.) >> %LogFile% (echo [InstallShield Silent]) > %ISSFile% (echo Version=v5.00.000) >> %ISSFile% (echo File=Response File) >> %ISSFile% (echo.) >> %ISSFile% (echo [File Transfer]) >> %ISSFile% (echo OverwriteReadOnly=NoToAll) >> %ISSFile% (echo.) >> %ISSFile% (echo [DlgOrder]) >> %ISSFile% (echo Dlg0=SdWelcome-0) >> %ISSFile% (echo Count=15) >> %ISSFile% (echo Dlg1=DlgMachine-0) >> %ISSFile% (echo Dlg2=DlgInstallMode-0) >> %ISSFile% (echo Dlg3=DlgAdvanced-0) >> %ISSFile% (echo Dlg4=SdRegisterUser-0) >> %ISSFile% (echo Dlg5=SdLicense-0) >> %ISSFile% (echo Dlg6=DlgClientServer-0) >> %ISSFile% (echo Dlg7=DlgInstanceName-0) >> %ISSFile% (echo Dlg8=SetupTypeSQL-0) >> %ISSFile% (echo Dlg9=DlgServices-0) >> %ISSFile% (echo Dlg10=DlgSQLSecurity-0) >> %ISSFile% (echo Dlg11=DlgCollation-0) >> %ISSFile% (echo Dlg12=DlgServerNetwork-0) >> %ISSFile% (echo Dlg13=SdStartCopy-0) >> %ISSFile% (echo Dlg14=SdFinish-0) >> %ISSFile% (echo.) >> %ISSFile% (echo [SdWelcome-0]) >> %ISSFile% (echo Result=1) >> %ISSFile% (echo.) >> %ISSFile% (echo [DlgMachine-0]) >> %ISSFile% (echo Type=1) >> %ISSFile% (echo Result=1) >> %ISSFile% (echo.) >> %ISSFile% (echo [DlgInstallMode-0]) >> %ISSFile% (echo Type=32) >> %ISSFile% (echo Result=1) >> %ISSFile% (echo.) >> %ISSFile% (echo [DlgAdvanced-0]) >> %ISSFile% (echo AdvType=4) >> %ISSFile% (echo Result=1) >> %ISSFile% (echo.) >> %ISSFile% (echo [SdRegisterUser-0]) >> %ISSFile% (echo szName=%CompanyName%) >> %ISSFile% (echo Result=1) >> %ISSFile% (echo.) >> %ISSFile% (echo [SdLicense-0]) >> %ISSFile% (echo Result=1) >> %ISSFile% (echo.) >> %ISSFile% (echo [DlgClientServer-0]) >> %ISSFile% (echo Type=2) >> %ISSFile% (echo Result=1) >> %ISSFile% (echo.) >> %ISSFile% (echo [DlgInstanceName-0]) >> %ISSFile% (echo InstanceName=%InstanceName%) >> %ISSFile% (echo Result=1) >> %ISSFile% (echo.) >> %ISSFile% (echo [SetupTypeSQL-0]) >> %ISSFile% (echo szDir=%SQLpath%) >> %ISSFile% (echo Result=301) >> %ISSFile% (echo szDataDir=%SQLDataPath%) >> %ISSFile% (echo.) >> %ISSFile% (echo [DlgServices-0]) >> %ISSFile% (echo Local-Domain=61680) >> %ISSFile% (echo AutoStart=15) >> %ISSFile% (echo SQLDomain=MSFT) >> %ISSFile% (echo SQLDomainAcct=ServiceSQL) >> %ISSFile% (echo SQLDomainPwd=097f738fb19d058f27b5) >> %ISSFile% (echo AgtDomain=MSFT) >> %ISSFile% (echo AgtDomainAcct=ServiceSQL) >> %ISSFile% (echo AgtDomainPwd=097f738fb19d058f27b5) >> %ISSFile% (echo Result=1) >> %ISSFile% (echo.) >> %ISSFile% (echo [DlgSQLSecurity-0]) >> %ISSFile% (echo LoginMode=2) >> %ISSFile% (echo szPwd=097f738fb19d058f27b5) >> %ISSFile% (echo Result=1) >> %ISSFile% (echo.) >> %ISSFile% (echo [DlgCollation-0]) >> %ISSFile% (echo collation_name=%SQLCollation%) >> %ISSFile% (echo Result=1) >> %ISSFile% (echo.) >> %ISSFile% (echo [DlgServerNetwork-0]) >> %ISSFile% (echo NetworkLibs=-268431361) >> %ISSFile% (echo TCPPort=%TCPPort%) >> %ISSFile% (echo TCPPrxy=Default) >> %ISSFile% (echo NMPPipeName=\\.\pipe\sql\query)>> %ISSFile% (echo Result=1) >> %ISSFile% (echo.) >> %ISSFile% (echo [SdStartCopy-0]) >> %ISSFile% (echo Result=1) >> %ISSFile% (echo.) >> %ISSFile% (echo [License-0]) >> %ISSFile% (echo LicenseMode=%LicenseMode%) >> %ISSFile% (echo LicenseLimit=%LicenseLimit%) >> %ISSFile% (echo Result=1) >> %ISSFile% (echo.) >> %ISSFile% (echo [SdFinish-0]) >> %ISSFile% (echo Result=1) >> %ISSFile% (echo bOpt1=0) >> %ISSFile% (echo bOpt2=0) >> %ISSFile%

Finally, to extend the logic for a Service Account we need to replace in the code the following section.

echo [DlgServices-0]) >> %ISSFile% REM If we are not running under LocalSystem then set up the service account REM with the domain name and password if not "%SvcActName%"=="LocalSystem" ( (echo Local-Domain=61680) >> %ISSFile% (echo AutoStart=15) >> %ISSFile% (echo SQLDomain=%SvcActDom%) >> %ISSFile% (echo SQLDomainAcct=%SvcActName%) >> %ISSFile% (echo SQLDomainPwd=%SvcActPwd%) >> %ISSFile% (echo AgtDomain=%SvcActDom%) >> %ISSFile% (echo AgtDomainAcct=%SvcActName%) >> %ISSFile% (echo AgtDomainPwd=%SvcActPwd%) >> %ISSFile% ) else ( (echo Local-Domain=3855) >> %ISSFile% (echo AutoStart=15) >> %ISSFile% ) (echo Result=1) >> %ISSFile% (echo.) >> %ISSFile%

To set the security modes use the following code.

 

(echo [DlgSQLSecurity-0]) >> %IISFile% REM If we are not using MixedMode authentication we set the LoginMode=1 REM else the LoginMode is 2 with a password for the "sa" account if "%MixedMode:~0,1%"=="N" ( (echo LoginMode=1) >> %IISFile% ) else ( (echo LoginMode=2) >> %IISFile% (echo szPwd=%SQLsaPwd%) >> %IISFile% ) (echo Result=1) >> %IISFile% (echo.) >> %ISSFile%

With this we have come to the end of the process of creating our .iss file. Copy this file into the Windows Directory and rename it as setup.iss. Now create the command line within the batch file as under:

REM Installing SQL Server silently specifying the setup.iss file (echo Launching SQL setup, commands %AppPath%\setupsql.exe -s -m -SMS -f1 "%WinDir%\setup.iss".) (echo Launching SQL setup, commands %AppPath%\setupsql.exe -s -m -SMS -f1 "%WinDir%\setup.iss".) >> %LogFile% start /wait "%AppPath%\setupsql.exe" -s -m -SMS -f1 "%WinDir%\setup.iss" >> %LogFile% 2>>&1

You can use the Start/wait command to tell the batch file to wait till the installation of the SQL Server has finished. Once it has finished we can check for successful installation by setting the value for RETVAL.

In this section of the tutorial we have learnt how to create an .iss file and use for a unattended installation of the SQL Server. In the section that follows we shall see how to do a custom installation of the server.