Shulou(Shulou.com)06/01 Report--

Overview

The biggest advantage of Alwayson over database mirroring is readable copies. While bringing readable copies, a new feature is to configure read-only routes to achieve read-write separation. Of course, the read-write separation here is a little exaggerated and can only be called semi-read-write separation. See the following article to see why it is called semi-read-write separation.

Db01:192.168.1.22

Db02:192.168.1.23

Db03:192.168.1.24

Monitor ip:192.168.1.25

Configure availability groups

Availability copy concept

Connection access types supported by secondary roles

1. No connection

No users are allowed to connect. The secondary database is not available for read access. This is the default behavior in auxiliary roles.

two。 Read only intentional connections

The secondary database only accepts connections from ApplicationIntent=ReadOnly and cannot be connected by other connection methods.

3. Allow any read-only connection

The secondary database is all available for read access connections. This option allows older clients 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 behavior of the primary character.

two。 Only read / write connections are allowed

Allow ApplicationIntent=ReadWrite or connections with no connection conditions set. Connections to ApplicationIntent=ReadOnly are not allowed. Allowing only read-write connections helps prevent customers from mistakenly connecting the read-intent workload to the master copy.

Configuration statement

-query availability replica information SELECT * FROM master.sys.availability_replicas--- establish read pointer-establish tcp connection for each replica on the current primary ALTER AVAILABILITY GROUP [Alwayson22] MODIFY REPLICA ONN'db01' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = non-compliant TCPread) ALTER AVAILABILITY GROUP [Alwayson22] MODIFY REPLICA ONN'db02' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_) ALTER AVAILABILITY GROUP [Alwayson22] MODIFY REPLICA ONN'db03' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = primary role SECONDARY_ROLE))-corresponding read-only route copy for every possible primary role configuration-- list list has a priority relationship The one in front has a higher priority. When db02 is normal, read-only routes can only go to db02. If db02 fails, read-only routes can only 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 priority relations 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: this is only configured for roles that may become master copies. There is no read-only route list for db03 because you do not want to switch the master copy to DB03. The more master copies you configure, the more things you have to do later, including backups, jobs, and so on.

Now that read-only routing is configured, don't forget to create login users on each copy of alwayson.

Login mode

1.C# connection string

Server= snooping IP;database=;uid=;pwd=;ApplicationIntent=ReadOnly

2.ssms: other connection parameters

-intended read connection only

ApplicationIntent=ReadOnly

-read-write connection

ApplicationIntent=ReadWrite

Configure hosts

-- configure 192.168.1.22 db01.ag.com 192.168.1.23 db02.ag.com192.168.1.24 db03.ag.com-- configuration to use hostname to connect 192.168.1.22 db01192.168.1.23 db02192.168.1.24 db03

Note: this step is only configured for clients that do not join the domain. If non-domain clients are not configured with hosts, they cannot connect using listening IP and hostname, and the database server does not need to configure this item!

Connection test

1.ReadOnly

You can see that the ApplicationIntent=ReadOnly connection property is used to correctly connect to the read-only copy DB02. ApplicationIntent=ReadWrite is the same.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.