SQL Server AlwaysOn Read-write detach configuration

Tags: mssql/read-only routing

Overview

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.

db01:192.168.1.22db02:192.168.1.23db03:192.168.1.24 monitoring ip:192.168.1.25 Configuring availability Groups

Availability Replica Concepts

Connection access types supported by the worker role

1. No connection
No user connections are allowed. 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
The secondary database is all 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 connections
The primary database allows both read and write connections and read-only connections. This is the default behavior of the primary role.

2. Allow only read/write connections
Allow applicationintent=ReadWrite or connect without setting a connection condition. 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 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'))ALTERAvailabilityGROUP [Alwayson22]MODIFY REPLICA onN'db03'  with(Secondary_role (Read_only_routing_url=N'tcp://db03.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 DB03ALTERAvailabilityGROUP [Alwayson22]MODIFY REPLICA onN'DB01'  with(Primary_role (read_only_routing_list=('DB02','db03')));ALTERAvailabilityGROUP [Alwayson22]MODIFY REPLICA onN'DB02'  with(Primary_role (read_only_routing_list=('DB01','db03')));--QueryPriority 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

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.

Log in mode

1.c# Connection string
Server= listens to ip;database=;uid=;p wd=; Applicationintent=readonly

2.ssms: Other Connection parameters
---Intent-only read-only connection
Applicationintent=readonly
---Read and write connections
Applicationintent=readwrite

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

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. As of version 2016, the two issues still exist, and hopefully future versions will improve.

SQL Server AlwaysOn Read-write detach configuration