SQL Server AlwaysOn Read-write detach configuration

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

db02:192.168.1.23

db03:192.168.1.24

Monitor ip:192.168.1.25

Configure the Availability Group

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/135426/201706/135426-20170613112651696-1072170469. PNG "style=" border:0px; "/>

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
A connection that allows applicationintent=readwrite or does not have a connection condition set. The applicationintent=readonly connection is not allowed. Only read-write connections are allowed to help prevent customers from mistakenly connecting read-intent workloads to the primary replica.

Configuration statements

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Border:none; "/>

---Querying availability replica information Select * from master.sys.availability_replicas---Building a read pointer  -  Establish a replica for each replica on the current primary for the TCP connection Alter availability group [alwayson22]modify replica onn ' Db01 '  with (secondary_role  (read_only_routing_url = n ' tcp://db01.ag.com:1433 ')) ALTER  Availability group [alwayson22]modify replica onn ' db02 '  with (SECONDARY_ROLE  ( Read_only_routing_url = 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 routing replicas for each possible primary role the--list list has a precedence relationship, with higher precedence in the front, 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 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 precedence 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

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Border:none; "/>

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

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Border:none; "/>

--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 for connection 192.168. 1.22 db01192.168.1.23 db02192.168.1.24 db03

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Border:none; "/>

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

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/135426/201706/135426-20170613223549915-656934900. PNG "style=" border:0px; "/>

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/135426/201706/135426-20170613223729087-968451592. PNG "style=" border:0px; "/>

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

SQL Server AlwaysOn Read-write detach configuration