Product articles SQL Compare Database Builds and Deployments
Comparing and Deploying SQL Server…

Comparing and Deploying SQL Server Databases using the SQL Compare Command Line on Linux or Windows

SQL Compare now includes a Linux-based command line interface (CLI), as well as a Windows-based one, for doing schema comparison and deployments of SQL Server databases. This article offers worked examples of CLI scripts for various schema comparison tasks, to get you up and running with either version.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Contents

On investigating the new SQL Compare command line for Linux, I wanted to try out a range of its features, so I installed it directly on a Linux-based Synology NAS server, and used it to access SQL Server instances on the network, and to process files such as backups directly on the Linux filesystem.

In doing so, it hit me that, like many tools that have evolved to meet the wishes of its users, a lot of the SQL Compare command line’s features lurk undiscovered. I discovered a few myself, even though I know the tool well enough to have uncovered the legendary Easter egg. To assist anyone new to the SQL Compare CLI, I’ve provided an overview of all the major functions by example. I’m hoping that there are surprises for most readers, regardless of whether your SQL Server instances run on Windows or Linux.

How SQL Compare works

SQL Compare is a utility to compare and synchronize the structure of SQL Server databases. It will compare two SQL Server databases, inspect the differences, and generate a synchronisation, or deployment, script that will bring the metadata of the target to the same level as the source, preserving the existing data where possible. You have a lot of control over the way it does this comparison, the way it compares database objects, the type of script it generates, and what it ignores. You can inspect the resulting sync script, and alter it if necessary, before applying it to the target.

The source and target don’t have to be working databases, because SQL Compare can also work with a representation of a database, such as a build script, a script folder, a SQL Compare ‘snapshot’ artefact, or a SQL Change Automation build artefact. It will parse the DDL code in the database script, or a whole folder of scripts, that represents the database, creating a ‘model’ of the database that it can then compare to the source or target. It can also read scripts directly from source control or from an SQL Change Automation project file (.sqlproj), though these features aren’t fully documented or supported using CLI parameters; you can do it by setting up a project file in the GUI and then specifying it in the CLI.

If the target is an empty database, you get what is, in effect, a build script for the source. If the target is an empty directory, it becomes a DDL code directory with the different types of database objects placed in appropriate subdirectories.

If the target is an existing database, the sync script will create any objects that exist only in the source, drop any objects that exist only in the target, and update any object that is in both so that its definition matches that in the source. Similarly, if the target is an existing DDL code directory, files are added, removed or altered to reflect the source database.

If you use a scripts folder to represent a database, you can write custom scripts that SQL Compare will append automatically to the beginning or end of the synchronization script. It is also possible to provide files with executable INSERT statements, to load small quantities of static data into tables.

SQL Compare can provide an HTML or Excel file that is used to provide a report, when it is used from the command line or script.

Why SQL Compare CLI for Linux?

The Windows version of SQL Compare command line can already access a SQL Server database hosted on Linux or MacOs. Connections are made via TDS over the network so SQL Server and the various tools such as SSMS are agnostic about hosting platforms. It can also read files such as backup files, or source code, stored on Linux-based servers that use Samba.

However, if you are working in Linux or MacOs and have SQL Server installed locally, then having a SQL Compare CLI for Linux is more convenient and cheaper than having to start the Windows version in a Windows VM.

Even Windows users often inhabit a network of mixed platforms and so, in order to test out SQL Compare CLI for Linux, I installed it on a Linux-based Synology fileserver to see if it could rapidly access all the shared files such as backups without needing network access. It could. Was it possible to run it via PowerShell? Yes.

How to run SQL Compare CLI

There are three different ways of running SQL Compare CLI. As well as the conventional approach of providing switches, you can use an XML file to provide the parameters and options:

sqlcompare /Argfile:PathToMyArgFiles\ArgFile.xml

This is handy for repetitive work if you aren’t using a script. If you need to type in SQL Compare commands, and you start using more complex processes and options, you will come to like this second, file-based, approach.

The third way is to set up exactly what you want to do using the GUI version of the tool, make sure that you select all the database objects that you wish to include, and then save the project file. Subsequently, you can run the project from the command line.

sqlcompare /project:"PathToMyArgFiles\MyProjectFile.scp"

Whichever way you choose to provide the settings for the CLI, you can then save them as a project file, using /OutputProject: "PathToMyArgFiles\MyProjectFile.scp" and use those instead when you repeat the operation. You can force it to over-write an existing .scp file of the same name by adding /force in the command line.

Running Linux SQL Compare CLI

The SQL Compare CLI for Linux is provided as a docker container image. When using SQL Compare CLI for Linux, the backslash (‘\’) is an escape character in bash. This makes the XML file approach more attractive. If you need to use the bash shell, you should therefore specify instances in the switches with a double backslash when using the Linux command line:

/server1:MyServer\\MyInstance

SQL Compare Linux CLI is in a container, so the filesystem is isolated from the rest of the system. This means that you need to ‘mount’ a directory on the host filesystem that allows SQL Compare to read or write your files, such as scripts folders and project files. To do this, you use Docker’s --mount switch to pass host directories through to SQL Compare CLI.

To avoid any unnecessary frustration, it is worth knowing that:

  • The Linux system must be able to translate SQL Server host names to IP addresses. The DNS server must be aware of DHCP assignments; in the simpler configurations, this is easily done by assigning the DHCP server as the DNS server for the Linux host.
  • You should ensure you run Docker commands as a member of the docker group (or alternatively as root, using sudo).
  • You need to check the Redgate license and apply the CLI switch /IAgreeToTheEULA

I installed the sqlcompare container (Redgate/sqlcompare:14) to a convenient Linux NAS server that had Docker installed. In order to get to the Linux command line, I then ran PuTTY from a Windows machine (any good SSH and Telnet application or emulator will do).

The container has, within the redgate directory, scripts and scriptsfolder directories for CLI scripts and the scripts representing the source or target database, respectively. We can use this directory, on the NAS server in my case, for the mount operation. This enabled me to pass files to and from the container without needing anything like FTP. Even better than just passing files between container and host, I can mount, via Docker, to the specific directory on the NAS where the files are stored. This allows CLI to write directly to the shared directories that are visible to Windows. The SQL Compare HTML reports are written directly to a website hosted by the NAS server.

PowerShell has an SSH emulator, Posh-SSH, that should enable you to automate processes in PowerShell scripts. Here it is doing a basic ls command:

For Windows authentication, ensure that Kerberos has been initialized with 'kinit' and a Service Principal Name has been registered for the SQL Server, to allow Kerberos authentication.

Working with Scripts

Here are a few examples of CLI scripts, for various comparison tasks, to get up and running. In each case, I’ll show the standard CLI example first, then the equivalent Linux version.

Basically, the Linux version works much like the Windows version, but slower in my case because of the overhead of creating the container from the image when running tasks that took only a short time. The only real inconvenience for a Windows user is the chore of the docker command and the mount.

If I don’t show a Linux version for a script in any of the subsequent examples, it’s because it’s the same as the Windows version, except for starting with the additional docker command and mount operation.

Script out a database on a server to a script folder as object-level scripts

For just generating, or completely overwriting, a script folder such as you’d use in source control, you don’t need to define a target. Using Windows authentication:

sqlcompare /s1:MyServer\\MyInstance /db1:MyDatabase /makeScripts:"PathToMyScriptFolder"

To do the same thing using SQL Server Authentication:

sqlcompare /s1:MyServer\\MyInstance /db1:MyDatabase /userName1:User1 /password1:MyPassword /makeScripts:"PathToMyScriptFolder"

If the folder already exists an error will occur. To completely recreate the folder, use the /force switch.

On Linux, this would be…

sudo docker run –rm --interactive –tty \
   --mount type=bind,src= PathToMyScriptFolder,dst=/scriptsfolder \
    redgate/sqlcompare:14 /IAgreeToTheEULA \
    /s1:MyServer\\MyInstance /db1:MyDatabase \
    /userName1:User1 /password1:MyPassword \
    /scripts2:"/scriptsfolder"

Script out a database as a single build script file

Sometimes, you need a single build script that can be used subsequently to build the database so that all objects are created in the correct order for a successful build.

sqlcompare /Server1:MyServer\Instance /Database1:MyDatabase /empty2 /ScriptFile:" PathToMyScriptFolder\MyDatabaseBuild.sql" /Force

On Linux this would be, assuming I’d created a directory called /usr/local/share/Redgate

sudo docker run --rm --interactive --tty \
    --mount type=bind,src=/mypath,dst=/scripts \
    redgate/sqlcompare:14 /IAgreeToTheEULA \
    /s1:MyServer  /db1:MyDatabase \
    /userName1:User1 /password1:MyPassword  \
    /empty2 /force /scriptfile:"/scripts/MyDatabaseScript.sql"

You can use a switch to determine the output text character encoding type:

/syncScriptEncoding:<UTF8|UTF8WithPreamble|Unicode|ASCII>

Merge scripts into an existing scripts folder

Normally you would do this for just a subset of the database, although this can also be used to create a source directory from a single build script. We compare a source script folder, /src1, to a target script folder, /src2, and use the /synchronize switch:

sqlcompare /scr1: "PathToMyScriptFolder" /scr2:"PathToMyGithubFolder" /synchronize /Force

This will update, create or delete just the necessary files in the target scripts folder and will read even a single build script, in /scr1, to do so.

Script out just the differences between two databases

sqlcompare /Database1:MySourceDatabase /Database2:MyTargetDatabase /exclude:Identical  /ScriptFile:"PathToMyScriptFolder\MyDatabaseBuild.sql"

Working with SCA Migration files

Use an SQL Change Automation project file (.sqlproj) for the source.

sqlcompare /sca1:"PathToMySQLproj\MyScaProject.sqlproj" /db2:MyTargetDatabase

Use an SQL Change Automation project file (.sqlproj) for the target.

sqlcompare /db1:MySourceDatabase /sca2:"C:\ScaProject\MyScaProject.sqlproj"

The project file must already exist in each case.

Working with SQL Compare Snapshots

A SQL Compare Snapshot is just a point-in-time copy of the metadata of a database.

Make a Snapshot

Here is how to make a snapshot from a local database:

sqlcompare /db1:MyDatabase /makeSnapshot:"PathToMySnapshotFile\MyDatabase.snp"

Here is a Linux version that does a snapshot of a database on a network server. I have created a path to bind to, on a shared volume I’ve created that is accessible to Windows machines. This allows me to read existing snapshots directly and rapidly from the Linux filesystem without any network access.

sudo docker run --rm --interactive --tty \
    --mount type=bind,src=/volume1/redgate/scripts,dst=/scripts \
    redgate/sqlcompare:14 /IAgreeToTheEULA \
    /s1:MyUser /db1:MyDatabase \
    /userName1:MyUser /password1:MyPassword  \
    /makeSnapshot:"/scripts/MyDatabase.snp"

If you do not have SQL Compare in a location where you have either the source or target database with which you need to do a comparison, you can create it using SQL Snapper. See: Compare those hard-to-reach servers with SQL Snapper.

Compare two snapshots

This allows you to compare two databases when neither of them are accessible, or if you wish to compare different archaic versions of databases to see quickly what has changed.

sqlcompare /snapshot1: "PathToMySnapshotFile\MySourceDatabase.snp" /snapshot2:"PathToMySnapshotFile\MyTargetDatabase.snp"

Comparing Databases

There are a variety of ways to compare a source and target database…

Comparing local databases to see if they are identical

sqlcompare /Database1:MySourceDatabase /Database2:MyTargetDatabase /assertidentical

This will return exit code 0 if the comparison process finds that the database objects that you specify, all of them in this case, are identical, else it returns exit code 79.

Comparing local databases via SQL Server credentials

sqlcompare /db1: MySourceDatabase /userName1:User1 /password1:MyPassword/db2: MyTargetDatabase /userName2:User2 /password2:MyOtherPassword

This is a bad way of providing SQL Server credentials in a script. Only the project files provide a way of passing an encrypted password to the CLI.

Comparing network or remote databases via SQL Server credentials

sqlcompare /Server1:MyServer\Instance /db1:MySourceDatabase /userName1:User1 /password1:MyPassword/Server2:MyServer\Instance /db2:MyTargetDatabase /userName2:User2 /password2: MyOtherPassword

Comparing and synchronizing two network databases with Windows auth

sqlcompare /Server1:MyServer\Instance /db1:MySourceDatabase /Server2:MyServer\Instance /db2:MyTargetDatabase /Synchronize

Comparing single database table

sqlcompare /Server1:MyServer\Instance /db1:MySourceDatabase /Server2:MyServer\Instance /db2:MyTargetDatabase /Include:table/Include:table:\[MyTableName\] /verbose

Reporting

If you are comparing databases at the command line, you will get an ASCII report of the comparison that you can redirect out to a file (>output.txt). You will normally also want to generate a report of the comparison. This uses the switch:

/report:<filename> (Alias: /r)

which generates a report and writes it to the file that you specify. The type of report is defined by the /reporttype switch. If the file already exists an error will occur, unless you have also used the /force switch. The only useful types are Xml, if you need to create your own report, Html, an Interactive HTML report that is the choice if you use a local site to monitor a comparison, and Excel, best for mail attachments.

As well a report, you should also specify a log file. See Log Files from the CLI later in the article.

Comparing network databases via SQL Server credentials with an HTML report

sqlcompare /Server1:MyServer\Instance /db1:MySourceDatabase /userName1:User1 /password1:MyPassword/Server2:MyServer\Instance /db2:MyTargetDatabase /userName2:User2 /password2: MyOtherPassword /report:"PathToMyReportFile /MyReport.html" /reportType:HTML

Working with the Scripts folder

A script folder represents the schema and (optionally) its static data. It can contain just a single build script or a series of object creation scripts, one for each object in the schema when the scripts folder is created.

It can also contain static data scripts, consisting of a .sql file containing INSERT scripts in individual files, one each for each table. I show how to create these in Scripting out SQL Server Data as Insert statements via PowerShell. When you deploy to a scripts folder, a .sdcs file is saved for each table. These files contain index information that enables SQL Data Compare to compare static data. However, the operation will work without these.

Working with the Scripts folder database information file

With SQL Compare, the top level of the scripts folder contains an XML file called RedGateDatabaseInfo.xml with some details about the database from which the scripts were derived. This information is about the objects in the schema, the SQL Server version, and the collation of the database from which the scripts folder was created. Sometimes, it doesn’t get saved into source control, and you need to add it.

It is possible to derive this XML file in SQL, as follows:

From PowerShell, you can create this file when you create a scripts folder. If, for example, you have a server object called $srv, a source directory called $SourceDirectory and a database called $db, you can do this…

Log files from the Command line

Log files are generally used for debugging, but it is useful to log all errors and warnings while developing a script. Even in a production script, you need to be alerted about errors.

You do this via a CLI switch:

/logLevel:<None|Error|Warning|Verbose>

The Warning level will report both warning and error messages.

Return codes from the command line

You should always check the error code returned by SQL Compare: 0 means success and anything else means an error. The way that these errors are produced can be modified by using the switches /AbortOnWarnings, /IAgreeToTheEULA , /IgnoreParserErrors, /Include:Identical, /force, /assertidentical or /activateSerial.

/AbortOnWarnings Specifies that SQL Compare will not perform a synchronization if there are any serious synchronization warnings. The Parameter can be None, Medium or High
/IAgreeToTheEULA Signify that you agree to the Ts and Cs. The Linux version errors out if you don’t add this
/IgnoreParserErrors Syntax errors were found in a script folder that was being compared. To ignore this error (62), use this switch
/Include:Identical Lists all objects when compared databases are identical.
/force Avoids error 74 that is returned if the program attempts to write to a file that already exists. SQL Compare will over-write the file instead
/assertidentical Error 79 is returned is when nonidentical DBs are compared if you use /assertidentical. It provides a useful check on changes being made ‘unofficially’.
/activateSerial Use this to prevent error 402, ‘There is no acceptable license’, due to licenses being out of date.

Options used in the Command line

The /Options switch is the best way of tweaking the way that SQL Compare works. To specify several options, you must separate the options using commas:

/Options:<option1>,<option2>,<option3>

Option Alias What it does
AddDatabaseUseStatement adus Adds a USE statement at the top of the SQL synchronization script.
AddWithEncryption we Adds a WITH ENCRYPTION option to stored procedures etc.
CaseSensitiveObjectDefinition cs Treats items as case-sensitive. Treats object names such as ATable and atable as different and carries out case-sensitive comparisons on stored procedures etc.
ConsiderNextFilegroupInPartitionSchemes cfgps Consider next filegroups in partition schemes.
DecryptPost2kEncryptedObjects dp2k Decrypt encrypted objects.
Default d The default options for a sensible comparison: IgnoreFillFactor, IgnoreWhiteSpace, IncludeDependencies, IgnoreFileGroups, IgnoreUserProperties, IgnoreWithElementOrder, IgnoreDatabaseAndServerName, DecryptPost2kEncryptedObjects.
DisableAndReenableDDLTriggers drd Disable and later re-enable DDL triggers.
DoNotOutputCommentHeader nc Do not include comments in the output script. Do not write the comment header in the output script.
DropAndCreateInsteadOfAlter dacia When this option is selected, SQL Compare replaces ALTER statements in the deployment script with DROP and CREATE statements for the following objects: – Views – Stored Procedures – Functions – Extended Properties – DDL Triggers – DML Triggers. Note that if you select this option, you must also select the Add Object Existence Checks option, or the deployment script will fail.
ForceColumnOrder f Force table column order to be identical. If additional columns are inserted into the middle of a table this option will force a rebuild of the table so the column order after synchronization is correct.
IgnoreBindings ib Ignore bindings. Ignores bindings on columns and user defined types (e.g. sp_bindrule and sp_bindefault clauses won’t be scripted).
IgnoreCertificatesAndCryptoKeys icc Ignore certificates, symmetric and asymmetric keys.
IgnoreChangeTracking ict Ignores change tracking when comparing and synchronizing databases.
IgnoreChecks ich Ignore check constraints. Ignores CHECK constraints when comparing and synchronizing databases.
IgnoreCollations ic Ignore collation order. Ignores collation orders on character datatype columns when comparing and synchronizing databases.
IgnoreComments icm Ignore comments. Ignores comments when comparing views, stored procedures etc. Note that comments will still appear in the synchronization scripts.
IgnoreConstraintNames icn Ignore constraint and index names. Ignores the names of indexes, foreign keys, primary keys, default, unique and check constraints. The names will still be scripted and synchronized.
IgnoreDatabaseAndServerName idsn Ignores database and server names in synonyms.
IgnoreDatabaseName idn This is a deprecated option, as a synonym of IgnoreDatabaseAndServerName.
IgnoreDataCompression idc Ignores page and row compression when comparing tables and indexes. Compression won’t be ignored when the databases are deployed. When the ‘Ignore filegroups’ option is selected, compression is ignored for partitioned tables.
IgnoreExtendedProperties ie Ignore extended properties. Ignores extended properties when comparing and synchronizing databases.
IgnoreFileGroups ifg Ignore file groups. Ignores FILEGROUP clauses on tables and keys when comparing and synchronizing databases.
IgnoreFillFactor if Ignore FILL FACTOR and index padding. Ignores the fill factor in indexes and primary keys when comparing and synchronizing databases.
IgnoreFullTextIndexing ift Ignore full text indexing. Ignores full text indexes, catalogs etc. when comparing and synchronizing databases.
IgnoreIdentityProperties iip Ignores IDENTITY properties.
IgnoreIdentitySeedAndIncrement isi Ignore identity seed and increment values. Ignores identity seed and increment values when comparing databases. Note that they will still appear in synchronization scripts.
IgnoreIndexes ii Ignore indexes. Ignores indexes, statistics, unique constraints and primary keys when comparing and synchronizing databases. This option is used for memory-optimized tables if the only differences between the tables are index changes. If there are other differences between the tables, all indexes are deployed.
IgnoreIndexLockProperties iilp Ignore the lock properties of indexes.
IgnoreInsteadOfTriggers iit Ignore INSTEAD OF triggers. Ignores INSTEAD OF triggers when comparing and synchronizing databases.
IgnoreKeys ik Ignore foreign keys. Ignores foreign keys when comparing and synchronizing databases.
IgnoreMigrationScripts ims When this option is selected, SQL Compare will ignore migration scrip ts when you compare databases.
IgnoreNocheckAndWithNocheck inwn Ignores the NOCHECK and WITH NOCHECK arguments on foreign keys and check constraints. When this option is selected, SQL Compare always applies constraints, even when NOCHECK and WITH NOCHECK are enabled. The ‘Ignore WITH NOCHECK‘ option will automatically be selected.
IgnoreNotForReplication infr Ignores NOT FOR REPLICATION in foreign keys, identities, constraint s and triggers.
IgnoreNullability in Ignore whether or not a column can allow NULL values. This means that when you change a column from NULL to NOT NULL (or vice versa), it won't appear as a difference during comparison, or be deployed as a change.
IgnorePerformanceIndexes ipi Ignores everything that the ‘IgnoreIndexes’ option ignores except primary keys and unique constraints.
IgnorePermissions ip Ignore permissions. Ignores permissions on objects when comparing and synchronizing objects.
IgnoreQueueEventNotifications iqen Ignore event notifications on queues.
IgnoreQuotedIdentifiersAndAnsiNullSettings iq Ignore SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements. Ignores these common SET statements when comparing views, stored procedures etc. Note that these statements will still appear in synchronization scripts.
IgnoreReplicationTriggers irpt Ignore replication triggers.
IgnoreSchemaObjectAuthorization isoa Ignores authorization clauses on schema objects.
IgnoreSquareBrackets isb Ignore starting and ending square brackets in object names which have been escaped using square brackets. This applies to textual objects such as stored procedures, triggers, etc.
IgnoreStatistics ist Ignore statistics. Ignores statistics when comparing and synchronizing.
IgnoreStatisticsNorecompute isn Ignore STATISTICS_NORECOMPUTE on indexes.
IgnoreSystemNamedConstraintNames iscn Ignore system named constraint and index names. Ignores the names of system named indexes, foreign keys, primary keys, default, unique and check constraints. The names will still be scripted and synchronized.
IgnoreTriggerOrder ito Ignore trigger order.
IgnoreTriggers it Ignore DML triggers. Ignores DML triggers when comparing and synchronizing databases.
IgnoreTSQLT itst Ignores the tSQLt schema and its contents, the tSQLtCLR assembly, the SQLCop schema and its contents, and any schemas and their contents with the tSQLt.TestClass extended property set.
IgnoreUserProperties iup Ignore users’ properties in comparison.
IgnoreUsersPermissionsAndRoleMemberships iu Ignore users’ permissions and role memberships.
IgnoreWhiteSpace iw Ignore white space. Ignores white space (newlines, tabs, spaces etc.) when comparing objects.
IgnoreWithElementOrder iweo Ignore the order of ‘with elements’ in non-CLR stored procedures, functions, triggers, DDL triggers, and views.
IgnoreWithEncryption iwe Ignore WITH ENCRYPTION statements on triggers, views, stored procedures and functions. This option overrides ADD ENCRYPTION.
IgnoreWithNocheck iwn Ignores the WITH NOCHECK argument on foreign keys and check constraints. When this option is selected, disabled constraints aren’t ignored, so this option is useful if you want to find out if a constraint is disabled.
IncludeDependencies incd Include dependencies. Includes dependent objects when comparing and synchronizing databases. For example, if a view depends on a table then the table will be synchronized when synchronizing the view.
NoAutoColumnMapping nacm Do not automatically map similarly named columns in mapped tables. Only columns with identical names will be mapped.
NoDeploymentLogging ndl Disables the behavior to append a logging statement at the end of the generated deployment script to log the deployment details to SQL Server Log in order for SQL Monitor to detect the deployment and mark it on the timeline for performance data.
NoErrorHandling neh Removes error handling from the deployment scripts to produce more readable SQL. Note that if you deploy using SQL Compare, the deployment is always stopped when an error is encountered. This must be used in combination with NoTransactions.
None n None.
NoTransactions nt Removes transactions from the synchronization scripts to produce more readable SQL. If the deployment includes memory-optimized objects, you must select this option to create a valid script.
ObjectExistenceChecks oec When this option is selected, SQL Compare checks for the existence of objects affected by the deployment by adding IF EXISTS statements in the deployment script. This option can be useful, for example, if you want to run the deployment script multiple times.
ThrowOnFileParseFailed tofpf Strict scripts folder registration. Throw an exception on registration if there is an exception when parsing a script.
UseClrUdtToStringForClrMigration uclr Do not use ALTER ASSEMBLY to change CLR types. This option is used only for SQL Server 2005 databases. If CLR types are to be synchronized, this option forces two rebuilds of the table with conversion to and from strings to update the CLR types, instead of using ALTER ASSEMBLY. For a detailed explanation, see the online help. This option affects the synchronization only.
UseCompatibilityLevel ucl Uses a database’s compatibility level instead of the SQL Server version.

Conclusion

Where the network resources are predominantly on Linux, it makes sense to have a Linux version of SQL Compare for those cases where a process is easiest to perform in Linux. Where work is on a live database that isn’t on the local workstation, there is little point in a Linux SQL Compare because all the work is done via network TDS IP connections from a Windows machine or VM. However, when comparing local databases, or where the actual work involves files, such as scripts folders, snapshots or project files, or comparing versions in source control to generate synchronization scripts, then it makes sense to be able to host SQL Compare in Linux or MacOS. I can certainly imagine that if the automation scripts are run in Bash, or the other components in the build are all Linux-based, it would be easier to use Linux SQL Compare.

 

Tools in this post

SQL Compare

Compare and synchronize SQL Server database schemas

Find out more