SQL Server always on read/write detach configuration method

After using SQL Server always on technology, if the configuration is the default configuration, there will be a high primary server CPU situation occurs, such as the default configuration is as follows:

Customization is required to solve this problem.

Let's first look at the meaning of these options.

Connections in the master role

• Allow all connections
  • If the current server is a primary role, primary instance allows all connections (e.g. read/write/Manage)
• Allow read/write connections
  • If the current server is a primary role, primary instance only allow read/write connections (if you connect via SSMs, you will be an error, and sqlcmd is an error)

Readable secondary replicas

• Is
  • If the current server is a primary role, all secondary servers are visible (through SSMs you can see the structure, data, but not change)
• Read-only Intent
  • If the current server is a primary role, all secondary servers allow only read connections (you need to add a key when establishing the connection to indicate a read-only connection: applicationintent=readonly)
• Whether
  • If the current server is a primary role, all the secondary servers can not see (through SSMs can connect, but can not see, will error, such as below)

To establish a read-write separation method:

First Kind

1. 1. To set the properties of a specific availability group is: The readable copy is Yes
   2. The client uses a direct-attached copy to forward the traffic for select
   3. Exposed IP address at least 2: Listener IP and replica IP (if more replicas are available, IP hash can be used for more customization)

The second Kind

1. 1. To set the properties of a specific availability group: read-only intent for readable secondary replicas
2. 2. Execute SQL script, build read pointer
   3. Execute SQL script, establish primary, read db ur list relationship
   4. Only 1 Exposed IP addresses: listener IP

The first approach can be more customizable, but it's out of the SQL Server always on technology, so it's not discussed

The second way for the client to be more stupid point, but the custom strength is small, all rely on the future of Ms How to improve this piece, and here are some pits ...

Here are the pits:

Pit 1:ui After the graphical interface is set up, you also need to execute scripts to establish read/write separation support

Build read pointer-establishes [instance name=>instance TCP URL] Map for each SQL Server instance on the current primary

-Since there are 2 instance (including the primary characters), the relationship between the 2 instance is established on the primary alter availability GROUP [alwayson]modify REPLICA Onn ' Lab-sql1 ' with (secondary_role (Read_only_routing_url = N ' tcp://lab-sql1.lab-sql.com:1433 ')) ALTER availability GROUP [AlwaysOn] MODIFY REPLICA onn ' lab-sql2 ' with (secondary_role (Read_only_routing_url = N ' tcp://lab-sql2.lab-sql.com:1433 '))

Establish primary, read db ur list relationship-establish a corresponding read only URL list (with priority concept) for each primary on the current primary

--For each server that might become a primary role, create a read-only list, the following code is readonly server, so the priority is 1ALTER availability GROUP [alwayson]modify REPLICA onn ' lab-sql2 ' with (Primary_role (read_only_routing_list= (' LAB-SQL1 '))); ALTER availability GROUP [alwayson]modify REPLICA onn ' lab-sql1 ' with (Primary_role (read_only_routing_list= (' lab-sql2 ') ));--If an additional lab-sql3 secdonary is added, the SQL variable is alter availability GROUP [alwayson]modify REPLICA onn ' lab-sql2 ' with (PRIMARY _role (read_only_routing_list= (' lab-sql1 ', ' lab-sql3 ')); ALTER availability GROUP [alwayson]modify REPLICA onn ' lab-sql1 ' with (Primary_role (read_only_routing_list= (' lab-sql2 ') , ' lab-sql3 '));--the list in the preceding statement has a precedence relationship, and the preceding one has a higher priority

This relationship can be viewed in the following statement, along with the corresponding priority:

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

The routing_priority here is the priority level.

Pit 2: Clients need to specify the database to access and join the ReadOnly keyword

C # Connection string

• • Server= Listener ip;database=testdb3;uid=sa;pwd=111111; Applicationintent=readonly

SSMs mode

Pit 3:hosts File Settings

Because SQL Server always on depends on the Windows cluster, and the Windows cluster relies on Active Directory, and the server where the client is located is likely not joined to the domain, there is a problem with parsing here

Because of this way of reading and writing, the client is actually connected to the listener IP, and then after negotiation, the client is then connected to the specific copy (with the TCP URL, the full name is used, such as: sql1.ad.com this format, in the ad outside the default unresolved), so you need to modify the Hosts file, for each may become read the full name of an additional record, as follows:

192.168.0.1        lab-sql1.lab-sql.com192.168.0.2        lab-sql2.lab-sql.com

Summarize

1. The comparison of read and write separation in simple cases is applicable
2. Applies only to coarse-grained read and write separations, because an additional connectionstring is added instead of being built on a normal connection string.
3. If the distribution rules for read-write separation are complex, it is not applicable

SQL Server always on read/write detach configuration method