SQL Compare main job is to find schema changes between two SQL Server databases. It will make a script matching the source database's schema to a target database's structure. It talks about how this can help with database development and release, such as making database scripts in version control, finding database drift, and automatically making deployment scripts.
Compare SQL changeshave been around for almost 20 years and have been used in ways that the people who made it could never have dreamed.
What is SQL Compare?
SQL Compare lets you look at two or more SQL Server tables side by side using a graphical user interface. It will look at the structural differences between the source and target databases and show the changes in the SQL DDL code of each table and code module next to each other. It will make a deployment script, also known as a "1-way synchronization" script or "migration" script, that will change the target database's structure to match the source database's structure. The script will try to keep the current data as much as possible and will give you a warning if it can't. Using its options and filters, you can change how SQL Compare compares items and which ones it doesn't look at.
Compare SQL changeshave a command line interface for both Windows and Linux. This interface may be subject to a different, automation-based license. Because of this, you can use scripts to make comparisons and deploy. SQL Compare can make a report from an HTML or Excel file from the command line or a script.
SQL Change Automation and SQL Source Control have the compare engine built into SQL Compare.
How does SQL Compare work?
Compare SQL changeslet you see how different versions of the same SQL Server database compare to each other in terms of their models. SQL Compare can work with any representation of a database from which it can build a "model" of the structure of that database. This means that the source and target don't have to be "live" SQL Server databases. In this case, it can turn a scripts folder with a list of DLL scripts for each database object into a model of the database or even just one build script. These can also be used with an SQL Compare Snapshot, an SQL Source control project, or an SQL Change Automaton project file.
When the comparison is run, SQL Compare will list database items unique to either the source or the target or present in both but not the same. It can automatically create the release (or "schema migration") script that will change the target so that its metadata is the same as the source. It will also show the differences in the metadata for each object in the form of a script.
If the target is a database, the script will add any objects missing from the target and drop any missing from the source. It will also change the definition of any object in both places to fit the source. Also, if the goal is an existing DDL code directory, files are changed to match the source database by adding, removing, or adding new ones. SQL Compare will let you know about any possible problems in all of these situations, even if it can't save the existing data. Before sending the migration script to the target, you can review it and make the necessary changes.
Compare SQL changeshave filters that let you choose which objects to compare, many choices that let you choose how to do the comparison, and any scripting that comes after.
The auto-generated schema migration script in SQL Compare works within a transaction and will roll back if mistakes happen. You will be told that if some objects, like full-text indexes, can't be deployed in a transaction, those objects will be deployed one at a time.
Conclusion
It can be hard to be sure that this kind of partially customized and partially generated deployment is always safe, even if it is run on the wrong version of the target database by chance. Suppose problems with SQL Compare auto-generated deployment processes keep stopping the deployment process. In that case, you should use a migrations-based deployment tool like Compare SQL ChangesAutomation to make a script that will upgrade a target database from a certain version to a new version.
You must be logged in to post a comment.