SQL 2008 R2 on Windows Server 2008 R2 Cluster Install
8:30 PM
SQL 2008 R2 on Windows Server 2008 R2 Cluster Install
These directions assume you have already created the Windows cluster. Remember to validate the Windows cluster after you build it. If you do not validate the cluster you will receive an error. If you receive the Microsoft Cluster Services cluster validation report failure (http://support.microsoft.com/kb/953748) go to Failover Cluster manager and click Validate this cluster.
Creation of a new SQL Server cluster.
Installing and Configuring MSDTC: (For additional MSDTC information, click here.)
To install and configure MSDTC:
1. Open the Failover Cluster Management console on any of the cluster node.
2. Under the cluster name, right-click on Server and Applications and select Configure a Service or Application. This will run the High Availability Wizard
3. In the Service or Application dialog box, select Distributed Transaction Coordinator (DTC) and click Next.
4. In the Client Access Point dialog box, enter the name and IP address of the clustered MSDTC. This should be a different IP addresses and host name from the one that the Windows Server 2008 cluster is already using. Click Next.
5. In the Select Storage dialog box, select the disk subsystem that will be used by MSDTC. These disk subsystems have to be defined as available storage in your cluster. In the example below, I have used the disk volume F:\ and left the disk volume E:\ for SQL Server later in the installation process. Click Next
6. In the Confirmation dialog box, validate the configuration you have selected for MSDTC and click Next
7. In the Summary dialog box, click Close. This completes the installation of MSDTC on the cluster.
You can validate your installation of MSDTC by expanding the Services and Applications node and check the cluster name of MSDTC. Make sure that all of the dependency resources are online
Installing SQL Server 2008 on a Windows Server 2008 cluster (For additional information click here.)
To install SQL Server 2008:
1. Run setup.exe from the installation media to launch SQL Server Installation Center. Click on the Installation link on the left-hand side
2. Click the New SQL Server failover cluster installation link. This will run the SQL Server 2008 Setup wizard
3. In the Setup Support Rules dialog box, validate that the checks return successful results and click Next.
4. In the Product Key dialog box, enter the product key that came with your installation media and click Next.
5. In the License Terms dialog box, click the I accept the license terms check box and click Next.
6. In the Setup Support Rules dialog box, click Install. Validate that the checks return successful results. The checks will return a few warnings. The way NICs are configured at OpSource, their network binding order, normally results in a warning. Click Next to proceed.
7. In the Feature Selection dialog box, select only the components that you want installed. For the Shared feature directory, we normally install Client Tools, Integration Services, Client Tools Backward Compatibility, SQL Server Books Online & the Management Tools. Click Next.
8. In the Instance Configuration dialog box, enter the SQL Server Network Name. This is the name that will be available on the network for the clients. In this example, default instance is selected. The standard naming practice at OpSource is DataCenterClientNameSQL#PorS.
As an example: IADOPSOURCESQL01P or IADOPSOURCESQL01S
The section on Detected SQL Server instances and features on this computer would make sense if there are other SQL Server instances running on the server. It is blank in this example because there are no other instances installed.
9. In the Disk Space Requirements dialog box, check that there is enough space on the local disks to install the SQL Server 2008 binaries and click Next.
10. In the Cluster Resource Group dialog box, check the resources available on the Windows Server 2008 cluster. This will tell you that a new Resource Group will be created on the cluster for SQL Server. To specify the SQL Server cluster resource group name, use either the drop-down box to specify an existing group to use or type the name of a new group to create it. Click Next.
11. In the Cluster Disk Selection dialog box, select the available disk groups that are on the cluster for SQL Server 2008 to use. In this example, two clustered disk groups - APPS and APPS2 - have been selected to be used by SQL Server 2008. One disk resource will be used for the system databases while the other one for the user databases. Click Next.
12. In the Cluster Network Configuration dialog box, enter the IP address and subnet mask that your SQL Server 2008 cluster will use. Deselect the checkbox under the DHCP column and use a static IP addresses. If you have not disabled your IPv6 adapters and protocols, it would be better to uncheck the row for IPv6
13. In the Cluster Security Policy dialog box, accept the default value of Use service SIDs (recommended). In Windows Server 2003, we specify domain groups for all SQL Server services but in Windows Server 2008, this is the recommended option. (For more information on using service SIDs for SQL Server 2008, click here.)
14. In the Server Configuration dialog box, enter the domain account to be used for SQL Server service accounts in the Service Accounts tab. Leave the Collation selection at its default unless the customer requests a specific collation. Note that the startup type is set to manual for all cluster-aware services and cannot be changed during the installation process. Click Next.
15. In the Database Engine Configuration dialog box, select the Mixed Mode authentication and enter a password for sa. Make sure to add the currently logged on user (yourself) to be a part of the SQLServer administrators group, click the Add Current User button.
16. On the Data Directories tab, enter the path where your system and user database files will be created. This will default to the first shared disk in the cluster so in case you want to change it to the other shared disks to be used by SQL Server 2008, modify accordingly. Click Next
17. In the Error and Usage Reporting dialog box, click Next.
18. In the Cluster Installation Rules dialog box, verify that all checks are successful and click Next.
19. In the Ready to Install dialog box, verify that all configurations are correct. Click Next.
20. In the Complete dialog box, click Close. This concludes the installation of a SQL Server 2008 Failover Cluster
Failover Cluster Management Tool
At the completion of a successful installation and configuration of the node, you now have a fully functional failover cluster instance. To validate, open the Failover Cluster Management console, and click on SQL Server (MSSQLSERVER) under Services and Applications. Make sure that all dependencies are online
But the SQL Server 2008 failover cluster is only a one node failover cluster. We still have to add the second node to the SQL Server 2008 cluster to have high availability.
Adding a node on a SQL Server 2008 Failover Cluster
Now that there is working failover cluster, it is made HA by adding nodes. The number of nodes which can be added in a failover cluster depends on the version SQL Server being used. Standard Edition SQL Server 2008 can support up to two nodes in a failover cluster while the Enterprise Edition supports up to sixteen nodes
To add a node on a SQL Server 2008 failover cluster:
1. Run setup.exe from the installation media to launch SQL Server Installation Center
2. Click on the Installation link on the left-hand side. Click the Add node to a SQL Server failover cluster link. This will run the SQL Server 2008 Setup wizard.
At this point
3. In the Setup Support Rules dialog box, validate that the checks return successful results and click OK.
4. In the Product Key dialog box, enter the product key that came with your installation media and click Next.
5. In the License Terms dialog box, click the I accept the license terms check box and click Next.
6. In the Setup Support Rules dialog box, click Install. Validate that the checks return successful results. Again, make sure to fix any errors returned by this check before proceeding with the installation.
7. In the Cluster Node Configuration dialog box, validate that the information for the existing SQL Server 2008 cluster is correct.
8. In the Service Accounts dialog box, verify that the information is the same as what you have used to configure the first node.
9. In the Error and Usage Reporting dialog box, click Next
10. In the Add Node Rules dialog box, verify that all checks are successful and click Next
11. In the Ready to Add Node dialog box, verify that all configurations are correct and click Install
12. In the Complete dialog box, click Close. The installation of the additional node of on the SQL Server 2008 Failover Cluster is now complete.
You can validate your cluster installation by expanding the Services and Applications node and check the cluster name of your SQL Server instance. Here you will see an option to move the service to another node. This option can be used now to test failover the cluster or during maintenance windows. During those windows you can perform Windows patching on the passive node, then failover the cluster to switch the active node to the passive node and then patch the other node.
Additional Information:
MSDTC
The Microsoft Distributed Transaction Coordinator (MSDTC) is a transaction manager that permits client applications to include several different data sources in one transaction and which then coordinates committing the distributed transaction across all the servers that are enlisted in the transaction. A lot of people ask why we need to install MSDTC prior to installing SQL Server. If you are using distributed transactions or running SQL Server on a cluster, this is definitely a must. SQL Server uses the MSDTC service for distributed queries and two-phase commit transactions, as well as for some replication functionality.
Configuring MS DTC in Windows Server 2003 clusters as defined in this Microsoft KB article is not pretty straight-forward. Windows Server 2008 made it simpler by providing a more straightforward process with fewer steps and less configuration.
SQL 2008 Cluster
You've gone this far, don't stop now. Only after we have managed to prepare everything can we proceed to install SQL Server 2008 on this cluster. Since we've already installed .NET Framework 3.5 with Service Pack 1 and Windows Installer 4.5 from Part 1, we no longer have to worry about them as they both are prerequisites whether you are doing a single server or a cluster installation. There are two options to install SQL Server 2008 on a cluster. The first one is by using the Integrated failover cluster install with Add Node option and the second one is the Advanced/Enterprise installation option. The process outlined below will take into account the first option.
Setting Up Windows Service Accounts for SQL Server 2008 R2
Each service in SQL Server represents a process or a set of processes to manage authentication of SQL Server operations with Windows. This topic describes the default configuration of services in this release of SQL Server, and configuration options for SQL Server services that you can set during SQL Server installation.
Depending on the components that you decide to install, SQL Server Setup installs the following services:
• SQL Server Database Services - The service for the SQL Server relational Database Engine.
• SQL Server Agent - Executes jobs, monitors SQL Server, fires alerts, and enables automation of some administrative tasks.
Note
For SQL Server and SQL Server Agent to run as services in Windows, SQL Server and SQL Server Agent must be assigned a Windows user account. For more information about how to customize account information for each service, see How to: Install SQL Server 2008 R2 (Setup).
• Analysis Services - Provides online analytical processing (OLAP) and data mining functionality for business intelligence applications.
• Reporting Services - Manages, executes, creates, schedules, and delivers reports.
• Integration Services - Provides management support for Integration Services package storage and execution.
• SQL Server Browser - The name resolution service that provides SQL Server connection information for client computers.
• Full-text search - Quickly creates full-text indexes on content and properties of structured and semistructured data to provide document filtering and word-breaking for SQL Server.
• SQL Server Active Directory Helper - Publishes and manages SQL Server services in Active Directory.
• SQL Writer - Allows backup and restore applications to operate in the Volume Shadow Copy Service (VSS) framework.
Important
Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as setting permissions in the Windows Registry so that the new account can read the SQL Server settings. Other tools such as the Windows Services Control Manager can change the account name but do not change associated settings. If the service cannot access the SQL Server portion of the registry, the service may not start properly.
These directions assume you have already created the Windows cluster. Remember to validate the Windows cluster after you build it. If you do not validate the cluster you will receive an error. If you receive the Microsoft Cluster Services cluster validation report failure (http://support.microsoft.com/kb/953748) go to Failover Cluster manager and click Validate this cluster.
Creation of a new SQL Server cluster.
Installing and Configuring MSDTC: (For additional MSDTC information, click here.)
To install and configure MSDTC:
1. Open the Failover Cluster Management console on any of the cluster node.
2. Under the cluster name, right-click on Server and Applications and select Configure a Service or Application. This will run the High Availability Wizard
3. In the Service or Application dialog box, select Distributed Transaction Coordinator (DTC) and click Next.
4. In the Client Access Point dialog box, enter the name and IP address of the clustered MSDTC. This should be a different IP addresses and host name from the one that the Windows Server 2008 cluster is already using. Click Next.
5. In the Select Storage dialog box, select the disk subsystem that will be used by MSDTC. These disk subsystems have to be defined as available storage in your cluster. In the example below, I have used the disk volume F:\ and left the disk volume E:\ for SQL Server later in the installation process. Click Next
6. In the Confirmation dialog box, validate the configuration you have selected for MSDTC and click Next
7. In the Summary dialog box, click Close. This completes the installation of MSDTC on the cluster.
You can validate your installation of MSDTC by expanding the Services and Applications node and check the cluster name of MSDTC. Make sure that all of the dependency resources are online
Installing SQL Server 2008 on a Windows Server 2008 cluster (For additional information click here.)
To install SQL Server 2008:
1. Run setup.exe from the installation media to launch SQL Server Installation Center. Click on the Installation link on the left-hand side
2. Click the New SQL Server failover cluster installation link. This will run the SQL Server 2008 Setup wizard
3. In the Setup Support Rules dialog box, validate that the checks return successful results and click Next.
4. In the Product Key dialog box, enter the product key that came with your installation media and click Next.
5. In the License Terms dialog box, click the I accept the license terms check box and click Next.
6. In the Setup Support Rules dialog box, click Install. Validate that the checks return successful results. The checks will return a few warnings. The way NICs are configured at OpSource, their network binding order, normally results in a warning. Click Next to proceed.
7. In the Feature Selection dialog box, select only the components that you want installed. For the Shared feature directory, we normally install Client Tools, Integration Services, Client Tools Backward Compatibility, SQL Server Books Online & the Management Tools. Click Next.
8. In the Instance Configuration dialog box, enter the SQL Server Network Name. This is the name that will be available on the network for the clients. In this example, default instance is selected. The standard naming practice at OpSource is DataCenterClientNameSQL#PorS.
As an example: IADOPSOURCESQL01P or IADOPSOURCESQL01S
The section on Detected SQL Server instances and features on this computer would make sense if there are other SQL Server instances running on the server. It is blank in this example because there are no other instances installed.
9. In the Disk Space Requirements dialog box, check that there is enough space on the local disks to install the SQL Server 2008 binaries and click Next.
10. In the Cluster Resource Group dialog box, check the resources available on the Windows Server 2008 cluster. This will tell you that a new Resource Group will be created on the cluster for SQL Server. To specify the SQL Server cluster resource group name, use either the drop-down box to specify an existing group to use or type the name of a new group to create it. Click Next.
11. In the Cluster Disk Selection dialog box, select the available disk groups that are on the cluster for SQL Server 2008 to use. In this example, two clustered disk groups - APPS and APPS2 - have been selected to be used by SQL Server 2008. One disk resource will be used for the system databases while the other one for the user databases. Click Next.
12. In the Cluster Network Configuration dialog box, enter the IP address and subnet mask that your SQL Server 2008 cluster will use. Deselect the checkbox under the DHCP column and use a static IP addresses. If you have not disabled your IPv6 adapters and protocols, it would be better to uncheck the row for IPv6
13. In the Cluster Security Policy dialog box, accept the default value of Use service SIDs (recommended). In Windows Server 2003, we specify domain groups for all SQL Server services but in Windows Server 2008, this is the recommended option. (For more information on using service SIDs for SQL Server 2008, click here.)
14. In the Server Configuration dialog box, enter the domain account to be used for SQL Server service accounts in the Service Accounts tab. Leave the Collation selection at its default unless the customer requests a specific collation. Note that the startup type is set to manual for all cluster-aware services and cannot be changed during the installation process. Click Next.
15. In the Database Engine Configuration dialog box, select the Mixed Mode authentication and enter a password for sa. Make sure to add the currently logged on user (yourself) to be a part of the SQLServer administrators group, click the Add Current User button.
16. On the Data Directories tab, enter the path where your system and user database files will be created. This will default to the first shared disk in the cluster so in case you want to change it to the other shared disks to be used by SQL Server 2008, modify accordingly. Click Next
17. In the Error and Usage Reporting dialog box, click Next.
18. In the Cluster Installation Rules dialog box, verify that all checks are successful and click Next.
19. In the Ready to Install dialog box, verify that all configurations are correct. Click Next.
20. In the Complete dialog box, click Close. This concludes the installation of a SQL Server 2008 Failover Cluster
Failover Cluster Management Tool
At the completion of a successful installation and configuration of the node, you now have a fully functional failover cluster instance. To validate, open the Failover Cluster Management console, and click on SQL Server (MSSQLSERVER) under Services and Applications. Make sure that all dependencies are online
But the SQL Server 2008 failover cluster is only a one node failover cluster. We still have to add the second node to the SQL Server 2008 cluster to have high availability.
Adding a node on a SQL Server 2008 Failover Cluster
Now that there is working failover cluster, it is made HA by adding nodes. The number of nodes which can be added in a failover cluster depends on the version SQL Server being used. Standard Edition SQL Server 2008 can support up to two nodes in a failover cluster while the Enterprise Edition supports up to sixteen nodes
To add a node on a SQL Server 2008 failover cluster:
1. Run setup.exe from the installation media to launch SQL Server Installation Center
2. Click on the Installation link on the left-hand side. Click the Add node to a SQL Server failover cluster link. This will run the SQL Server 2008 Setup wizard.
At this point
3. In the Setup Support Rules dialog box, validate that the checks return successful results and click OK.
4. In the Product Key dialog box, enter the product key that came with your installation media and click Next.
5. In the License Terms dialog box, click the I accept the license terms check box and click Next.
6. In the Setup Support Rules dialog box, click Install. Validate that the checks return successful results. Again, make sure to fix any errors returned by this check before proceeding with the installation.
7. In the Cluster Node Configuration dialog box, validate that the information for the existing SQL Server 2008 cluster is correct.
8. In the Service Accounts dialog box, verify that the information is the same as what you have used to configure the first node.
9. In the Error and Usage Reporting dialog box, click Next
10. In the Add Node Rules dialog box, verify that all checks are successful and click Next
11. In the Ready to Add Node dialog box, verify that all configurations are correct and click Install
12. In the Complete dialog box, click Close. The installation of the additional node of on the SQL Server 2008 Failover Cluster is now complete.
You can validate your cluster installation by expanding the Services and Applications node and check the cluster name of your SQL Server instance. Here you will see an option to move the service to another node. This option can be used now to test failover the cluster or during maintenance windows. During those windows you can perform Windows patching on the passive node, then failover the cluster to switch the active node to the passive node and then patch the other node.
Additional Information:
MSDTC
The Microsoft Distributed Transaction Coordinator (MSDTC) is a transaction manager that permits client applications to include several different data sources in one transaction and which then coordinates committing the distributed transaction across all the servers that are enlisted in the transaction. A lot of people ask why we need to install MSDTC prior to installing SQL Server. If you are using distributed transactions or running SQL Server on a cluster, this is definitely a must. SQL Server uses the MSDTC service for distributed queries and two-phase commit transactions, as well as for some replication functionality.
Configuring MS DTC in Windows Server 2003 clusters as defined in this Microsoft KB article is not pretty straight-forward. Windows Server 2008 made it simpler by providing a more straightforward process with fewer steps and less configuration.
SQL 2008 Cluster
You've gone this far, don't stop now. Only after we have managed to prepare everything can we proceed to install SQL Server 2008 on this cluster. Since we've already installed .NET Framework 3.5 with Service Pack 1 and Windows Installer 4.5 from Part 1, we no longer have to worry about them as they both are prerequisites whether you are doing a single server or a cluster installation. There are two options to install SQL Server 2008 on a cluster. The first one is by using the Integrated failover cluster install with Add Node option and the second one is the Advanced/Enterprise installation option. The process outlined below will take into account the first option.
Setting Up Windows Service Accounts for SQL Server 2008 R2
Each service in SQL Server represents a process or a set of processes to manage authentication of SQL Server operations with Windows. This topic describes the default configuration of services in this release of SQL Server, and configuration options for SQL Server services that you can set during SQL Server installation.
Depending on the components that you decide to install, SQL Server Setup installs the following services:
• SQL Server Database Services - The service for the SQL Server relational Database Engine.
• SQL Server Agent - Executes jobs, monitors SQL Server, fires alerts, and enables automation of some administrative tasks.
Note
For SQL Server and SQL Server Agent to run as services in Windows, SQL Server and SQL Server Agent must be assigned a Windows user account. For more information about how to customize account information for each service, see How to: Install SQL Server 2008 R2 (Setup).
• Analysis Services - Provides online analytical processing (OLAP) and data mining functionality for business intelligence applications.
• Reporting Services - Manages, executes, creates, schedules, and delivers reports.
• Integration Services - Provides management support for Integration Services package storage and execution.
• SQL Server Browser - The name resolution service that provides SQL Server connection information for client computers.
• Full-text search - Quickly creates full-text indexes on content and properties of structured and semistructured data to provide document filtering and word-breaking for SQL Server.
• SQL Server Active Directory Helper - Publishes and manages SQL Server services in Active Directory.
• SQL Writer - Allows backup and restore applications to operate in the Volume Shadow Copy Service (VSS) framework.
Important
Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as setting permissions in the Windows Registry so that the new account can read the SQL Server settings. Other tools such as the Windows Services Control Manager can change the account name but do not change associated settings. If the service cannot access the SQL Server portion of the registry, the service may not start properly.
0 comments