SQL Server AlwaysOn read/write splitting configuration text tutorial, alwayson text

SQL Server AlwaysOn read/write splitting configuration text tutorial, alwayson text

Overview

Compared with database images, Alwayson has the biggest advantage in terms of readable copies. It also adds a new feature, that is, configuring read-only routes to implement read/write splitting; of course, the read/write splitting here is a little exaggerated. It can only be called semi-read/write splitting! Read the following article to see why semi-read/write splitting is called.

Database: SQLServer2014

Db01: 192.168.1.22

Db02: 192.168.1.23

Db03: 192.168.1.24

Listener ip Address: 192.168.1.25

Configure availability Group

Availability replica concept secondary roles support connection access types

1. No connection

No user connection is allowed. Secondary databases cannot be used for read access. This is the default behavior in the secondary role.

2. Read-Only intended connections

The secondary database only accepts connections with ApplicationIntent = ReadOnly. Other connection methods cannot be used.

3. allow any read-only connection

All secondary databases can be used for read access connections. This option allows clients of earlier versions to connect.

Connection access types supported by the primary role

1. Allow all connections

The primary database allows both read-write and read-only connections. This is the default action of the primary role.

2. Only read/write connections are allowed.

Allow connections with ApplicationIntent = ReadWrite or with no connection conditions set. Connection of ApplicationIntent = ReadOnly is not allowed. Only allow read/write connections can help prevent the customer from mistakenly connecting the read intention workload to the primary copy.

Configuration statement

--- Query availability copy information SELECT * FROM master. sys. availability_replicas --- create read pointer-on the current primary, create a copy for each REPLICA for the tcp connection alter availability group [Alwayson22] modify replica onn 'db01' WITH (SECONDARY_ROLE (role = n' TCP: // db01.ag.com: 1433 ') alter availability group [Alwayson22] modify replica onn 'db02' WITH (SECONDARY_ROLE (role = n' TCP: // db02.ag.com: 1433 ')) alter availability group [Alwayson22] modify replica onn 'db03' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = n'tcp: // db03.ag.com: 1433 ')) ---- configure the corresponding read-only route copy for each possible primary role -- the list has a priority relationship and has a higher priority. When db02 is normal, the read-only route can only be to db02, if db02 fails, the read-only route can be routed to DB03ALTER availability group [Alwayson22] modify replica onn 'db01' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('db02', 'db03 '))); alter availability group [Alwayson22] modify replica onn 'db02' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('db01', 'db03'); -- query the priority relationship SELECT ar. replica_server_name, rl. routing_priority, (SELECT ar2.replica _ server_name FROM sys. availability_read_only_routing_lists rl2 JOIN sys. availability_replicas AS ar2 ON rl2.read _ only_replica_id = ar2.replica _ id WHERE rl. replica_id = rl2.replica _ id AND rl. routing_priority = rl2.routing _ priority AND rl. read_only_replica_id = 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: The configuration is only for roles that may become primary replicas. Here, the read-only routing list is not configured for db03 because you do not want to switch the primary copy to DB03, the more Master replicas you configure, the more things you need to do later, including backups and jobs.

The read-only route has been configured. Do not forget to create a login user on each alwayson copy.

Logon Method

C # connection string server = listener IP address; database =; uid =; pwd =; ApplicationIntent = ReadOnly

Ssms: other connection Parameters

--- Only intended read connections
ApplicationIntent = ReadOnly
--- Read/write connection
ApplicationIntent = ReadWrite configure hosts

Configure to use the listening ip address to connect to 192.168.1.22 db01.ag.com 192.168.1.23 db02.ag. com192.168.1.24 db03.ag.com -- configure to use the hostname to connect to 192.168.1.22 db0119255.1.23 db02192.168.1.24 db03

Note: This step is only configured on clients that do not have a domain. If hosts is not configured on a non-domain client, the listener IP address and hostname cannot be used to connect to the database server. You do not need to configure this option on the database server !!!

Connection test

1. ReadOnly

The ApplicationIntent = ReadOnly connection property is correctly connected to the read-only copy DB02. The same applies to ApplicationIntent = ReadWrite.

20170714 supplement

SQLServer2016 supports multiple read-only copies to share the load. The read-only route list is modified as follows:

ALTER AVAILABILITY GROUP [Alwayson21]MODIFY REPLICA ONN'HD21DB01' WITH(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('HD21DB02','HD21DB03','HD21DB04'),'HD21DB01')));ALTER AVAILABILITY GROUP [Alwayson21]MODIFY REPLICA ONN'HD21DB02' WITH(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('HD21DB01','HD21DB03','HD21DB04'),'HD21DB02')));

When HD21DB01 is the master node, HD21DB02, HD21DB03, and HD21DB04 share the read pressure evenly. When HD21DB02, HD21DB03, and HD21DB04 cannot be accessed, the read connection accesses HD21DB01. The following is an example:

Overview

From the above we can see that the read/write splitting of the read-only route enables the connection to the primary copy or secondary copy through the connection attribute ApplicationIntent = ReadOnly \ ReadWrite, this means that you need to configure multiple connection strings on the application to manually configure the code to write or read-only. That's why I initially said this was the cause of semi-read-write splitting. Another drawback is that although two read-only copies are configured, only the read-only secondary node with the highest priority can provide read-only connections at a time. Only when the read-only replica with the highest priority fails can it be routed to the next read-only copy. This means that currently only two replicas are providing read/write operations, and load balancing for read operations cannot be provided between multiple read-only replicas at the same time.

Summary

The preceding section describes the SQL Server AlwaysOn read/write splitting configuration. I hope it will be helpful to you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!