AlwaysOn实现只读路由

1.配置只读路由

①配置A副本的只读路由属性（ReadOnly代表‘只读意向’）                                                    
ALTER AVAILABILITY GROUP [testAG]                                                     
MODIFY REPLICA ON N'WIN-14VNU7CGQO1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));                                                     
②配置A副本的只读路由URL                                                    
ALTER AVAILABILITY GROUP [testAG]                                                    
MODIFY REPLICA ON N'WIN-14VNU7CGQO1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'tcp://WIN-14VNU7CGQO1.fnst.com:1433'));                                                    
③配置B副本的只读路由属性                                                    
ALTER AVAILABILITY GROUP [testAG]                                                    
MODIFY REPLICA ON N'WIN-14VNU7CGQO2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));                                                    
④配置B副本的只读路由URL                                                    
ALTER AVAILABILITY GROUP [testAG]                                                     
MODIFY REPLICA ON N'WIN-14VNU7CGQO2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'tcp://WIN-14VNU7CGQO2.fnst.com:1433'));                                                     
⑤配置A副本作为主副本时候的只读路由表                                                    
ALTER AVAILABILITY GROUP [testAG]                                                     
MODIFY REPLICA ON N'WIN-14VNU7CGQO1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('WIN-14VNU7CGQO2','WIN-14VNU7CGQO1')));                                                     
⑥配置B副本作为主副本时候的只读路由表                                                    
ALTER AVAILABILITY GROUP [testAG]                                                     
MODIFY REPLICA ON N'WIN-14VNU7CGQO2' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('WIN-14VNU7CGQO1','WIN-14VNU7CGQO2')));

配置完成后：使用 SELECT * FROM sys.availability_read_only_routing_lists 查看路由表

确认一下应该是下面的形式 ：        
A    B    
A    A    
B    A    
B    B

为什么这么配置请分析如下过程
1.正常运行时候，A作为主副本，B作为辅助副本，客户端连接字符串指定数据源是侦听器地址
2.此时发送只读请求
3.侦听器收到只读数据请求，有主副本A来处理,主副本A发现是ReadOnly，就查询路由表，发现第一条符合，就把只读请求交给辅助副本B来处理
4.此时主副本A失效了，那么由AlwaysOn的高可用可知，会让辅助B作为了主副本，等原来的主副本A恢复之后，让A成为新的辅助副本（当然这些对客户端是透明的）
5.副本A恢复之后，再发送一条只读请求
6.此时侦听器使用主副本B来处理这个请求，如果不像上面的设置方法，就找不到B到A的路由，也就不能实现所谓的高可用

2.配置的确认
例如：有一个可用组testAG，其中有两个副本A和B，其中主副本为A，辅助副本为B，并且在AlwaysOn可用组内设定了Listener

A和B的配置如下：

3.测试只读路由
客户端程序中指定连接字符串：connectStr = "Data Source=tcp:193.160.26.30,1433;Initial Catalog=test;Integrated Security=True;ApplicationIntent=ReadOnly;MultiSubnetFailover=True";
重要参数说明：
DataSource：tcp:193.160.26.30,1433 这个填写的是侦听器的地址
ApplicationIntent=ReadOnly 说明这个连接是一个只读意向的连接，这样的情况下，请求发送到主副本A上，主副本发现是只读的请求会先产看只读路由表，然后通过
主副本A转发到辅助副本B上

会出现的问题：如果客户端这时候的连接字符串指定了ApplicationIntent=ReadOnly，它只表明这是一个只读意向的请求，但是不能保证请求一点是只读的，如果是写请求
就会出现失败的情况。所以在进行写操作的时候不能设置这个选项。

测试程序如下：

主副本： 192.168.24.28        
辅助副本：192.168.24.32        
侦听器：192.168.24.30:1433

代码如下：

namespace AlwaysonTest                                                            
{                                                            
    public partial class Form1 : Form                                                            
    {                                                            
                                                            
        public Form1()                                                            
        {                                                            
            InitializeComponent();                                                            
            Control.CheckForIllegalCrossThreadCalls = false;                                                            
        }                                                            
                                                            
        private void btnInsert_Click(object sender, EventArgs e)                                                            
        {                                                            
                                                            
            Thread t = new Thread(Write);                                                            
            t.IsBackground = true;                                                            
            t.Start();                                                            
                                                            
        }                                                            
                                                            
        private void btnReadData_Click(object sender, EventArgs e)                                                            
        {                                                            
                                                            
            Thread t = new Thread(Read);                                                            
            t.IsBackground = true;                                                            
            t.Start();                                                            
        }                                                            
                                                            
        public void Write()                                                            
        {                                                            
            string name = System.DateTime.Now.ToString();                                                            
                 //插入数据的时候，连接字符串不指定ReadOnly                                                        
            string connectStrW = "Data Source=tcp:193.160.26.30,1433;Initial Catalog=test;Integrated Security=True;MultiSubnetFailover=True";                                                            
                                                                        
            int count = Convert.ToInt32(textBox4.Text);                                                            
                                                            
                                                            
            using (SqlConnection conn = new SqlConnection(connectStrW))                                                            
            {                                                            
                for (var i = 1; i <= count; i++)                                                            
                {                                                            
                    conn.Open();//打开数据库                                                              
                    //创建数据库命令                                                              
                    SqlCommand cmd = conn.CreateCommand();                                                            
                    //创建查询语句  在写的操作过程中，写之前先读操作，测试不加ReadOnly时候只读路由有没有效果                                                            
                    cmd.CommandText = "select count(*) from test1";                                                            
                    int x = (int)cmd.ExecuteScalar();                                                            
                    //创建查询语句                                                              
                                                            
                    Thread.Sleep(1000);                                                            
                    cmd.CommandText = "Insert into test1 values" + '(' + '\'' + name + '\'' + ')';                                                            
                    cmd.ExecuteNonQuery();                                                            
                    textBox1.Text = (x + i).ToString();                                                            
                    conn.Close();                                                            
                }                                                            
            }                                                            
                                                            
        }                                                            
                                                            
        public void Read()                                                            
        {    //查询数据的时候，连接字符串指定ReadOnly                                                        
            string connectStr = "Data Source=tcp:193.160.26.30,1433;Initial Catalog=test;Integrated Security=True;ApplicationIntent=ReadOnly;MultiSubnetFailover=True";                                                            
                                                                        
            int count = Convert.ToInt32(textBox4.Text);                                                            
                                                                        
            for (var j = 0; j <= count; j++)                                                            
            {                                                            
                Thread.Sleep(1000);                                                            
                using (SqlConnection conn = new SqlConnection(connectStr))                                                            
                {                                                            
                    conn.Open();//打开数据库                                                              
                    //创建数据库命令                                                              
                    SqlCommand cmd = conn.CreateCommand();                                                            
                    //创建查询语句                                                              
                    cmd.CommandText = "select count(*) from test1";                                                            
                    int x = (int)cmd.ExecuteScalar();                                                            
                    textBox2.Text = x.ToString();                                                            
                    conn.Close();                                                            
                }                                                            
            }                                                            
                                                            
        }                                                            
                                                            
    }                                                            
}