[Azure][SQL]透過 DACPAC 來達到地端和雲端資料庫的版本控管

[SQL][Azure]透過 DACPAC 來達到地端和雲端資料庫的版本控管

最近工作有點變動,比較多都在開發系統,因此部落格也就荒廢好一陣子了,利用一點時間把這幾天使用 DAC 的一點心得整理一下。

 

話說因為最近開發的系統上會使用到 Azure SQL Database,但平常測試的時候又會先使用地端 SQL Server 的資料庫,因此就需要使用到一個機制來同步兩邊的 Schema 變更。基本上要達到這樣的功能可能有不少的方式,但因為自己比較習慣使用 SSMS,因此在這個部分我就不使用其他的軟體來進行,直接使用 SSMS 上的資料層應用程式 ( Data-tier Applications ) 來解決這個部分的問題,下面的展示案例我會使用 SQL Server 2014 的 SSMS,但基本上 SQL 2008 R2 ~ SQL 2016 都是相同的。除此之外,你也可以使用新版的工具去連接較早之前的資料庫版本,詳細的相容性支援可以參考 MSDN ( 網址 : https://technet.microsoft.com/zh-tw/library/ee210549(v=sql.105).aspx )

 

當我們在 SSMS 上操作的時候,當我們選擇資料庫,按下滑鼠右鍵選擇「工作」 的時候,可以看到出現這樣的選單,中間紅色的部分是關於 「BACPAC」,雖然他可以包含資料,除非你後續不改變資料結構,那麼紅色的部分產出 BACPAC 會是個不錯的選擇。而因為我們會持續地變更資料結構,因此我們會使用下圖的藍色範圍內的其他幾項來做處理。

image

 

當我們一開始有個資料庫之後,在第一次發布之前,我們要先做個「註冊」的動作。在我的測試資料庫 A 內,目前有兩個資料表 BigTable 和 SmallTable,其中 BigTable 內有十萬筆資料。每個資料庫只需要註冊一次就可以了

image

 

選擇「下一步

image

 

這裡的版本資訊是後續用來比對的基礎,因此第一個版本你可以設定為 1.0.0.0,後續的版本號碼一定要比前面的版本號碼來的大,確認好版本就可以選擇「下一步

image

 

查看一下設定沒有問題就可以選擇「下一步

image

 

此時就會將版本資訊紀錄下來,這裡要特別注意的是,一但你將資料庫註冊之後,該資料庫如果要刪除之前,有可能因為還有一些 Metadata 資料,因此最好是要先刪除註冊資訊,再去真正刪除資料庫。

image


 

完成註冊之後,接下來我們就可以透過「擷取資料層應用程式」將資料庫的結構給匯出成為 DACPAC 的格式

image

 

此時我們就看是否要變更封裝檔的描述和檔案位置,沒有問題就可以選擇「下一步」。

image

image

image

 

如果都沒有問題,那我們就可以完成 DACPAC 的匯出了,有些時候可能用到一些跨資料庫的物件或一些不支援的格式,可能就要先去資料庫上處理一下,才可以進行匯出的處理。


 

基本上 DACPAC 是一個只包含結構描述的壓縮檔案,因此你可以把原本的副檔名再加上 .zip,用你習慣的壓縮軟體去開啟,就可以去查看每個設定檔案的內容了。

image

 

當我們匯出第一個版本的 DACPAC 檔案之後,接著我們要將這個給匯入到 Azure SQL Database 當中,這裡我直接使用 SSMS 來做,因此先透過 SSMS 去註冊我的 Azure SQL Database 的伺服器

image

 

在 Instance 下面的「資料庫」節點上按下滑鼠右鍵選擇「部署資料層應用程式

image

 

接下來就一步一步就可以很容易完成部署了。

image

 

選擇要部署的檔案

image

image

image

 

這裡要看你的網路速度和結構的多寡,會需要等待一點時間去處理。

image

 

基本上這樣就會產生一個新的 Azure SQL Database,並且跟地端的資料庫具有相同的結構了。這裡要稍微提醒一下,因為我們剛剛在部署的時候,是沒有選擇 Azure SQL Database 的效能等級,因此如果你有需要調整的話,則可以透過 Azure Portal 上的設定來做調整。

image

 

基本上因為只有匯入結構描述,因此如果有需要同步資料的話,則可能需要透過 BCP 或者是 SSMS 上面的匯出資料/匯入資料,來協助同步資料的部分。

image


 

接著我在地端的資料庫上做一些資料結構的調整,為了方便了解改了那些,因此我用指令碼來進行,當然這樣的指令碼可以直接放到 Azure SQL Database 上執行。只是在現實狀況下,很多時候我們在維護資料庫的時候,可能會直接透過 SSMS GUI 的介面來做處理,或者是很可能有人改了資料庫內的設定,但忘記通知到大家。因此透過 SSMS 的版本控制,會是比較保險的一種方式。

-- 刪除
DROP Table SmallTable
GO

-- 修改
ALTER TABLE BigTable Add A5 DATE DEFAULT GETDATE();
GO

-- 建立
CREATE TABLE [authority](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](150) NULL,
	[password] [nvarchar](100) NULL,
	[role] [int] NULL,
	[email] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_authority] PRIMARY KEY CLUSTERED ([id] ASC)
)
GO

 

一樣在剛剛變動的資料庫上,跟第一次匯出初始版本的方式相同,也是按下滑鼠右鍵,選擇「工作」→「擷取資料庫應用程式

image

 

image

 

這裡我們要調整版本號碼,看你們自己的命名習慣,有些人習慣用三個號碼當版本號碼,最後一碼當成建立日期,反正只要比前一個版次的號碼來的大就可以了。這裡我很簡單的就設定為 1.0.0.1 的版本。

image

image

image

 

當我們完成在地端的資料庫擷取之後,就會產生一個新的 DACPAC 的檔案。此時我們透過 SSMS 連接 Azure SQL Database 的伺服器,選擇「升級資料庫應用程式

image

image

 

此時選擇剛剛所匯出的檔案

image

 

接下來他會檢查目的端的資料庫,是否有另外做過其他的變動調整,如果沒有的話,那我們就可以選擇「下一步」繼續執行。

image

 

如果擔心更新的過程中有意外,可以勾選這個「失敗時回復」的選項,當有異常發生的時候,就會回到執行之前的狀態了。

image

 

因為我們的處理過程中有刪除資料表,所以這裡會有個警告跟你說有可能資料遺失,因此下方「儘管可能遺失資料仍繼續」的部分要勾選起來才能繼續進行。如果你擔心這個變更的過程有做了甚麼處理會有特別的影響,也可以按下下方「儲存指令碼」,他會將需要執行的動作轉換成 SQL 指令,方便讓你做查看會對那些會有受到影響。

image

image

image

 

當變更完成之後,我們重新更新 SSMS ,就可以看到在 Azure SQL Database 上面,可以看到已經跟地端的資料庫結構做了同步。

image