sql 远程连接 路由 1433 慢_sql server 2012 集群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();

}

}

}

}

}

检测结果：

开始之前，打开Sql Server Profiler进行数据的分析。

如下图所示，为了排除干扰，发现程序执行前是没有数据的读写的。

测试点一： 写操作不加ReadOnly限制，是不是能够在主副本写成功

结果如下图，

结论：因为只有主副本有操作记录，可以说明是在主副本进行写入成功的

测试点二:读操作加上ReadOnly限制，它的处理副本是主副本还是辅助副本(清除上次记录)

结果如下图：

结论：左侧主副本的内容和我们的查询内容无关，右侧辅助副本全部都是我们的查询操作，所以可以证明存在ReadOnly的时候读操作都被辅助副本执行

测试点三:读操作不加ReadOnly限制，它的处理副本是主副本还是辅助副本

Read()操作中连接字符串去掉ApplicationIntent=ReadOnly，并清空上面操作信息，然后执行程序

结论：在不加ReadOnly选项的时候，读操作全部有主副本进行了处理

测试点四:写操作加上ReadOnly限制，能够被主副本进行处理

在Write()中连接字符串中加上ApplicationIntent=ReadOnly，清空上面操作记录，执行程序

结论：上图可以知道，在写操作的时候，如果有ReadOnly会报错