Build SQL Server AlwaysOn fourth from 0 (Configure geo-machine room nodes)

This article is the fourth to build SQL Server AlwaysOn from 0, which begins to build a node in an offsite room

Note 1.

Note It is best to have at least 2 nodes in the GEO node, or a quorum warning when a manual failover occurs on the local node, prompting the WSFC cluster to have an offline risk

In the case where there is only one offsite node, although the WINDOWS2012R2 has a dynamic quorum mechanism, the entire WSFC cluster may not get any votes when the local node is not gracefully down

That is, off-site nodes will not get the number of votes resulting in the entire WSFC cluster offline!!

Note 2.

When a manual failover occurs, it takes 10 minutes to update the DNS cache, so when a manual failover occurs, the connection to SQL Server with the listener IP is slow because the DNS cache is still being updated

Steps

This article still uses step by step to describe how to build an AlwaysOn computer room node

New addition of computer room node machine name:

1. Install the failover cluster on an offsite node

2. Open the Failover Cluster Manager on the local computer room node machine and add a node

3. Verify the configuration

4, to solve the new node OU different problems, only need to modify the organizational unit OU, do not have to modify the site, because if the local computer room and the domain of the remote room set site, in the verification of the configuration will be warned, of course, can be ignored or can be corrected

Because it's just a warning yier, ignoring it doesn't matter.

5. Add node success

6. Install SQL Server on the new node and optimize SQL Server, ignoring installation and optimization steps

7. Add the new node in the remote machine room to the AlwaysOn availability Group, open the AlwaysOn availability properties interface, and the availability group name is: AGWMSJXC

You can see the addition of the Remote machine Room node, the remote machine Room node has not been joined to the availability group, that is, the current availability group has not recognized this remote computer room node

8. Add the VIP of another subnet to the listener, this step can be ignored if the availability group does not have an availability Group listener enabled

If the availability group has listeners enabled, there are two scenarios

1, the network segment of the remote node is the same as the local computer room, for example, are 192.168.1.x, then this step does not need to do

2, the remote node network segment with the local computer room is not the same, that is, cross-subnet, such as the local computer room is 192.168.1.x, the remote room is 192.168.10.x, then this step needs to do

Now the listener IP has two, one is the IP of the local computer room network segment, one is the IP of the remote computer room network segment

After a new listener VIP is added, the Failover Cluster Manager will automatically take this listener VIP resource offline

9. Create a new test availability group, which is used to open/unlock 5022 ports on local nodes and offsite nodes

After the build, in the original AG refresh will see the offsite node Jxca-wms08 has been automatically joined to the availability group AGWMSJXC

10, backup the database to restore to the geo-node jxca-wms08, jxca-wms08 on the offsite node, connect the database to the availability group by clicking

11, the Site node added complete

12. Manually failover the primary replica to an offsite node

Note that when there is only one geo-node, you are verifying that the WSFC quorum voting configuration column appears with a warning!

After failover, the listener IP of the offsite node is found to be online, and the listener IP of the local node is offline

13. Use the Offsite node listener IP to connect to SQL Server on the offsite node and write the test data

14. Manually fail over the primary replica back to local node

15, with the local node listener IP connection to SQL Server, found that the remote node listener IP data write has been synchronized to the local computer room node

16. Add the remote machine room IP of the WSFC cluster IP Address resource, select the server name under the cluster core resource in the WSFC Manager, right-click Properties

Add a vip:192.168.7.130 of WSFC in an offsite room

The VIP is displayed offline after the add succeeds because the current WSFC master node is not on the node in the offsite room, but on the node in the local machine room

17. Transfer the WSFC master node to a node in an offsite room and shut down all local nodes

Shut down all the nodes in the local machine room.

The WSFC cluster is now connected with the WSFC VIP of the Remote machine room node

You can see that the primary node of the WSFC has been transferred to the WIN-BDKSOOLDV18 node, and the cluster resource is still online

18. No domain-joined application server (IIS server) if you need to connect an AlwaysOn cluster with a listener name, the workaround is to modify

The Hosts file of the application server, which writes the listener name and the listener IP so that the client can connect to the AlwaysOn cluster with the listener name, the client's Webconfig file

Write the listener name, so that even if the failover to the remote node does not need to modify the application server's Webconfig file, of course, the application server joined the domain is not necessary,

A domain-joined Application server automatically queries DNS Manager on DC to find AlwaysOn listener names

Hosts file

Testaglisten  192.168.10.91testaglisten  192.168.11.91

tip : In fact, the 16th step is not required, you can not add the VIP of WSFC's off-site subnet, but when all the nodes in the local machine room are shut down

You can't connect a WSFC cluster with a local vip:192.168.6.60, and you can't manage the WSFC cluster

Attached structure diagram

Two network segments, so there will be two listener IPs, but only one listener IP is online at the same time, viewed in the WSFC cluster Manager

Primary site: 192.168.6x

Dr Site: 192.168.7.x

After failing over to the DR site

Reference article:

http://blog.itpub.net/29371470/viewspace-2087672

http://www.tech-coffee.net/understand-failover-cluster-quorum/

If there is a wrong place, welcome everyone to shoot brick O (∩_∩) o

The copyright of this article is owned by the author and cannot be reproduced without the author's consent.

Build SQL Server AlwaysOn fourth from 0 (Configure geo-machine room nodes)