The basic idea behind SQL Compare is to check for schema discrepancies between two SQL Server databases. It can change the schema of one database to match another by generating a script to do just that. From automatically creating deployment scripts to identifying database drift and producing scripts in version control, this article covers it all as it pertains to database development and deployment.
Nearly two decades after its inception, Compare SQL changes continue to serve purposes that its creators could never have foreseen.
What is SQL Compare?
The SQL Compare graphical user interface allows users to quickly compare databases built using SQL Server. As part of its analysis, it will compare the two databases’ structures and display the SQL DDL code for each table and module side by side. The script will update the target database to match the source database in structure, keeping current data wherever possible and warning if it isn’t. It’s called a deployment script, a 1-way synchronization script, or a migration script. You may choose how SQL Compare executes this comparison and what items it ignores with its options and filters.
Compare SQL changes have a command line interface for Linux and Windows, which a separate automation-based license could govern. It enables you to automate deployments and comparisons through scripting. When run from a script or command line, SQL Compare can generate reports in Excel or HTML format.
SQL Change Automation and SQL Source Control incorporate the SQL Compare comparison engine.
How is SQL Compare implemented?
Database schemas can be compared using SQL Compare, and this tool is most useful when comparing versions of the same SQL Server database. The source and target databases don’t need to be live SQL Server instances; Compare SQL changes can work with any database representation, allowing it to build a “model” of the database’s structure. An example of a scripts folder contains DLL scripts for each database object; this folder can be parsed into either a database model or a single build script. Likewise, it is compatible with SQL Change Automaton, SQL Source Control, and SQL Compare Snapshot projects.
When SQL Compare is done, it will produce a list of database items that are unique to either the source or the target or that are unique to both but have differences. In script form, it will show you where the object metadata differs from the source and how to auto-generate the deployment script to change the target object’s metadata to match the source.
If the destination is a database, the script will change the definition of any object in both the source and the target to match, drop any object in the target that does not exist, and create any object in the source. Also, if the destination is an existing DDL code directory, files can be added, removed, or changed to match the database source. SQL Compare will alert you to potential problems in any scenario, even if it cannot save your current data. The generated migration script can be reviewed and adjusted before being applied to the target.
Compare SQL changes have filters that let you choose which items to compare and a bunch of settings that allow you to control the comparing and scripting processes.
When mistakes are detected, the SQL Compare auto-generated schema migration script will automatically roll back and continue running within the transaction. Any objects that cannot be deployed in a transaction, including complete text indexes, will be deployed separately after you receive a warning.
ConclusionSince its inception many years ago, Compare SQL has evolved in response to the needs and wants of its users. Its goal in developing a robust set of choices was to make it helpful to the broadest possible audience of developers and database administrators. A few address scripting styles, and many figure out which SQL Server object properties in a database make a difference. Many figures show that SQL Server objects are completely disregarded and not included in synchronization scripts. Some are incredibly helpful, some are just convenient, and a select few have alleviated the suffering of a few people dealing with uncommon issues.
