使用Visual Studio進行SQL Server源代碼管理和部署

        英文原文：SQL Server Source Control and Deployment with Visual Studio

        本文將為讀者你介紹 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 來說處理會顯得過于復雜。