[SQL][Azure]透過 Replication 將地端資料同步到 Azure SQL Database 上

越來越多應用要採用 Azure SQL Database 的服務,在本文中教介紹在一版標準版就可以使用的 Replication,將地端的 SQL Server 上的資料庫同步到 Azure SQL Database 服務內。

這一次在台中分享 SQL 基本語法之後,下課後就有朋友拋出一個問題,有甚麼比較簡單的方式可以把地端的資料庫同步到雲端呢 ? SQL Server CDC 雖然不錯,但那個是 Enterprise 版本才有的功能啊 ! 當下沒有想到有甚麼比較好的方案,在回家的路上想到說,之前看 SQL Server 2016 CTP 的時候,隱約有看到 Azure SQL Database ,好像有可以透過 Replication 來同步的功能,因此回來後馬上來找一下。

 

在 Channel 9 上面, Jean-Yves Devant ( 網址 )有展示如何透過 SQL Server 2016 CTP 版本來實作,而按照作者的說法,以下的版本都可以支援

  • Community Technology Preview (CTP) 3.0 of SQL Server 2016
  • SQL Server 2014 Service Pack 1 Cumulative Update 3
  • SQL Server 2014 RTM Cumulative Update 10
  • SQL Server 2012 Service Pack 2 Cumulative Update 8 ( SQL Server 2012 SP3 有包含 CU9,因此可以直接上 SP3 )

剛好手邊最近都是測試 SQL Server 2014,因此就使用 SQL Server 2014 SP1 CU3 的版本來做測試,如果需要這個版本的話,則可以前往該 網址 進行申請,之後就會收到一封含有下載網址的 URL。

因此當我更新完 SQL Server SP14 CU3 之後,就可以利用 SSMS 來連接測試機上的 SQL Server 資料庫和 Azure SQL Database 服務 ( 要注意 Azure SQL Database 要採用 v12 的版本才可以,同時不要忘記調整 Azure SQL Database 伺服器上的防火牆的設定喔 )

連接好之後,那我們就開始拿資料庫進行測試。首先用 SSMS 選擇「複寫」→「本機發行集」→「新增發行集」,透過發行集精靈來做設定。

選擇測試資料庫

目前只有支援單向的交易式複寫,因此這裡我們選擇「交易式發行集

選擇所要發行的資料表或相關物件,如果需要特別調整,也可以透過發行項屬性來做設定。

根據您的狀況調整資料篩選條件

這裡我們選擇「立即建立快照集」,但這不一定要選擇這個,您可以根據您實際狀況調整

接下來要做「安全性設定」,用來設定要用甚麼樣的身分來讀取異動資料

這裡我就直接利用 SQL Agent 的服務帳號來執行,這裡基本上您也可以設定其他有權限的帳號,只要能有足夠的權限就好。選擇好就可以回到原本的精靈畫面

這裡要給一個發行集的名稱,因此我就先命名為 Sync to Azure


通常建立發行集都比較沒有問題,接下來就要設定「訂閱」。透過 SSMS 選擇「複寫」→「本機發行集」→[Sync to Azure]→「新增訂閱」,就可以透過「新增訂閱精靈」來進行相關設定

選擇前面所建立出來的發行集

在這裡要注意一定只能選擇使用地端的 SQL Server 做 Push 的方式,不能選擇透過 Azure SQL Database 服務來做 Pull 的方式,這一點要特別注意一下。

預設只有本機,但因為我們要同步到 Azure SQL Database 上,因此選擇下方「新增訂閱者」→「加入 SQL Server訂閱者」,可別以為 Azure 服務不是 SQL Server,就選錯了。

接著會彈出 Login 視窗,這裡輸入要連接 Azure SQL Database 伺服器的連接資訊

連線完成之後,我們就可以將 Azure SQL Database 給加入到「訂閱資料庫」了

接著設定散發代理程式安全性

在「連接到訂閱者」部分要記得做調整,將 Azure SQL Database 的帳號給輸入,設定好之後就可以按下確定回到原本精靈的畫面

接下來就是設定同步的頻率了,如果你希望地端資料庫一有異動,就同步到 Azure 上隨時保持最新的狀況,那麼就選擇「連續執行」;或者是按照你希望更新的頻率去做排程,也是一種方式。

這裡我選擇立即更新初始化


看起來似乎都是那麼順利,一下就完成相關設定,但當我們看 Azure SQL Database 的話,則可能會沒有任何資料表有同步過去,似乎有點問題發生。

因此我們透過 SSMS→「複寫」→「啟動複寫監視器」,可以看到有一些的錯誤訊息。之所以有這樣的錯誤訊息,是因為前面我們使用 SQL Agent 的服務帳號來讀取和產生發行集,但預設 SQL Agent 在 Replication 的目錄下是沒有權限的。

因此我們來到的 SQL Server 預設的 Replication 的暫存目錄下,利用檔案總管來進行「安全性」設定。選擇「編輯」來加入新的帳號

因為 SQL Server 2012 開始使用服務帳號,因此這裡就可以指定為「NT Service\SQLSERVERAGENT

並且指定這個帳號對目錄可以完全控制

設定完成之後,再度回到複寫監視器,此時在「代理程式」內針對剛剛的錯誤,選擇「啟動代理程式

此時因為 SQL Agent 已經可以針對目錄做寫入,因此會發現經過一段時間( 看你的資料量有多少囉 ) 之後,就會完成快照。


既然沒有錯誤了,那我們就來看看同步的狀況,透過 SSMS 重新 Refresh 之後,可以看到 Azure SQL Database 上已經有和地端相同的資料表了。

透過指令我們可以看到,在 Azure 上的資料庫不只資料結構已經同步,就連資料也同步上去了。

因此接下來我們在測試資料庫上做一些調整,透過指令來新增一筆客戶資料上去,從下面的指令中可以看出,原本 91 筆資料在新增之後,資料表內已經有 92 筆的資料。

而在測試資料庫上測試完畢之後,接著我們又回到 Azure 上的資料庫,再重新執行一次下面的指令,會發現資料表內的資料已經新增完成了。


透過上述的說明,相信大家也可以很容易地完成相關的 Replication 處理。之所以能夠這樣的進行,主要是 Azure SQL Database 在去年底 ( 2015 ) 加上訂閱服務的功能之後,我們可以很順利的將資料透過 Replication 來配合 Azure 上的 Subscriber,來完成單向的交易式複寫。就個人目前測試起來,如果單向要將資料給同步到 Azure 上面,看起來都還算容易,但如果要達到雙向,或者是從雲端同步到地端,目前似乎也沒有甚麼比較好的解決方案了。SeedCloudTR diagram