In parts 1 and 2 the cluster was made ready for the SQL install and the Active Directory objects were created. In the final part of the series we will finally get to creating the ini file and installing SQL.

As an FYI this script is designed to install the following components along with the Database Engine:

• SQL Server Replication
• Full-Text Search
• Business Intelligence Development Studio
• Client Tools Connectivity
• Integration Services
• Client Tools Backwards Compatibility
• Client Tools SDK
• SQL Server Books Online
• Management Tools – Basic
  • Management Tools – Complete
• SQL Client Connectivity SDK
• Microsoft Sync Framework

The ini can be quickly modified to remove certain of these components if they are not required (I’m looking to add selections for these items to a future version).

Copy the script to the server

Download the CreateClusteredSQLInstallFile.ps1 and copy it over to the server. From there it can either be opened directly in PowerShell ISE or called from within the PowerShell shell.

If calling the script from the shell you might get an error:

If so you’ll need to reopen the shell as an administrator and change the execution policy to RemoteSigned

set-executionpolicy Remotesigned

This time it should run without any errors.

Working through the script

The first thing to come up is an informational box letting you know that you’ll be creating ini files:

Next, depending upon whether you started the script through the shell or the ISE you’ll see different things…in the ISE you’ll see a pop-up window asking if you want to “Install a new clustered instance of add node to an existing cluster?”, this is in case you want to generate an ini file for a cluster you already have in place. By default this will create files for a new clustered install of SQL Server:

In the shell you will just get a prompt”:

A new window will appear asking where you want to put the ini files when they are created. I’ve chosen to put them in C:SQL Install ini (this is the same if you use the shell or the ISE)

Next you’ll be prompted to enter the SQL virtual name (going forward I’ll be showing the prompts through the ISE, keep an eye on the shell if that’s what you are using). This is the disabled computer account we created in Active Directory:

The next prompt will ask for an instance name, here I’m going with PROD (which will give us the SQL instance of GROOPROD):

Follow this up by entering the IP address:

The next two prompts are for the SQL Service and SQL Agent accounts to be used:

Now choose a root folder for the SQL system databases (master/model/msdb). A subdirectory of SQLSystem will automatically be created, as such I am using the root of G:

A similar window will pop-up and ask for the location of TempDB, the data and log files and backups. This is just another case of choosing the appropriate locations. As a note from the location you choose the following subfolders will be created and used:

• TempDB – <root>MSSQLData
• SQL Data Files – <root>MSSQLData
• SQL Log Files – <root>MSSQLLogs
• Backups – <root>MSSQLBackup

The final step is a prompt that will appear asking what failover clustered disks should be used. These will be listed under available storage in the Failover Cluster Manager. Only the disks to actually be used should be added. This is a comma separated list of those disks (the screenshot shows the disks next to the prompt for easy identification):

A completion pop-up appears indicating what files were created and how to use them:

The filenames are generated based upon the user creating the script and the time that the script started executing (this helps to prevent accidental duplicates).

If you’d used the shell to execute the script you’d expect to see the following:

I’ve got files, so now what?

Now that we have generated the ini files it’s time to put them to use. The first thing I like to do is rename them to something more appropriate (like the SQL virtual name and their purpose) so

become

I also like to store these files away in a repository, this way it’s a very quick and easy operation to add a node to a cluster or find out what installation settings were used.

Can we do the SQL install already?

We sure can, it’s go time! But first, take a quick look at the ini file that was created:

;File created by: AWESOMESAUCENic
;File creation date: 2011-07-17 16:47
;Script to install new SQL clustered instance
;SQLSERVER2008 Configuration File
[SQLSERVER2008]
IACCEPTSQLSERVERLICENSETERMS=”TRUE”
FAILOVERCLUSTERNETWORKNAME=”GROO”
INSTANCEID=”PROD”
INSTANCENAME=”PROD”
FAILOVERCLUSTERGROUP=”SQL Server (PROD)”
FAILOVERCLUSTERIPADDRESSES=”IPv4;192.168.137.150;Public Network;255.255.255.0″
INSTALLSHAREDDIR=”C:Program FilesMicrosoft SQL Server”
INSTALLSHAREDWOWDIR=”C:Program Files (x86)Microsoft SQL Server”
INSTANCEDIR=”C:Program FilesMicrosoft SQL Server”
ACTION=”InstallFailoverCluster”
FEATURES=SQLENGINE,REPLICATION,FULLTEXT,BIDS,CONN,IS,BC,SDK,BOL,SSMS,ADV_SSMS,SNAC_SDK,OCS
HELP=”False”
INDICATEPROGRESS=”True”
QUIET=”True”
QUIETSIMPLE=”False”
X86=”False”
ENU=”True”
ERRORREPORTING=”False”
SQMREPORTING=”False”
FILESTREAMLEVEL=”0″
ISSVCSTARTUPTYPE=”Automatic”
ISSVCACCOUNT=”NT AUTHORITYNetworkService”
SQLCOLLATION=”SQL_Latin1_General_CP1_CI_AS”
FTSVCACCOUNT=”NT AUTHORITYLOCAL SERVICE”
SQLSYSADMINACCOUNTS=”awesomesaucenic”
SQLSVCACCOUNT=”awesomesauceGroo_SQLEngine”
AGTSVCACCOUNT=”awesomesauceGroo_SQLAgent”
INSTALLSQLDATADIR=”G:SQLSystem”
SQLTEMPDBDIR=”G:TempDBMSSQLData”
SQLUSERDBDIR=”G:SQLDataMSSQLData”
SQLUSERDBLOGDIR=”G:SQLDataMSSQLLogs”
SQLBACKUPDIR=”H:MSSQLBackup”
FAILOVERCLUSTERDISKS=”SQL Backup Disk” “SQL Data Disk”

 

 

That’s all the information needed to perform the install.

Now open up an elevated command prompt and go to the location of the install media. Now just call setup.exe and pass along the location and name of the SQLInstall.ini file along with the SQL and Agent passwords, then hit enter:

Now go and make yourself a beverage of your choice, the install will take a little while….after an indeterminate amount of time you should see the following nondescript message in the command prompt window:

Not exactly a whole bunch of bells and whistles there, but “Setup result: 0” tells us that the install was successful (even though it’s somewhat cryptic).

We can test this by cranking up SSMS and connecting:

Add another node

A single node cluster while nice isn’t particularly highly available, so we need to have this SQL instance deployed on the other node(s). This is a quick and painless task. Just copy over the SQL Install ini folder to the other node(s), login and run the setup.exe, this time calling the AddNode.ini file:

Again, go for a walk around the block, talk to your colleagues and eventually come back to:

Rinse and repeat for as many nodes as you need.

Summary

Setup your cluster, ensure Active Directory is good to go and use CreateClusteredSQLInstallFile.ps1 to quickly build your ini file to install SQL Server on to your cluster. It’s quick and easy. It allowed me to deploy 4 SQL Server instances to 5 nodes in under 4 1/2 hours.

Give it a try, let me know what you think. I’ll be looking to enhance it in the future.

Read Part 1

Read Part 2

In Part 1 I showed how the cluster was readied for the SQL install. In this short part 2 I’ll go over the Active Directory configuration components so that everything is ready for us to start performing the install.

Create the computer account

The first thing is to create a new Computer object in Active Directory. This will be the virtual name used in the cluster.

Once added we need to go back in and edit the properties of the Computer (you’ll need to use Advanced Features for this bit, look under the view menu item).

Go to the security tab of the Computer properties and hit the Add button. In the Select Users, Computers, Service Accounts or Groups window hit Object Types and ensure that Computers is checked. The cluster account will be used to manage the Computer resource (in this case CLUSTER1) so we add CLUSTER1$

And then give CLUSTER1$ Full Control over the computer object.

Finally the computer object needs to be disabled (if it’s not disabled your cluster install will not work correctly and it will fail to come online)

Create the service accounts

In the interest of security I’ll be using one account for the SQL Engine and another for the SQL Agent. Quickly create a couple of accounts with nice secure passwords (that of course you will keep somewhere secure, like in a KeePass file).

I like to give the accounts good descriptive names. It makes them easier to find later (actually the best way is to have a separate OU for the SQL accounts and for the SQL computer objects as it makes it a lot easier to manage and create Group Policies for them).

The boring stuff is done

The setup items are complete, we’re now ready to create the ini file….in part 3