︿
Top

2016年1月8日 星期五

SQL Server 資料庫版本控管 (Part 1) : Schema Compare


因為專案需求, 前一陣子在進行 SQL Server 的 Tables 設計, 及 Functions, Stored Procedures 的撰寫; 發現版本控管是一件很重要的事情, 萬一沒有作好, 就可能發生以下狀況, 例如:
(1) AP 與 DB 的版本無法相符, 導致程式掛掉.
(2) 個人開發境 DB, 整合測試環境 DB, 正式環境 DB 的資料庫物件, 或多或少存在一些差異, 要如何作同步?

本系列文章, 主要係參考 Will 保哥在 Slide Share 及 Channel 9 分享的 Slides 及 影片; 再加上筆者以往的開發經驗彙整而成.

另外, 相關的方案/專案, 均是採用 Visual Studio Online 的 TFS 作為版控服務, 而不是採用 Git.



名詞定義
茲定義一些名詞或縮寫, 供後續篇章使用.


  • SSDT : SQL Server Data Tools
說明: 此為微軟所提供的 Visual Studio 套件, 以便在 Visual Studio 進行 SQL Server 資料庫的 設計, 開發, 建置, 測試, 發行, 就像在 Visual Studio 中開發應用程式一樣容易。可至此下載
  • SSMS: SQL Server Management Studio
說明: 此為 SQL Server 的前端管理, 開發整合環境.
  • Visual Studio Online
說明: 此為微軟提供的程式開發協同合作服務, 在 Visual Studio 2013 的年代, 稱為 Visual Studio Online; 在 Visual Studio 2015 的年代, 改稱為 Visual Studio Team Services. 其實, 其本質均相同; 本文一律採用 Visual Studio Online 這個名稱.

資料庫環境說明

如下圖:
  • 共有 3 位開發人員, 每位在其各自的筆電都安裝有 SQL Express
  • 開發及單元測試完成後, 要將修改過的資料庫物件 (ex: Tables, Views …) 同步到 MyDB_Test 進行整合測試
  • 整合測試完成,  要將修改過的資料庫物件 (ex: Tables, Views …) 同步到 MyDB_Prod 上線
  • 因為設備不足, 本文係以同一個 DB Server 的 3個 不同資料庫來呈現 ( MyDB, MyDB_Test, MyDB_Prod )
  • 假設目前 MyDB 及 MyDB_Test 均內含 2 個 Table, 4 個 Function, 1 個 Stored Procedure; 相關細節, 請參考 <附錄一>


應用程式說明

範例應用程式, 係為一個 Console 應用程式, 外加一個 SQL 子資料夾, 內含以下 .sql 檔; 同時加入 Visual Studio Online 進行版控.
  • Tables.sql
  • Functions.sql
  • Stored Procedures.sql

加入至 Visual Studio Online 版控

連結至您的 Visual Studio Online 主頁, 建立 TEAM 專案

https://<youridentifier>.visualstudio.com/#

將 Visual Studio 2013 的方案, 加入到剛才建立的 TEAM 專案
C:\Users\JASPER~1\AppData\Local\Temp\SNAGHTML86284a.PNG




檢視版更記錄






開始進行異動

CASE 1: 加入 Stored Procedure

加入 usp_get_all_emps 這個 stored procedure, 以取得所有的員工資料及所屬部門名稱
C:\Users\JASPER~1\AppData\Local\Temp\SNAGHTML129d129.PNG

CASE 2: 修改 Table Layout

將 DeptName 及 EmpName 由 nvarchar(10) 改為 nvarchar(20)
C:\Users\JASPER~1\AppData\Local\Temp\SNAGHTML126399b.PNG

開發環境與整合測試環境同步
在前一個部份, 我們在開發環境調整了一些資料庫的物件, 但要如何異動至整合測試環境呢?
最初想到的, 當然是自己手工寫相關的異動 Script (ex: ALTER TABLE …), 但這樣也太辛苦了, 萬一異動的內容不少, 很容易漏掉, 也很容易寫錯.
這裡就會用到 SSDT 的功能了.
我們先把目前修改後的版本, 以 SSMS 執行, 並簽入至 Visual Studio Online.
利用 SSDT 提供的功能, 同時連結開發環境與整合測試環境, 再進行比對
下一個頁面, 要特別注意 …
  • 來源: 係指較新的版本; 亦即要被採用的版本
  • 目標: 係指較舊的版本; 亦即要被蓋掉的版本
目前尚未建立至 MyDB, MyDB_Test 的連接, 所以要先建立一下.

來源資料庫設定
C:\Users\JASPER~1\AppData\Local\Temp\SNAGHTML13a3078.PNG
C:\Users\JASPER~1\AppData\Local\Temp\SNAGHTML13cdd88.PNG
C:\Users\JASPER~1\AppData\Local\Temp\SNAGHTML13d56bd.PNG

目標資料庫設定

C:\Users\JASPER~1\AppData\Local\Temp\SNAGHTML13eafa5.PNG
C:\Users\JASPER~1\AppData\Local\Temp\SNAGHTML13f6ebf.PNG
C:\Users\JASPER~1\AppData\Local\Temp\SNAGHTML13fe287.PNG

進行比較
下圖顯示了有 3 個地方有差異, 可以選擇 [更新] 或 [產生指令碼]


[產生指令碼] 產生後, 可以用 SSMS 到整合測試環境執行, 但要注意, 此時 SSMS 必須採用 SQLCmd 模式.


[更新] 就是直接把異動套用到目標, 亦即整合測試環境; 作這個動作要小心, 萬一 來源與目標剛好選反了, 那就 …

總結

採用 SQL Script 的文字檔案作為資料庫物件的描述, 看來不錯; 但總少了一些東西, 例如: 在撰寫 SQL Script 時, 沒有像 SSMS 一樣, 可以提供 intellisense 的功能.
因此, SSDT 提供了另外一種 Visual Studio 的專案類型, 稱之為 SQL Server 資料庫專案, 這也是下一篇文章要說明的部份.


參考文件


<附錄一> 相關SQL Script參考


?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
-- =========================================================
-- Tables.sql
-- =========================================================
 
-- ---------------------------------------------------------
-- [00] 刪除檔案
-- ---------------------------------------------------------
 
-- 員工資料檔
IF EXISTS (
  SELECT *
 FROM sys.tables
 JOIN sys.schemas
   ON sys.tables.schema_id = sys.schemas.schema_id
   WHERE sys.schemas.name = N'dbo'
  AND sys.tables.name = N'Emp'
)
  DROP TABLE dbo.Emp
GO
 
-- 部門資料檔
IF EXISTS (
  SELECT *
 FROM sys.tables
 JOIN sys.schemas
   ON sys.tables.schema_id = sys.schemas.schema_id
   WHERE sys.schemas.name = N'dbo'
  AND sys.tables.name = N'Dept'
)
  DROP TABLE dbo.Dept
GO
 
-- ---------------------------------------------------------
-- [01] 部門資料檔
-- ---------------------------------------------------------
CREATE TABLE dbo.Dept
(   DeptId                  int             not null  -- 部門代碼 PKEY
,   DeptName                nvarchar(10)    not null default ''     -- 部門名稱
    CONSTRAINT PK_Dept PRIMARY KEY CLUSTERED ( DeptId )
);
GO
 
-- ---------------------------------------------------------
-- [02] 員工資料檔
-- ---------------------------------------------------------
CREATE TABLE dbo.Emp
(   EmpId                   int             not null  -- 員工代碼 PKEY
,   EmpName                 nvarchar(10)    not null default ''   -- 員工名稱
,   DeptId                  int             not null                -- 部門代碼 FKEY
    CONSTRAINT PK_Emp PRIMARY KEY CLUSTERED ( EmpId )
    CONSTRAINT FK_Emp_Dept FOREIGN KEY ( DeptId ) REFERENCES Dept ( DeptId )
);
GO
 
-- =========================================================
-- Functions.sql
-- =========================================================
 
-- ---------------------------------------------------------
-- [00] 刪除
-- ---------------------------------------------------------
 
-- ufn_get_date
IF OBJECT_ID (N'dbo.ufn_get_date') IS NOT NULL
   DROP FUNCTION dbo.ufn_get_date
GO
 
-- ufn_tomorrow
IF OBJECT_ID (N'dbo.ufn_tomorrow') IS NOT NULL
   DROP FUNCTION dbo.ufn_tomorrow
GO
 
-- ufn_yesterday
IF OBJECT_ID (N'dbo.ufn_yesterday') IS NOT NULL
   DROP FUNCTION dbo.ufn_yesterday
GO
 
-- ufn_today
IF OBJECT_ID (N'dbo.ufn_today') IS NOT NULL
   DROP FUNCTION dbo.ufn_today
GO
 
-- ---------------------------------------------------------
-- [01] ufn_today : 取得系統日期
-- ---------------------------------------------------------
CREATE FUNCTION dbo.ufn_today( )
RETURNS date
AS
BEGIN
    RETURN DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0);
END
GO
 
-- ---------------------------------------------------------
-- [02] ufn_yesterday : 取得昨天日期
-- ---------------------------------------------------------
CREATE FUNCTION dbo.ufn_yesterday( )
RETURNS date
AS
BEGIN
    RETURN DATEADD(day, DATEDIFF(day, 0, GETDATE()), -1);
END
GO
 
-- ---------------------------------------------------------
-- [03] ufn_tomorrow : 取得明天日期
-- ---------------------------------------------------------
CREATE FUNCTION dbo.ufn_tomorrow( )
RETURNS date
AS
BEGIN
    RETURN DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1);
END
GO
 
-- ---------------------------------------------------------
-- [04] ufn_get_date : 取得 n 天 的日期
-- ---------------------------------------------------------
CREATE FUNCTION dbo.ufn_get_date(@pi_n int = 0)
RETURNS date
AS
BEGIN
    RETURN DATEADD(day, DATEDIFF(day, 0, GETDATE()), @pi_n);
END
GO
 
-- =========================================================
-- Stored Procedures.sql
-- =========================================================
 
-- ---------------------------------------------------------
-- [00] 刪除
-- ---------------------------------------------------------
IF EXISTS (
  SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'usp_get_all_depts'
)
   DROP PROCEDURE dbo.usp_get_all_depts
GO
 
-- ---------------------------------------------------------
-- [01] usp_get_all_depts: 取得所有部門資料
-- ---------------------------------------------------------
 
CREATE PROCEDURE dbo.usp_get_all_depts
AS
    SELECT  *
      FROM  Dept;
 
 
    RETURN 0;
GO


張貼者： 於
標籤： , , , ,

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)