SQL Server 資料庫版本控管 (Part 3) : Schema Compare with .dacpac (資料層應用程式)

1. 情境

廠商要將資料庫結構異動, 發行至客戶測試機. 但廠商及客戶開發人員, 均不能連接至資料庫進行結構異動, 必須由客戶 DBA 進行處理.

回想以前在寫資料庫版本管控的文章時, 印象中 Will 保哥有提到 .dacpac 可以處理該狀況, 故作了一下複習及實作.

相關的程式碼, 可 由此下載.

2. 初始環境建立:

(1) 利用 SSMS 建立測試資料庫, 如下的 SQL Script.

CREATE DATABASE CookiesDb;
GO
                

USE CookiesDb;
GO
-- ===========================
-- 移除 Table
-- ===========================
DROP TABLE IF EXISTS [dbo].[Products];
GO
DROP TABLE IF EXISTS [dbo].[Categories]
GO
-- ===========================
-- 建立 Table
-- ===========================
CREATE TABLE [dbo].[Categories]
( [CategoryId]       INT           NOT NULL
, [CategoryName]     NVARCHAR(30)  NOT NULL
  CONSTRAINT PK_Catgories PRIMARY KEY ( [CategoryId] )
);
GO
CREATE TABLE [dbo].[Products]
( [ProductId]        INT           NOT NULL
, [ProductName]      NVARCHAR(30)  NOT NULL
, [CategoryId]       INT           NOT NULL
, [Price]            INT           NOT NULL
  CONSTRAINT PK_Products PRIMARY KEY ( [ProductId] )
, CONSTRAINT FK_Categories FOREIGN KEY ( [CategoryId] )
                           REFERENCES [dbo].[Categories] ( [CategoryId] )
);
GO
                

USE CookiesDb;
GO
-- ===========================
-- 新增測試資料
-- ===========================
INSERT INTO [dbo].[Categories]
VALUES ( 1, N'餅乾類' )
,      ( 2, N'蛋糕類' )
,      ( 3, N'奶酪類' )
;
GO
INSERT INTO [dbo].[Products]
VALUES ( 1, N'夏威夷豆塔', 1, 50 )
,      ( 2, N'堅果塔', 1, 55 )
,      ( 3, N'養生堅果', 1, 60 )
,      ( 4, N'乳酪塔', 1, 60 )
,      ( 5, N'檸檬塔', 1, 60 )
,      ( 6, N'草莓塔', 1, 65 )
,      ( 7, N'輕乳酪蛋糕(片)', 2, 65 )
,      ( 8, N'重乳酪蛋糕(片)', 2, 70 )
,      ( 9, N'抹茶奶酪', 3, 90 )
,      ( 10, N'草莓奶酪', 3, 85 )
,      ( 11, N'芒果奶酪', 3, 85 )
,      ( 12, N'紅豆奶酪', 3, 80 )
;
GO
                

(2) 參考文件 2, 3 的說明, 利用 Visual Studio 2019 + SSDT 建立資料庫專案, 並將前述建立的資料庫結構匯入. 結果參考 [附錄1] 的截圖.

3. 異動資料庫專案

(1) 修改 Products 的 Price 欄位資料型態, 由 INT 改為 DECIMAL(6,2). 參考 [附錄2] 的截圖.

(2) 加入一個 View, 將 Products 與 Categories 作 JOIN. 參考 [附錄3] 的截圖.

4. 建立及比對 .dacpac

(1) 廠商將目前的資料庫專案, 製作快照, 產生 .dacpac 檔案), 複製給客戶開發人員. 參考 [附錄4] 的 截圖.

(2) 請客戶 DBA 由測試環境, 利用 SSMS (工作/擷取資料層應用程式(K)), 產生.dacpac 檔案, 複製給客戶開發人員. 參考 [附錄5] 截圖.
註: 為了方便作比對, 我把匯出的 .dacpac 檔案, 放在與 [附錄4] 相同的資料夾. 模擬客戶開發人員的環境.

(3) 由客戶開發人員 利用 Visual Studio 2017 + SSDT 進行 2 個 .dacpac 檔的比對. 產生差異的 Script. 參考 [附錄6] 的截圖.

• 來源: 廠商提供的 .dacpac
• 目標: 客戶 DBA 提供的 .dacpac

(4) 將差異的指令碼, 交給客戶的 DBA 審核及執行.

5. 結論

在 DBA FIRST 的情境下, .dacpac 用來作資料庫結構比對, 真的蠻好用的.

供有興趣的朋友參考.

[附錄1]

將已建立的資料庫結構, 匯入至資料庫專案.

[附錄2]

修改資料庫專案: 將 Products.Price 欄位資料型態, 由 INT 改為 DECIMAL(6,2)

[附錄3]

修改資料庫專案: 加入一個 View, 將 Products 與 Categories 作 JOIN

[附錄4]

廠商將目前的資料庫專案, 製作快照, 產生 .dacpac 檔案)

[附錄5]

請客戶 DBA 由測試環境, 利用 SSMS (工作/擷取資料層應用程式(K)), 產生.dacpac 檔案

為了方便作比對, 我把匯出的 .dacpac 檔案, 放在與 [附錄4] 相同的資料夾. 模擬客戶開發人員的環境.

[附錄6]

客戶開發人員將 .dacpac 作比較, 產生差異的 SQL Script

按下黃框的 [產生指令碼].

將產生的 SQL Script 交給客戶的 DBA; 由 DBA 進行資料庫結構異動.

當然, 有些 SQL Script 要作一些調整, 例如: Line 47 .. Line 50 要作 remark, 不然執行到 Line 48 就會停住了.

6. 參考文件

• 01. Will 保哥, SQL Server 資料庫版本控管
• 02. 傑士伯的IT學習之路, SQL Server 資料庫版本控管 (Part 1) : Schema Compare
• 03. 傑士伯的IT學習之路, SQL Server 資料庫版本控管 (Part 2) : Schema Compare
• 04. Yowko's Notes, 你認識 SQL Server 的資料層應用程式(Data-tier Applications - DAC)嗎?
• 05. RedGate, How to compare two DacPacs using SQL Compare