Build SQL Server AlwaysOn second (configure failover cluster) starting from 0

This second article, starting from 0, builds SQL Server AlwaysOn, which focuses on how to build a failover cluster because AlwaysOn is a Windows-based failover cluster

Before you explain the steps, you need to understand the failover cluster quorum configuration

The following image is from the Windows Server2012 System Configuration Guide

Quorum configuration for four clusters:

1, most nodes: This configuration does not use the quorum disk, and the so-called majority node is the number of normal nodes in the majority of cases, the cluster will provide services, or stop service. This configuration applies to clusters of for odd pages nodes, such as 5-node clusters, with a minimum of 3 normal nodes, and the cluster will serve

2, most nodes and disks: for the cluster of even nodes, he calculates the quorum disk in the number of legal quantities, for example, 4 nodes + 1 quorum disk node cluster, you can treat it as a cluster of 5 nodes, when the number of normal nodes must be at least 3, the cluster will provide service

3. Most nodes and file sharing: It is similar to (most nodes and disks), but the quorum disk changes to a file in a shared folder

4, no majority: only the disk, as long as the quorum disk offline, the cluster will stop providing services (not recommended, this way has been a long time ago)

Simply say the witness disk and the witness shared folder

The witness shared folder is the way the witness disk is introduced in Windows 2008 because the previous witness disk (the quorum disk) requires shared storage, which means that each node needs to mount the same disk, which is called the witness disk, which is placed on the shared storage

After the witness sharing folder is launched, we can use shared folders without the need for large shared storage.

We use a failover cluster with only two quorum configurations: (Majority node) and (majority node and file share)

If the cluster nodes are odd, use the majority node

If the cluster node is an even number, use the majority node and file share (you need to configure a shared folder, each node can access the shared folder, and the shared folder machine does not need to join the domain)

Attention:

Domain control does not require the Failover Cluster service and SQL Server to be installed, nor does it need to be joined to a failover cluster
All machine firewalls are turned off.
Two nodes need to install the same update, it is recommended not to turn on the Automatic Update feature, manually updated by the system administrator

SQL Server-AlwaysOn supports up to one primary replica and four secondary replicas, allowing up to three synchronous-commit availability replicas (including primary replicas), allowing up to two automatic failover replicas (including primary replicas)

Steps

This is also the step by step way to show you

1. Install failover cluster, two nodes install failover Cluster service simultaneously

2, two nodes after the failover cluster is installed, log off on one of the nodes, and then log on to the computer using the Dcadmin domain user

3. Open Failover Cluster Manager

4. In the Select Server or cluster interface, click the Browse button to add all the servers that will be joined to the cluster, and then click the Next button.

5. It is best to run all tests in the Validate a configuration wizard and to see all the settings for the cluster between servers, including network, shared disk, operating system, and so on.

You can view the report

There must be no failure in the report, or you will need to check what is causing the failure and fail to build the failover cluster.

Warnings to look at the situation, for the storage of the warning, because so far not add any storage device, here can be ignored, there are network warning

Because each node has only one network card, normally also need a heartbeat network card, so there will be a warning, because the experimental environment this warning can be ignored

The cluster report is stored under this path

C:\Windows\Cluster\Reports

6. Click Finish

7. Create Cluster Wizard

8. Enter the cluster name and VIP

Note: This is only the management name and management IP of the cluster, regardless of AlwaysOn

Since we do not currently have any storage, it is not checked to add all eligible storage to the cluster

The view report can see the appropriate disk for which the disk witness could not be found because we have not yet witnessed the shared folder or the quorum disk, which can be ignored

9. Cluster creation Complete

Virtual name of the cluster can be seen in domain-controlled AD users and computers

10, because we are two nodes failover cluster, so we need to add a shared folder, on the domain control to establish a shared folder, so that two cluster nodes can access

Note : If it is an odd number of nodes, this step is not required!

It doesn't matter if the shared folder is in the same machine (domain network) and not in the domain (standalone machine).

Production environment do not put the shared folder on the domain control!

Notice the conditions for the cluster to stop service

11. Create a new Quorumshare folder as a shared folder on the C drive of the domain control

Quorumshare folder permissions for everyone Full Control and dcadmin domain user read and Write permissions (on the safe side)

12. UNC path: \\WIN-FELBG10UU7F\quorumshare

Build a text file under the Quorumshare folder

13. Test the ability to access shared folders on two cluster nodes

Two nodes are logged in with the domain user dcadmin and test that the other two nodes can access the Quorumshare shared folder

14. Go back to the Failover Cluster Manager and fill in the File share path: \\WIN-FELBG10UU7F\quorumshare

Note: If the Quorumshare folder does not have Write permission , it will be rejected when filling in the file share path

15, you can see the shared folder will be generated under the VerifyShareWriteAccess.txt and Witness.log two files, as for the role of the two files everyone look at their filenames to know

Failover cluster is configured to complete here

Windows Server2012 System Configuration Guide

Configuring the Cluster network (SQL cluster, not AlwaysOn)

Public network: The 192.168.8.0 client can communicate with the cluster nodes through this network, and to allow communication between the cluster nodes through this network (as a standby network for the private network's standby network heartbeat), Pineapple said to change the AlwaysOn mirror IP is very troublesome, Requires downtime

Private network: 192.168.9.0 This network is used only as a heartbeat

iSCSI Network: 192.168.10.0 A private network that uses the iSCSI communication protocol to communicate with a target server, cannot communicate between cluster nodes, or can be used to communicate with clients

Public network
Allow cluster network traffic on this network and allow clients to connect through that network

Private network
Allow cluster network traffic on this network

iSCSI Network
Do not allow cluster network traffic on this network

This site is not shared with storage

Best practices: Do not separate the network segment, only one public192.168.8.0, two network cards to do teaming, preferably load-balanced type, without the Active-backup main standby mode, share the pressure

If you separate the network segments, such as

Primary replica Nic 1:192.168.8.20; NIC 2:192.168.9.20

Secondary replica NIC 1:192.168.8.21; NIC 2:192.168.9.21

Once the secondary replica of the NIC 2 is bad, to use a network card to replace, into a cross-subnet, and the secondary replica of the network card 1 also bear the client traffic

If you do not separate the network segment but did not do network card teaming

Primary replica Nic 1:192.168.8.20; NIC 2:192.168.8.21

Secondary replica NIC 1:192.168.8.22; NIC 2:192.168.9.23

Once the secondary replica of the NIC 2 is broken, to use a network card to replace, but the secondary replica of the network card 1 to assume client traffic

Best settings: do not separate the network segment, regardless of whether the NIC has been teaming

are set to allow cluster network traffic on this network and allow clients to connect through that network, that is, keep the default settings

If the cluster node does not communicate with the witness shared folder, such as domain shutdown, and a certain amount of time is reached, the roles and server groups in the Server Manager panel will be displayed in red

Click Services to see the failover Cluster service is suspended

Permissions issues

The permissions of the domain user and the failover cluster user in the ad User and Computer Management interface need to be added to the red box below, or the listener may be created with an error.

Create failed for availability Group Listener ' sqlcdb01temp '. (MICROSOFT.SQLSERVER.SMO)

The WSFC cluster could not bring the Network name resource with DNS name ' sqlcdb01temp ' online. The DNS name may have been taken or has a conflict with existing name services, or the WSFC Cluster service could not be RU Nning or May is inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information. The attempt to create the network name and IP address for the listener failed. The WSFC service may is running or may is inaccessible in it current state, or the values provided for the network NA Me and IP address may incorrect. Check the state of the WSFC Duster and validate the network name and IP address with the network administrator. (Microsoft SQL Server, error:19471)

Reference article: https://blogs.msdn.microsoft.com/alwaysonpro/2013/10/30/ Create-availability-group-listener-fails-with-message-19471-the-wsfc-cluster-could-not-bring-the-network-name-resource-on line/

Summarize

Through the above steps to demonstrate that the failover cluster configuration is complete, I hope you can see clearly, step by step configuration, basically there is no problem

The next article formally says SQL Server AlwaysOn builds

Build SQL Server AlwaysOn second (configure failover cluster) starting from 0