Fine-tuning Scripted Database Comparisons with SQL Compare
When comparing databases, save your standard options and filters to a single project file, and then use PowerShell to script them out to the command line. This allows you to reuse the same project file across multiple comparison operations.
From the GUI to command line automation
When using SQL Compare to compare two databases, most of the complexity is in specifying the options that describe how it should do the schema comparison, and the filters for objects that should be excluded or included.
If you want to script your database comparisons, the easiest approach is to start in the GUI. Do a few comparisons, making changes to the filters and options until you get them right. With any complex tool, this is far easier to do visually than in the underworld of the CLI interface. Having refined the way that comparisons are done, you simply save the results in a project file. When you need to do repeated operations, you just run the command-line version of SQL Compare, using either a batch job or a PowerShell script, supplying the project file as a parameter.
So far, this is easy, but if you need to compare a larger number of databases or perform a lot of different operations, it can all get a bit cumbersome. The problem is that the project file specifies the source and target data sources, as well as all the filters and options. If you need to compare different versions of the same database or do several different operations, such as saving script directories, reading snapshots and so on, then each operation would need a project file, each with its unique settings.
Imagine that you suddenly find that you need to change just one option. You’ll have lots of project files to alter, and each one would require the SQL Compare GUI. Project files can’t be altered directly: it isn’t supported. What you can do though is to read the settings from a project file.
The approach I suggest in this article is to use the SQL Compare GUI to set up a project file to store the standard filter and options settings for all the schema comparison operations you do on a database, rather than having a project file for every operation. The idea is that rather than have a project file for every operation, you get as close as possible to one project file per database.
When you are automating database comparisons, you then read the configuration from the project file for every process that uses the same configuration settings. I’ll supply PowerShell functions that will read all the option and filter settings from the project file, requiring you to specify only the required data sources for the comparison, and any other required parameter ‘switches’ like the report type. All this information is saved into an XML Argument file that is then passed directly to the SQL Compare command line for execution.
Saving and reusing SQL Compare configuration information
SQL Compare uses three types of XML file to record how a comparison should be done: a Project files (.scp). a filter file (.scpf) and an XML Argument file (.xml). SQL Compare uses and maintains only the first two, but the command-line can read all three.
Project files
A project file is the source for all the configuration information for a schema comparison. Amongst other factors, it specifies the following:
- Data sources – identifies the source and target databases to be compared
- Filters – to include or exclude certain classes of objects (like users, roles, DDL triggers), or even specific objects, from comparisons and deployments. These appear in the project file as a set of “include” switches and can be saved and shared separately. If you stick to excluding classes of objects only, rather than specifying objects by name, you can reuse of the same filter settings across any comparison projects involving the same database.
- Options – specify details of how the comparison and deployment should be done. These appear in the project file as a comma-delimited
<options>list and can’t be saved separately. Broadly there are two types of options:- Ignore options – ignore certain types of differences between objects during comparison and deployment, such as differences in collations when comparing character data, or differences in fill factor when comparing indexes and keys
- Behavior options – control certain aspects of Compare behavior when running comparisons and deployments, such as whether to add object existence checks to deployment scripts.
Once you have a comparison configured exactly as you want it, in the GUI, you save the results in a project file (.scp). By default, they are stored in the SharedProjects directory of your SQL Compare directory (%USERPROFILE%\Documents\SQL Compare\SharedProjects).
When you run the command-line version of SQL Compare, you can supply the project file as a parameter. This will run the operation exactly as you specified it. It is a great way of doing the same operation repetitively.
Filters
In the SQL Compare GUI, you can use the left-hand Filter designer to configure the filter rules. All the filter settings will be saved automatically in the project file, but you can also use the filter pane to save them in their own ‘Filter’ file (.scpf), which you can edit subsequently. See Using Filters to Fine-tune Redgate Database Deployments for details.
By default, the filter files are stored in the Filters directory of your SQL Compare directory (%USERPROFILE%\Documents\SQL Compare\Filters). While it’s probably best to generate them only via the SQL Compare GUI you can also, like any XML file, edit them with a text editor.
Once you’ve saved the file, you can supply it to SQL Compare command line session using the /filter switch. These files can also be shared with other Compare users, used in other Redgate tools, such as SQL Source Control, DLM Dashboard, and SQL Change Automation. The options can’t be saved or retrieved in the same way. If this were possible, then this would be a very short article.
Saving filters is important as they can be used in other Redgate tools such as SQL Change Automation or SQL Source Control. You can use them in automation processes by specifying the filter files within SQL Change Automation PowerShell cmdlets, such as invoke-DatabaseBuild, using the -FilterPath parameter. When you are using filters with SQL Change Automation migration projects, you can export the rules to a file called Filter.scpf within the root of the SQL Change Automation project folder, alongside the .sqlproj file. When SQL Change Automation is used to import new changes, the rules defined within the filter file will be applied: any objects that do not satisfy the rules will be excluded from the generated migrations, programmable objects and/or offline schema model.
Options
In the SQL Compare GUI, you use Tools | Project options dialog to specify how SQL Compare does a comparison. See Exploring the SQL Compare Options.
The options are saved in the individual project file but, unlike filters, collections of options do not have their own file format. You can share filters but not options. However, it is simple to extract them from project files as a list.
The following PowerShell snippet will list out the options as a comma-delimited list, from a file called Adventureworks.scp:
|
1 2 3 |
[xml]$xmlDocument='' [xml]$xmlDocument.Load("$($env:userprofile)\OneDrive\Documents\SQL Compare\SharedProjects\Adventureworks.scp") $xmlDocument.SelectSingleNode('//Options').'#text' -replace '\, ', ',' |
In the second example, I list out all the options for all the files I have in my shared project folder:
|
1 2 3 4 5 6 |
dir "$($env:userprofile)\OneDrive\Documents\SQL Compare\SharedProjects\*.scp"| foreach {[xml]$xmlDocument.Load($_.FullName); @{file=$_.Name; options=@($($xmlDocument.SelectSingleNode('//Options').'#text' -split ',')); } } |
The simplest way, I think, of setting the options and filters for working with SQL Compare is to save them in a ‘general preferences’ project file. This project file can then be opened in the GUI, set to comparing a source to a target, be they databases, script folders, backup files, snapshots or whatever. Then save it for that precise task. Alternatively, you can start a new project in SQL Compare, and set up the options and filters as you wish, using your default options, and loading existing stored filter files as necessary to override the default settings.
If you are using SCA, you can specify options to the Cmdlets via the -SQLCompareOptions parameter. These options aren’t in a file but in a comma-delimited list. The first PowerShell fragment I show above will get them for you from a project file easily.
Using XML Argument files
Instead of using the project file on the command line there are, alternatively, advantages to passing in the configuration information as an XML Argfile, which is merely the command-line parameters or arguments expressed as an XML file. You just specify the one argument. It saves time and you can store them in directories so that they are all applied to SQL Compare in a batch process. Each XML file can express a single comparison. They can be generated by scripts. I go into the details about their use in Using the SQL Compare command line with Argfiles, and list the advantages and disadvantages.
Scripting using a project file
It is very quick for a script to go to the source of the configuration information, which is the project file. If it is more convenient to get filters from a filter file directly, that is possible, but options don’t have their own file. These are kept in SQL Compare GUI as default and custom options and placed in the project files that are saved by the GUI.
This is most easily demonstrated in this script which includes a couple of helper functions to do the task. The filters and options are inserted afresh into an XML argument file every time it is run. This means that you can get the latest version of your option and filter preferences every time the SQL Compare script is executed.
There is a Write-FilterFileFromProjectFile function that takes a project file and saves the filter settings as a separate .scpf file. There is also a couple of helper functions. The (CreateArgsFileFromHashTable) function references the filter file and saves that and all the other configuration information from the project file into an XML Argument file. The SavedPassword helper function allows you to avoid with the problem of saving unencrypted passwords in the project file, if you use SQL authentication (see Using the SQL Compare command line with Argfiles).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 |
<#----------------- .SYNOPSIS Copy out filters to a filter file from a project file .DESCRIPTION a function that takes a SQL Compare project file (.scp) and extreacts the filters from it. it then saves them as a SQL Compare project filter file (.scpf) file .EXAMPLE Write-FilterFileFromProjectFile ` "$($env:userprofile)\OneDrive\Documents\SQL Compare\SharedProjects\Adventureworks.scp" ` "$($env:Temp)" ` "AnotherTryoutProject" .NOTES this is used to pass filters from a project file to one or more XML Argument files #> function Write-FilterFileFromProjectFile ( [string]$SCPFilePath, [string]$DestinationDirectory, [string]$ProjectName) { $xmlDoc = [System.Xml.XmlDocument]@" <?xml version="1.0" encoding="utf-8" standalone="yes"?> <!--Extracted from a project --> <NamedFilter version="1" type="SQLCompareFilter"> <!--This filter can be loaded using the SQL Compare UI or used with a SQL Compare command line session using the /filter switch--> <FilterName>$ProjectName</FilterName> <Filter version="1" type="DifferenceFilter"> <FilterCaseSensitive>False</FilterCaseSensitive> </Filter> </NamedFilter> "@ if (!(test-path -Path $SCPFilePath -PathType Leaf)) { Write-Error "Cannot find the SQL Compare Project file $SCPFilePath" } [xml]$xmlDocument = '' [xml]$xmlDocument.Load("$SCPFilePath") $SingleNode = $xmlDocument.SelectSingleNode('//ProjectFilter/Filters') $TheFilters = $xmlDoc.ImportNode($SingleNode, 1) $null = $xmlDoc.SelectSingleNode('//Filter').AppendChild($TheFilters) if (!(test-path -Path $DestinationDirectory -PathType Container)) { $null = New-Item -Path $DestinationDirectory -ItemType "directory" -Force } $xmlDoc.Save("$DestinationDirectory\$ProjectName.scpf"); } # 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 CreateArgsFileFromHashTable( [hashtable]$TheHashTableParameters, #the hash table with the parameters [string]$projectFile, # the scp file from which you are taking the list of options and filters [string]$projectName, #Your name for this project [string]$DestinationDirectory #Where the files should go ) { <# Load up the options from the project file #> if (!(test-path -Path $projectfile -PathType Leaf)) { Write-Error "Cannot find the SQL Compare Project file $projectfile" } [xml]$xmlDocument = '' [xml]$xmlDocument.Load("$projectfile") <# and add it to the hash table #> $TheHashTableParameters += @{ 'Options' = ($xmlDocument.SelectSingleNode('//Options').'#text' -replace '\, ', ',') } <# Now read the filters and save them under the project name #> Write-FilterFileFromProjectFile ` "$projectfile" ` "$DestinationDirectory" ` "$projectName" $TheHashTableParameters += @{ 'Filter' = "$DestinationDirectory\$ProjectName.scpf" } <#Now create the actual argument file#> $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("$DestinationDirectory\$ProjectName.xml"); #save it to the user temp area } function SavedPassword ($SqlUserName, $server) { # just a way of preventing any passwords being put in a script $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 } |
Now, with these functions, we can use the project file of a SQL Compare project, for the AdventureWorks database, to provide the correct options and filters.
Some simple database comparisons using the same project file
To demonstrate the technique, let’s do two different operations that use the one project file. This project file has a filter to allow you to compare just one schema. Imagine, if you will, that you are a developer with responsibility for a particular schema in the database, and you wish to see differences in the schema between two different versions of the database.
The first example, ‘Comparing the same schema in two live databases’, will be a script that provides an on-screen comparison of two versions of the same database within a browser.
The second script, ‘Generating a single-schema build script’, uses the same project file. In this example, we are only doing single database. In reality, you’ll usually be doing these tasks for several databases, and I’ve shown a way of using several Argfiles. We can just generate an Argfile for each comparison task and execute the entire directory. Having stored the Argfiles in a directory we can apply each one to SQL Compare.
|
1 2 3 |
<# now we can execute sql Compare CLI with all the argfiles #> Get-ChildItem -Path "$($env:Temp)\Comparisons" -Filter '*.xml'| foreach{SQLCompare "/Argfile:$($_.fullname)"} |
Comparing the same schema in two live databases
We’ll just do a simple comparison of two databases first.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
<# Unless you like writing XML, the easiest way of creating these XML Argument files in PowerShell is probably this. We just put our parameters into a hash table and pass it to a function. if you are using windows authentication (integrated security) leave out the userid#> <# For our first example we want to display all the differences between two databases. We'll do this by creating an HTML report that will load into our current browser. Before you run this, get the options and filters absolutely as you like them, in the GUI, save the project file and note where it is #> $SQLCompareAlias= "${env:ProgramFiles(x86)}\Red Gate\SQL Compare 14\sqlcompare.exe" Set-Alias SQLCompare $SQLCompareAlias -Scope Script if (!(test-path ((Get-alias -Name SQLCompare).definition) -PathType Leaf)) { Write-error 'The alias for SQLCompare is not set correctly yet' } #---required Data --- <# leave out the userName entries if using Windows authentication#> $MyParameters = @{ 'Database1' = 'Adventureworks2016'; 'Server1' = 'MyFirstServer'; 'Database2' = 'Adventureworks2016' 'Server2' = 'MyOtherServer'; 'userName1' = 'MyUID'; 'userName2' = 'MyUID'; 'report' = "${env:temp}\MyReport.html"; 'force' = $null; 'reportType' = 'HTML'; 'loglevel' = 'Warning'; } $ProjectName='CompareAdventureworks' $TheSCPFile="$($env:userprofile)\OneDrive\Documents\SQL Compare\SharedProjects\OnlyThePersonSchema.scp" #---End of required Data --- <# Now we add any passwords that we need, using our helper function#> if ($MyParameters.username1 -ne $null -and $MyParameters.Server1 -ne $null ) { $MyParameters += @{'Password1'=(SavedPassword $MyParameters.username1 $MyParameters.server1)} } if ($MyParameters.username2 -ne $null -and $MyParameters.Server2 -ne $null ) { $MyParameters += @{'Password2'=(SavedPassword $MyParameters.username2 $MyParameters.server2)} } <# now we have the details we create the Args file on the fly #> CreateArgsFileFromHashTable ` $MyParameters $TheSCPFile $ProjectName "$($env:Temp)" <#the hash file with all the spec.#> <# the scp file from which you are taking the list of options and filters#> ` <#Your name for this project .#> <#Where the SCpf and arg files should go .#> <# now we know excactly from the args file gow the comparison should be done #> SqlCompare /Argfile:"$($env:Temp)\$ProjectName.xml" >"${env:temp}\summary.txt" Remove-Item -Path "$($env:Temp)\$ProjectName.xml" #remove the file in case there is a password start "${env:temp}\MyReport.html" #display the HTML result report |
Generating a single schema build script
If you are doing one of the many different types of comparison, your arguments have to be appropriate. I give a few examples in ‘Using the SQL Compare command line with Argfiles‘
As another example, we’ll generate a build script for just a single schema. We also want to do a build script that can be used in Flyway, without transactions. As this is going to be a regular operation, we need to automate it. We therefore do a trial run with SQL Compare GUI until we get it absolutely right and check the results with Flyway. Having done that we can carry on using the project as a script.
Generating a script would be something like …
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
<# Make sure that you are accessing the right version of SQL Compare #> $SQLCompareAlias= "${env:ProgramFiles(x86)}\Red Gate\SQL Compare 14\sqlcompare.exe" Set-Alias SQLCompare $SQLCompareAlias -Scope Script if (!(test-path ((Get-alias -Name SQLCompare).definition) -PathType Leaf)) { Write-error 'The alias for SQLCompare is not set correctly yet' } <#Specify what you want #> <# comment out the userName entries if using Windows authentication#> $MyParameters = @{ 'Database1' = 'Sigrid'; 'Server1' = 'MyFirstServer'; 'userName1' = 'MyUID'; 'empty2'=$null; 'scriptFile'="$($env:Temp)\MyScriptFile.sql"; 'force'=$null; } $ProjectName='MakeItHappenToo' $TheSCPFile="$($env:userprofile)\OneDrive\Documents\SQL Compare\SharedProjects\ OnlyThePersonSchema.scp" <# add any passwords #> if ($MyParameters.username1 -ne $null -and $MyParameters.Server1 -ne $null ) { $MyParameters += @{'Password1'=(SavedPassword $MyParameters.username1 $MyParameters.server1)} } if ($MyParameters.username2 -ne $null -and $MyParameters.Server2 -ne $null ) { $MyParameters += @{'Password2'=(SavedPassword $MyParameters.username2 $MyParameters.server2)} } <# create the arg file and the filter file #> CreateArgsFileFromHashTable ` $MyParameters <#the hash file with all the spec.#> ` $TheSCPFile ` <# the scp file from which you are taking the list of options and filters#> ` $ProjectName ` "$($env:Temp)" <# and now do the comparison to generate the script #> SqlCompare /Argfile:"$($env:Temp)\$ProjectName.xml" >"${env:temp}\$ProjectName-summary.txt" Remove-Item -Path "$($env:Temp)\$ProjectName.xml" #remove the file in case there is a password |
Next steps
What we’ve done so far can be achieved far simpler with a project file, assuming you have domain authentication. It is only when we have a larger provisioning or scripting task than this that the solution we have here comes into its own. With this routine in place, you can do a whole batch of database comparisons and scripting by iterating through a large set of hash tables, probably stored on disc as JSON files or XML (I just write than and execute them as PowerShell files). You can also use several filters and options on the same source and target (as when scripting schemas independently, for example). By separating ‘what you do’ (the options and filters) from ‘what you do it with’ (servers, databases and credentials), we make it much easier to scale up the many database activities for which SQL Compare is so useful.
Conclusions
It might seem that I’m an automation freak, but it is rather more that I’m impatient to do real development work rather than spend a lot of time on repetitive chores. They’re important, of course, but only bearable if they’re scripted. By separating the configuration task, specifying what and how database comparisons and scripting is done, from the job of specifying the databases and servers that are the sources or targets of the comparison or scripting, it make a lot more tasks easy to automate and maintain. I don’t mind maintaining a scripting system if it can be done easily from the GUI. That just saves more time for the more creative work.
Tools in this post
You may also like
-
Article
Database Development with GitHub
How can you use GitHub to do team-based database development? This article proposes a process that splits development work into task-based GitHub branches, incorporates daily database builds and integration testing, and uses Redgate tools to automate tasks such as provisioning, database scripting, and testing.
-
Article
Retrospective Database Source Control with SQL Compare
You've found a database that is not in source control. What do you do? Phil Factor shows how to use SQL Compare to generate all the missing object scripts, in Git, and then keep them up-to-date automatically, in response to any further database changes, during development.
-
Article
Comparing Two SQL Server Databases: When, Why, and How
SQL Compare has a simple premise: it will compare the two SQL Server databases for schema differences. It will generate a script that will make the schema of a target database the same as that of the source database. This article explores all the ways this can be helpful during database development and deployment, from generating database scripts in version control, to detecting database drift, to auto-generating deployment scripts.
-
Article
Building a Database Directly from Object-level Source Scripts
Table manifests, and object manifests, which are just ordered lists, are a very useful output from and development database build. This article show how to generate a table manifest in SQL, and once you have it, you'll start to find several uses for it, besides the build process.
-
