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.

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;border-top-width:0px; border-bottom-width:0px;border-left-width:0px;padding-top:0px; "title=" Cluster "border=" 0 "alt=" Cluster "src=" http ://s3.51cto.com/wyfs02/m00/59/c5/wkiol1thustxckpoaafdagjogzc333.jpg "width=" 556 "/>

1, add failover clustering server function in each node.

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;border-top-width:0px; border-bottom-width:0px;border-left-width:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m02/59/c5/wkiol1thusshymjtaadzwjomdle561.jpg "width=" 553 "/>

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:

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;border-top-width:0px; border-bottom-width:0px;border-left-width:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m00/59/c8/wkiom1thucnrucadaaekzfvl2gw012.jpg "width=" 941 "/>

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 the temp db file on a local fast disk, but it is important to note that the same path must be established on each node so that SQL Server holds the tempdb file.

• 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.

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;border-top-width:0px; border-bottom-width:0px;border-left-width:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m00/59/c8/wkiom1thucnhthicaag390w6cyc259.jpg "width=" 775 "/>

The domain name and IP address of the cluster need to be defined, and Failover Cluster Manager will access the cluster through the domain names or IP. The cluster disk view after the cluster creation is complete is as follows:

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;border-top-width:0px; border-bottom-width:0px;border-left-width:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m01/59/c8/wkiom1thucrziyubaahs_-qqz0g793.jpg "width=" 901 "/>

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.

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;border-top-width:0px; border-bottom-width:0px;border-left-width:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m02/59/c5/wkiol1thusfam_f4aaiwhspyw5u171.jpg "width=" 795 "/>

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.

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;border-top-width:0px; border-bottom-width:0px;border-left-width:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m00/59/c5/wkiol1thusfzju5waaoanu4v-gc432.jpg "width=" 816 "/>

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.

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;border-top-width:0px; border-bottom-width:0px;border-left-width:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m00/59/c8/wkiom1thuczatu7vaairwo76yvw977.jpg "width=" 815 "/>

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

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;border-top-width:0px; border-bottom-width:0px;border-left-width:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m01/59/c8/wkiom1thuc3job_taaj_aajdb04880.jpg "width=" 814 "/>

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), at which point the SQL Server cluster instance selects the local DTC instance of the node on which the instance resides. 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.

To achieve high availability of DTC, you need to install the application Server role on each node.

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;border-top-width:0px; border-bottom-width:0px;border-left-width:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m02/59/c5/wkiol1thusmy3mesaalv3eoatsa289.jpg "width=" 797 "/>

In Windows Failover Cluster Manager, in the SQL Server instance, right-click Add Storage, and make sure that the disk that is used for the DTC is joined to the SQL Server instance resource.

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;border-top-width:0px; border-bottom-width:0px;border-left-width:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m02/59/c8/wkiom1thuc6xenkiaakmvm4nvdi891.jpg "width=" 904 "/>

On the SQL Server instance, right-click Add Resource > More Resources > Distributed Transaction Coordinator to create a dedicated instance of DTC for the SQL Server instances.

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;border-top-width:0px; border-bottom-width:0px;border-left-width:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m00/59/c5/wkiol1thusur-rxnaamxuhngcuw132.jpg "width=" 904 "/>

Right-click the newly created DTC instance new distributed Transaction Coordinator, and in dependencies, configure the previously prepared cluster disk and host name (using the cluster name of SQL Server) for the DTC instance.

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;border-top-width:0px; border-bottom-width:0px;border-left-width:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m00/59/c8/wkiom1thuc-jtd-1aaobwtwk144196.jpg "width=" 904 "/>

Right-click the DTC instance to select Bring Online to create a dedicated DTC instance for the SQL Server cluster instance. While this configuration improves efficiency, it makes the DTC instance a resource that is dependent on SQL Server, and the failure of the DTC instance will cause the SQL Server cluster instance to fail.

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;border-top-width:0px; border-bottom-width:0px;border-left-width:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m01/59/c5/wkiol1thuszzvh4oaandi5vhdb8037.jpg "width=" 904 "/>

Next we need to make the appropriate configuration for the DTC instance. Log in to any node of the cluster, select Component Services in the Server Manager menu tool, or Start Component Services using command DCOMCNFG. In the DTC cluster instance, right-click Properties, such as configure security. After the configuration is complete, the DTC service restarts, and the same configuration is seen in the same location as the other nodes.

It is also important to pay special attention to the same configuration on the application server that originated the distributed transaction.

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;border-top-width:0px; border-bottom-width:0px;border-left-width:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m01/59/c8/wkiom1thudgrdqfraan2gkdcvto325.jpg "width=" 858 "/>

Iv. testing the DTC and SQL Server clusters

Below we will use Microsoft's DTCPing tool (which can be downloaded here) to test whether the DTC between the server CLOUD-PM-DA01 and the SQL Server cluster cloud-pm-sql01 is working correctly.

• First needs to make the same configuration on the server cloud-pm-da01 as it did on the local DTC on the SQL Server node, that is, the two sides do the same.

• Shut down the firewall in advance, because the DTC uses RPC to communicate, and RPC uses the port between 1024-65535 dynamically, currently we do not know which ports are open in the firewall, so we first ensure that the test is successful and then consider the firewall problem.

• Runs the DTCPing tool on both the server CLOUD-PM-DA01 and the active node of the SQL Server cluster. The valid node here is the node where the DTC instance resides and the node where the instance of SQL Server is located. Since the DTC instance is exclusive to the instance of SQL Server as configured above, the DTC instance is located on the same node as the SQL Server instance, and the NetBIOS access to the DTC instance over the network is also cloud-pm-sql01. For example, there are two nodes Cloud-pm-cn01 and CLOUD-PM-CN02, then the instance of SQL Server is on CLOUD-PM-CN02, then the DTC instance is CLOUD-PM-CN02, Then run the DTCPing tool on the CLOUD-PM-CN02.

• Enter cloud-pm-sql01 on the DTCPing tool on the server CLOUD-PM-DA01, and then click the Ping button to perform the test. Normally, the following message will be obtained, indicating that DTC communication is normal CLOUD-PM-DA01 to cloud-pm-sql01 direction. Similarly, the DTCPing tool in CLOUD-PM-CN02 tests the DTC communication to CLOUD-PM-DA01.

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;border-top-width:0px; border-bottom-width:0px;border-left-width:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m02/59/c9/wkiom1thudgbqi9vaahbyap6taw501.jpg "width=" 606 "/>

• Finally we join the firewall settings. Run the DTCPing tool on CLOUD-PM-CN02 and run the Netstat–anob command to see which ports dtcping are listening on. You will find that the ports you listen to are different every time dtcping starts. As mentioned above, since the DTC uses RPC for communication, and RPC uses ports between 1024-65535 dynamically, it is not feasible to open such a wide range of ports, so it is necessary to limit the port range used by RPC, and note that this will affect the process of using RPC, not just the DTC.

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;border-top-width:0px; border-bottom-width:0px;border-left-width:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m00/59/c5/wkiol1thus7dpseiaakutbpxqj4586.jpg "width=" 634 "/>

Select Component Services in the Server Manager menu tool or use the command DCOMCNFG to start Component Services, configure MyComputer > Properties > Default Protocols, select connection-oriented TCP/IP properties and join the port range, which requires a restart of the computer. Add the same port range exception in the firewall (this should open the TCP port, and the port range is large enough to have more than 100 ports, plus port range assignment and default dynamic port Allocation Select Internet range). Similarly, in the application server and cluster nodes in the same configuration, it is important to note that when the cluster node is non-valid state, the configuration may not be available, you need to move the SQL Server instance to the node after the configuration. More DTC issues can be found in the troubleshooting MSDTC issues with the DTCPing tool.

650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;border-top-width:0px; border-bottom-width:0px;border-left-width:0px;padding-top:0px; "title=" image "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m02/59/c9/wkiom1thudpbl3xlaajg4lycadq374.jpg "width=" 615 "/>

• In the firewall, open both SQL Server 1433 ports and SQL Server Agent 135 ports in SQL Server Management Studio to test whether the connection cloud-pm-sql01 is normal, in failover The SQL Server failover cluster is complete when the Cluster manager tests whether the cluster is capable of failing over properly.

Build a SQL Server 2012 failover cluster in Windows Server R2