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