How to install SQL Server on an active/passive cluster?

SQL Server clustering is a collection of two or more physical servers known as nodes with access to shared resources forming a public network that serves as the interface for applications. SQL Server service, SQL Server agent service, and disks are example of shared resources. Each of the nodes communicates constantly with each other through aninternal private network inside the cluster. This is also known as the heartbeat.  The heartbeat checks whether a node is up and running. Only one node in an active/passive cluster can access to the shared resources at a time. When active node losses heartbeat or communication to the other node in the cluster, the passive node will take over the ownership of shared resources to service all requests coming in to the cluster. This is known as failover.

SQL Server clustering is created on top of Microsoft Cluster Services (MSCS).  MSCS is capable of detecting hardware and software failures and automatically change the control of the resources to a functioning node when needed.  To coordinate the distributed transactions between 2 or more nodes, the Microsoft Distributed Transaction Control(MSDTC) service is needed. Within a cluster, the Quorum Drive contains the update-to-date information about the state of the cluster. Without it, the cluster can’t function at all.

Prior to installing SQL Server on a cluster, you should ask or work with the System Administrators to install and configure the Microsoft Cluster Services (MSCS). In addition, you should have your checklist ready for verification and preparation for the installation. It is recommended that you write down the answer next to each of the check items. The following is an example of such list:

1. Know the nodes for the cluster and their ip addresses and access to both nodes.
2. The SQL Server installation product key.
3. The SQL Server components to be installed.
4. The SQL Server virtual server name.
5. Default or named instance?
6. The shared disks to select.
7. The SQL Server virtual IP address.
8. The subnet mask. Usually it is 255.255.255.0
9. The dedicated domain account for the SQL Server service.
10. The dedicated domain account for SQL Server Agent service.
11. The dedicated domain accounts for each of the services to be installed (SSAS, SSIS, and/or SSRS).
12. The SQL Server Collation to select.
13. Windows or Mixed authentication mode?
14. The list of users to be the SQL Server administrator for the instance.
15. The default locations for the data and log files for the user databases and tempdband also the location for the backup directory.
16. Do you need to enable FILESTREAM?

Before running the installation wizard, you should run the validation wizard for your cluster configuration to make sure all components are compliant:

1. Click the “Start” button then click on “Administrative Tools” and then click on “Failover Cluster Management.”
2. In “Failover Cluster Manager,” click on the “Validate a Configuration” link.
3. In “Select Server or a Cluster,” browse to or enter the cluster name or the nodes.
4. In “Testing Options,” select the option “Run all tests (recommended)” and then click “Next.”
5. In “Review Storage Status,” select the option “Take the listed services or applications offline, so that the associated disks can be tested. These services or applications might be unavailable for a significant time. They will be brought online again after the tests.”
6. Click “Next.”
7. Review the “Failover Cluster Validation” report.

After you had the checklist items filled in and ran the cluster configurations validation check, you are ready for the installation of SQL Server on the cluster. Here we go:

1. Insert the CD or on the installation folder, run setup.
2. On the “SQL Server Installation Center” page, click “Installation” link on the left hand column, then click on the link “New SQL Server failover cluster installation” on the right.
3. On the “Setup Support Files” page, click the “Install” button.
4. The System Configuration Checker runs a discovery operation on your computer. Review the details by clicking on the “Show details >>” button if necessary. Click the “Ok” button to continue.
5. On the “Product Key” page, key in the product key and then click the “Next” button.
6. On the “License Terms” page, check the “I accept the license terms” checkbox and then click the “Next” button to continue.
7. On the “Setup Support Rules” page, review and correct the errors and warning if there are any before proceeding. If all green, meaning successfully, then click the “Next” button to continue.
8. On the “Feature Selection” page, select the components that you need not what you wish to have for your installation.
9. On the “Instance Configuration” page, key in the SQL Server virtual name in the “SQL Server Network Name” textbox and also determine if you wish to have a default instance or a named instance. Make the selection for the corresponding radio button. If you selected the “Named instance” option, you must key in the name for the named instance. By default, the instance named is used as the Instance ID. You can leave as the default. Click the “Next” button to continue.
10. On the “Disk Space Requirements” page, the installer checks if you have enough disk space on your local disks to install the SQL Server 2008 binaries. You should see the green check mark(s) indicating that you have sufficient amount of disk space for the installation. Click the “Next” button to continue.
11. On the “Cluster Resource Group” page, select “SQL Server (MSSQLSERVER)” in the drop-down box for “SQL Server cluster resource group name:” or you can type in a new one as the cluster resource group. Click the “Next” button to continue.
12. On the “Cluster Disk Selection” page, select the shared disks for the SQL Server resource cluster group. Click the “Next” button to continue.
13. On the “Cluster Network Configuration” page, specify the “IP Address” and “Subnet Mask” for the SQL Server cluster. Deselect the checkbox for “DHCP” as you will be using static IP addresses. Uncheck “IPv6” if it is checked. Click the “Next” button to continue.
14. On the “Cluster Security Policy” page, select the option “Use service SIDs (recommended).” This is the recommended option for Windows Server 2008 and later.
15. On the “Server Configuration” page and in the “Service Accounts” tab, specify the “Account Name” and “Password” for the SQL Server Agent service, the SQL Server Database Engine service, and other services that you had selected in the components section page. Microsoft recommends individual accounts for each of the services in addition to following the principle of Least Amount of Privileges.
16.  Still on the “Server Configuration” page, click the “Collation” tab and then specify the collation as per the application(s) requirements. Don’t select the default unless you are absolutely sure or else it will be a royal pain in the behind to correct this. Stop and confirm the correct collation before moving on. Seriously! Once you have selected the collation, click the “Next” button to continue.
17. On the “Database Engine Configuration” page and in the “Account Provisioning” tab, select the option “Windows authentication mode” or the option “Mixed Mode (SQL Server authentication and Windows authentication).”  If you had selected the latter, then you must key in the “sa” password. In the “Specify SQL Server administrators” section, click the “Add Current User” button to add the user running the installation as an administrator. You can also add more users by clicking on the “Add…” button.
18. Still on the “Database Engine Configuration” page, click the “Data Directories” tab to specify and default directories for the data, user databases data and log, tempdb data and log, and backup.
19. Still on the “Database Engine Configuration” page, click the “FILESTREAM” tab to enable Filestream on this instance if needed. Click the “Next” button to continue.
20. On the “Error and Usage Reporting” page, check the corresponding optional checkboxes if you wish then click the “Next” button to continue.
21. On the “Cluster Installation Rules” page, the installation validation system validates the configuration and selection of the specified SQL Server components.  If everything passed, clicked the “Next” button to continue.
22. On the “Ready to Install” page, you can see the components that were selected. Click the “Install” button to start the installation.
23. On the “Installation Progress” page, you can see the installation progress status as the bar move to the right end.
24. Cross your fingers and wait for the “Complete” page and hope to see the green check mark “Your SQL Server 2008 failover cluster installation is complete.” Click the “Close” button to end.

You will now need to add the second node in the failover cluster to enable high-availability.

1. On the passive node, start the installation.
2. On the “SQL Server Installation Center” page, click the “Installation” link on the left hand column and click on the “Add node to a SQL Server failover cluster” link on the right.
3. On the “Setup Support Rules” page,  validate the checks return all successful results, then click the “Ok” button to continue.
4. On the “Product Key” page, key in the product key and then click the “Next” button.
5. On the “License Terms” page, check the “I accept the license terms” checkbox and then click the “Next” button to continue.
6. On the “Cluster Node Configuration” page, select the virtual SQL Server instance name on the “SQL Server instance name” drop-down box. Click the “Next” button to continue.
7. On the “Service Accounts” page, specify the “Account Name” and “Password” for the SQL Server Agent service, the SQL Server Database Engine service, and other services that you had selected in the components section page. Microsoft recommends individual accounts for each of the services in addition to following the principle of Least Amount of Privileges. Once done, click the “Next” button to continue.
8. On the “Error and Usage Reporting” page, review and correct the errors and/or warnings if needed and then click the “Next” button to continue.
9. On the “Ready to Add Node” page, review the configurations and selections you have made and then click “Next” to continue.
10. On the “Add Node Progress” page, the status progress bar will move to the right end as per the installation progress.
11. On the “Complete” page, nothing is sweeter than seeing the green check mark next to “Your SQL Server 2008 failover cluster add node operation is complete.” Click the “Close” button to end.

This is what the SQL Server cluster looks like in the Cluster Admin: Screen-shot.

After verifying that the latest services pack is compatible with the application(s) you can then apply the latest service pack or hotfixes on the Active/Passive SQL Server cluster with the following steps:

1. On the passive node (Node2), apply the service pack or hotfixes.
2. Reboot the passive node (Node2).
3. On the active node (Node1), failover the SQL Server resource. The passive node (Node2) that you had already patched will become the active node. To failover the SQL Server resource, open the “Failover Cluster Management,” under “Services and Applications,” right-click on “SQL Server (MSSQLSERVER),” then click on “Move this service or application to another node >” and click on the option to move to the passive node.
4. On the passive node (Node1), apply the service pack or hotfixes.
5. Reboot the passive node (Node1).

You can verify the current service pack and version build number by running the following query:

view source

1	-- Querying the SQL Server Instance level info

2

SELECT

3	SERVERPROPERTY('ServerName') AS [SQLServer]

4	,SERVERPROPERTY('ProductVersion') AS [VersionBuild]

5	,SERVERPROPERTY ('Edition') AS [Edition]

6	,SERVERPROPERTY('ProductLevel') AS [ProductLevel]

7	,SERVERPROPERTY('IsIntegratedSecurityOnly') AS[IsWindowsAuthOnly]

8	,SERVERPROPERTY('IsClustered') AS [IsClustered]

9	,SERVERPROPERTY('Collation') AS [Collation]

10	,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS[CurrentNodeName]