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

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

Phil Factor演示了如何使用XML argfiles將參數(shù)傳遞給SQL Compare，從而消除了每個(gè)目標(biāo)數(shù)據(jù)庫(kù)所需的許多修改數(shù)據(jù)庫(kù)模式比較和部署過(guò)程所涉及的繁瑣腳本。

有很多方法可以使用PowerShell自動(dòng)執(zhí)行需要執(zhí)行SQL Compare的許多常規(guī)開(kāi)發(fā)工作。您選擇的方法取決于您的要求。本文旨在指出從命令行界面（CLI）運(yùn)行XML時(shí)，使用XML將詳細(xì)指令傳遞給SQL Compare的一些優(yōu)點(diǎn)。

需要在多臺(tái)計(jì)算機(jī)上安裝SQL比較命令行的自動(dòng)化程序需要Redgate Deploy或SQL Toolbelt許可證。有關(guān)完整的詳細(xì)信息，請(qǐng)參閱文檔的“更改分發(fā)的命令行”頁(yè)面。

將參數(shù)傳遞給SQL Compare CLI

SQL Compare CLI允許您從XML參數(shù)文件（argfile）或使用項(xiàng)目文件傳遞參數(shù)，以及在命令行尾部使用開(kāi)關(guān)的更常用方法。如果您需要大量參數(shù)但無(wú)法從GUI生成項(xiàng)目文件，則此功能很有用。

使用XML argfile傳遞參數(shù)的原因有很多。自動(dòng)生成項(xiàng)目文件不是一個(gè)好主意，而支持XML argfile。這意味著您可以從頭開(kāi)始為每個(gè)任務(wù)創(chuàng)建XML argfile，并可以按計(jì)劃執(zhí)行所有任務(wù)。您還可以使用SQL Compare有時(shí)需要的所有許多細(xì)節(jié)來(lái)執(zhí)行一般任務(wù)，而只需填寫(xiě)更改的細(xì)節(jié)，例如服務(wù)器，數(shù)據(jù)庫(kù)或憑據(jù)，而所有其他選項(xiàng)，開(kāi)關(guān)等保持不變。我在使用Linux或Windows上的SQL Compare命令行比較和部署SQL Server數(shù)據(jù)庫(kù)的文章中給出了所有這些說(shuō)明。

我們可以總結(jié)這些方法的一些優(yōu)點(diǎn)和缺點(diǎn)。

當(dāng)重復(fù)很多或使用大量命令行選項(xiàng)時(shí)，使用XML argfile為命令行界面指定參數(shù)特別有用。這是用于SQL比較的簡(jiǎn)單XML參數(shù)文件（argfile）：

<?xml version="1.0"?>
<commandline>
  <database1>FirstDatabase</database1>
  <server1>SecondHostname</server1>
  <database2>SecondDatabase</database2>
  <server2>SecondHostname</server2>
</commandline>

<?xml version="1.0" encoding="UTF-8"?>
<commandline>
   <sourcecontrol1 />
   <version1>HEAD</version1>
   <server2>TargetDBServer</server2>
   <database2>TargetDB</database2>
   <scriptsfolderxml>MyScriptFolderXml.sfx</scriptsfolderxml>
   <migrationfolderxml>MyMigrationFolderXml.mfx</migrationfolderxml>
   <include>Assembly</include>
   <include>FullTextCatalog</include>
   <include>Function</include>
   <include>Schema</include>
   <include>StoredProcedure</include>
   <include>Table</include>
   <include>View</include>
   <include>identical</include>
   <Synchronize />
</commandline>

sqlcompare /Argfile:MyXMLfilename.xml

指定開(kāi)關(guān)

開(kāi)關(guān)可以以長(zhǎng)或短的形式包含在其開(kāi)關(guān)名稱中，作為名稱，其值作為XML值。如果您有多個(gè)值（例如在/options開(kāi)關(guān)中允許的值），則可以將它們用逗號(hào)分隔：

<options> n，oc，t </ options>

從哈希表中的參數(shù)列表創(chuàng)建Argfiles

對(duì)我而言，在PowerShell中創(chuàng)建這些XML argfile的最簡(jiǎn)單方法是將參數(shù)放入哈希表中，并將其傳遞給幫助函數(shù)以將其轉(zhuǎn)換為正確的XML格式。它消除了手工制作XML的許多煩人的錯(cuò)誤。

<# Unless you like writing XML, the easiest way of creating these files in PowerShell is probably this. We just put our parameters into a hash table and pass it to a function. 
 #>
$MyParameters = @{
    'Database1' = 'Sigrid';
    'Server1' = 'MyFirstServer';
    'Database2' = 'Sigrid'
    'Server2' = 'MySecondServer';
    'userName1' = 'MyUsername1';
    'password1' = 'MyPassword1';
    'userName2' = 'MyUsername2';
    'password2' = 'MyPassword2';
    'report' = "${env:temp}\MyReport.html";
       'force' = $null;
    'reportType' = 'HTML';
    'loglevel' = 'Warning';
    'options' = 'default'
}
# just a helper function to translate the hash table to the XML format expected
# neither of the built-in cmdlets can cut it because they wrap the hash table up
Function SaveHashTableAsSQLCompareXML([hashtable]$TheHashTableParameters,[string]$WhereToStoreIt)
{
    $xmlDoc = [System.Xml.XmlDocument]'<?xml version="1.0"?>';
    $commandline = $xmlDoc.AppendChild($xmlDoc.CreateElement('commandline'));
    $TheHashTableParameters.GetEnumerator() | foreach {
        $Element = $commandline.AppendChild($xmlDoc.CreateElement($_.Name));
        if ($_.Value -ne $null) # if it is a switch with a parameter
        {
            $textNode = $Element.AppendChild($xmlDoc.CreateTextNode($_.Value));
        }
    }
    $xmlDoc.Save("$WhereToStoreIt"); #save it to the user temp area
}
SaveHashTableAsSQLCompareXML $MyParameters "${$env:temp}\Paramfile.xml"
sqlCompare /Argfile:"${env:temp}\Paramfile.xml" >"${env:temp}\summary.txt"
start "${env:temp}\MyReport.html" #display the HTML result report

這是一些用于各種任務(wù)的argfile示例。對(duì)于未加密的密碼，讀者會(huì)有些不解，但稍后再解決。

編寫(xiě)腳本的新版本

<?xml version="1.0"?>
<commandline> <!-- Script out a new version of the script folder -->
  <database1>Sigrid</database1>
  <server1>MyFirstServer</server1>
  <userName1>MyUsername</userName1>
  <password1>MyP@55w0rd</password1>
  <makescripts>MyPathTo\Tryout</makescripts>
  <force/>
</commandline>

<?xml version="1.0"?>
<commandline> <!-- generate a build script -->
  <database1>Sigrid</database1>
  <server1>MyFirstServer</server1>
  <userName1>MyUsername</userName1>
  <password1>MyP@55w0rd</password1>
   <empty2/> 
  <scriptFile>MyPathTo\MyScriptFile.sql</scriptFile>
  <force/>
</commandline>

<?xml version="1.0"?>
<commandline> <!--report on any differences between two databases -->
  <loglevel>Warning</loglevel>
  <password1>MyP@55w0rd</password1>
  <force />
  <Server2>MyOtherServer</Server2>
  <password2>MyP@55w0rd</password2>
  <userName2>MyUsername</userName2>
  <options>default</options>
  <Server1>MyFirstServer</Server1>
  <reportType>HTML</reportType>
  <userName1>MyUsername</userName1>
  <Database2>Sigrid</Database2>
  <Database1>Sigrid</Database1>
  <report>MyPathTo\MyReport.html</report>
</commandline>

<?xml version="1.0"?>
<commandline> 
<!-- Script out, and report on, the differences between two databases -->
  <loglevel>Warning</loglevel>
  <password1>MyP@55w0rd</password1>
  <force />
  <Server2>MyOtherServer</Server2>
  <password2>MyP@55w0rd</password2>
  <userName2>MyUsername</userName2>
  <options>default</options>
  <Server1>MyFirstServer</Server1>
  <reportType>HTML</reportType>
  <userName1>MyUsername</userName1>
  <database2>Sigrid</database2>
  <database1>Sigrid</database1>
  <exclude>Identical</exclude>  
  <scriptfile>MyPathTo\MyDatabaseBuild.sql</scriptfile>
  <report>MyPathTo\MyReport.html</report>
</commandline>

<?xml version="1.0"?>
<commandline> 
<!-- make a snapshot of a database -->
  <Server1>MyFirstServer</Server1>
  <database1>Sigrid</database1>
  <userName1>MyUsername</userName1>
  <password1>MyP@55w0rd</password1>
  <loglevel>Warning</loglevel>
  <force />
  <makesnapshot>MyPathTo\MyDatabase.snp</makesnapshot>
  <options>default</options>
</commandline>

使用XML argfile執(zhí)行SQL Compare CLI

<# now we can execute sql Compare CLI with all the argfiles #>
Get-ChildItem -Path "${env:temp}" -Filter '*.xml'| 
   foreach{SQLCompare  "/Argfile:$($_.fullname)"}

動(dòng)態(tài)生成argfile

如果您沒(méi)有任何argfiles怎么辦？我們可以即時(shí)生成它們！一旦存在argfile，您就可以在每次要重新執(zhí)行它時(shí)使用上面的一個(gè)劃線。

<# We just have a list of servers, databases and (in this case userids and passwords) #>
@(
    @{
        'Database' = 'Sigrid'; 'Server' = 'MyOtherServer';
          'userid' = 'MyUsername'; 'password' = 'MyP@55w0rd' },
    @{
        'Database' = 'Abnego'; 'Server' = 'MyOtherServer';
        'userid' = 'MyUsername'; 'password' = 'MyP@55w0rd' },
    @{
        'Database' = 'Antipas'; 'Server' = 'MyOtherServer';
        'userid' = 'MyUsername'; 'password' = 'MyP@55w0rd' },
    @{
        'Database' = 'Archaelus'; 'Server' = 'MyOtherServer';
        'userid' = 'MyUsername'; 'password' = 'MyP@55w0rd' },
    @{ 'Database' = 'Adeliza'; 'Server' = 'MyFirstServer' },
    @{ 'Database' = 'Sigrid'; 'Server' = 'MyFirstServer' }
    
) | foreach{
    "<?xml version=""1.0""?>
<!-- make a snapshot of a database $($_.Database) on $($_.Server) -->
<commandline> 
  <Server1>$($_.Server)</Server1>
  <database1>$($_.Database)</database1>
  $(if ($_userid -ne $null)
        {
            "<userName1>$($_.userid)</userName1>
  <password1>$($_.password)</password1>"
        })
  <loglevel>Warning</loglevel>
  <force />
  <makesnapshot>${env:temp}\$($_.Database)-$($_.Server).snp</makesnapshot>
  <options>default</options>
</commandline>
">"${env:temp}\Snap-$($_.Database)-$($_.Server).xml"
}
<# now we can execute sql Compare CLI with all the argfiles #>
Get-ChildItem -Path "${env:temp}" -Filter 'Snap*.xml' |
foreach{ SQLCompare  "/Argfile:$($_.fullname)" }

密碼存儲(chǔ)問(wèn)題

如前所述，許多讀者會(huì)為將未加密的密碼放入文件中而大驚小怪。這總是一個(gè)壞主意。好的，將它們保存在您的用戶區(qū)域中，以便獲得NTFS訪問(wèn)控制所提供的保護(hù)措施。但是，密碼也必須加密。

實(shí)際上，如果您完全使用SQL Server身份驗(yàn)證，那么您的SQL Compare項(xiàng)目文件也應(yīng)存儲(chǔ)在您的用戶區(qū)中（在PowerShell中，“ ${env:temp}”是指您用戶區(qū)中的臨時(shí)目錄）。這是因?yàn)椋M管密碼是在項(xiàng)目文件中加密的，但這樣做的方式是，無(wú)論Windows / Linux身份如何，任何人都可以使用加密的密碼，并且他們可以粘貼到其項(xiàng)目文件中以使用SQL訪問(wèn)數(shù)據(jù)庫(kù)。

要存儲(chǔ)憑據(jù)，Microsoft建議在PowerShell中使用Import-CliXml和Export-CliXml。該Export-Clixmlcmdlet使用Windows數(shù)據(jù)保護(hù)API加密憑據(jù)對(duì)象。加密可確保只能通過(guò)您的用戶帳戶以及僅在該計(jì)算機(jī)上解密憑據(jù)對(duì)象的內(nèi)容。導(dǎo)出的CLIXML argfile不能在其他計(jì)算機(jī)上或該計(jì)算機(jī)上的其他用戶使用。請(qǐng)參閱使用PowerShell在磁盤(pán)上安全存儲(chǔ)憑據(jù)和使用PowerShell安全密碼：加密憑據(jù)。

import-Module sqlserver #import all the libraries for SMO
$SQLserver = 'MyFirstServer'
$SqlUserName = 'MyUsername'
if ($SqlUserName -ne $null)
{
$SqlEncryptedPasswordFile = `
"$env:USERPROFILE\$($SqlUserName)-$($SQLserver).xml"
# test to see if we know about the password in a secure string stored in the user area
    if (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf)
    {
        #has already got this set for this login so fetch it
        $SqlCredentials = Import-CliXml $SqlEncryptedPasswordFile
        
    }
    else #then we have to ask the user for it (once only)
    {
        #hasn't got this set for this login
        $SqlCredentials = get-credential -Credential $SqlUserName
        $SqlCredentials | Export-CliXml -Path $SqlEncryptedPasswordFile
    }
    $ServerConnection = new-object `
        "Microsoft.SqlServer.Management.Common.ServerConnection" `
        ('MyFirstServer', $SqlCredentials.UserName, $SqlCredentials.Password)
}
else
{
    $ServerConnection =
    new-object "Microsoft.SqlServer.Management.Common.ServerConnection" `
    ($csb.server)
}
$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") $ServerConnection
$s.Databases

SQL Compare argfiles的問(wèn)題在于命令行界面通過(guò)CLI直接從磁盤(pán)讀取它們，并且我們不允許將敏感信息（例如密碼）作為參數(shù)單獨(dú)傳遞。這意味著必須在產(chǎn)品中解決在argfile中以純文本格式存儲(chǔ)密碼的問(wèn)題。

在Argfiles中存儲(chǔ)密碼的解決方案

為了快速解決此問(wèn)題，并為SQL Compare的現(xiàn)有用戶提供解決方案，我們笨拙地添加了“即時(shí)”密碼。

讓我們從那些argfiles重新開(kāi)始。我們現(xiàn)在不打算在其中輸入密碼。如果他們有用戶名，那么我們需要即時(shí)添加適當(dāng)?shù)拿艽a，以創(chuàng)建XML argfile的臨時(shí)版本，然后將其傳遞給SQL Compare。它可以用于username1or username2，也可以用于password1or password2。

<# We just have a list of servers, databases and (in this case Userids and logins) #>
@(
    @{
        'Database' = 'Sigrid'; 'Server' = 'MyOtherServer'
    },
    @{
        'Database' = 'Abednego'; 'Server' = 'MyOtherServer'
    },
    @{
        'Database' = 'Antipas'; 'Server' = 'MyOtherServer'
    },
    @{
        'Database' = 'Archaelus'; 'Server' = 'MyOtherServer';
        'userid' = 'MyUsername'
    },
    @{
        'Database' = 'Adeliza'; 'Server' = 'MyFirstServer';
        'userid' = 'MyUsername'
    },
    @{
        'Database' = 'Sigrid'; 'Server' = 'MyFirstServer';
        'userid' = 'MyUsername'
    }
    
) | foreach{
    "<?xml version=""1.0""?>
<!-- make a snapshot of a database $($_.Database) on $($_.Server) -->
<commandline> 
  <Server1>$($_.Server)</Server1>
  <database1>$($_.Database)</database1>
  $(if ($_.userid -ne $null)
        {
            "<userName1>$($_.userid)</userName1>
  <password1>$($_.password)</password1>"
        })
  <loglevel>Warning</loglevel>
  <force />
  <makesnapshot>${env:temp}\$($_.Database)-$($_.Server).snp</makesnapshot>
  <options>default</options>
</commandline>
">"${env:temp}\Snap-$($_.Database)-$($_.Server).xml"
}

現(xiàn)在，我們必須創(chuàng)建一個(gè)幫助函數(shù)來(lái)獲取密碼。首次對(duì)任何用戶和服務(wù)器運(yùn)行此功能時(shí)，都會(huì)從您那里獲取密碼，并且您必須輸入該密碼。此后，它將從安全存儲(chǔ)中獲取該密碼。

function SavedPassword ($SqlUserName, $server)
{
    $SqlEncryptedPasswordFile = `
    "$env:USERPROFILE\$($SqlUserName)-$($server).xml"
    # test to see if we know about the password in a secure string stored in the user area
    if (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf)
    {
        #has already got this set for this login so fetch it
        $SqlCredentials = Import-CliXml $SqlEncryptedPasswordFile
        
    }
    else #then we have to ask the user for it (once only)
    {
        #hasn't got this set for this login
        $SqlCredentials = get-credential -Credential $SqlUserName
        $SqlCredentials | Export-CliXml -Path $SqlEncryptedPasswordFile
    }
    $SqlCredentials.GetNetworkCredential().password
}

現(xiàn)在，我們重新?tīng)I(yíng)業(yè)。可悲的是，它在代碼中有點(diǎn)復(fù)雜，但這絲毫不會(huì)減慢速度。我們依次提取每個(gè)argfile，對(duì)其進(jìn)行對(duì)等以查看其是否需要密碼，如果需要，請(qǐng)插入密碼。我們將每個(gè)副本復(fù)制到一個(gè)新的臨時(shí)文件，并將其傳遞給SQL Compare。使用它后，我們會(huì)立即將其刪除！

Get-ChildItem -Path "${env:temp}" -Filter 'Snap-*.xml' |
foreach{
    $content = [System.IO.File]::ReadAllText($_.fullname);
    $xmlContent = [xml]$content
    write-output "$($xmlContent.'#comment')"
    $server1 = $xmlContent.commandline.server1
    $server2 = $xmlContent.commandline.server2
    $username1 = $xmlContent.commandline.username1
    $username2 = $xmlContent.commandline.username2
    
    if ($username1 -ne $null)
    {
        $xmlContent.commandline.password1 = (SavedPassword  $username1 $server1)
    }
    if ($username2 -ne $null)
    {
        $xmlContent.commandline.password2 = (SavedPassword  $username2 $server2)
    }
    $Tempfile="${env:temp}\Temp_$($_.basename).xml"
    $xmlContent.Save($Tempfile)
    SQLCompare  "/Argfile:$Tempfile" 
    Remove-Item -Path "$Tempfile"
}

結(jié)論

我最喜歡使用argfiles將參數(shù)傳遞給SQL Compare的地方是，您可以通過(guò)將特定任務(wù)所需的所有argfile收集到一個(gè)目錄中，然后依次將每個(gè)argfile傳遞給SQL Compare來(lái)完成很多工作。它省去了很多腳本，意味著您可以通過(guò)添加，修改或刪除argfiles來(lái)修改整個(gè)任務(wù)。與項(xiàng)目文件不同，這些XML argfile可以通過(guò)腳本或在文本編輯器中輕松修改。

如前所述，如果您選擇使用argfiles并使用PowerShell，則需要解決未加密密碼的問(wèn)題。

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

SQL Prompt：SQL語(yǔ)法提示工具

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

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