How to Deploy SQL Server 2008 R2 Cluster

Silent Installation Info

Setup.exe /SQLSVCPASSWORD="**********\**" /AGTSVCPASSWORD="*\*********\**" /ASSVCPASSWORD="*\*********\**" /ISSVCPASSWORD="*\*********\**" /RSSVCPASSWORD="*\***********" /ConfigurationFile=MyConfigurationFile.INI

Put this into an NSH script. Parameterize the "************", and grab it as a property (encrypted). Be mindful of escape characters in the password.

blcli_connect
blcli_execute PropertyClass getDefaultValue Class://SystemObject SQLSVCPASSWORD AGTSVCPASSWORD ASSVCPASSWORD ISSVCPASSWORD RSSVCPASSWORD
blcli_storeenv ENC_PWD
blcli_execute Property decryptPropertyValue $ENC_PWD
do
Setup.exe /SQLSVCPASSWORD="$SQLSVCPASSWORD" /AGTSVCPASSWORD="$AGTSVCPASSWORD" /ASSVCPASSWORD="$ASSVCPASSWORD" /ISSVCPASSWORD="$ISSVCPASSWORD" /RSSVCPASSWORD="$RSSVCPASSWORD" /ConfigurationFile=MyConfigurationFile.INI
done
blcli_disconnect

Changes and additions necessary in the Parameter/INI file for unattended deployments:

Set QUIET="True"
Comment out UIMODE (place a semi-colon in front of it)
Add "\ \" to the INSTALLSQLDATADIR root directory path (that is, change "U:" to "U:\ \" )
Remove the full path from the following parameters (that is, change "U:\OLAP\Temp" to "Temp"):
ASDATADIR="Data"
ASLOGDIR="Log"
ASBACKUPDIR="Backup"
ASTEMPDIR="Temp"
ASCONFIGDIR="Config"
Add the following new parameters:
IACCEPTSQLSERVERLICENSETERMS="True"
AGTSVCPASSWORD="***"
ASSVCPASSWORD="***"
SQLSVCPASSWORD="***"
RSSVCPASSWORD="***"

Note

You must use the psexec utility to call the setup.exe command via BMC Server Automation. Otherwise, the SQL install will fail when attempting to add the password values to the XML file that it creates for unattended responses during the install process. Utilize the '-u' and '-p' options to provide domain account credentials for psexec to use. The '-h' option may also be necessary to have psexec execute the command with elevated privileges.

Creating Windows 2008 Clusters through BMC Server Automation

The Cluster Feature must be added to a Server before it can be clustered. The following command can be packaged to install the feature:

start /w ocsetup FailoverCluster-FullServer

The 'cluster.exe' command can be used to create clusters, add nodes to clusters, add storage to clusters, and destroy clusters. Here are some tips:

Utilize 'psexec' with the '-h' option to perform the 'cluster' command, as it must be performed with a domain account and elevated access
Additionally, if UAC is enabled, the cluster command will receive an 'access denied' error, so it must be disabled first

To install SQL in ‘Failover Cluster’ mode on servers, they must be part of a server cluster. The following procedure describes how to create clusters through BMC Server Automation.

Adding the ‘psexec’ Utility to the Depot

‘Psexec’ is a Microsoft utility that can be used to set a current user context during command executions. By default, the RBAC Roles in BMC Server Automation map to a local account, and the ‘cluster’ command must be executed as a domain account. Therefore, BMC Server Automation can utilize ‘psexec’ to run the cluster command as a domain account.

From the Microsoft website, download PSTools, which includes the psexec utility.
Add psexec to the BMC Server Automation depot as a depot file.

For more information, see Adding files to the depot.

Creating a Create_Cluster BLPackage

Next, create a package that will utilize ‘psexec’, along with the ‘cluster’ command to create clusters based on configurable parameters such as the cluster’s name, network address, and domain account/password information with the ability to create clusters.

Right-click the ‘psexec’ Depot File and select Add to Depot As > BLPackage.
Choose to ‘softlink’ the file within the BLPackage.
Reset the ‘psexec’ ‘Path’ to /C/tmp/psexec.exe.

Create the following Local Properties within the BLPackage:

Name	Description	Type	Default value
CLUSTER_NAME	Name of cluster to create	string	<enter your cluster name>
CLUSTER_IP	Cluster IP to use	string	<enter cluster IP>
DOMAIN_ACCT	Domain account to create cluster with	string	<enter domain account>
DOMAIN_PWD	Domain account password	encrypted string	<enter domain account password> Note: This will add the server that is assigned this package as a node in the cluster, by default.
NODES	Servers to place in the cluster	string	??TARGET.NAME??
SUBNET_MASK	Subnet mask of the cluster's IP	string	255.255.255.0

Create an external command set named Create Cluster, with the following command:
```
C:\tmp\psexec /accepteula -h -u ??DOMAIN_ACCT?? -p ??DOMAIN_PWD?? cluster /cluster:??CLUSTER_NAME?? /create /node:??NODES?? /ipaddr:??CLUSTER_IP??/??SUBNET_MASK??
```
This command utilizes psexec to perform the cluster action, along with the parameter values supplied to the local properties that you created in the BLPackage.

Adding UAC Disable/Enable actions to the BLPackage

To successfully run the ‘cluster’ command on a Windows2008 server, UAC must be disabled on it. As UAC is enabled on servers by default, registry key values will be modified to first disable UAC, allow the ‘create cluster’ external command to run, and then re-enable UAC.

Right-click on the BLPackage icon within the BLPackage and select Import Assets to import the following registry key:
HKLM\Software\Microsoft\Windows\CurrentVersion\Policies\System\EnableLUA
Set the Data field to: 0x00000000
Set the Reboot field to After item deployment.
Right-click the Registry Key asset and select Move to Top.
Right-click the BLPackage again and select Import Assets, and import a duplicate of the same registry key (as in step 1).
On the duplicate key, set the Data field to: 0x00000001.
Set the Reboot field to After item deployment.
Right-click the duplicate key and select Move to Bottom (if it is not already the last item in the BLPackage).
Save the BLPackage.

The BLPackage should now perform the following actions when deployed to a server:

Disable UAC and reboot the server (to enact UAC modifications).
Create a cluster based on the Local Property values that are assigned to the BLPackage during the job deployment execution.
Re-enable UAC and reboot the server (to enact UAC modifications).

For more information, see Adding a BLPackage to the Depot.

Creating a Create_Cluster Deploy Job

To schedule and execute the Create_Cluster BLPackage to create clusters when desired, you must create a Deploy Job.

Right-click the Create_Cluster BLPackage and select Deploy.
Enter an appropriate name for the Job, such as Create_Cluster, and save the job in a folder in the Jobs workspace, such as Cluster Configuration (use the option to create the folder, if necessary). Then click Next.
In the Value/Name field of the Package Property fields, revise the default values for the Local Properties, as listed in Creating a Create_Cluster BLPackage.
Select the server to run the cluster command against as the target, and then click Next.
Enter email notification info, if desired, and then click Next.
Set the job to execute now or schedule an appropriate date and time for the job to run. Then click Finish.

For more information, see Creating and modifying Software and BLPackage Deploy Jobs.

Packaging and deploying SQL2008 R2

Our packaging and deployment of SQL2008 R2 consisted of the following steps:

Creating the SQL unattended response file
Creating a Configuration file for the INI response file
Creating a Clustering property class and instance
Creating the SQL Cluster package
Deploying the SQL Cluster package
Monitoring Deploy Job progress and reviewing results

Creating the SQL unattended response file

You can deploy SQL2008 R2 in unattended fashion by using a properly generated .ini file. You can be accomplish this by executing the SQL setup.exe binary and manually stepping through the installation.

During .ini file generation, SQL will perform a number of checks that must complete successfully for the installation to proceed, such as assigning it to a valid SQL cluster, valid cluster disk configuration, and valid cluster network.

Run the Setup file as administrator.
In the wizard, select New SQL Server failover cluster installation.
Enter desired values when prompted, for install parameters such as features to install and SQL Network Name, and provide appropriate domain accounts to utilize for SQL services such as the SQL Agent Service Startup Account. You can modify any of these settings at a later time through the .ini file, but providing default information reduces the required post-installation setup.
At the Ready to Install step, DO NOT click Install. Instead, record the location of the .ini file from the Configuration file path field. This file will be used as the basis for unattended installations. Click Cancel to exit the installation.
Copy the configuration file to the SQL install binary source and rename it sql2k8r2_cluster.ini.
Open the sql2k8r2_cluster.ini file in any text editor and edit it with the following changes:
- Set QUIET="True"
- Comment out UIMODE by inserting a semi-colon at the beginning of the line.
- Add a space character to the INSTALLSQLDATADIR root directory path, For example, change "U:" to "U: "
- Remove the full path from the following parameters (for example, change "U:\OLAP\Temp" to "Temp"):
  - ASDATADIR="Data"
  - ASLOGDIR="Log"
  - ASBACKUPDIR="Backup"
  - ASTEMPDIR="Temp"
  - ASCONFIGDIR="Config"
- Add the following new parameters:
  - ACCEPTSQLSERVERLICENSETERMS="True"
  - AGTSVCPASSWORD="***"
  - ASSVCPASSWORD="***"
  - SQLSVCPASSWORD="***"
  - RSSVCPASSWORD="***"

Creating a Configuration file for the INI response file

A BMC Server Automation configuration file is used to parse a file for value substitution. In this way, values generated by the manual install while creating the SQL unattended response file and recorded in the sql2k8r2_cluster.ini file can be used for parameter substitution in BMC Server Automation packages.

Click the Configuration menu item and select Config Object Dictionary View.
In the Configuration Object Dictionary (displayed in the lower right-hand area of the console), click the green + button.
In the New Configuration Object wizard, select Configuration File.
In the second panel of the wizard, selectWindows in the Operating System field. Then browse to the sql2k8r2_cluster.ini file from the server containing this file. Finally, select name = value grammar as the Grammar file, as the INI file format is to show the SQL install options by the option name and value separated by an = sign:
After the Configuration file is created for the INI file, you can view the file in its parsed format by browsing the server that hosts the sql2k8r2_cluster.ini file, expanding the Configuration item part, and highlighting it:

Creating a Clustering property class and instance

In BMC Server Automation, you can create property instances to supply default parameters for objects such as BLPackages, thus reducing or eliminating the amount of parameter substitution that would need to be performed on a regular basis when deploying packages based on standard criteria (that is, a SQL cluster default instance installation using default install paths).

Click theConfiguration menu item and select Property Dictionary View.
In the Property Dictionary (in the lower right-hand corner of the console), selectthe Custom Property Classes and click the green + button.
Enter SQL_SERVER as the name of the new property class.
Right-click the newly created SQL_SERVER class and select New Sub-class.
Enter Clustering as the name of the new sub-class.
Select the Clustering sub-class in the dictionary and click the green + button.
Create a property for every field in the INI file that can be parameterized. If relevant, enter a default value for any specific parameter. In addition, select the Required check box for your properties.
Note

You can set most Properties can be set as String Simple Type properties. However, properties containing password information (such as the service account password properties) should be set to Encrypted String to mask sensitive password data. In addition, properties with set values of True or False can be set as Boolean type properties.

Creating the SQL Cluster package

Right-click an appropriate folder within the Depot workspace and select New > BLPackage.
Enter a name for the BLPackage, and ensure that Live Server Objects is selected.
On the next panel, click the green + button to add a server object to the package. Browse to the server containing the sql2k8r2_cluster.ini file that was generated during the creation of the SQL unattended response file, and select it from the Configuration object type.
Click Finish to create the package.
Open the newly created BLPackage to modify it.
Expand thesql2k8r2_cluster.ini file in the newly created package to view the individual installation parameters, along with the values supplied during its creation.
Click the Local Properties tab within the BLPackage.
Create a new local property by clicking the green + button and selecting the Type to be Property class. Then expand the Custom Property Class until the Clustering class that you created is displayed.
For Default value, select the Default_Cluster_2K8R2 instance that you created.
By creating a local property that links to the property instance, all of the properties and their corresponding values can now be utilized in this BLPackage.
Return to the Package tab and begin to replace hard-coded property values for sql2k8r2_cluster.ini with the instance properties that you defined.
Double-click the Value 1 field of each property to parameterize and highlight the current hard-coded value in order to replace it.
Click the Properties tab and scroll to the SQL_INSTALL_CONFIG local property created previously, which holds the instance properties of the Clustering Class. Then click the right arrow to view individual Clustering class properties
Select the property that corresponds to the INI field selected. The value assigned to the Default_Cluster_2K8R2 instance property will now be utilized in the INI file for the package.
Some fields should be left with their hard-coded values. For example, the Quiet field should always be set to True to enable an unattended deployment of SQL, so there is no need to parameterize it.
After all the fields from the sql2k8r2_cluster.inifile have been parameterized, create additional local properties for the domain account and password that the package should be deployed with on the target systems. Create the following local properties and assign them the appropriate values:
- RUNAS_ACCOUNT (type: string)
- RUNAS_PWD (type: encrypted string)
After all the fields from the sql2k8r2_cluster.ini file have been parameterized and local properties have been created for a domain account and its password to execute the package as, additional assets can be imported into the BLPackage, as well as the command instructions created for it.

Import the psexecutility into the package. This utility will be used to execute the SQL install command using the credentials that will be provided for the RUNAS_ACCOUNT and RUNAS_PWD local property values:
1. Right-click the BLPACKAGE icon and select Import Assets:
2. Select Depot Files:
3. Click the green + button to add the PsExec.exe depot file to the package and set it as a soft-linked" file. This file is located in the Utilities depot folder and it was previously imported directly into the depot as a depot file.
4. After it is imported into the BLPackage, reset its destination path to be the temporary directory of the server, using NSH format (for example, "/C/tmp/PsExec.exe").
To make the SQL install source available to the package, add an external command (a command that is performed on the target during deployment) to the BLPackage.
1. Right-click the BLPACKAGE icon and select Add External Command.
2. Enter a 'Name' for the command and then enter the commands to perform on the intended targets in the Cmd field.
  In this example the following actions will be performed on the intended targets:
  - Any previously existing mapped z drive will be removed.
  - Drive z will be mapped to the root location of the SQL install source folder.
  - The SQL install source folder (and its entire contents) will be copied to the C:\tmp directory of the intended targets.
3. After the Obtain SQL Source external command is completed, right-click it and select Move to Top.
  
  Now when the BLPackage is deployed, the first action of the package will be to copy the SQL install source over to the intended targets. Then the configuration file sql2k8r2_cluster.ini will perform the parameter substitutions specified against the sql2k8r2_cluster.ini file included in the SQL install source folder that is included in the copy operation. Next, the psexec utility will be copied to the "C:\tmp" path of the targets.
Perform another Add an External Command action to create the install instructions for the package. Enter Install SQL in the Name field, and then enter the install command in the Cmd field.
Use only fully qualified paths in the external command.

Use the following definitions for the external command:
- In this sequence, the SQL setup.exe command is called via the psexec utility, along with the configuration file parameter, which uses the unattended response file sql2k8r2_cluster.ini (which has been updated with the instance property values defined in the Default_Cluster_2K8R2 instance.
- Note that the -u and -p parameters of the psexec utility are used to provide the values assigned to the RUNAS_ACCOUNT and RUNAS_PWD local properties defined earlier.
- The /accepteula and -h parameters are also needed in the psexec syntax to perform an unattended acceptance of the psexec license agreement and perform the installation with elevated privileges.
Add another external command to clean up the SQL install source from the temporary directory on the intended targets after the install is performed, as well as the psexec utility.
Save the BLPackage.

Deploying the SQL Cluster package

Right-click the newly created BLPackage and select Deploy to create a Deploy Job for it.
Enter a name and description for the Deploy Job, and select an appropriate folder within the Jobs workspace to store it.
In the next panel, the BLPackage is displayed in thePackage field, and the Default_Cluster_2K8R2 Property Instance is specified by default for assigning values to the sql2k8r2_cluster.ini file. If you created multiple property instances for the Clustering property class created, then you can now optionally select a different instance.
Select the targets to receive the SQL cluster install package.
Optionally enter email recipient info to receive notifications on the Deploy Job's completion status.
Select Execute Job now to kick off the deployment immediately.

Alternatively, schedule a specific date and time for the deployment to occur.
Set the Logging Level to All Info or to any other level that you prefer, and set appropriate reboot options.
Accept the default settings for Simulate and Stage Options and Commit Options, or modify these settings as necessary.
Enter a timeout value for the Deploy Job, in minutes, in the JOB_TIMEOUT property (if desired). 0 means no time out value.
Click Finish to deploy (or schedule) the Deploy Job.

Monitoring Deploy Job progress and reviewing results

From the "Tasks in Progress" tab, double-click the job to see the status of the analysis when it is running: