[SQL Server] 透過AG Listener讀取AlwaysOn唯讀資料庫

2016-02-05

3975
0
AlwaysOn

.NET程式存取AlwaysOn可用性群組(Availability Group)下的唯讀複本有兩個連線方式:

1.直接連接次要複本所在的資料庫伺服器。

2.透過Availability Group Listener (AG Listener)唯讀路由讀取。

如下表，LAB環境內有2台資料庫伺服器，配合Cluster 多了.53的Cluster VIP，也建立AlwaysOn AG Listener VIP .50。

IP位址	備註
192.168.120.51	DB1伺服器(主要複本所在)
192.168.120.52	DB2伺服器(同步唯讀複本所在)
192.168.120.53	DBCluster VIP
192.168.120.50	AG Listener VIP

AP程式端要存取唯讀資料庫可以直接連192.168.120.52，也可以透過AG Listener(192.168.120.50) 自動Routing到唯讀資料庫，當AG切換時，唯讀路由也會自動切換。

測試前，我們建立以下ReadOnly Routing:

主要複本目前所在	唯讀資料庫Routing順序
DB1	DB2,DB1
DB2	DB1,DB2

測試程式連線字串:

<add name="ReadOnlyConnectionString" 
         connectionString="Data Source=192.168.120.50;
         Initial Catalog=IBD;User ID=ImDeveloper;Password=ThisIsPassw0rd;
         Application Name=UnitTest;ApplicationIntent=ReadOnly" 
         providerName="System.Data.SqlClient"/>

測試程式邏輯:

每0.5秒打1筆交易，查詢語法是到資料庫執行@@servername 查詢，持續40秒。 (若程式open connection遇到sql error 983(正在切換)，等待0.5秒後自動Retry)

@@servername顯示目前所在主機名稱

for (int i = 0; i < 80; i++)
{
    Thread.Sleep(500);
    using (SqlConnection conn = new SqlConnection
(ConfigurationManager.ConnectionStrings["ReadOnlyConnectionString"].ConnectionString))
    {
        try
        {
            conn.Open();
        }
        catch (SqlException sqle)
        {
            if (sqle.Number == 983)
            {
                Thread.Sleep(500);
                Console.WriteLine("{0} {1}:{2}", DateTime.Now.ToString("hh:mm:ss.fff"), sqle.Number, sqle.Message);
                continue;
            }
            else
            {
                throw sqle;
            }
        }
        
        using (SqlCommand cmd = new SqlCommand("", conn))
        {
           //取得資料庫主機名稱
           cmd.CommandText = @" SELECT @@servername AS '伺服器名稱' ";
           SqlDataAdapter adapter = new SqlDataAdapter(cmd);
           DataTable table = new DataTable();
           adapter.Fill(table);
           //顯示回傳值
           for (int j = 0; j < table.Columns.Count; j++)
           {
               Console.Write("{0} {1}:{2}", DateTime.Now.ToString("hh:mm:ss.fff"), table.Columns[j].ColumnName, table.Rows[0][j]);
           }
           Console.WriteLine("");
        }
    }
}

測試結果:

當主要複本在DB1，ReadOnly Routing到DB2；下午13:28:48在DB2執行AG 容錯移轉(FailOver)；主要複本切到DB2，ReadOnly Routing到DB1: 聰明!。