他們繼續前進并進行部署。有開發商戴維添加遷移腳本，以確保最大SickLeaveHours的HumanResources.Employee是小于或等于65，然后一切都會進展順利，但他沒有，并部署失?。?/span>
從開始構建期間，在加載測試數據后，他們在重新啟用約束時會遇到類似的錯誤。
現在，讓我們看看在兩種情況下，他們如何都能獲得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 將數據遷移腳本作為部署后腳本的一部分運行，然后再重新啟用約束和索引并檢查它們是否都“受信任”。部署完成。
摘要
即使源數據庫和目標數據庫具有相同的表名和列，也無法保證可以成功將數據從一個復制到另一個。如果您要從其他來源（例如外部應用程序）加載數據，則所有選擇均無效。為什么是這樣？這是由于目標數據庫中的限制而導致的，這些數據庫選擇了重復項，不良數據以及參照完整性問題。這些必須解決，否則您將始終面臨性能缺陷。您可能還會因數據問題而遭受更嚴重的打擊。如果您作為開發人員沒有直接訪問數據的權限，或者作為操作人員沒有時間或專門知識來完成這項工作，那一切都會變得更糟。
這段代碼旨在防止發生此類問題。它根據目標數據庫中的約束檢查數據，并為您提供在啟用約束之前或在啟動自動化過程（如自動化構建）之前需要修復的數據列表。
相關產品推薦：
SQL Prompt：SQL語法提示工具
SQL Toolbelt：Red Gate產品套包
SQL Monitor：SQL Server監控工具
標簽：
本站文章除注明轉載外，均為本站原創或翻譯。歡迎任何形式的轉載，但請務必注明出處、不得修改原文相關鏈接，如果存在內容上的異議請郵件反饋至chenjj@ke049m.cn
文章轉載自：
半島外圍網上直營 
11次
相關產品
正式授權
最新文章 
半島外圍網上直營相關的文章 
微信
在線咨詢
渝公網安備
50010702500608號
客服熱線