Jess Maton

30 September 2019

• SQL Compare
• Database Builds and Deployments

SQL Compare 14.0: A Command Line Interface on Linux and support for SQL Server 2019

SQL Compare is the industry standard tool for comparing and deploying database schema changes, and version 14.0 now provides a Command Line Interface (CLI) on Linux (available as a beta, at time of writing). Teams working with SQL Server on Linux, or across multiple platforms, benefit from seamless development processes, driven by SQL Compare, without the expense or ‘friction’ of needing to maintain a Windows VM on their Linux servers.

SQL Compare 14.0 also comes with support for SQL Server 2019, so users can work in the most up-to-date version and access SQL Server’s newest features, while using SQL Compare.

SQL Compare Command Line on Linux

We’ve been hearing from a rapidly growing number of customers who have run SQL Compare on Windows for many years, and rely on it every day, and now need access to the same development and deployment capabilities when working with SQL Server databases on Linux.

With SQL Compare 14.0, in addition to the traditional Windows Installer, the CLI is also available as a docker container image for Linux. This means that teams can compare database schema, find and fix errors, and create deployment scripts in minutes, all on their preferred operating system (OS).

Why a CLI for Linux?

The DevOps movement has long preferred services that can run on Linux, and within containers. The dread that Ops teams have traditionally felt, on being given care of a Windows application, stems largely from the need to set up a completely different set of Windows maintenance routines, using unfamiliar tools.

With SQL Server now running comfortably on Linux, Ops teams can allocate, configure and manage SQL Server instances, for development and test work, running on Linux servers. They can also run High Availability systems, including clusters, on Linux. Cost savings are often a consideration too, with comparable workloads often proving more economical, in terms of hourly rate, to run on Azure Virtual Machines on Linux rather than Windows.

Many development teams, as a result of all this, need or choose to develop on MacOS/Linux, or now inhabit a network of mixed platforms, for example doing core development on Windows but using docker images of SQL Server, running on Linux or MacOS, for test data. These teams can now run SQL Compare CLI directly on Linux, rather than having to start the Windows version from a Windows Virtual Machine.

When comparing a live, remote database, users can continue to use the tool on a Windows machine or VM, via network TDS IP connections to the remote servers. However, when comparing local databases running on Linux, or where you need to work with files, such as scripts folders, snapshots or project files, then you’ll want to run SQL Compare command line natively on Linux (or MacOS).

Getting Started with the Linux CLI

To install the SQL Compare CLI for Linux, visit the documentation page. There, you’ll find a link to pull the docker image from docker hub, and then you can run it, following the guidelines supplied. You’ll also find in the docs a few example commands for operations such as comparing a live database to a scripts folder, and so on.

Once you have these working, check out Phil Factor’s dissection of the SQL Compare CLI and its capabilities. He installs it on a Linux-based NAS server and shows how to run a whole range of database and script-based comparison operations and automate them using PowerShell.

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"

Using the Linux CLI to create a snapshot of a database that’s running on a Linux SQL Server

He demonstrates how to rapidly pass files to and from the container and the host, and how to mount to the specific directory on the NAS where the files are stored, meaning that the Compare CLI can write directly to the shared directories that are visible to Windows.

Support for SQL Server 2019

SQL Server 2019 promises easier management of big data environments with big data clusters, data virtualization capabilities, more advanced analytics, and a lot more.

SQL Compare 14.0 comes with support for SQL Server 2019, so early adopters of the latter still get full support from the former, during development work. Any new features or enhancements that affect the definition of any database schema objects will be fully supported by the comparison, synchronization, and deployment features of SQL Compare.

For example, Azure SQL database, and now SQL Server 2019, include a new ADD SENSITIVITY CLASSIFICATION command, which allows us to apply data sensitivity classifications to our columns, so we can the audit who access to this data. With SQL Compare 14.0, you’ll immediately be able to detect and deploy any changes in these classification settings.

Synchronizing column sensitivity classifications in SQL Server 2019 databases

Try out SQL Compare 14.0, let us know what you think

The SQL Compare 14.0 CLI on Linux is currently in beta, so we’d love to hear what you think about this new feature and how we can improve it.

If you already have an active support license with SQL Compare, you can upgrade within your client or download the update directly from our website. If you’re new to SQL Compare, download a free 14-day trial.