Build a SQL Server 2012 failover cluster in Windows Server R2

It is necessary to note that the SQL Server failover cluster (SQL Server Failover Cluster) We build is an availability cluster, not a load-balanced cluster, which is designed to ensure continuity and availability of services, not to improve service performance.

SQL Server always lacks its own products in a load-balanced cluster, which is provided by third-party manufacturers, but SQL Server failover clusters are long-standing, and an availability group is available in SQL Server 2012 (AlwaysOn high Availability Groups), we know that Microsoft's failover cluster (Windows Server Failover clustering, WSFC) typically requires shared storage, and the SQL Server failover cluster is based on WSFC. An availability group can implement SQL Server failover without relying on shared storage, which provides a highly available solution for implementing SQL Server for environments that do not have shared storage, and about AlwaysOn features can be found in related documents. What we're doing here is still a SQL Server failover cluster with two nodes based on shared storage.

I. Building a Windows Failover cluster (WSFC)

The SQL Server failover cluster is WSFC-based, so we need to set up a WSFC in two nodes beforehand, where WSFC is just a container, where multiple roles can be placed for failover of those roles. In order to build a WSFC, in addition to the need for a domain environment, but also in the node, storage, network and other aspects to prepare.

1, add failover clustering server function in each node.

2, ensure that the update of the operating system of the node is consistent, the newly installed system is either updated to the latest, or temporarily not updated.

3, configure the Management Network and heartbeat network in each node, although an available network can build clusters, but best practice is still separate.

4. Configure the shared storage disk in each node, initialize and format the disk, and assign the drive letter. The shared storage disks can be either IP san and FC San-based disks or file server-based virtual disks, which can be referenced in Windows Server 2012 virtualization testing: Storage. The disks visible in the node are as follows:

In order to build a SQL Server failover cluster, at least two shared disks need to be prepared: Cluster witness disk Q, cluster disk s for storing SQL Server databases and log files. In addition, we need to configure the Distributed Transaction Coordinator (distributed Transaction Coordinator, DTC) for the clustered instance of SQL Server, so we need to prepare disk m for DTC. Microsoft recommends storing all types of SQL Server files separately, and it is best practice to prepare two or more shared disks, each storing the user database, backup, and user database log files, which requires at least another cluster disk L. We have the following configuration for storage:

• Cluster witness Disk Q
• DTC Disk M
• SQL Server program: Local Disk C
• User database file: Cluster disk S
• User Database log file: Cluster disk L
• tempdb file: Local Disk D,sql Server 2012 supports the ability to place a temp db file on a local fast disk.
• Backup file: Cluster disk S

It is also worth mentioning that SQL Server 2014 does not provide support for cluster shared volumes, so only cluster disks can be used here.

5. Use failover Cluster Manager to verify and create the cluster. The cluster disk view after completion is as follows:

Ii. Installing a SQL Server failover cluster

After the Windows Failover Cluster (WSFC) build succeeds, the foundation for the SQL Server failover cluster is completed, and then we continue to complete the SQL Server section. First install SQL Server Failover Cluster on one node, and then another node installation to join the cluster node.

In the SQL Server Cluster section, verify that the warning here is primarily to build a warning warning, escalation warning, and firewall warning for the Windows failover cluster to continue.

Select Database Engine services and management components, and note that there is only database engine services and Analysis Services support clusters and no other services are supported. Other components can be added later if needed, but add additional builds when selecting Add features to an existing installation, and then select Perfom a new installation of SQL Server 2012 instead of ad D features to an existing instance of SQL Server 2012, otherwise existing clustered or cluster-prepared instance error will occur, specifically refer to instal Ling SQL integration Services after SQL Cluster Setup has completed.

Configure a network name, similar to the computer name, in which the database instance will be accessed in the future.

Iii. Configuring the DTC and SQL Server clusters

The Distributed Transaction Coordinator (distributed Transaction Coordinator, DTC) is a service that is installed and running by default in Windows. The main purpose of the DTC is to implement distributed transactions to ensure consistency across process communications, where the process can be two processes on the same computer or processes from different computers. So in Microsoft's world, often see the figure of DTC.

If you only install the SQL Server database engine independently, you do not need to configure DTC. However, in scenarios where a distributed transaction is required to run SQL Serve Integration Services (SQL Server Integration Services, SSIS) or to build a SQL Sever failover cluster, you need to configure the DTC. Not configuring the DTC does not affect the installation of the SQL Server cluster, but the DTC is not configured correctly and the functionality of the SQL Server cluster will be affected.

Windows Server 2008 and later versions can have multiple instances of DTC in a Windows cluster, which can be either a clustered instance or a local instance (where the "instance" concept is similar to an instance of the SQL Server database engine and is run as an operating system service , is a copy of the same executable program that is running in the Windows cluster as an instance, which relies on the Windows cluster for failover, and even configures a dedicated DTC instance for each instance of SQL Server in the SQL Server cluster. The SQL Server cluster instances Select the DTC instance in the following order:

• With a dedicated instance of the DTC instance of SQL Server, the DTC instance is a resource since the instance of SQL Server, and if the DTC instance fails, it will cause a failure of the SQL Server instance. This is only available for SQL Server 2008 and later versions.

• Use the command MSDTC to map a DTC instance to a SQL Server instance using a DTC instance that is mapped to an instance of SQL Server.

• With the default DTC cluster instance, SQL Server 2008 and later versions can create multiple DTC instances in a Windows cluster, the first one created as the default instance, and the DTC cluster instance not specified for the SQL Server instance. This instance can therefore be used by other applications as well.

• Use the DTC instance that is installed on the local computer.

Because the SQL Server cluster instance is not automatically re-selected after making a selection, for example, the SQL Server cluster instance chooses a dedicated DTC instance, and even if the instance fails, the next available DTC instance will not be replaced unless the dedicated DTC instance is manually deleted, so Microsoft recommends that the SQL Server 2008 and later versions either create a dedicated DTC instance for each instance of SQL Server in the SQL Server cluster, or do not create any DTC instances in the SQL Server cluster (where the DTC instance is a clustered instance, That is, DTC failover can be implemented). For more information about DTC, check here. Of course we will not do nothing here, we will configure a dedicated DTC instance for the SQL Server instance below.

Build a SQL Server 2012 failover cluster in Windows Server R2