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

點(diǎn)擊下載SQL Compare試用版

在開發(fā)過程中可能會(huì)發(fā)生由違反約束引起的構(gòu)建錯(cuò)誤，您通常會(huì)在其中構(gòu)建數(shù)據(jù)庫，臨時(shí)禁用約束，批量導(dǎo)入測試數(shù)據(jù)并重新啟用約束，以使它們“受信任”。例如，如果有人在不更新約束定義的情況下弄亂了測試數(shù)據(jù)，那么重新啟用約束會(huì)導(dǎo)致構(gòu)建錯(cuò)誤。如果在現(xiàn)有數(shù)據(jù)庫中部署對表約束的更改，并且以前有一些“不良數(shù)據(jù)”潛入這些表中，則可能會(huì)遇到類似的問題。一旦部署失敗，處理問題就不可避免地會(huì)導(dǎo)致中斷，而有人則逐個(gè)錯(cuò)誤地識別并修復(fù)每行不良數(shù)據(jù)。

本文將向您展示如何在SQL Compare部署期間通過預(yù)先找出所有沖突的行并開發(fā)腳本來修復(fù)數(shù)據(jù)來防止約束沖突“破壞構(gòu)建” 。偵探工作由一些自定義存儲(chǔ)過程完成，這些存儲(chǔ)過程從源數(shù)據(jù)庫中提取約束元數(shù)據(jù)（并將其保存在JSON約束文檔中），然后針對新的約束定義“測試”目標(biāo)數(shù)據(jù)庫中的數(shù)據(jù)。這將生成一個(gè)預(yù)先報(bào)告，列出哪些數(shù)據(jù)行（如果有）將在后續(xù)部署期間導(dǎo)致約束沖突，以及原因。有了這些，您可以開發(fā)腳本來修復(fù)錯(cuò)誤的數(shù)據(jù)，從而避免任何錯(cuò)誤。

在開發(fā)構(gòu)建期間，您可以批量加載數(shù)據(jù)，對其進(jìn)行測試并運(yùn)行腳本以修復(fù)任何沖突的行以及可重新啟用的約束。將更改部署到其他數(shù)據(jù)庫時(shí)，可以使用JSON約束文檔來測試目標(biāo)數(shù)據(jù)庫中的數(shù)據(jù)。您可以使用SQL Compare部署前腳本來修復(fù)數(shù)據(jù)，或者可能更安全，首先以“禁用”狀態(tài)部署任何新約束，然后使用SQL Compare部署后腳本來修復(fù)數(shù)據(jù)然后啟用它們。

為什么要擔(dān)心約束和不良數(shù)據(jù)？

約束以某種方式限制了列或列組合中允許的值。一個(gè)FOREIGN KEY約束限制值，那些已經(jīng)在引用表中存在。一個(gè)UNIQUE約束者禁用副本（如果用戶要求在亞利桑那州目前的銷售稅率，他們想要一個(gè)答案，而不是三個(gè)）。一個(gè)CHECK約束限制值，那些對自己有意義的業(yè)務(wù)。換句話說，這里有保護(hù)您數(shù)據(jù)完整性和一致性的工具。他們對數(shù)據(jù)進(jìn)行監(jiān)管，以確保其中不包含錯(cuò)誤，歧義和誤解。

但是，當(dāng)您在開發(fā)過程中構(gòu)建數(shù)據(jù)庫的新版本或?qū)⒏牟渴鸬浆F(xiàn)有數(shù)據(jù)庫時(shí)，為保護(hù)數(shù)據(jù)而正確添加的約束也會(huì)使您的速度變慢。

在開發(fā)過程中破壞構(gòu)建

在開發(fā)工作期間，一旦使用SQL Compare構(gòu)建了新版本的數(shù)據(jù)庫架構(gòu)，就需要加載測試數(shù)據(jù)。但是，您可能會(huì)遇到問題。首先，F(xiàn)OREIGN KEY約束將迫使您以反向依賴的順序填充表，從不引用其他表的表開始。其次，SQL Server將在插入行時(shí)根據(jù)現(xiàn)有約束嚴(yán)格檢查每一行，并在第一次違反時(shí)中止任務(wù)。
消息547，級別16，狀態(tài)0，第2行
ALTER TABLE語句與CHECK約束“ CK_Employee_MaritalStatus”沖突。數(shù)據(jù)庫“ AdventureWorks”的表“ HumanResources.Employee”的“ MaritalStatus”列中發(fā)生了沖突

看來數(shù)據(jù)在該MaritalStatus列中包含一些意外值。您需要找到并修復(fù)沖突的行，然后重試。

在加載數(shù)據(jù)之前，禁用所有約束和唯一的非聚簇索引會(huì)更加輕松快捷。這等效于管理麻醉劑的數(shù)據(jù)庫。您可以加載數(shù)據(jù)并進(jìn)行修改，而不會(huì)引起抗議，然后重新啟用所有約束，并添加option WITH CHECK，這意味著SQL Server將立即檢查所有數(shù)據(jù)以確保其符合所有現(xiàn)有表約束（您可以d還需要重建任何重建的唯一非聚集索引（如果禁用了它們）。

EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

在重新啟用所有約束之前，首先要列出所有未通過約束的行。在開發(fā)構(gòu)建期間，我們將與目標(biāo)數(shù)據(jù)庫建立連接，因此我們只需運(yùn)行新構(gòu)建，禁用約束，加載數(shù)據(jù)，針對實(shí)時(shí)約束元數(shù)據(jù)對其進(jìn)行測試，并設(shè)計(jì)一個(gè)腳本以在重新啟用約束之前進(jìn)行修復(fù)。

中斷對現(xiàn)有數(shù)據(jù)庫的部署

想象一下，您進(jìn)行了一些開發(fā)更改，從而改善了AdventureWorks中某些表的約束。所有這些都可以與您的測試數(shù)據(jù)集很好地配合，因此您可以使用SQL Compare生成部署腳本，在必要時(shí)進(jìn)行測試和修改，然后將其交付給您的Ops團(tuán)隊(duì)。他們需要在暫存中進(jìn)行全部測試，該暫存具有真實(shí)數(shù)據(jù)的副本，其中許多數(shù)據(jù)必然受到限制并且無法用于開發(fā)。

構(gòu)建失敗，Ops團(tuán)隊(duì)向您發(fā)送了一個(gè)包含錯(cuò)誤的報(bào)告，就像我們之前看到的那樣。似乎某些現(xiàn)有數(shù)據(jù)違反了新的約束定義，但是錯(cuò)誤消息只會(huì)告訴您約束條件失敗的第一行，而不是全部。您修復(fù)了該行，并且每次嘗試啟用約束時(shí)WITH CHECK都會(huì)遇到另一個(gè)錯(cuò)誤。

在這個(gè)階段，您或Ops團(tuán)隊(duì)如果無法完全訪問生產(chǎn)數(shù)據(jù)，則需要逐行檢查所有這些錯(cuò)誤，并提出一個(gè)腳本來修復(fù)數(shù)據(jù)。

或者，如果在運(yùn)行部署之前向開發(fā)團(tuán)隊(duì)提供了將失敗新約束的所有行以及唯一的非聚集索引的列表，則可以避免這種情況。Ops團(tuán)隊(duì)可以針對即將部署的以JSON格式存儲(chǔ)的約束元數(shù)據(jù)測試實(shí)時(shí)數(shù)據(jù)。這將生成報(bào)告，讓開發(fā)人員知道哪些值會(huì)導(dǎo)致問題，以便他們可以在生成以下內(nèi)容之前生成“數(shù)據(jù)清理”腳本來修復(fù)所有約束問題，檢查約束，唯一約束和外鍵約束。發(fā)布。

如何檢查和獲取約束報(bào)告

無論是將數(shù)據(jù)庫更新到新版本，還是保留數(shù)據(jù)，還是從頭開始構(gòu)建新版本，然后加載數(shù)據(jù)，約束錯(cuò)誤，重復(fù)錯(cuò)誤和參考錯(cuò)誤的發(fā)生頻率都令人驚訝。您已經(jīng)部署了新的數(shù)據(jù)庫版本，但是表并沒有改變，但是您面對著一個(gè)充滿紅色的消息窗格。也許有人厭倦了重復(fù)出現(xiàn)的重復(fù)行，合理而合理地收緊了約束。

為了避免所有這些，我們需要運(yùn)行一系列稍有不同的測試，以便進(jìn)行捕獲：

• 錯(cuò)誤的數(shù)據(jù)檢查 -對于可能違反CHECK約束條件的行
• 重復(fù)檢查 –查找違反UNIQUE顧問或非聚集索引的行。
• 關(guān)系完整性檢查 –對于違反FOREIGN KEY約束的行

壞消息是它需要編寫腳本，主要是使用SQL。好消息是，我已經(jīng)為您完成了此任務(wù)，并將其放入我的公共Github存儲(chǔ)庫中。我在一系列簡單文章中描述了它的工作方式：但是數(shù)據(jù)庫在開發(fā)中起作用！防止破壞約束，但是數(shù)據(jù)庫在開發(fā)中起作用了！避免重復(fù)的行，但是數(shù)據(jù)庫在開發(fā)中正常工作！檢查參照完整性。

對于每種類型的檢查，只有兩個(gè)存儲(chǔ)過程可以完成工作。名為的臨時(shí)存儲(chǔ)過程#List*將在系統(tǒng)目錄視圖中查詢索引或約束元數(shù)據(jù)，并將其以JSON格式存儲(chǔ)。名為的相應(yīng)臨時(shí)存儲(chǔ)過程將#Test*執(zhí)行其相應(yīng)#List*過程，運(yùn)行檢查并生成報(bào)告。

例如，獲取有關(guān)本地目標(biāo)數(shù)據(jù)庫中哪些行違反了哪些CHECK約束的詳細(xì)報(bào)告，如下所示：

DECLARE @OurFailedConstraints  NVARCHAR(MAX)
 EXECUTE #TestAllCheckConstraints @TheResult=@OurFailedConstraints OUTPUT
 SELECT @OurFailedConstraints AS theFailedCheckConstraints

• TestLoadedDataForCheckConstraints.sql
• TestLoadedDataForFKConstraints.sql和
• TestLoadedDataForUniqueConstraints.sql

我還在回購中包含了一個(gè)名為ExecuteConstraintsCode.ps1的PowerShell文件，該文件包含一個(gè)Assert-Constraints函數(shù)，一旦完成所有設(shè)置，所有這些檢查將變得更加容易。您只需將以上三個(gè)文件放在目錄中，函數(shù)就會(huì)將它們放起來并執(zhí)行。稍后將顯示一些示例。

一個(gè)簡單的演練：檢查違反約束的情況

讓我們來看一個(gè)簡單的示例，使用SQL Compare可以解決問題。我們將假裝AdventureWorks業(yè)務(wù)告訴我們，一名員工已超出其65小時(shí)病假的年度津貼，而人力資源部門卻不知道?，F(xiàn)有的限制允許0到120小時(shí)，因此需要解決。

開發(fā)人員Dave修改了employee表以解決此問題，并檢入代碼：

ALTER TABLE [HumanResources].[Employee]  WITH CHECK ADD  CONSTRAINT [CK_Employee_SickLeaveHours] CHECK  (([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(65)))

他們檢查排序規(guī)則是否設(shè)置為相同，然后運(yùn)行SQL Compare。它檢測到開發(fā)人員Dave的更改。

PRINT N'Adding constraints to [HumanResources].[Employee]'
GO
ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [CK_Employee_SickLeaveHours] CHECK (([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(65)))
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO

現(xiàn)在，讓我們看看在兩種情況下，他們?nèi)绾味寄塬@得JSON報(bào)告，該報(bào)告將為您提供違反約束條件的行的所有詳細(xì)信息，您可以從中準(zhǔn)備在發(fā)生錯(cuò)誤之前修復(fù)數(shù)據(jù)的遷移腳本。
在開發(fā)構(gòu)建過程中檢查約束是否違反

我們假設(shè)團(tuán)隊(duì)希望從源代碼管理中的腳本開始，在其中包含數(shù)據(jù)的工作數(shù)據(jù)庫。我們假設(shè)他們已經(jīng)在目錄中準(zhǔn)備好了開發(fā)數(shù)據(jù)的副本。請參考此處的操作方法。
該過程如下所示：

部署SQL Server數(shù)據(jù)庫時(shí)，SQL Compare可以通過兩種相當(dāng)不同的方式（即構(gòu)建和同步）用于部署。它可以更改現(xiàn)有目標(biāo)數(shù)據(jù)庫的模式以匹配源。如果開發(fā)人員正在構(gòu)建新數(shù)據(jù)庫，則目標(biāo)數(shù)據(jù)庫為空，因此部署腳本將修改空（model）數(shù)據(jù)庫架構(gòu)，使其與源數(shù)據(jù)庫匹配。如果您要編寫腳本以供常規(guī)使用，請使用SQL Compare CLI，或使用文檔更改，代碼分析檢查等更健壯的過程，請使用SQL Change Automation。

這是一個(gè)非常簡單的PowerShell腳本，用于使用SQL Compare CLI構(gòu)建具有新約束的新數(shù)據(jù)庫。您需要填寫SQL Compare的路徑，并指定從中獲取它的服務(wù)器和數(shù)據(jù)庫。如果您使用集成的安全性$MyUserId和$MyPassword對$null，否則填充它們：

Set-Alias SQLCompare "${env:ProgramFiles(x86)}\Red Gate\SQL Compare 13\sqlcompare.exe" -Scope Script
$MyServerInstance='MyServer'#The SQL Server instance
$MySourceDatabase='MyDatabase' #The name of the database
$MySourceDatabasePath = "$($env:HOMEDRIVE)$($env:HOMEPATH)\Documents\GitHub\$MySourceDatabase"
$MyNewDatabase = 'TargetDatabase'
$MyUserId='MyUserID'  #fill this in if you need credentials
$MyPassword='MyPassword' #fill this in if you need credentials
 #------
if ($MyUserId -ne $NULL)
    {
    SQLCompare /scripts1:$MySourceDatabasePath /username2:$MyUserId /Password2:$MyPassword `
               /database2:$MyNewDatabase /server2:$MyServerInstance /force
    }
else
    {
    SQLCompare /scripts1:$MySourceDatabasePath `
               /database2:$MyNewDatabase /server2:$MyServerInstance /force
    }
 if ($?) {'That went well'}
 else {"That Went Badly (code $LASTEXITCODE)"}

為了避免新外鍵或檢查約束出現(xiàn)錯(cuò)誤，直到我們有足夠的時(shí)間來識別和修復(fù)它們之前，我們需要禁用所有外鍵或檢查約束。

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

SELECT  'Alter Index '+IX.name+ ' ON '+QuoteName(Object_Schema_Name(tabs.object_id))+'.'+QuoteName(tabs.name)+ ' DISABLE'    AS Statement
         FROM sys.tables AS tabs
          INNER JOIN sys.indexes AS IX
            ON IX.object_id = tabs.object_id
      WHERE (IX.is_unique_constraint =1) OR (IX.type=2 AND IX.is_unique=1)

ALTER TABLE [HumanResources].[Employee] WITH NOCHECK ADD CONSTRAINT [CK_Employee_SickLeaveHours]
CHECK (([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(65)))

ALTER INDEX [AK_SalesTaxRate_StateProvinceID_TaxType] ON [Sales].[SalesTaxRate] DISABLE

3.加載數(shù)據(jù)

現(xiàn)在，我們需要獲取數(shù)據(jù)。我們可以使用我作為“ 在SQL Server數(shù)據(jù)庫和服務(wù)器之間復(fù)制所有數(shù)據(jù)”而發(fā)布的腳本。

盡管使用命令行BCP實(shí)用程序的本機(jī)BCP是最快的方法，但這絕不是唯一的方法。我已經(jīng)發(fā)布了很多有關(guān)使用JSON的信息。無論采用哪種數(shù)據(jù)存儲(chǔ)介質(zhì)，都可以使用其大容量加載庫Data.SqlClient.SqlBulkCopy來導(dǎo)入DataTables，Datareaders或數(shù)據(jù)行數(shù)組。用外行的話來說，任何可以表示為表格的東西都可以批量導(dǎo)入。當(dāng)需要“即時(shí)”清理時(shí)，我什至可以使用它導(dǎo)入CSV，然后在完成后將其啟用。

4.測試數(shù)據(jù)是否違反約束

開發(fā)構(gòu)建是最簡單的情況，因?yàn)閳F(tuán)隊(duì)正在檢查已與新版本同步或從新版本構(gòu)建的本地目標(biāo)數(shù)據(jù)庫，但未啟用新的約束或啟用唯一的非聚集索引。

這是一個(gè)相對簡單的過程，因?yàn)槟鸁o需獲取或存儲(chǔ)JSON約束文檔，因?yàn)槟恍铏z查目標(biāo)數(shù)據(jù)庫中的元數(shù)據(jù)并運(yùn)行測試即可。如果您對保存報(bào)告不感興趣，請加載所有臨時(shí)存儲(chǔ)過程并執(zhí)行：

DECLARE @OurFailedConstraints  NVARCHAR(MAX)
 EXECUTE #TestAllCheckConstraints @TheResult=@OurFailedConstraints OUTPUT
 SELECT @OurFailedConstraints AS theFailedCheckConstraints
EXECUTE # #TestAllUniqueConstraints @TheResult=@OurFailedConstraints OUTPUT
 SELECT @OurFailedConstraints AS theFailedCheckConstraints
EXECUTE #TestAllForeignKeyConstraints @TheResult=@OurFailedConstraints OUTPUT
 SELECT @OurFailedConstraints AS theFailedCheckConstraints

[
  {
    "success": "There were 1  check constraints that would fail data and no errors",
    "FailedChecks": [
      {
        "BadData": {
          "RowsFailed": "25",
          "ConstraintName": "[CK_Employee_SickLeaveHours]",
          "ConstraintTable": "[HumanResources].[Employee]",
          "Expression": "([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(65))",
          "BadDataSample": [
            {
              "BusinessEntityID": 1,
              "NationalIDNumber": "295847284",
              "LoginID": "adventure-works\\ken0",
              "JobTitle": "Chief Executive Officer",
              "BirthDate": "1969-01-29",
              "MaritalStatus": "S",
              "Gender": "M",
              "HireDate": "2009-01-14",
              "SalariedFlag": true,
              "VacationHours": 99,
              "SickLeaveHours": 69,
              "CurrentFlag": true,
              "rowguid": "F01251E5-96A3-448D-981E-0F99D789110D",
              "ModifiedDate": "2014-06-30T00:00:00"
            },
…etc…
]

現(xiàn)在，您設(shè)計(jì)了一個(gè)腳本，該腳本將修改所報(bào)告的數(shù)據(jù)行，以使其符合恒定條件，在新建并填充的數(shù)據(jù)庫上運(yùn)行它，然后重新啟用所有外鍵或檢查約束。

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

ALTER INDEX [AK_SalesTaxRate_StateProvinceID_TaxType] ON [Sales].[SalesTaxRate] REBUILD

關(guān)系數(shù)據(jù)庫在結(jié)構(gòu)（模式）和數(shù)據(jù)之間有很強(qiáng)的區(qū)別。其他類型的數(shù)據(jù)庫并非如此，這可能會(huì)引起混亂。盡管SQL Compare盡最大努力將目標(biāo)數(shù)據(jù)庫中的現(xiàn)有數(shù)據(jù)保留下來，但它無法做任何事情來使其與新的約束或唯一索引匹配，并且無法保證該數(shù)據(jù)現(xiàn)在有效。

無論您是否連接到目標(biāo)數(shù)據(jù)庫，我們都將討論該過程如何工作。這次，我們將看到如何使用PowerShell獲取JSON報(bào)告，該報(bào)告將預(yù)先告知正在部署的更改對數(shù)據(jù)的全部影響。
在連接的目標(biāo)數(shù)據(jù)庫中查找違規(guī)行

使用直接連接到目標(biāo)數(shù)據(jù)庫運(yùn)行同步的情況更簡單。再次，我們通過構(gòu)建一個(gè)空數(shù)據(jù)庫然后禁用其約束和唯一索引來進(jìn)行統(tǒng)計(jì)。但是，此數(shù)據(jù)庫現(xiàn)在成為與目標(biāo)數(shù)據(jù)和現(xiàn)有數(shù)據(jù)同步的源。如前所述，SQL Compare將生成一個(gè)部署腳本，當(dāng)將其應(yīng)用于目標(biāo)時(shí)，將使約束和唯一索引保持“關(guān)閉”狀態(tài)。完成此操作后，過程將像以前一樣進(jìn)行：

Assert-Constraints @(
 <# list of connection strings for each of the SQLservers that you need to execute code on #>
    @{
        'ServerConnectionString' = 'Server=MyServer;User Id=MyName;Persist Security Info=False';
        #and a list of databases you wish the string-based (EG JSON report) from. 
        'Databases' = @('Shadrak', 'Meshak', 'Abednego'); # do all these databases
        'RootDirectoryForOutputFile' = "$env:USERPROFILE\JSONDocumentation"; #the directory you want it in as subdirectories
        'minimumCompatibilityLevel' = 130; #specify the minimum database compatibility level. We check!
        'ScriptDirectory' = 'D:\Github\TestOutConstraints'; # where you store the project SQL files 
        'fileType' = 'json'; #the filetype of the files you save for each database for reports
        # and now a list of all the temporary stored procedures you'll need.
        'setupScripts' = @(
          'ListAllUniqueIndexes.sql', 'ListAllForeignKeyConstraints.sql', 'ListAllCheckConstraints.sql',
          'TestAllUniqueIndexes.sql', 'TestAllForeignKeyConstraints.sql', 'TestAllCheckConstraints.sql');
    <#This lot are used process 1- for testing the loaded data to ensure it complies with the constraints#>
        'FilesToExecute' = @(
            @{
                'scriptFileName' = 'TestLoadedDataForCheckConstraints.sql'; `
                'OutputFileName' = 'CheckConstraintsReport'
            },
            @{
                'scriptFileName' = 'TestLoadedDataForUniqueConstraints.sql'; `
                'OutputFileName' = 'UniqueConstraintsReport'
            },
            @{
                'scriptFileName' = 'TestLoadedDataForFKConstraints.sql'; `
                'OutputFileName' = 'FKConstraintsReport'
            }
        )
        'TearDownScripts' = @();
    }
)

有了報(bào)告，您就可以開發(fā)數(shù)據(jù)遷移腳本，運(yùn)行它并重新啟用所有約束和索引，以使它們受到信任。

使用JSON約束文件查找違規(guī)行

當(dāng)然，有時(shí)候您無法開發(fā)數(shù)據(jù)遷移腳本并無法以這種方式來部署更改，而無法直接連接到目標(biāo)，例如在部署到生產(chǎn)時(shí)。同樣，在某些情況下，開發(fā)團(tuán)隊(duì)也無法訪問Staging，而需要將部署腳本傳遞給Ops團(tuán)隊(duì)進(jìn)行測試。

在這種情況下，開發(fā)團(tuán)隊(duì)將根據(jù)數(shù)據(jù)庫的新版本創(chuàng)建必要的JSON約束腳本文件，而Ops團(tuán)隊(duì)將使用該文件來測試當(dāng)前版本的Staging中的數(shù)據(jù)，并創(chuàng)建數(shù)據(jù)遷移腳本或發(fā)送向開發(fā)團(tuán)隊(duì)返回報(bào)告，以便他們能夠做到。

Assert-Constraints @(
 <# list of connection strings for each of the SQLservers that you need to execute code on #>
  @{
    'ServerConnectionString' = 'Server=MyServer;User Id=PhilFactor;Persist Security Info=False';
    #and a list of databases you wish the string-based (EG JSON report) from. 
    'Databases' = @('NewAdventureworks2016'); # the database we do it on
    'RootDirectoryForOutputFile' = "$env:USERPROFILE\ConstraintDemo"; #the directory you want it in as subdirectories
    'minimumCompatibilityLevel' = 130; #specify the minimum database compatibility level. We check!
    'ScriptDirectory' = 'D:\Github\TestOutConstraints'; # where you store the project SQL files 
    'fileType' = 'json'; #the filetype of the files you save for each database for reports
    # and now a list of all the temporary stored procedures you'll need.
    'setupScripts' = @('ListAllUniqueIndexes.sql', 'ListAllForeignKeyConstraints.sql', 'ListAllCheckConstraints.sql',
      'TestAllUniqueIndexes.sql', 'TestAllForeignKeyConstraints.sql', 'TestAllCheckConstraints.sql');
    
    <#Save the lists of constraints as defined in the database #>
    'FilesToExecute' = @(
      @{
        'scriptFileName' = 'GetListofAllForeignKeyConstraints.sql'; `
        'OutputFileName' = 'FKConstraintsList.JSON'
      },
      @{
        'scriptFileName' = 'GetListofAllUniqueConstraints.sql'; `
        'OutputFileName' = 'UniqueConstraintsList.JSON'
      },
      @{
        'scriptFileName' = 'GetListofAllCheckConstraints.sql'; `
        'OutputFileName' = 'CheckConstraintsList.JSON'
      }
    )
    'TearDownScripts' = @();
  }
)

Assert-Constraints @(
 <# list of connection strings for each of the SQLservers that you need to execute code on #>
  @{
    'ServerConnectionString' = 'Server=MyOtherServer;User Id=PhilFactor;Persist Security Info=False';
    #and a list of databases you wish the string-based (EG JSON report) from. 
    'Databases' = @('Adventureworks2016'); # do all these databases
    'RootDirectoryForOutputFile' = "$env:USERPROFILE\ConstraintDemo"; #the directory you want it in as subdirectories
    'minimumCompatibilityLevel' = 130; #specify the minimum database compatibility level. We check!
    'ScriptDirectory' = 'D:\Github\TestOutConstraints'; # where you store the project SQL files 
    'fileType' = 'json'; #the filetype of the files you save for each database for reports
    # and now a list of all the temporary stored procedures you'll need.
    'setupScripts' = @('ListAllUniqueIndexes.sql', 'ListAllForeignKeyConstraints.sql', 'ListAllCheckConstraints.sql',
      'TestAllUniqueIndexes.sql', 'TestAllForeignKeyConstraints.sql', 'TestAllCheckConstraints.sql');
    
    <#Save the lists of constraints as defined in the database #>
    'FilesToExecute' = @(
          @{
            'scriptFileName' = 'TestJSONForUniqueIndexes.sql'; `
            'input' = @{ 'FileName' = 'UniqueConstraintsList.json' }; `
            'OutputFileName' = 'DelayedUniqueConstraintsReport'
          },
            @{
            'scriptFileName' = 'TestJSONForCheckConstraints.sql'; `
            'input' = @{ 'FileName' = 'CheckConstraintsList.json' }; `
            'OutputFileName' = 'DelayedCheckConstraintsReport'
          },
            @{
            'scriptFileName' = 'TestJSONForForeignKeyConstraints.sql'; `
            'input' = @{ 'FileName' = 'FKConstraintsList.json' }; `
            'OutputFileName' = 'DelayedFKConstraintsReport'
          }    )
            'TearDownScripts' = @();
          }
)

現(xiàn)在在Adventureworks2016數(shù)據(jù)庫上運(yùn)行腳本S：\ work \ Github \ TestOutConstraints \ TestJSONForUniqueIndexes.sql
警告：有2個(gè)索引與數(shù)據(jù)不匹配且沒有錯(cuò)誤
現(xiàn)在在Adventureworks2016數(shù)據(jù)庫上運(yùn)行腳本D：\ Github \ TestOutConstraints \ TestJSONForCheckConstraints.sql
警告：有1個(gè)檢查約束，它們將使數(shù)據(jù)失敗并且沒有錯(cuò)誤
現(xiàn)在在Adventureworks2016數(shù)據(jù)庫上運(yùn)行腳本D：\ Github \ TestOutConstraints \ TestJSONForForeignKeyConstraints.sql

這里有違反約束條件的行的完整報(bào)告。我向檢查約束違例添加了幾個(gè)索引重復(fù)項(xiàng)：

[
  {
    "success": "There were 1  check constraints that would fail data and no errors",
    "FailedChecks": [
      {
        "BadData": {
          "RowsFailed": "25",
          "ConstraintName": "[CK_Employee_SickLeaveHours]",
          "ConstraintTable": "[HumanResources].[Employee]",
          "Expression": "([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(65))",
          "BadDataSample": [
            {
              "BusinessEntityID": 1,
              "NationalIDNumber": "295847284",
              "LoginID": "adventure-works\\ken0",
              "JobTitle": "Chief Executive Officer",
              "BirthDate": "1969-01-29",
              "MaritalStatus": "S",
              "Gender": "M",
              "HireDate": "2009-01-14",
              "SalariedFlag": true,
              "VacationHours": 99,
              "SickLeaveHours": 69,
              "CurrentFlag": true,
              "rowguid": "F01251E5-96A3-448D-981E-0F99D789110D",
              "ModifiedDate": "2014-06-30T00:00:00"
            },
            {
              "BusinessEntityID": 4,
              "NationalIDNumber": "112457891",
              "LoginID": "adventure-works\\rob0",
              "OrganizationNode": "/1/1/1/",
              "OrganizationLevel": 3,
              "JobTitle": "Senior Tool Designer",
              "BirthDate": "1974-12-23",
              "MaritalStatus": "S",
              "Gender": "M",
              "HireDate": "2007-12-05",
              "SalariedFlag": false,
              "VacationHours": 48,
              "SickLeaveHours": 80,
              "CurrentFlag": true,
              "rowguid": "59747955-87B8-443F-8ED4-F8AD3AFDF3A9",
              "ModifiedDate": "2014-06-30T00:00:00"
            },
            {
              "BusinessEntityID": 88,
              "NationalIDNumber": "294148271",
              "LoginID": "adventure-works\\betsy0",
              "OrganizationNode": "/3/1/8/1/",
              "OrganizationLevel": 4,
              "JobTitle": "Production Technician - WC10",
              "BirthDate": "1966-12-17",
              "MaritalStatus": "S",
              "Gender": "F",
              "HireDate": "2009-12-18",
              "SalariedFlag": false,
              "VacationHours": 99,
              "SickLeaveHours": 69,
              "CurrentFlag": true,
              "rowguid": "EBCDBA1C-6C1D-4D36-90F7-1893755C85E3",
              "ModifiedDate": "2014-06-30T00:00:00"
            }
          ]
        }
      }
    ]
  }
]

運(yùn)行自動(dòng)化部署

在自動(dòng)部署過程中部署新約束時(shí)，您可以選擇何時(shí)修復(fù)數(shù)據(jù)。

您可以將數(shù)據(jù)遷移腳本作為SQL Compare（或SQL Change Automation）預(yù)部署腳本來運(yùn)行。請小心遵守部署前代碼的規(guī)則：對錯(cuò)誤進(jìn)行自己的回滾，并遵守在出現(xiàn)錯(cuò)誤時(shí)進(jìn)行設(shè)置的約定NOEXEC。但是，如果運(yùn)行了部署前腳本，但隨后的部署失敗了怎么辦？

如前所述，替代方法是將更改后的約束和唯一索引提交到處于禁用狀態(tài)的源代碼控制。在這種情況下，您可以使用SQL Compare或SQL Change Automation 將數(shù)據(jù)遷移腳本作為部署后腳本的一部分運(yùn)行，然后再重新啟用約束和索引并檢查它們是否都“受信任”。部署完成。

摘要

即使源數(shù)據(jù)庫和目標(biāo)數(shù)據(jù)庫具有相同的表名和列，也無法保證可以成功將數(shù)據(jù)從一個(gè)復(fù)制到另一個(gè)。如果您要從其他來源（例如外部應(yīng)用程序）加載數(shù)據(jù)，則所有選擇均無效。為什么是這樣？這是由于目標(biāo)數(shù)據(jù)庫中的限制而導(dǎo)致的，這些數(shù)據(jù)庫選擇了重復(fù)項(xiàng)，不良數(shù)據(jù)以及參照完整性問題。這些必須解決，否則您將始終面臨性能缺陷。您可能還會(huì)因數(shù)據(jù)問題而遭受更嚴(yán)重的打擊。如果您作為開發(fā)人員沒有直接訪問數(shù)據(jù)的權(quán)限，或者作為操作人員沒有時(shí)間或?qū)ｉT知識來完成這項(xiàng)工作，那一切都會(huì)變得更糟。

這段代碼旨在防止發(fā)生此類問題。它根據(jù)目標(biāo)數(shù)據(jù)庫中的約束檢查數(shù)據(jù)，并為您提供在啟用約束之前或在啟動(dòng)自動(dòng)化過程（如自動(dòng)化構(gòu)建）之前需要修復(fù)的數(shù)據(jù)列表。

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

SQL Prompt：SQL語法提示工具

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

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