David Atkinson

14 July 2015

How to enhance database deployments with SQL Compare using DLM Dashboard

For a long time, SQL Compare has been the industry standard for comparing and deploying SQL Server database schemas. Packed with features that can be used to maintain a history of deployments, it makes it possible to build up a record of which objects were changed, when a change was applied, who made it, the SQL that was used, and even save roll back scripts.

The new – and free – DLM Dashboard extends its capability yet further and makes SQL Compare even more indispensable.

To understand how, let’s firstly highlight the major features of SQL Compare that enable users to keep a record of what has been deployed, and understand how it has changed from its previous state.

Saved scripts

At the stage where deployment scripts are reviewed, they can be saved to a file share or version control repository. As this is a manual process, it can easily be forgotten and we recommend automatically saving the scripts, which can be enabled earlier in the Deployment Wizard.

Roll back scripts

Roll back scripts can be created by double clicking the large blue arrow in the SQL Compare comparison results screen. This reverses it and turns it from blue to green, meaning that the changes will be applied from the database on the right hand side to the database on the left.

Do remember to cancel out of the Deployment Wizard after the script has been saved, otherwise you risk overwriting the database on the left hand side.

Schema snapshots

Rather than using roll back scripts, some users prefer to generate a snapshot of the target’s schema prior to deployment. This is enabled through the ‘Backup target before deployment’ checkbox.

The snapshot allows differences to be calculated at a later date by loading two different snapshots into SQL Compare, and makes it simple to generate roll back scripts. This is done by loading the snapshot as the source data source and comparing it against the database to be rolled back.

Be aware that using this rollback mechanism will only restore your schema. Any data removed during a deployment can only be retrieved from a backup.

Comparison reports

A comparison report is an HTML version of what is shown on the comparison results screen in SQL Compare. This is a record of the objects that have changed, and the nature of the change. This is generated by visiting Tools/Generate Comparison Results Report…

Logging deployments with the new DLM Dashboard

As can be seen, SQL Compare gives users a number of options for comparing and synchronizing SQL Server database schemas.

What if, however, a script is run on the target database from SSMS, another tool, or indeed another person? What if a DBA has made an emergency fix? In this instance, the deployment may fail because the target database is not in its expected state.

To address issues like this, DLM Dashboard ensures you always have a complete picture of your database environments by recording changes made within SQL Compare – and any other changes made in any other way.

DLM Dashboard works by monitoring the server being deployed to, using a server-level DDL trigger. This means that it is bullet-proof to not only planned deployments, but also those ones that happen when no one is looking.

The Dashboard tells you when the change was made, what the schema changes are, the SQL that applied the change and the user who applied the change. It’s even possible to set up email alerts to notify you in real-time when changes are made, so that you know the changes that have been made and can maintain a history of those changes.

To improve the way you enable deployments, download DLM Dashboard, free, by running Check for Updates in SQL Compare, or download it directly from the DLM Dashboard download page:

We’d also love to hear how you use SQL Compare to enable your deployments. Do you use roll back scripts or schema snapshots, for example? How valuable are the comparison reports? How are you using DLM Dashboard alongside SQL Compare? And if you’re new to DLM Dashboard, what features could we add in future versions to enhance your user experience yet further? Send me an email about it because we really do value customer feedback.