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

點擊下載SQL Compare試用版

當使用所謂的“基于狀態”的數據庫部署方法時，您需要設計一些其他的自定義腳本來解決一些棘手的部署問題。例如，如果您使用SQL Compare或SQL Change Automation（SCA）來將包含對象級腳本的腳本文件夾與保存數據的目標數據庫同步，則可能需要一些自定義腳本來控制當前和當前數據庫之間的數據移動。

在執行之前，SQL Compare和SCA都將這些腳本放置在其生成的同步腳本的開頭或結尾。

自定義部署腳本有哪些優點？

通常，當您需要引入代碼來處理從一個數據庫版本到另一個版本的尷尬更改時，將使用自定義部署腳本。例如，如果要更改已存儲有數據的數據庫版本中的表，則偶爾需要從SQL Compare中“奪取”來確保舊數據全部移至改進版本中的正確位置。設計。表重命名或表拆分經常會發生這種情況。有時，包含非原子數據的單個列（例如，以逗號分隔的列表，XML或JSON）將需要分配到標準化列中。

您可能還會遇到依賴關系，更改數據庫屬性，添加計劃的任務，添加數據庫版本號等問題。在其他時候，您可能還需要更改數據庫級別的設置，管理角色成員身份，檢查是否存在正確的靜態數據，或者創建或更改SQL Server代理作業。所有這些事項都可以通過自定義部署腳本處理

從腳本目錄部署

SQL Compare比較兩個數據庫，即源數據庫和目標數據庫，并自動生成一個同步腳本，該腳本將修改目標架構，使其與源架構匹配。當您使用腳本目錄作為源時，SQL Compare允許您將額外的SQL腳本附加到它生成的同步腳本的開頭或結尾。

如果源是從版本控制系統獲取的腳本目錄，則SQL Compare允許部署前和部署后腳本，但每個腳本只能有一個。UI和命令行的SQL Compare允許使用13.4.7版本的版本，或SQL Change Automation從3.0.4版本的版本。

這些腳本必須包含在名為Custom Scripts的目錄中，該目錄位于Post-Deployment或Pre-Deployment的子目錄中。

每個腳本必須遵循某些簡單的約定。該腳本不會因錯誤而中止執行，因此它必須捕獲每個錯誤并報告它，并且如果是“致命”錯誤，還必須將其設置NOEXEC為ON。如果在部署后腳本之前發生錯誤，NOEXEC將為ON。因此，它將不會執行。切勿NOEXEC OFF在部署后腳本中使用該命令，因為同步腳本會使用該NOEXEC ON開關在發生任何錯誤后中止處理。如果您在發生錯誤后無意間重新啟用了執行，則會發生壞事

腳本的每個部分PRINT在開始時都應該有一條語句來描述其功能，這樣，如果出現錯誤，則很明顯是造成回滾的原因。

每個腳本都必須是可重新運行且冪等的。它必須能夠在所有預期的目標環境中運行，并且必須支持整個數據庫環境中數據庫排序規則的任何可能差異。

部署前和部署后腳本如何工作

這些自定義腳本的內容不是構建的一部分，因為它們直到比較完成后才執行，因此它們對SQL Compare自動生成的同步腳本沒有影響。在生成同步腳本之后但在執行之前，使用預部署腳本來更改目標數據庫。執行同步腳本后，將使用部署后腳本來更改目標。

例如，如果您在預部署子目錄中放置了一個包含創建表的自定義腳本，則該表將不包含在比較中。SQL Compare運行比較，生成同步腳本，在目標上執行部署前腳本，創建新表，然后執行同步腳本。另一方面，如果將相同的腳本放在“自定義腳本”目錄的“部署后”子目錄中，則SQL Compare將在運行其同步腳本后創建新表。

SQL Compare中沒有其他方法可以添加遷移邏輯。如果需要在完成比較之前更改目標，則必須在運行SQL Compare之前在單獨的腳本中進行操作。

例如，如果您需要從目標數據庫中的表中復制數據（將對其進行更改）并將其保存到臨時表中，則部署前腳本會很有用。然后可以在同步腳本中更改表，最后，可以在部署后腳本中將數據重新插入到新表中。您可能還需要使用部署后腳本來確保表中存在某些引用或靜態數據。

SQL Compare在比較中不考慮服務器范圍的對象，因此，如果需要在源和目標之間同步代理作業，則可以在部署前或部署后腳本中進行同步。其他任務（例如，檢查數據庫設置是否正確）必須使用部署前腳本，因為它們可以輕松更改后續腳本的執行方式。例如，如果排序規則不區分大小寫，而數據庫區分大小寫，則同步將無法進行。

部署后腳本可用于應用創建數據庫版本的特定變體所需的更改。例如，如果您根據立法區域擁有不同的工資單數據庫變體，則可以根據所需的立法區域進行切換。

您可能需要在主干中保留數據庫同一版本的多個變體，使用條件開關來生成正確的變體（例如，任何會計程序包可能每個稅區都有變體）。盡管可以有條件地運行一個CREATE或多個ALTER腳本，但這會使源代碼控制變得過于復雜，并使從腳本目錄進行的同步成為雷區。

我建議，最佳做法是在版本中包含所有代碼，并使用功能切換或功能開關（例如表中的值或擴展屬性）來實現正確的邏輯。僅可以通過簡單的邏輯（例如，檢查目標數據庫的名稱并相應地進行切換）在部署后腳本中設置此屬性。保留在功能或擴展屬性中的“軟”數據庫開關或切換開關允許使用同一部署測試所有變體。

使用部署前和部署后腳本時，需要考慮一些限制。

不支持使用SQLCMD語法和變量，除非您在SQL Compare之外，使用SQLCMD或在SQLCMD模式下使用SSMS分別執行同步腳本。

如果您選擇修改現有對象作為這些自定義腳本的一部分，則需要確保SQL Compare引擎不理會它們。您將需要在主腳本目錄中而不是“自定義腳本”目錄中具有新版本的對象源代碼，因此您將需要告訴SQL Compare也不要使用SQL Compare過濾器或/Exclude開關來創建或更改它們。以防止在比較中包含這些對象。

如果SQL Compare在源和目標之間進行比較并發現它們相同，則它將不會運行后腳本和前腳本，因為將沒有附加它們的同步腳本。

除非對這些腳本進行了徹底的測試，否則只有在執行部署腳本后才會發現錯誤，然后它們才會破壞構建，從而導致問題。在使用之前，請花一些時間對其進行徹底測試。

源代碼控制和自定義腳本

部署后和部署前腳本幾乎應始終保留在源代碼管理中。無論它們創建或更改的對象如何，狀態都必須處于源代碼管理中。通常，對于表拆分之類的更改，您只需添加一個SQL比較過濾器或/exclude切換為告訴SQL Compare在比較中不包括受影響的對象，因為它是在隨附的部署后腳本中完成的。這意味著源控件可以具有用于更改對象的SQL DDL代碼，而它們的存在不會干擾復雜的數據拆分。如前所述，在允許同步腳本進行除對象以外的其余所有更改之前，完全有可能使用預部署腳本將要更改的表中的現有數據從目標表中保存到目標數據庫中。您指定要排除在外；然后匹配的部署后腳本可以讀取臨時表并將數據放置在正確的位置。

兩種類型的自定義腳本（部署前和部署后）都應特定于版本。但是，由于它們是冪等的，因此即使它們意外重新運行，它們也通常不會傷害任何東西。腳本文件夾將僅反映一個版本，因此應易于為該版本提供正確的自定義腳本。

不一定要保證數據庫部署的目標。如果您不維護實時數據庫的版本號，那么您將需要確保自定義腳本將與“狂野的”所有可能的目標版本一起使用。

例子

這些不應該按原樣運行；例如，您需要填寫數據庫的名稱和版本號，或者根據需要調整信息。數據庫設置示例可能會對運行它的不幸數據庫產生微妙而廣泛的影響。它們也不代表如何執行這些任務的一般建議，因為您的情況可能會有所不同。例如，“存有數據的表”示例將很好，直到您有成千上萬的行，此時您將有一個更好的生活BULK INSERT。這些示例僅是為了說明，以向您展示可能性

在表中存儲數據

想象一下，您需要構建一個過時的PUBS數據庫版本，包括所有數據。該腳本必須位于“部署后”目錄中。這是一個確保dbo.publishers表僅包含原始數據的示例。

PRINT 'Ensuring that the original PUBS test data is there'
BEGIN TRY
MERGE INTO dbo.publishers AS target
  USING (VALUES
    ( '0736', 'New Moon Books', 'Boston', 'MA', 'USA' ), 
    ( '0877', 'Binnet & Hardley', 'Washington', 'DC', 'USA' ), 
    ( '1389', 'Algodata Infosystems', 'Berkeley', 'CA', 'USA' ), 
    ( '1622', 'Five Lakes Publishing', 'Chicago', 'IL', 'USA' ), 
    ( '1756', 'Ramona Publishers', 'Dallas', 'TX', 'USA' ), 
    ( '9901', 'GGG&G', 'M?nchen', NULL, 'Germany' ), 
    ( '9952', 'Scootney Books', 'New York', 'NY', 'USA' ), 
    ( '9999', 'Lucerne Publishing', 'Paris', NULL, 'France' )
)source(pub_id,pub_name,city,[state],country)  
ON target.pub_id = source.pub_id
WHEN NOT MATCHED BY TARGET THEN
    INSERT ( pub_id,pub_name,city,[state],country )
    VALUES ( pub_id,pub_name,city,[state],country )
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;
END try
BEGIN CATCH
    DECLARE @msg nvarchar(max)=Error_Message(), @severity int = ERROR_SEVERITY(),
            @State int = ERROR_State()
    RAISERROR(@msg, @severity, @state);
    SET NOEXEC ON
END CATCH

這些必須在部署前腳本中執行。為了使數據庫按預期運行，需要某些數據庫屬性設置。通常最好在部署時檢查這些設置，因為它們有時會產生細微的錯誤。排序規則，恢復模型和兼容性級別是顯而易見的，但是還需要檢查其他幾項，例如自動更新統計信息和自動創建統計信息。當然，您需要確定數據庫的正確設置。這些只是DBA可能建議的設置示例。

/* To run this Requires having ALTER permission on the target database. */
PRINT 'Ensuring that the settings are right for this particular database.
DECLARE @AutoCreateStatistics INT,@AutoUpdateStatistics INT,  
@ReadCommittedSnapshot INT,@AutoUpdateStatisticsAsynchronously INT,
@recovery_model_desc nvarchar(120),@compatibility_level int
SELECT  @AutoCreateStatistics=is_auto_create_stats_on,
        @AutoUpdateStatistics=is_auto_update_stats_on,
        @AutoUpdateStatisticsAsynchronously=is_auto_update_stats_async_on,
        @ReadCommittedSnapshot=is_read_committed_snapshot_on,
        @recovery_model_desc=recovery_model_desc, 
        @compatibility_level=[compatibility_level]
FROM    sys.databases
WHERE name='pubs'
IF @AutoCreateStatistics=0
  ALTER DATABASE current
    SET AUTO_CREATE_STATISTICS ON   
DECLARE @AnyErrors INT =@@error
IF @AutoUpdateStatistics=0
  ALTER DATABASE current
    SET AUTO_UPDATE_STATISTICS ON
SELECT @AnyErrors=@AnyErrors+@@Error
IF @AutoUpdateStatisticsAsynchronously=1
  ALTER DATABASE current 
    SET AUTO_UPDATE_STATISTICS_ASYNC OFF
SELECT @AnyErrors=@AnyErrors+@@Error
if @ReadCommittedSnapshot=0
  ALTER DATABASE current
    SET READ_COMMITTED_SNAPSHOT ON WITH rollback immediate
SELECT @AnyErrors=@AnyErrors+@@Error
if @recovery_model_desc<>'SIMPLE'
  ALTER DATABASE CURRENT SET RECOVERY SIMPLE
SELECT @AnyErrors=@AnyErrors+@@Error
/* normally you'd want it at your current product version
but you might, as in this case, need something different 
check product version with 
   SELECT SERVERPROPERTY('ProductVersion');
*/
IF @compatibility_level<>100 --Warning this is specially for old PUBS!
  ALTER DATABASE PUBS --a bad idea anywhere else
    SET COMPATIBILITY_LEVEL = 100 
SELECT @AnyErrors=@AnyErrors+@@Error
--See //docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql?view=sql-server-ver15 before doing this
IF NOT EXISTS (
  SELECT 1 FROM sys.databases 
    WHERE name= Db_Name() 
      AND collation_name='SQL_Latin1_General_CP1_CI_AI')
    ALTER DATABASE CURRENT COLLATE SQL_Latin1_General_CP1_CI_AI
SELECT @AnyErrors=@AnyErrors+@@Error
IF @AnyErrors>0
    SET NOEXEC ON

這可以作為部署前腳本或部署后腳本來完成。此示例腳本僅將版本號和說明作為JSON字符串插入。本文直接將數據與SQL Server數據庫對象相關聯，其中包含該代碼的擴展版本，該版本可以跟蹤版本以及何時應用版本。

PRINT N'Adding a version number'
GO
DECLARE @DatabaseInfo NVARCHAR(3750)
SELECT @DatabaseInfo =
  (
  SELECT Db_Name() AS "Name", '2.4.01 (Change This to your version)' AS "Version",
  'You will need to edit this string' AS "Description",
    GetDate() AS "Modified",
    SUser_Name() AS "by"
  FOR JSON PATH
  );
BEGIN TRY
IF not EXISTS
  (SELECT name, value  FROM fn_listextendedproperty(
     N'Database_Info',default, default, default, default, default, default) )
    EXEC sys.sp_addextendedproperty @name=N'Database_Info', @value=@DatabaseInfo
ELSE
  EXEC sys.sp_Updateextendedproperty  @name=N'Database_Info', @value=@DatabaseInfo
END TRY
BEGIN CATCH
    DECLARE @msg nvarchar(max)=Error_Message(), @severity int = ERROR_SEVERITY(),
            @State int = ERROR_State()
    RAISERROR(@msg, @severity, @state);
    SET NOEXEC ON
END CATCH

相關產品推薦：

SQL Prompt：SQL語法提示工具

SQL Toolbelt：Red Gate產品套包

SQL Monitor：SQL Server監控工具