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會報錯