SQL Server-on technology

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 role), the relationship between the 2 instance is established on the primary.ALTER availabilityGROUP[AlwaysOn]MODIFY REPLICAOnN‘Lab-sql1‘With(Secondary_role (Read_only_routing_url= n tcp:// Lab-sql1.lab-sql.com:1433 ' )) alter availability group [alwayson]modify REPLICA nlab-sql2 ' with ( Secondary_role (read_only_routing_url " 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 be the primary role, create a read-only list, and the following code, because of mutual ReadOnly server, has a priority of 1ALTER availabilityGROUP[AlwaysOn]MODIFY REPLICAOnN‘Lab-sql2‘With(Primary_role (read_only_routing_list=(‘Lab-sql1‘)));ALTER availabilityGROUP[AlwaysOn]MODIFY REPLICAOnN‘Lab-sql1‘With(Primary_role (read_only_routing_list=(‘Lab-sql2‘)));--If an additional lab-sql3 secdonary is added, SQL can becomeALTER availabilityGROUP[AlwaysOn]MODIFY REPLICAOnN‘Lab-sql2‘With(Primary_role (read_only_routing_list=(‘Lab-sql1‘,‘Lab-sql3‘)));ALTER availabilitygroup [alwayson]modify REPLICA onn< Span style= "color: #ff0000;" > ' --     

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

SelectAr.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_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 Span style= "color: #0000ff;" >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-on technology