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.
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.
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.
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.
Was this article helpful?
Also in DevOps
A lot of work with Flyway is going to take place on development machines with the Flyway command line installed. Another healthy chunk of the work will be on dedicated instances used for Continuous In...
Also in Blog
Going strong into its fourth year, we published the 2020 State of Database DevOps report at a time when we couldn’t have predicted the changes to come for every industry. However, it still contained...
Also about DLM Dashboard
As a DevOps engineer at Redgate, I’m one of those responsible for keeping the systems going behind the scenes. That’s everything from the internal systems and databases to the infrastructure that ...
Also about SQL Compare
It's a tedious task to have to compare two versions of a SQL script, side by side, for example to find differences between the version of the script on Production, and the one on the Test system. As a...