To understand what is the failover clustering and its general requirements you can go here.
To install failover clustering solution, here are essential steps to remember:
- 2 servers based on Windows Server 2008 need to be installed
- At least two network cards required: a public card (with dedicated IP) to connect each node in the cluster and a private cloud (or HA) that connects nodes to each other via a VLAN dedicated and private addressing mode and distinct (and no configuration gateway and DNS).
- Provide an IP address for MSDTC
- 5 (LUNs) partitions ( database systems, data, logs, backups, and tempDB) + 2 ( quorum , MSDTC) on a SAN.
- Create AD accounts service: One for the SQL engine and one for SQL Agent.
- And finally, the type of quorum that will be using the default Node and Disk Majority.
- .Net Framework 3.5 or above need be installed.
You can also take a look at the checklist of items to meet the needs before installing SQL Server 2008 R2 failover cluster: http://msdn.microsoft.com/en-us/library/ms189910(v=sql.100)
Implementation of a failover cluster
In our example, we will opt for a quorum , majority disc.
- Before installing the cluster, it is important to ensure the activation of the feature Failover Clustering. To enable (if not already), go to Server Manager and Features:
- On the first node, open the administration console of the cluster:
- Launch the validate a configuration wizard to create validate configuration. Then follow all steps in the wizard in the wizard step by step, by taking care to run all tests (hardware, software, networking storage, compatibility, etc…).
- Validate / check the record of the tests
Note : In case of errors, it is highly recommended to correct them before installing the cluster.
As soon as the validation done successfully, the construction of the cluster can be considered healthy. To do this:
- Launch the failover cluster management wizard to create a cluster ( create a cluster … In the previous steps).
- A create cluster wizard pop box will open where you need to add nodes to the cluster future.
Before You Begin
- Specify the details of the name and the IP address of the cluster that you need to create:
- Confirmation of the first configurations and upgrades.
- By default, the quorum type chooses the cluster Node and Disk Majority . However, it is possible to change its configuration (including when Windows chooses to opt for a different disk than the one you want) from the administration console of the cluster.
- This includes the possibility to choose the type of quorum configuration to use.
- Or even to confirm or modify the choice of dedicated disk quorum.
Installing and Configuring MSDTC
MSDTC ( Microsoft Distributed Transaction Coordinator ) is a Windows feature that coordinates distributed transactions across different nodes in a cluster.
To activate and install (on node 1):
- Open the management console cluster.
- Under the cluster name, click the right button on Services and Applications and select Configure a Service or Application … :
Select Distributed Transaction Coordinator (DTC):
Enter the name and IP of the MSDTC resource:
Specify a shared cluster available for MSDTC disk:
Confirm the configuration:
To implement functionality and to configure logs, follow the below guideline:
- Edit the following registry key on one of the nodes:
HKEY_LOCAL_MACHINE Cluster Resources <ResID> MSDTCPRIVATE MSDTC
Create a new “MaxLogSize” key (DWORD) and give it the value 512 (decimal)
- In the console Component Services, navigate to:
Component Services Computers My Computer Distributed Transaction Coordinator DTC Cluster Name> MSDTC> cluster
- In the properties registration tab, specify a capacity of logs
Implementation of SQL server nodes
Installing SQL Server 2008 R2 on the first node
The installation steps are similar to the installation of SQL Server 2008 R2 in standalone . We will here discuss the key steps :
Preparing to install:
Choice of components:
Specify the virtual network instance name:
By default, the instance name is used as the Instance ID. This is used to identify installation directories and registry keys for our instance of SQL Server, and is very useful when it is desired to run multiple instances within a cluster. For a default instance, the instance name and ID would be MSSQLSERVER.
- Checking constraints Storage
Specify the name of the resource group of SQL Server: SQL Server (MSSQLSERVER) :
- Select the disks used by SQL Server (data, logs and backups)
- Specify the public IP address that is used by the SQL Server address:
- Choice of security policy in the cluster allows to use SIDs Service :
- The following steps are usually the same as for installation in standalone. Cluster Installation Rules, it only remains to validate and complete the installation:
Adding a second node SQL Server 2008 R2
- On node 2 run the installation media on SQL Server 2008 R2, and then select Add Node to a SQL Server failover cluster:
Follow the same steps for installing SQL Server 2008 R2 on the first node (see previous section). This will include:
- Validate the information on the existing cluster:
- Make sure the service account (SQL, SQL Agent, …) is the same as for the first node.
After installation, you will be able to perform a failover from the first node to the second node, and vice versa: