使用SQL Compare命令行來自動化數據庫部署

SQL Compare是一款比較和同步SQL Server數據庫結構的工具。現有超過150,000的數據庫管理員、開發人員和測試人員在使用它。當測試本地數據庫，暫存或激活遠程服務器的數據庫時，SQL Compare將分配數據庫的過程自動化。

我們的團隊為商業組織開發實際應用程序；主要是格魯吉亞銀行。這些應用程序基于MS SQL Server作為數據庫的.Net-Windows-Forms應用程序。它們具有許多業務邏輯，這些邏輯包含在數據庫例程中，例如存儲過程、函數、視圖和SQL CLR。

毫不奇怪，考慮到客戶業務的性質，我們沒有訪問客戶數據庫或數據進行開發、測試或部署的權限。在TFS源代碼管理下，我們只有開發數據庫及其人工測試數據。開發人員在自己的數據庫副本上工作，每個副本都有自己的示例數據，并且他們使用Redgate SQL Source Control提交開發更改。然后，我們使用SQL Compare命令行來自動化數據庫部署。在本文中，我將解釋如何實現此目標，并舉例說明如何比較相同或不同分支中的數據庫的兩個修訂版，并生成部署腳本。

在源代碼管理中管理數據庫

我們的數據庫源代碼控制和分支策略很簡單。我們在Trunk中擁有最新的代碼庫；整個應用程序都在那里，包括其中的數據庫部分。所有新功能和錯誤修復最初都在Trunk中進行。我們創建的每個分支只是Trunk的一個副本，因此代表了代碼庫的完整時間點狀態。在應用了一些更改并簽入Trunk之后，我們可以根據需要將其合并到這些分支中的任何一個。通常，這是為了修復已報告的錯誤，但當對我們的客戶來說很重要時，我們還需要能夠合并小的功能更改。例如，并非每個客戶都能負擔得起部署每個版本的費用，因此他們部署的版本通常落后三個或四個版本。但是，他們仍然需要我們為當前版本部署緊急修復程序，偶爾會使用一些“專有”功能。

那么，當我們開發軟件時，這一切如何工作？讓我們將其稱為“under-source-control-application”（簡稱USCAPP）。我們在USCAPP_Trunk中擁有最新的代碼庫，并在TFS分支下提供了一些發行版本，稱為v241、v242等。

直接或通過合并進行的所有更改都可以通過Trunk及其分支的普通TFS檢入完成。在每次簽入時，TFS都會創建一個稱為變更集的東西，它具有唯一的參考號。變更集表示源代碼管理中整個代碼庫的快照。像任何其他源代碼控制系統一樣，TFS可以針對任何給定的變更集編號，為任何修訂生成代碼庫的時間點狀態。

當然，對于集合中的所有TFS項目（包括其分支機構），TFS變更集編號都是全局的，并且每次對該項目集合進行每次檢入時，TFS變更集編號都將逐漸增加。對我們來說，這意味著USCAPP_Trunk及其所有分支v241、v242等都共享相同的、全球的、不斷增長的變更集號。

開發人員進行更改，每個人都在自己的專用數據庫上工作，并通過SQL Source Control簽入更改，這些更改將更新USCAPP_Trunk中的代碼。根據需要，我們將所需的變更集合并到其他分支，在這些分支中創建新的變更集。因此，假設最新版本為v245，并且我們知道客戶A已將v242部署到生產環境中。該客戶尚不能升級到最新版本，但已部署了一個附加的升級腳本以修復一些錯誤并進行一些小的改進。換句話說，客戶A正在運行非常特定的v242版本，我們可以將其轉換為TFS變更集編號，該編號唯一地標識其已部署的分支v242的代碼庫的時間點狀態。

使用SQL Compare命令行自動生成更改腳本

我們的目標是使生成同步SQL腳本的過程自動化，該腳本覆蓋自上次發布腳本以來發生的所有更改。

假設客戶A已經部署了分支v242，并且該數據庫的發行版本標記有人類可讀的版本號2.4.2.0，該版本號對應于變更集編號87300，即它是在變更集87300是當前最新版本時發布的代碼庫的全局變更集編號。

此后一個月過去了，我們已經在數據庫中進行了更改，現在TFS中當前的更改集數量為88100。現在，我們要生成一個腳本，其中包含當月所做的每個更改，因此將數據庫的v2.4.2.0升級到變更集編號88100表示的狀態，我們將其稱為v2.4.2.1。

為此，我們需要從TFS中檢索數據庫的兩個時間點狀態，一個代表源數據庫（不會改變），另一個代表目標數據庫（我們要升級）。因此，對于客戶A，變更集88100代表源，而87300代表目標。我們需要比較這兩種狀態以找出差異，然后生成一個腳本以同步目標，以便其狀態與源相同。對于兩個數據庫中都存在但有差異的任何數據庫對象，必須更改目標中對象的定義以匹配其在源中的定義。應該創建源中存在但目標中不存在的任何對象，應該刪除目標中存在但源中不存在的任何對象。

好消息是，我們不必手動執行此操作。SQL Compare GUI和SQL Compare命令行均支持此功能。我們希望使該過程自動化，因此我們使用命令行并將適當的參數傳遞給該命令行以生成同步腳本。我們還需要仔細記錄該腳本將數據庫的2.4.2.0版本升級到v2.4.2.1。當然，這里我們也需要一些保護措施。其中一項是檢查，該檢查將停止在不是v2.4.2.0的任何數據庫上運行此腳本。在這里，我不會進行演示，但是最后，我將更詳細地討論這些需求。

比較同一分支中的兩個修訂

首先，我將描述我們如何發布稱為“修復”的腳本，該腳本主要用于部署一些錯誤修復和較小的改進。主要版本保持不變。

我們使用SQL Compare命令行進行此操作，傳遞一個XML參數文件（argfile），該文件包含指示SQL Compare如何執行比較的所有必需命令行開關的值。或者，您可以指定每一個到命令行的開關，或在PowerShell中“splat”參數。

在這種情況下，唯一需要傳遞給SQL Compare的參數是XML Argfile的合格文件名，稱為“shared.xml”

“％programfiles（x86）％\?Red?Gate?\?SQL?Compare?13?\?sqlcompare”?/Argfile:"shared.xml“

argfile的內容應完全按照SQL Compare命令行的在線文檔中的說明填寫。這是真實的示例：

<commandline>
??<SourceControl1?/>
??<Revision1>88100</Revision1>
??<SourceControl2?/>
??<Revision2>87300</Revision2>
??<Options>NoDeploymentLogging,IgnoretSQLt,IgnoreFillFactor,IgnoreWhiteSpace,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,CaseSensitiveObjectDefinition,ObjectExistenceChecks,DropAndCreateInsteadofAlter,ForceColumnOrder,DoNotOutputCommentHeader,IgnoreUsersPermissionsAndRoleMemberships</Options>
??<ScriptsFolderXML>Command?Line\SourceControlAddress?v242.xml</ScriptsFolderXML>
??<Filter>Command?Line\Filters\Shared.scpf</Filter>
??<ReportType>Interactive</ReportType>
??<Report>Command?Line\Output\Shared.html</Report>
??<ScriptFile>Command?Line\Output\Shared.sql</ScriptFile>
??<Force?/>
??<Verbose?/>
</commandline>

Argfile包含五個命令行開關，我們使用它們來定義所需的行為。/ Sourcecontrol1和/ Sourcecontrol2切換指定我們的源，和目標，是源控制腳本的一個文件夾，在這種情況下，版本分別為88100和87300。

<SourceControl1?/>
<Revision1>88100</Revision1>
<SourceControl2?/>
<Revision2>87300</Revision2>

所述<ScriptsFolderXML>開關包含完整的文件路徑為XML文件，SourceControlAddress v242.xml。該文件如下所示，包含分支v242的數據庫的源代碼控制地址：

<?xml?version="1.0"?encoding="utf-16"?standalone="yes"?>
<ISOCCompareLocation?version="1"?type="TfsLocation">
??<ServerUrl>http://tfs:8080/tfs/projects</ServerUrl>
??<SourceControlFolder>$/USCAPP/Branches/v242/Database/Schema</SourceControlFolder>
</ISOCCompareLocation>

這是SQL Compare應從中恢復87300和88100變更集的地址。當執行SQL Compare的命令行版本時，它將把這些變更集還原為“腳本文件夾”（在撰寫本文時，還原到Windows Temp中的文件夾中），并使用88100作為源和87300作為目標進行比較，以生成最終的升級腳本。

比較兩個不同分支中的數據庫

我們用來發布已經在Trunk中完成的所有新功能的過程與錯誤修正版本稍有不同，但是主要概念保持不變。同樣在這種情況下，我們必須比較數據庫架構的兩個不同狀態。即使它們的“真理來源”作為TFS源代碼管理中的版本存在，它們也被導出到文件夾，作為Redgate稱為“腳本文件夾”的東西。然后可以將它們作為兩個數據庫模式進行比較。在這種情況下，不同之處在于我們不是在一個TFS分支中比較由變更集表示的兩個修訂版（或時間點狀態），而是在現在表示版本的兩個分支之間進行比較。

要逐步進行：該過程首先從Trunk分支中創建一個新分支，并為其指定一個適當的名稱。例如，如果v2.4.2是USCAPP應用程序的最后發行版本，那么在發行該版本時，我們已經創建了一個名為v242的分支。現在，我們已經對Trunk進行了更多更改，從邏輯上講，我們將發布v2.4.3版本，因此我們的新分支將稱為v243，從那時開始，就其所包含的內容而言，它將作為Trunk分支的確切副本。

現在，我們應該比較兩個單獨分支的兩個變更集。我們用于比較的變更集必須是剛剛創建的新v243分支的變更集，并且是客戶A已應用的上一個分支v242的最新發布的部署腳本所對應的變更集。此比較將揭示僅在Trunk的數據庫上發生的更改，而上一個分支v242的數據庫中缺少這些更改。

為此，我們需要指定一個而不是兩個源代碼管理文件夾位置，一個用于包含源/?ScriptsFolderXML1的TFS分支，另一個用于包含目標/?ScriptsFolderXML2的分支。我們使用SQL Compare保留關鍵字“HEAD”來指定我們想要源分支的最新的源控件更改集。生成的Argfile如下所示：

<commandline>
??<SourceControl1?/>
??<Revision1>HEAD</Revision1>
??<SourceControl2?/>
??<Revision2>88100</Revision2>
??<Options>NoDeploymentLogging,IgnoretSQLt,IgnoreFillFactor,IgnoreWhiteSpace,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,CaseSensitiveObjectDefinition,ObjectExistenceChecks,DropAndCreateInsteadofAlter,ForceColumnOrder,DoNotOutputCommentHeader,IgnoreUsersPermissionsAndRoleMemberships</Options>
??<ScriptsFolderXML1>Command?Line\SourceControlAddress?v243.xml</ScriptsFolderXML1>
??<ScriptsFolderXML2>Command?Line\SourceControlAddress?v242.xml</ScriptsFolderXML2>
??<Filter>Command?Line\Filters\Shared.scpf</Filter>
??<ReportType>Interactive</ReportType>
??<Report>Command?Line\Output\Shared.html</Report>
??<ScriptFile>Command?Line\Output\Shared.sql</ScriptFile>
??<Force?/>
??<Verbose?/>
</commandline>

這是目標的源代碼管理腳本位置XML文件（SourceControlAddress v242.xml）：

<?xml?version="1.0"?encoding="utf-16"?standalone="yes"?>
<ISOCCompareLocation?version="1"?type="TfsLocation">
??<ServerUrl>http://tfs:8080/tfs/projects</ServerUrl>
??<SourceControlFolder>$/USCAPP/Branches/v242/Database/Schema</SourceControlFolder>
</ISOCCompareLocation>

這是源代碼之一（SourceControlAddress v243.xml）：

<?xml?version="1.0"?encoding="utf-16"?standalone="yes"?>
<ISOCCompareLocation?version="1"?type="TfsLocation">
??<ServerUrl>http://tfs:8080/tfs/projects</ServerUrl>
??<SourceControlFolder>$/USCAPP/Branches/v243/Database/Schema</SourceControlFolder>
</ISOCCompareLocation>

再一次，我們僅使用Argfile的地址作為唯一參數來調用SQL Compare命令行：

“％programfiles（x86）％\?Red?Gate?\?SQL?Compare?13?\?sqlcompare”?/Argfile:"shared.xml“

在SQL Compare命令行完成其工作之后，在“Shared.sql” 文件中，我們有了可以在目標數據庫上運行的升級腳本，以將其升級到最新的主要版本。

進一步要求

在現實生活中，我們始終需要仔細檢查自動生成的腳本，添加檢查和控件，以確保例如以正確的順序將所有必需的升級腳本應用到預期的數據庫版本。我們還需要對SQL Compare的自動生成的部署腳本進行少量添加和自定義，例如處理數據插入或向每個腳本添加標頭信息（創建腳本時，版權信息，聯系信息等）。 或在每個自動生成的腳本的末尾附加一些動態生成的SQL腳本，以識別客戶。

通過使用自定義遷移腳本修改SQL Compare部署，可以實現很多這樣的目標，盡管實際上我們遇到了一些困難，例如它們減慢了SQL Source Control的運行速度或部署前和部署后腳本。

對我們來說，另一個需要考慮的因素是，SQL Compare遷移和部署前或部署后腳本是靜態的，而我們的要求是動態生成的腳本。相反，我們在Visual Studio中構建了一個簡單，輕便的工具，允許開發人員對SQL Compare腳本進行小的動態添加和自定義。

我避免在這里進行深入研究的另一個復雜性是，對于我們的每個客戶，我們的源代碼管理干線將客戶數據庫的所有共享邏輯與包含該組織專有的定制代碼的小型例程結合在一起。在本文中，我演示了如何使用SQL Compare命令行來部署所有客戶通用的數據庫結構和代碼。盡管該過程與特定于客戶的例程基本相同，但是需要進行一些小的調整，以確保始終將獨有功能僅部署到該客戶的生產數據庫中，并且沒有任何客戶可以看到專門為另一位客戶編寫的邏輯。我將在下一篇文章中描述我們如何實現這一目標！

結論

我們的經驗是Redgate Source Control和SQL Compare可以協同工作，并且對我們自動化腳本生成過程起到了很大的作用。SQL Compare允許對其從Git或TFS源代碼控制中提取腳本的方式進行非常精細的控制，從而為我們節省了大量手動腳本編寫。我們將看到能夠自動生成相應的回滾（降級）腳本以及升級腳本的更多可能性。它只需要反轉我們用于源和目標的變更集并啟動SQL Compare命令行即可！它是一種多功能工具。

本教程內容到這里就結束了，希望對您有所幫助~