SQL Compare是一款比較和同步SQL Server數(shù)據(jù)庫結(jié)構(gòu)的工具。現(xiàn)有超過150,000的數(shù)據(jù)庫管理員、開發(fā)人員和測試人員在使用它。當(dāng)測試本地數(shù)據(jù)庫，暫存或激活遠(yuǎn)程服務(wù)器的數(shù)據(jù)庫時，SQL Compare將分配數(shù)據(jù)庫的過程自動化。

點擊下載SQL Compare試用版

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

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

自定義部署腳本有哪些優(yōu)點？

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

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

從腳本目錄部署

SQL Compare比較兩個數(shù)據(jù)庫，即源數(shù)據(jù)庫和目標(biāo)數(shù)據(jù)庫，并自動生成一個同步腳本，該腳本將修改目標(biāo)架構(gòu)，使其與源架構(gòu)匹配。當(dāng)您使用腳本目錄作為源時，SQL Compare允許您將額外的SQL腳本附加到它生成的同步腳本的開頭或結(jié)尾。

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

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

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

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

每個腳本都必須是可重新運行且冪等的。它必須能夠在所有預(yù)期的目標(biāo)環(huán)境中運行，并且必須支持整個數(shù)據(jù)庫環(huán)境中數(shù)據(jù)庫排序規(guī)則的任何可能差異。

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

這些自定義腳本的內(nèi)容不是構(gòu)建的一部分，因為它們直到比較完成后才執(zhí)行，因此它們對SQL Compare自動生成的同步腳本沒有影響。在生成同步腳本之后但在執(zhí)行之前，使用預(yù)部署腳本來更改目標(biāo)數(shù)據(jù)庫。執(zhí)行同步腳本后，將使用部署后腳本來更改目標(biāo)。

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

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

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

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

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

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

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

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

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

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

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

除非對這些腳本進行了徹底的測試，否則只有在執(zhí)行部署腳本后才會發(fā)現(xiàn)錯誤，然后它們才會破壞構(gòu)建，從而導(dǎo)致問題。在使用之前，請花一些時間對其進行徹底測試。

源代碼控制和自定義腳本

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

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

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

例子

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

在表中存儲數(shù)據(jù)

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

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

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

/* 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字符串插入。本文直接將數(shù)據(jù)與SQL Server數(shù)據(jù)庫對象相關(guān)聯(lián)，其中包含該代碼的擴展版本，該版本可以跟蹤版本以及何時應(yīng)用版本。

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

相關(guān)產(chǎn)品推薦：

SQL Prompt：SQL語法提示工具

SQL Toolbelt：Red Gate產(chǎn)品套包

SQL Monitor：SQL Server監(jiān)控工具