ApexSQL Diff, a SQL Server comparison and synchronization tool includes the ability to work from within SQL Server Management Studio. In addition of comparing and synchronizing SQL databases directly from SSMS, it can also compare and synchronize backups, script folders, snapshots and versions from source control projects. This should improve productivity and speed up common tasks.
DBAs sometimes need to compare structures between SQL database tables, or even whole SQL Server databases for that matter. For example, a stored procedure was executed while in SSMS, and it needs to be checked if everything went well. Is it really needed to minimize SSMS, launch ApexSQL Diff and start a new project, then set up the source and the destination database while entering the credentials for both? ApexSQL Diff’s SSMS add-in can compare SQL databases directly in just two simple clicks.
In order to install the ApexSQL Diff add-in, during the ApexSQL Diff installation process, an option will be provided to choose the SSMS version to be used. If there are different SSMS versions installed, choice can be made to use the add-in in all or just the ones that are needed:
Once ApexSQL Diff is integrated, in SSMS’s Object Explorer, right-click the SQL database that needs to be compared, and in the Schema compare sub-menu, click the Set as source command to select the database as the source or Set as destination command to select the database as the destination:
The Schema compare query tab will be shown, with the selected database in the appropriate panel, depending on the selection:
Use the SSMS’s Object Explorer as previously described to select the second SQL database that will be used in the SQL comparison process, or use the opposite panel directly:
To switch the source and destination databases, click the Reverse button:
It is still possible to select different SQL comparison data source types. Available choices are the database, backup, script folder, snapshot and source control:
After setting up data sources, switch to the Options tab to define comparison options like ignoring or including an attribute or object during comparison. The SQL database comparison add-in offers a set of predefined choices to compare SQL databases directly in SSMS, which can be accessed directly:
To set options do the following:
- For application defaults – click the ApexSQL defaults button
- For project defaults – click the My defaults button
- To save custom default options – click the Save as my defaults button
To better understand how a selected option affects the comparison, click the information tooltip on the right side of a desired option and a new window will be displayed showing an explanatory example:
Finally, to start the comparison process, click the Compare button. It will open the ApexSQL Diff standalone application where the comparison results will be shown. To create a SQL synchronization script and/or synchronize the SQL databases, continue using the standalone application.
Useful links:
How to: Compare and Synchronize the Data of Two Databases
How to: View Data Differences
Compare and Synchronize Data in One or More Tables with Data in a Reference Database
March 2, 2015
