SQL Compare使用教程:如何避免在SQL Server部署过程中违反约束-控件新闻-慧都网

SQL Compare使用教程:如何避免在SQL Server部署过程中违反约束

翻译|使用教程|编辑：杨鹏连|2020-08-13 11:54:37.867|阅读 344 次

概述：本文将向您展示如何在SQL Compare部署期间通过预先找出所有冲突的行并开发脚本来修复数据来防止约束冲突“破坏构建” 。

SQL Compare是一款比较和同步SQL Server数据库结构的工具。现有超过150,000的数据库管理员、开发人员和测试人员在使用它。当测试本地数据库，暂存或激活远程服务器的数据库时，SQL Compare将分配数据库的过程自动化。

点击下载SQL Compare试用版

在开发过程中可能会发生由违反约束引起的构建错误，您通常会在其中构建数据库，临时禁用约束，批量导入测试数据并重新启用约束，以使它们“受信任”。例如，如果有人在不更新约束定义的情况下弄乱了测试数据，那么重新启用约束会导致构建错误。如果在现有数据库中部署对表约束的更改，并且以前有一些“不良数据”潜入这些表中，则可能会遇到类似的问题。一旦部署失败，处理问题就不可避免地会导致中断，而有人则逐个错误地识别并修复每行不良数据。

本文将向您展示如何在SQL Compare部署期间通过预先找出所有冲突的行并开发脚本来修复数据来防止约束冲突“破坏构建” 。侦探工作由一些自定义存储过程完成，这些存储过程从源数据库中提取约束元数据（并将其保存在JSON约束文档中），然后针对新的约束定义“测试”目标数据库中的数据。这将生成一个预先报告，列出哪些数据行（如果有）将在后续部署期间导致约束冲突，以及原因。有了这些，您可以开发脚本来修复错误的数据，从而避免任何错误。

在开发构建期间，您可以批量加载数据，对其进行测试并运行脚本以修复任何冲突的行以及可重新启用的约束。将更改部署到其他数据库时，可以使用JSON约束文档来测试目标数据库中的数据。您可以使用SQL Compare部署前脚本来修复数据，或者可能更安全，首先以“禁用”状态部署任何新约束，然后使用SQL Compare部署后脚本来修复数据然后启用它们。

为什么要担心约束和不良数据？

约束以某种方式限制了列或列组合中允许的值。一个FOREIGN KEY约束限制值，那些已经在引用表中存在。一个UNIQUE约束者禁用副本（如果用户要求在亚利桑那州目前的销售税率，他们想要一个答案，而不是三个）。一个CHECK约束限制值，那些对自己有意义的业务。换句话说，这里有保护您数据完整性和一致性的工具。他们对数据进行监管，以确保其中不包含错误，歧义和误解。

但是，当您在开发过程中构建数据库的新版本或将更改部署到现有数据库时，为保护数据而正确添加的约束也会使您的速度变慢。

在开发过程中破坏构建

在开发工作期间，一旦使用SQL Compare构建了新版本的数据库架构，就需要加载测试数据。但是，您可能会遇到问题。首先，FOREIGN KEY约束将迫使您以反向依赖的顺序填充表，从不引用其他表的表开始。其次，SQL Server将在插入行时根据现有约束严格检查每一行，并在第一次违反时中止任务。
消息547，级别16，状态0，第2行
ALTER TABLE语句与CHECK约束“ CK_Employee_MaritalStatus”冲突。数据库“ AdventureWorks”的表“ HumanResources.Employee”的“ MaritalStatus”列中发生了冲突

看来数据在该MaritalStatus列中包含一些意外值。您需要找到并修复冲突的行，然后重试。

在加载数据之前，禁用所有约束和唯一的非聚簇索引会更加轻松快捷。这等效于管理麻醉剂的数据库。您可以加载数据并进行修改，而不会引起抗议，然后重新启用所有约束，并添加option WITH CHECK，这意味着SQL Server将立即检查所有数据以确保其符合所有现有表约束（您可以d还需要重建任何重建的唯一非聚集索引（如果禁用了它们）。

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

即便是最平静的DBA，这仍然是一个恐惧时刻，它害怕看到指示违反约束的错误。如果幸运的话，一切都会好起来的，每个约束都已启用且受信任。如果运行不顺利，SQL Server会将约束标记为“不可信 ”。如果您决定跳过该检查，则在使用该WITH NOCHECK选项启用约束时，速度会快很多，但约束将不受信任。这是一个坏消息，因为尽管所有启用的约束都将应用于新插入的行，但优化器在为访问表的查询设计执行计划时不能使用不受信任的约束。

在重新启用所有约束之前，首先要列出所有未通过约束的行。在开发构建期间，我们将与目标数据库建立连接，因此我们只需运行新构建，禁用约束，加载数据，针对实时约束元数据对其进行测试，并设计一个脚本以在重新启用约束之前进行修复。

中断对现有数据库的部署

想象一下，您进行了一些开发更改，从而改善了AdventureWorks中某些表的约束。所有这些都可以与您的测试数据集很好地配合，因此您可以使用SQL Compare生成部署脚本，在必要时进行测试和修改，然后将其交付给您的Ops团队。他们需要在暂存中进行全部测试，该暂存具有真实数据的副本，其中许多数据必然受到限制并且无法用于开发。

构建失败，Ops团队向您发送了一个包含错误的报告，就像我们之前看到的那样。似乎某些现有数据违反了新的约束定义，但是错误消息只会告诉您约束条件失败的第一行，而不是全部。您修复了该行，并且每次尝试启用约束时WITH CHECK都会遇到另一个错误。

在这个阶段，您或Ops团队如果无法完全访问生产数据，则需要逐行检查所有这些错误，并提出一个脚本来修复数据。

或者，如果在运行部署之前向开发团队提供了将失败新约束的所有行以及唯一的非聚集索引的列表，则可以避免这种情况。Ops团队可以针对即将部署的以JSON格式存储的约束元数据测试实时数据。这将生成报告，让开发人员知道哪些值会导致问题，以便他们可以在生成以下内容之前生成“数据清理”脚本来修复所有约束问题，检查约束，唯一约束和外键约束。发布。

如何检查和获取约束报告

无论是将数据库更新到新版本，还是保留数据，还是从头开始构建新版本，然后加载数据，约束错误，重复错误和参考错误的发生频率都令人惊讶。您已经部署了新的数据库版本，但是表并没有改变，但是您面对着一个充满红色的消息窗格。也许有人厌倦了重复出现的重复行，合理而合理地收紧了约束。

为了避免所有这些，我们需要运行一系列稍有不同的测试，以便进行捕获：

错误的数据检查 -对于可能违反CHECK约束条件的行
重复检查 –查找违反UNIQUE顾问或非聚集索引的行。
关系完整性检查 –对于违反FOREIGN KEY约束的行

我们从源数据库中提取约束列表作为JSON约束文件，然后使用它来测试数据。我们将测试结果存储在JSON报告文件中。

坏消息是它需要编写脚本，主要是使用SQL。好消息是，我已经为您完成了此任务，并将其放入我的公共Github存储库中。我在一系列简单文章中描述了它的工作方式：但是数据库在开发中起作用！防止破坏约束，但是数据库在开发中起作用了！避免重复的行，但是数据库在开发中正常工作！检查参照完整性。

对于每种类型的检查，只有两个存储过程可以完成工作。名为的临时存储过程#List*将在系统目录视图中查询索引或约束元数据，并将其以JSON格式存储。名为的相应临时存储过程将#Test*执行其相应#List*过程，运行检查并生成报告。

例如，获取有关本地目标数据库中哪些行违反了哪些CHECK约束的详细报告，如下所示：

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

在我的GitHub存储库中，我通过提供三个脚本（每个检查类型对应一个脚本）使事情变得尽可能简单：

TestLoadedDataForCheckConstraints.sql
TestLoadedDataForFKConstraints.sql和
TestLoadedDataForUniqueConstraints.sql

每个人都在其两个关联的存储过程中执行代码。测试完成后，您将获得三个JSON报告，其中涉及需要完成多少工作才能清理数据。

我还在回购中包含了一个名为ExecuteConstraintsCode.ps1的PowerShell文件，该文件包含一个Assert-Constraints函数，一旦完成所有设置，所有这些检查将变得更加容易。您只需将以上三个文件放在目录中，函数就会将它们放起来并执行。稍后将显示一些示例。

一个简单的演练：检查违反约束的情况

让我们来看一个简单的示例，使用SQL Compare可以解决问题。我们将假装AdventureWorks业务告诉我们，一名员工已超出其65小时病假的年度津贴，而人力资源部门却不知道。现有的限制允许0到120小时，因此需要解决。

开发人员Dave修改了employee表以解决此问题，并检入代码：

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

不幸的是，Dave心不在a，他忘了签入所需的迁移脚本，以处理超过65小时的任何现有数据，然后消失在敏捷会议中。在缺席的情况下，并且没有意识到问题或其含义，AdventureWorks开发人员决定使用SQL Compare部署更改。他们将源设置为源控制目录，在这种情况下，目标是其AdventureWorks的暂存副本，其中包含所有测试数据。同样，如果他们只需要从头开始构建，它可能是一个空数据库。

他们检查排序规则是否设置为相同，然后运行SQL Compare。它检测到开发人员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

他们继续前进并进行部署。有开发商戴维添加迁移脚本，以确保最大SickLeaveHours的HumanResources.Employee是小于或等于65，然后一切都会进展顺利，但他没有，并部署失败：

从开始构建期间，在加载测试数据后，他们在重新启用约束时会遇到类似的错误。

现在，让我们看看在两种情况下，他们如何都能获得JSON报告，该报告将为您提供违反约束条件的行的所有详细信息，您可以从中准备在发生错误之前修复数据的迁移脚本。
在开发构建过程中检查约束是否违反

我们假设团队希望从源代码管理中的脚本开始，在其中包含数据的工作数据库。我们假设他们已经在目录中准备好了开发数据的副本。请参考此处的操作方法。
该过程如下所示：

1.建立数据库

部署SQL Server数据库时，SQL Compare可以通过两种相当不同的方式（即构建和同步）用于部署。它可以更改现有目标数据库的模式以匹配源。如果开发人员正在构建新数据库，则目标数据库为空，因此部署脚本将修改空（model）数据库架构，使其与源数据库匹配。如果您要编写脚本以供常规使用，请使用SQL Compare CLI，或使用文档更改，代码分析检查等更健壮的过程，请使用SQL Change Automation。

这是一个非常简单的PowerShell脚本，用于使用SQL Compare CLI构建具有新约束的新数据库。您需要填写SQL Compare的路径，并指定从中获取它的服务器和数据库。如果您使用集成的安全性$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)"}

2.禁用约束和唯一索引

为了避免新外键或检查约束出现错误，直到我们有足够的时间来识别和修复它们之前，我们需要禁用所有外键或检查约束。

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

在这种情况下，当我们使用SQL Compare进行构建时，在上一阶段，它将确保在同步结束时它们处于该状态。完成测试并修复数据后，您将有一个自动化的步骤来重新启用约束并重建索引。

3.加载数据

现在，我们需要获取数据。我们可以使用我作为“ 在SQL Server数据库和服务器之间复制所有数据”而发布的脚本。

尽管使用命令行BCP实用程序的本机BCP是最快的方法，但这绝不是唯一的方法。我已经发布了很多有关使用JSON的信息。无论采用哪种数据存储介质，都可以使用其大容量加载库Data.SqlClient.SqlBulkCopy来导入DataTables，Datareaders或数据行数组。用外行的话来说，任何可以表示为表格的东西都可以批量导入。当需要“即时”清理时，我什至可以使用它导入CSV，然后在完成后将其启用。

4.测试数据是否违反约束

开发构建是最简单的情况，因为团队正在检查已与新版本同步或从新版本构建的本地目标数据库，但未启用新的约束或启用唯一的非聚集索引。

这是一个相对简单的过程，因为您无需获取或存储JSON约束文档，因为您只需检查目标数据库中的元数据并运行测试即可。如果您对保存报告不感兴趣，请加载所有临时存储过程并执行：

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

另外，您将使用PowerShell运行检查，如稍后将演示的那样，以将约束更改部署到具有现有数据的数据库。无论哪种方式，从生成的JSON报告中，您都会立即看到在启用约束并重建这些唯一约束和唯一非聚集索引时会引起问题的任何数据！

[
  {
    "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…
]

5.修复数据，启用约束和索引

现在，您设计了一个脚本，该脚本将修改所报告的数据行，以使其符合恒定条件，在新建并填充的数据库上运行它，然后重新启用所有外键或检查约束。

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

您可以UNIQUE使用关键字REBUILD而不是启用索引DISABLE。需要小心，特别是因为某些开发人员可能已经将该DISABLE关键字用作删除索引的一种懒惰方式！

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

测试和修复目标数据库中的现有数据

关系数据库在结构（模式）和数据之间有很强的区别。其他类型的数据库并非如此，这可能会引起混乱。尽管SQL Compare尽最大努力将目标数据库中的现有数据保留下来，但它无法做任何事情来使其与新的约束或唯一索引匹配，并且无法保证该数据现在有效。

无论您是否连接到目标数据库，我们都将讨论该过程如何工作。这次，我们将看到如何使用PowerShell获取JSON报告，该报告将预先告知正在部署的更改对数据的全部影响。
在连接的目标数据库中查找违规行

使用直接连接到目标数据库运行同步的情况更简单。再次，我们通过构建一个空数据库然后禁用其约束和唯一索引来进行统计。但是，此数据库现在成为与目标数据和现有数据同步的源。如前所述，SQL Compare将生成一个部署脚本，当将其应用于目标时，将使约束和唯一索引保持“关闭”状态。完成此操作后，过程将像以前一样进行：

这是用于测试所有检查约束，外键约束和唯一索引的PowerShell代码。没有输入，因为脚本可以从同步数据库中获取所有元数据：

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' = @();
    }
)

在此示例中，有三个数据库，分别位于MyServer上的Shedrak，Meshak和Abednego，它们已升级到最新版本，但禁用了约束。如果启用了约束并且使用启用了禁用的唯一索引，您将获得有关所有问题的报告。WITH CHECKREBUILD

有了报告，您就可以开发数据迁移脚本，运行它并重新启用所有约束和索引，以使它们受到信任。

使用JSON约束文件查找违规行

当然，有时候您无法开发数据迁移脚本并无法以这种方式来部署更改，而无法直接连接到目标，例如在部署到生产时。同样，在某些情况下，开发团队也无法访问Staging，而需要将部署脚本传递给Ops团队进行测试。

在这种情况下，开发团队将根据数据库的新版本创建必要的JSON约束脚本文件，而Ops团队将使用该文件来测试当前版本的Staging中的数据，并创建数据迁移脚本或发送向开发团队返回报告，以便他们能够做到。

要获取JSON约束脚本文件，我们可以在现有级别的现有构建数据库上以新级别运行以下PowerShell。如果没有，则可以从源代码管理目录构建一个空数据库。唯一的不同是这次我们只是将约束列表保存到JSON中：

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' = @();
  }
)

这是生成的JSON约束文件：

Ops团队现在可以对照目标数据库中的数据检查它们：

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' = @();
          }
)

这是输出消息：

现在在Adventureworks2016数据库上运行脚本S：\ work \ Github \ TestOutConstraints \ TestJSONForUniqueIndexes.sql
警告：有2个索引与数据不匹配且没有错误
现在在Adventureworks2016数据库上运行脚本D：\ Github \ TestOutConstraints \ TestJSONForCheckConstraints.sql
警告：有1个检查约束，它们将使数据失败并且没有错误
现在在Adventureworks2016数据库上运行脚本D：\ Github \ TestOutConstraints \ TestJSONForForeignKeyConstraints.sql

这里有违反约束条件的行的完整报告。我向检查约束违例添加了几个索引重复项：

[
  {
    "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"
            }
          ]
        }
      }
    ]
  }
]

Ops团队本质上将其作为部署前脚本运行结果数据迁移脚本，然后重新运行数据检查。希望不会再有违反的行被报告，并且他们可以继续部署架构更改，以使用新的约束定义将Staging升级到新版本。

运行自动化部署

在自动部署过程中部署新约束时，您可以选择何时修复数据。

您可以将数据迁移脚本作为SQL Compare（或SQL Change Automation）预部署脚本来运行。请小心遵守部署前代码的规则：对错误进行自己的回滚，并遵守在出现错误时进行设置的约定NOEXEC。但是，如果运行了部署前脚本，但随后的部署失败了怎么办？

如前所述，替代方法是将更改后的约束和唯一索引提交到处于禁用状态的源代码控制。在这种情况下，您可以使用SQL Compare或SQL Change Automation 将数据迁移脚本作为部署后脚本的一部分运行，然后再重新启用约束和索引并检查它们是否都“受信任”。部署完成。

摘要

即使源数据库和目标数据库具有相同的表名和列，也无法保证可以成功将数据从一个复制到另一个。如果您要从其他来源（例如外部应用程序）加载数据，则所有选择均无效。为什么是这样？这是由于目标数据库中的限制而导致的，这些数据库选择了重复项，不良数据以及参照完整性问题。这些必须解决，否则您将始终面临性能缺陷。您可能还会因数据问题而遭受更严重的打击。如果您作为开发人员没有直接访问数据的权限，或者作为操作人员没有时间或专门知识来完成这项工作，那一切都会变得更糟。

这段代码旨在防止发生此类问题。它根据目标数据库中的约束检查数据，并为您提供在启用约束之前或在启动自动化过程（如自动化构建）之前需要修复的数据列表。