使用 Visual Studio 進行 SQL Server 原始碼管理和部署

本文將為讀者你介紹Visual Studio 2013中SQL Server Data Tools（SSDT）這一特性，該特性可以用於在原始碼優先這一方法論中管理資料庫。簡單來說，對於資料庫表、檢視等物件的修改，都將以原始碼的方式在Visual Studio中完成。原始碼可以與生產環境的資料庫進行比較，並且在編譯後生成一份部署指令碼。

前提

• SQL Server 2005或之後的版本
• Visual Studio 2013

如果你正在使用SQL Server 2014，請確保你已經更新了SSDT，可以在Visual Studio中的“更新與擴充套件”選單項中進行更新。

對資料庫進行反向工程

通過“匯入”指令，SQL Server Data Tools可以對一個現有的資料庫進行反向工程，將其轉換至一個空的專案中。需要指出的是，該專案必須保證是完全空的，一旦你在這個專案中加入了任何檔案，“匯入”指令就無法應用於資料庫上了。

首先，建立一個全新的SQL Server Database專案，右鍵單擊Solution Explorer中的專案，並選擇“匯入”，再選擇“資料庫”。

資料庫專案並不強制你使用任何特殊的資料夾結構。與C#不同，它使用資料夾結構作為預設的名稱空間，而SSDT允許你將某個schema中的物件放入為另外的schema所建立的資料夾中。儘管如此，該匯入工具仍然會為你推薦一些資料夾結構，作為專案的起點。包括以下選項：

• 空
• Schema
• 物件型別
• Schema/物件型別

即便在小型資料庫中，我也建議你使用Schema/物件型別結構。如果你只使用Schema結構，不久你就會發現你會不停地開啟各個檔案，僅僅是為了檢視該檔案是什麼型別的。而如果你只使用物件型別結構，那就會促使開發者們傾向於將所有物件都建立在dbo這個schema下。

資料庫設定

在“專案設定”這個標籤頁下可以找到資料庫的通用設定。如果你沒有找到想要的設定項，也可以通過SQL語句直接新增。在本文稍後的“儲存”這一節中你將看到相關的示例。

除錯選項

在我們離開專案設定這個視窗前，你還需要設定一個除錯資料庫。每次你的應用程式開始執行時，資料會自動部署到這個除錯資料庫中。如果你選項的資料庫名稱不存在，那麼在你的專案初次執行時，會自動建立這個資料庫。

一般來說，你應該會選擇“在目標資料庫中刪除物件，但在專案中保留”這一選項，否則你的除錯資料庫就會累積在開發過程中曾一度建立，而後被刪除的這些物件。

只差最後的一步，你就可以完成資料庫的設定，讓它支援自動部署了。你需要開啟解決方案的屬性設定，並將該資料庫專案設定為啟動專案的一個依賴項。

儲存

如果你需要指明額外的，或者是特定的檔案組，你可以使用專案內建的物件模板，它們將建立類似於以下的指令碼：

ALTER DATABASE [$(DatabaseName)]
ADD FILEGROUP [FileGroup1]

請注意這裡對sqlcmd變數的使用[$(DatabaseName)]，如果你希望在同一臺伺服器上部署同一個資料庫專案的多個拷貝，它將會很有用。

安全

在配置一個新資料庫時，人們經常會犯的錯誤就是一上來就為所有人分配所有的訪問權，並且假設將來某一天你會鎖定這些訪問權。安全設定往往是十分微妙的，尤其在使用高階特性時，它很可能出乎你的意料。因此，更好的方式是在建立使用者帳號的時候不要分配任何許可權，隨後在需要時為他們分配對特定物件和特性的訪問權。

Schema

資料庫匯入流程會將schema定義建立在Security資料夾中，而不是該schema所對應的同名資料夾中。請記住，資料夾的位置並不重要，你可以選擇將schema的定義移動到其它資料夾中。

登入帳號與使用者

雖然從技術角度上來說，登入帳號是一個伺服器物件，而不是資料庫物件，但你仍然可以將它包含在專案中。如果該登入帳號已經存在了，那麼在部署時會自動忽略它。

當你在建立登入帳號時，請不要使用“USE master”指令，部署工具會自動為你進行處理。

在預設情況下，新建立的登入帳號和使用者還不能連線到資料庫伺服器。這意味著你需要在指令碼中加入一行“GRANT CONNECT TO [userName]”語句。

其它型別的安全物件

其它型別的安全物件，例如角色、非對稱鑰和證照等等也可以用類似的方式進行建立。

建立資料庫表

在建立一張新表時，只需右鍵單擊將包含該表的資料夾，並選擇“新增一張表”。在這裡要提供schema與表名，中間用.分隔。接下來，你就可以使用設計器或SQL編輯器開始新增各個欄位了。

索引

索引可以作為表定義的一部分一同新增，也可以作為一個單獨的命令新增在同一個檔案中。理論上，你也可以為每個索引建立一個單獨的檔案，不過這種方式未免有些過於麻煩了。

當你在獨立的語句中建立索引時，需要注意在每個語句間使用GO語法作為批處理的分隔符。

文件

在Visual Studio 2013中，你再也沒有藉口不為表與欄位新增任何註釋了。表級別的描述資訊需要在“屬性”皮膚中進行設定，你也可以同樣使用“屬性”皮膚設定欄位的描述，但多數人喜歡在表設計器中設定欄位的註釋。

這其實並非SQL Server的一種新特性，早在很久之前SQL Server就支援為表和欄位新增註釋了。這一工具只是將操作替換為sp_addextendedproperty這一囉嗦的語法而已。

EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', 
@value=N'The employee key, which is called empId in some older tables.', 
@level0type=N'SCHEMA',
@level0name=N'dbo', 
@level1type=N'TABLE',
@level1name=N'Employee', 
@level2type=N'COLUMN',
@level2name=N'EmployeeKey'

部署

在除錯時進行自動部署

要開啟自動部署功能，你需要首先開啟資料庫專案設定中的Debug標籤頁進行修改。一般來說，你需要修改連線字串資訊。如果該資料庫比較小，而且你能夠通過指令碼重新載入所有資料，那麼選擇自動重建資料庫也不是一個壞主意。否則，你需要讓它刪除所有不在原始碼控制系統中的物件。這樣可以避免你無意中使用了某些在生產環境中並不存在的資料庫物件。

接下來，你需要將該資料庫專案設定為啟動專案的一個依賴項，你需要在“解決方案”設定對話方塊，而不是在專案屬性中完成這一步驟。

釋出

通過右鍵單擊資料庫專案，你就可以開啟發布對話方塊。首先填入目標資料庫的連線字串，接下來你可以開啟“高階”標籤頁進行應用程式的相關設定。舉例來說，在生產環境中，你可能會選擇在釋出時觸發備份步驟。而在開發或預釋出伺服器上，將不包含在專案中的物件全部刪除也是很重要的一點。請你仔細檢查這些設定，其中有許多部分需要認真考慮。

接下來你應該儲存這個釋出指令碼，它會生成一個.publish.xml檔案，今後你會經常用到它。要載入這個檔案時，只需在Solution Explorer皮膚中雙擊它就可以了。

如果你選擇部署到自己的工作機器上，可以直接單擊“釋出”按鈕繼續。而如果是部署到共享環境上，那就應當選擇“生成指令碼”選項。多數情況下，你不會再去檢查生成的檔案，而是在Data Tools Operations皮膚中單擊“檢查預覽”，它會列出該指令碼內容的一個小結。

如果檢查後，你對這些操作覺得都沒問題了，就可以通過“釋出”選項重新執行這段釋出指令碼。

修改部署指令碼

有些時候，你需要對釋出指令碼進行一些手動修改。這種情況通常發生在對某個表的操作會產生資料丟失的情況下。例如將某個欄位設為不允許空值，或者是修改欄位資料型別。

在Visual Studio中是不能夠直接修改部署指令碼的，你需要將指令碼拷貝到SQL Server Management Studio中。請確保你在“查詢”選單中開啟了SQLCMD模式，因為它需要進行某些設定，例如資料庫名稱等等。

還有一種情況下你可能需要修改部署指令碼，就是將指令碼分解為多個小的片段。比方說，除了某些常見的修改之外，你可能還需要為某張資料量很大的表新增一些索引。由於建立這些索引的過程時間可能會很長，你或者會決定將建立索引的改動在幾個小時之後再進行，而在那之前仍然要執行指令碼的其它部分。

重構日誌

SQL Server Data Tools內部維護著一份重構日誌，它也減少了你手動編輯部署指令碼的需要。不幸的是這一特性很容易在無意之中被忽略。如果你在設計器視窗中修改了某個欄位的名稱，這次改動就會記錄在日誌中。但如果你直接對包含這張表定義的原始SQL進行編輯，那麼日誌中就不會存在這次修改的記錄。

你或許不會立即發現這一問題，但當你開發部署資料庫時，這段指令碼會嘗試刪除某個欄位，隨後再新增一個新欄位，而不是你所期望的對欄位直接重新命名。一旦發生這種情況，你必須選擇要麼立即回滾你的改動，並按照“正確的方法”再做一遍，意味著你需要使用表設計器進行操作。或者你也可以選擇對部署指令碼進行手動修改。

建立檢視、自定義函式和儲存過程

如果你的應用程式是基於ORM開發的，那麼基本上可以忽略這一節內容。

基礎

與表的建立一樣，對於你將檢視、自定義函式和儲存過程放在哪裡並沒有強制的規定。如果你選擇使用Schema/物件型別這一檔案結構，那麼預設的設定是為這三種物件各自建立一個獨立的資料夾。還有一種選擇是參考SQL Server Management Studio的模式，將它們各自分解到細粒度的資料夾中。

有兩種方式可以將新物件加入專案中，你可以選擇基於模板建立，在其中填入各種內容，例如引數列表或是檢視內容。或者你也可以選擇在SQL Server Management Studio中建立好該物件，如果在我不確定該物件的具體內容，並且預計會持續進行修改時，通常我會選擇這種方式。一旦我確信了其中的內容，我就會將其儲存為一個SQL檔案，隨後匯入到我的專案中。

新增安全設定

假設這個資料庫已經進行了合適的安全防護，接下來你就需要將各種物件對使用者進行授權了。雖然有多種方式可以完成這一任務，但我還是建議將GRANT語句放在與定義檢視、自定義函式和儲存過程相同的檔案中。這樣一來，你一眼就能看到可以執行這段過程的使用者是否都賦予了許可權。

部署之前與之後的指令碼

SQL Server Data Tools允許你建立一個單獨的部署前指令碼，以及一個單獨的部署後指令碼。這些指令碼並非一次性的遷移指令碼，而是在每次部署時都會執行的內容。

這兩個指令碼的命名分別為Script.PreDeployment.sql和Script.PostDeployment.sql。它們並非真正的SQL指令碼，而是一種稱為SQL CMD的變體。通過SQL CMD可以對其它SQL指令碼進行引用。

認識到以上這點很重要，因為你的部署前與部署後指令碼很容易變得混亂，為了避免混亂的產生，我建議你將這類指令碼作為目錄一樣處理，將每一類的操作分別儲存在不同的指令碼檔案中。以下是一個典型的部署前指令碼的示例：

PRINT N'Enabling CLR';
EXEC sp_configure 'clr enabled' , '1';
RECONFIGURE;
:r ".\OneTimeScripts\Migrate records to not use customer type 6.sql"

下面是相對應的部署後指令碼：

:r .\Data\UserType.sql
:r .\Data\CustomerType.sql
:r .\Data\AccountLevel.sql

時機

部署前指令碼的執行時機處於資料庫建立之後，而在其它物件加入資料庫之前。它允許你修改伺服器與資料庫級別的設定，而這些設定並沒有直接通過SSDT暴露出來。

部署後指令碼執行的時機總在最後，正如其名稱所暗示的一樣，它是在其它所有資料庫物件都更新後才執行的。

編寫一次性指令碼

編寫一次性指令碼並不容易。首先，SSDT中並沒有一次性指令碼的概念，因為它不併清楚某段指令碼是否曾經執行過，因此你所能做的就是儘量用if語句覆蓋你的指令碼，以此進行判斷。

處理一次性指令碼的另一種方法是手動修改部署指令碼，這種方式具有高度靈活性，但卻是不可重複的，你必須對每個環境重新應用所有的改動。

載入表資料的模式

SSDT中所缺少的另一項特性是表的載入，理想的情況下，所有的查詢表資料都應該儲存在原始碼控制中，如同其它資料庫物件一樣。但由於這一特性的缺乏，我們不得不通過其它替代途徑實現這一功能。

其中一種途徑是使用MERGE語句。首先，為你的目標表建立一個結構類似的臨時表，隨後在該臨時表中加入你所需的資料。隨後你就可以使用MERGE語句執行必要的插入、更新和刪除操作。

另外一種途徑是編寫一個插入或更新的儲存過程，然後就可以通過呼叫該儲存過程對目標表中的每一行進行操作了。這種方法允許你處理複雜的邏輯，但無法通過這種方式刪除過期的資料。

連線資料庫

對於大型系統來說，僅僅使用schema作為名稱空間往往是不夠的。出於多種不同的原因，例如部署週期、安全性、效能和災備，往往會將物件分佈在多個資料庫、乃至多臺伺服器上。

只要你對這些情況做好計劃，SSDT都能夠處理。你需要注意的一點是迴圈引用的限制，某個由SSDT管理的資料庫可以對另一個資料庫進行引用，哪怕後者處於不同的伺服器上，只要你能夠建立一個清晰的編譯順序就可以實現這一點。這種情形和.net專案的引用是完全一樣的。

一種常見的情形是，某個新建的資料庫需要引用某個遺留系統的資料庫。首先要為舊資料庫建立指令碼，並將其匯入到一個空的專案中。你並不需要使用SQL Server Data Tools管理這個遺留資料庫，只需要它所對應的後設資料就可以了。如果需要，你也可以僅僅匯入那些新資料庫所依賴的物件。

接下來你需要新增一個資料庫引用，在這裡你需要做出一個重要的決定，是否讓舊的資料庫也駐留在同一臺伺服器上。雖然你也可以事後改變想法，但過程既麻煩又容易出錯。以下是新增引用的對話方塊：

（單擊圖片以放大）

其中的資料庫變數以及可選的伺服器變數會對你的自定義函式及儲存過程的實際名稱產生影響，因此請確保你使用了[$(variable)]這一模式，否則你可能會遇到編譯錯誤。當你實際釋出時，系統會要求你為這些變數填入實際名稱。

當你按下“載入值”按鈕時，在專案級別所定義的預設值將作為實際的值填入這些變數中。為了在今後的部署中節約時間，你也可以將這些數值儲存為釋出檔案的一部分。

SQL CLR

雖然在SQL Server中使用C#程式碼的做法曾經一度遭受一些非議，但在某些情況下還是非常有用的。在某些場合中，由JIT編譯的.NET程式碼比起解釋型的T-SQL會得到更好的效能。而對於某些資料型別來說，例如幾何資料與地理位置，你完全沒有其它選擇。

使用SQL CLR的一個主要問題在於部署。通常來說，要將一個.NET程式集部署到SQL Server中，需要手動將DLL拷貝到某處，隨後一個一個地進行手動新增。SSDT的釋出工具能夠消除這一問題，它會將程式集進行編碼成為一個SQL語句，與部署指令碼的其餘部分進行內聯。

外部專案與程式集

按照約定，加入SSDT專案的程式集會首先新增到一個名為“Assemblies”的資料夾中。雖然這並非必須，但它會使得對DLL的管理更加容易。如果你決定遵循這一建議，請首先完成這一步驟。

接下來，你可以通過常用的.NET“新增引用”對話方塊新增對某個專案或是程式集的引用。

最後一步是將你需要在SQL中使用的型別與函式暴露出來，你可以在“新增專案”對話方塊中的SQL CLR資料夾選項中找到對應的模板。這種做法與手動註冊SQL CLR型別和函式沒有分別。

內部專案

內部專案是指駐留在資料庫專案中的某個C#專案，它將隨著資料庫自動構建及部署，因此你不必擔心對引用及程式集的管理。雖然你無法實現CLR版本的靜態程式碼分析功能，但除此之外它的行為與其它C#專案相比完全一致。

內部專案的一個有用的特性在於，你不需要顯式地通過SQL註冊函式。編譯器看到SqlFunction屬性之後，會自動在專案定義的預設名稱空間中建立正確的SQL原型。

重編譯問題

當你應用CLR專案時，無論是內部專案還是外部專案，重編譯都會成為問題。尤其是當你在某個計算欄位中使用了某個SQL CLR函式的時候。

本質上，問題在於Visual Studio會經常對C#專案進行重編譯，哪怕沒有任何改變。由於新的版本中使用了一個新的hash碼，釋出工具就會認為該專案是個新的版本，並且重新部署整個程式集。在之前所提到的計算欄位的場景中，釋出工具就需要對受影響的表中的每一行進行重新計算。

當這個問題出現時，可以將SQL CLR程式碼遷移到某個外部專案中，隨後將編譯後的專案程式集放到Assemblies資料夾中，就所同一個第三方類庫一樣。要注意的是這種方式有一個風險，那就是你很可能在進行變更的時候忘了更新該程式集。

全文索引

SQL Server Data Tools對全文索引僅提供了部分支援，它對於建立與管理全文索引目錄提供了良好的支援，你也可以將FTS索引新增到任何需要使用它的表中。你可能遇到的問題在於，SSDT僅支援空的非索引字表（StopList），這是因為非索引字表中的內容被視為資料，而不是schema，因此SSDT不知道如何將它的內容保持更新。

有一個應對該問題的臨時方案，就是使用部署後指令碼載入其中的資料，正如同對於查詢表資料的處理方式一樣。一旦建立了非索引字表之後，釋出工具將不會再修改其中的資料。

版本號

SQL Server本身對於版本號並沒有一個強烈的概念。在理論上，你可以在某個編譯後資料層的應用（dacpac）中加入版本號，但這種方式在實踐中是難以執行的。目前並沒有一種非常良好的方式能夠獲取到這個值，你只能選擇在構建後指令碼中對dacpac版本進行自動增值處理。

你可以考慮採取這種方式：建立一個內部專案，其中只包含一個名為dbo.GetDatabaseVersion的函式，該函式能夠從C#程式集中讀取版本號。如果你在程式集版本中使用了萬用字元，版本號就能夠自動增加。

另一個選擇是建立一個名為dbo.GetDatabaseVersion的T-SQL函式，它會返回一個硬編碼的值，你可以在需要時手動增加它的值。

Brett Gerhardi的建議是，為資料庫版本建立一個擴充套件屬性。參考以下例子：

EXEC sp_addextendedproperty @name='DbVersion', 
@value ='1.015', 
@level0type = NULL,
@level0name = NULL, 
@level1type = NULL,
@level1name = NULL, 
@level2type = NULL,
@level2name = NULL

如果不使用構造前指令碼，那麼你必須手動更新它的值。

持續整合

在理論上，你可以使用SqlPackage.exe自動部署資料庫。Anuj Chaudhary在部落格中釋出了一篇帖子，名為“SqlPackage.exe —— 自動化SSDT部署”，為你介紹了這一過程。但在實踐中，你會發現在某些場合中如果不對更新進行手動調整，對於SSDT來說處理會顯得過於複雜。