SQL Server AlwaysOn Configuration two nodes plus shared folder quorum witness

Tags: mssql/node and shared folder most

Overview

Prior to the majority of node quorum configuration, most nodes generally 3 nodes above the odd number of nodes; The common use of 3 node nodes is a waste because AlwaysOn read-only routing can only take advantage of a read replica, However, starting from Windows2008, you can use a shared folder instead of a node to act as a quorum; this saves one server, which requires only two database servers plus any one computer's shared folder, but generally chooses to create a shared folder on the domain server. You can use an AlwaysOn shared network location.

Database: SQLServer2014 SP2

Os:windows Server 2008R2

I. Configuration of arbitration

Can be configured when you create a failover cluster, or you can create a right-click Cluster after the cluster to modify the quorum configuration

The shared folder path here uses the shared network path that the domain server has created

Note: The permissions for this path must have read and write permissions for the Everyone role, or the cluster will receive the following error message

Second, read and write separation

When two nodes are used, the read-write separation configuration and the three nodes are slightly different, assuming I now have only db01,db02 two nodes

1.Alwayson Property Configuration

Note: Connections in the main role are different here and three nodes, three nodes here you can select "Allow only read/write connections", here is the ability to select "Allow all connections", otherwise one of the nodes can not access the read-only routed access will fail (return error 982).

2. Statement configuration

---Create a read pointer-a TCP connection for each replica to be established on the current primaryALTERAvailabilityGROUP [Alwayson22]MODIFY REPLICA onN'DB01'  with(Secondary_role (Read_only_routing_url=N'tcp://db01.ag.com:1433'))ALTERAvailabilityGROUP [Alwayson22]MODIFY REPLICA onN'DB02'  with(Secondary_role (Read_only_routing_url=N'tcp://db02.ag.com:1433'))----Configure the corresponding read-only routed copy for each possible primary role--The list lists have priority relationships, with higher precedence in front, and when DB02 is normal, read-only routing is only available to DB02, and if DB02 fails read-only routing, it can be routed to DB01ALTERAvailabilityGROUP [Alwayson22]MODIFY REPLICA onN'DB01'  with(Primary_role (read_only_routing_list=('DB02',' Db01 ')));ALTERAvailabilityGROUP [Alwayson22]MODIFY REPLICA onN'DB02'  with(Primary_role (read_only_routing_list=('DB01',' DB02 ')));

--Query Priority RelationshipSELECTAr.replica_server_name, rl.routing_priority, (SELECTAr2.replica_server_name fromsys.availability_read_only_routing_lists Rl2JOINSys.availability_replicas asAr2 onrl2.read_only_replica_id=ar2.replica_idWHERErl.replica_id=rl2.replica_id andRl.routing_priority=rl2.routing_priority andrl.read_only_replica_id=rl2.read_only_replica_id) as 'Read_only_replica_server_name' fromsys.availability_read_only_routing_lists RLJOINSys.availability_replicas asAr onrl.replica_id=ar.replica_id

Will find the configuration read-only routing list There is a difference between this and the previous three nodes, I identified it with green, and the previous three nodes were DB03.

Three nodes: When the DB01 node is unreachable, the DB02 node becomes the primary node to act as a read-write copy, and the DB03 node acts as a read replica.

Two nodes: When the DB01 node is unreachable, the DB02 node becomes the primary node, which acts as a read-write copy and also acts as a reading replica.

When the DB01 service is stopped, the quorum is automatically switched to DB02, and both read-write and read-only connections are connected to the DB02. There's no demo here. You can also see the single-node DB02 online.

Summary

Compared to three nodes, using two nodes can reduce the labor cost of a single server. After all, the failure situation is not many. But also have to say the disadvantage of this scheme, that is, when a node failure of the other node to assume both read and write and read-only twice times the burden, before using the program must be evaluated whether a node can support this load pressure!!!

Building and joining a domain reference:http://www.cnblogs.com/chenmh/p/4444168.html

To build a failover cluster reference:http://www.cnblogs.com/chenmh/p/4479304.html

AlwaysOn Build Reference:http://www.cnblogs.com/chenmh/p/4484176.html

AlwaysOn read-Write separation reference:http://www.cnblogs.com/chenmh/p/7000236.html

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly give the link. Welcome to the exchange of discussions

SQL Server AlwaysOn Configuration two nodes plus shared folder quorum witness