Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


ADS Enables "Hands Off" SQL Server Installs : Page 3

Does setting up SQL Server on multiple servers mean running setup.exe over and over? No, it doesn't. With the ADS Controller, you can deploy SQL Server to many servers at once, without having to visit each machine individually.


WEBINAR: On-Demand

Unleash Your DevOps Strategy by Synchronizing Application and Database Changes REGISTER >

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

<!-- Code provided by DevX (http://www.devx.com) as part of an article ************************************************************************************* Job Name: InstallSQL2.xml Job Description: Install SQL Server and SP3a with a custom .iss file (SQLINS.iss) Job Purpose: Installs SQL Server, and uses a custom .iss file Version: 1.1 Last Updated By: Rob Hawthorne on 20th August 2004 Version Info: Added the ability to suppress the registry key. Notes: The job first downloads the specific .iss file to the target machine,
performs parameter substitution, and then runs the installation of
SQL Server using the .iss file as a reference. Usage: Register this job in ADS, and then run against a device to install
SQL Server Input Parameters: Return Values: ************************************************************************************* --> <sequence version="1" description="Install SQL Server 2000 with Parameter Substitution"
command="" xmlns="http://schemas.microsoft.com/ads/2003/sequence"> <task description="Copy .iss file to device" doesReboot="false"> <command>/BMONITOR/BmFileXfer.exe</command> <parameters> <parameter>-d</parameter> <parameter>"C:\Build Env\SrcTree\SQL2KEE\SQLINS.ISS"</parameter> <parameter>"\device\harddisk0\partition1\Windows\Temp\SQLINS.ISS"</parameter> </parameters> </task> <task description="Configure Registry to supress warning dialog (only req'd. for Win2K3)"
doesReboot="false"> <command delivery="none" target="device">.\cmd.exe</command> <parameters> <parameter>/c REG ADD "HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\AppCompatFlags"
/v {ff25f0b5-c894-45f4-a29d-1bdd0c7926cd} /t REG_DWORD /d 1</parameter> </parameters> </task> <task description="Configure .iss File with Parameters" doesReboot="false"> <command>/BMONITOR/bmstrrep.exe</command> <parameters> <parameter>"\device\harddisk0\partition1\Windows\Temp\SQLINS.ISS"</parameter> <parameter>"^szDir^"</parameter> <parameter>"$szDir$"</parameter> </parameters> </task> <task description="Install SQL Server" doesReboot="false"> <command delivery="none" target="device">.\cmd.exe</command> <parameters> <parameter>/c start /wait \\IDSTEST01\SrcTree\SQL2KEE\x86\SETUP\setupsql.exe
-s -m -SMS -f1 "C:\Windows\Temp\sqlins.iss"</parameter> </parameters> </task> <task description="Copy .iss file to device (SP3a)" doesReboot="false"> <command>/BMONITOR/BmFileXfer.exe</command> <parameters> <parameter>-d</parameter> <parameter>"C:\Build Env\SrcTree\SQL2KEE\SQLDEF.ISS"</parameter> <parameter>"\device\harddisk0\partition1\Windows\Temp\SQLDEF.ISS"</parameter> </parameters> </task> <task description="Configure SP3a .iss File with Parameters" doesReboot="false"> <command>/BMONITOR/bmstrrep.exe</command> <parameters> <parameter>"\device\harddisk0\partition1\Windows\Temp\SQLDEF.ISS"</parameter> <parameter>"^szDir^"</parameter> <parameter>"$szDir$"</parameter> </parameters> </task> <task description="Install SQL Server SP3a" doesReboot="false"> <command delivery="none" target="device">.\cmd.exe</command> <parameters> <parameter>/c start /wait \\IDSTEST01\SrcTree\SQL2KSP3a\x86\SETUP\setupsql.exe
-s -m -SMS -f1 "C:\Windows\Temp\sqldef.iss"</parameter> </parameters> </task> <task description="Reboot Computer" doesReboot="true"> <command>/BMONITOR/reboot</command> </task> </sequence>

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 username\password pair from the .iss file.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date