SQL Server AlwaysOn Read-write detach configuration

The biggest advantage of AlwaysOn relative to database mirroring is the readable copy, which adds a new feature to the read-only route, which is to configure read-only routing to achieve read-write separation; Of course, the read-write separation here is slightly exaggerated, can only be called semi-read and write separation bar! Look at the next article to know why it is called semi-read and write separation.

Database: SQLServer2014

db01:192.168.1.22

db02:192.168.1.23

db03:192.168.1.24

Monitor ip:192.168.1.25

Configure the Availability Group

Availability Replica Concepts connection access types supported by the worker role

1. No connection is allowed for any user connection. The secondary database is not available for read access. This is the default behavior in the worker role.

2. Read-only intent connection the secondary database accepts only applicationintent=ReadOnly connections, and other connections cannot be connected.

3. Allow any read-only connection secondary database to be fully available for read access connections. This option allows a lower version of the client to connect.

connection access types supported by the primary role

1. Allow all connected primary databases to allow both read and write connections and read-only connections. This is the default behavior of the primary role.

2. Allow only read/write connections to allow applicationintent=ReadWrite or connections that do not have a connection condition set. Connections to applicationintent=ReadOnly are not allowed. Only read-write connections are allowed to help prevent customers from mistakenly connecting read-intent workloads to the primary replica.

Configuration Statements

---Query Availability Replica informationSELECT*FromMaster.sys.availability_replicas---Create a read pointer-a TCP connection for each replica to be established on the current primaryALTER availabilityGROUP[Alwayson22]MODIFY REPLICAOnN‘Db01‘With(Secondary_role (Read_only_routing_url= N‘tcp://db01.ag.com:1433‘))ALTER availabilityGROUP[Alwayson22]MODIFY REPLICAOnN‘Db02‘With(Secondary_role (Read_only_routing_url= N‘tcp://db02.ag.com:1433‘))ALTER availabilityGROUP[Alwayson22]MODIFY REPLICAOnN‘Db03‘With(Secondary_role (Read_only_routing_url= N‘tcp://db03.ag.com:1433‘))----Configure a copy of the corresponding read-only route 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 DB03ALTER availabilityGROUP[Alwayson22]MODIFY REPLICAOnN‘Db01‘With(Primary_role (read_only_routing_list=(‘Db02‘,‘Db03‘)));ALTER availabilityGROUP[Alwayson22]MODIFY REPLICAOnN‘Db02‘With(Primary_role (read_only_routing_list=(‘Db01‘,‘Db03‘)));--QueryPriority relationshipSELECTAr.replica_server_name, Rl.routing_priority, (SELECTAr2.replica_server_nameFromSys.availability_read_only_routing_lists Rl2JOIN Sys.availability_replicasAs Ar2On rl2.read_only_replica_id=ar2.replica_idwhere rl.replica_id =and rl.routing_priority = rl2.routing_priority and rl.read_only_replica_id =< Span style= "color: #000000;" > rl2.read_only_replica_id) as  "read_only_replica_server_name ' from sys.availability_read_only_routing_lists RL join Sys.availability_replicas as ar on rl.replica_id = ar.replica_id    

Note: This is only configured for the role that may become the primary replica, where DB03 is not configured with a read-only routing list because it does not want to switch the primary replica to DB03, and the more primary replicas you have, the more things you need to do, including backups, jobs, and so on.

Until this read-only route is configured, do not forget to create a login user on each AlwaysOn replica.

Login Mode C # connection string server= listen ip;database=;uid=;p wd=; Applicationintent=readonlyssms: Other connection Parameters---read-only connection applicationintent=readonly---read-write connection applicationintent= ReadWrite Configuring the Hosts

--Configure the connection using the listening IP 192.168.1.22    db01.ag.com 192.168.1.23    db02.ag.com192.168.1.24    db03.ag.com--configuration using hostname to connect 192.168.1.22    db01192.168.1.23    db02192.168.1.24    db03

Note: This step is only configured on non-domain-joined clients, and the database server side does not need to configure this if the hosts that are not domain-aware are not configured to connect using the Listening IP and hostname!!!

Connection test

1.ReadOnly

You can see that using the Applicationintent=readonly connection property is correctly connected to the read replica DB02. Applicationintent=readwrite.

20170714 Supplement

SQLSERVER2016 supports multiple read replica load sharing read-only operations, and the read-only routing list is modified as follows:

ALTER availability GROUP [alwayson21]modify REPLICA Onn‘Hd21db01‘With (Primary_role (read_only_routing_list= (‘Hd21db02‘,‘Hd21db03‘,‘hd21db04 hd21db01 '  " hd21db02  with ( Primary_role (read_only_routing_list= ( "hd21db01< Span style= "color: #800000;" > ',  ' hd21db03 ' 

When hd21db01 as the primary node, hd21db02,hd21db03,hd21db04 evenly shares the read pressure when HD21DB02,HD21DB03,HD21DB04 is unable to access the read connection to the HD21DB01;

Overview

From the above we can see that read-write separation is done through the Connection property applicationintent=readonly\readwrite whether the connection is connected to the primary or secondary replica, which means that multiple connection strings need to be configured on the application side manual configuration code is write-down or read-only. That's why at first I said it was a half-written separation. Another drawback is that although two read replicas are configured, each time a read-only replica with a high priority is provided with a read-only connection, only the high-priority read replica fails to be routed to the next read replica. This means that only 2 copies are currently available for read and write operations, and that the load balancing of read operations is not available between multiple read replicas.

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 configuration two nodes plus shared folders Quorum witness:http://www.cnblogs.com/chenmh/p/7156719.html

SQL Server AlwaysOn Read-write detach configuration