In a SQL database lifecycle two major deployment tasks are: the building of a new SQL database and an upgrade of the existing one. ApexSQL Diff is a SQL database comparison tool that can help for both of them; it allows deploying SQL scripts saved in a script folder to a new or an existing SQL database. In various “real world” scenarios oversights and omissions are plausible, leading to many errors or database structure/objects corruption. In cases like these, it’s good to have a script ready so that a SQL database with test objects can be easily rebuilt even if something goes wrong.
How can ApexSQL Diff help with these SQL script deployment and database SQL database building tasks?
To build a SQL database directly from the script folder used as the source simply create a new SQL database, leave it blank and use it as the comparison and synchronization destination. ApexSQL Diff will synchronize it by creating all objects from the script folder used as the source.
The process is similar when upgrading a SQL database, except that the destination will be a SQL database that already has some objects. When starting ApexSQL Diff and opening a new project, the available options are shown in the New project window:
By selecting the Script folder as a data source type and setting up the script folder location, the script source is set up and all that remains is to select the destination data source. Additional options are available under the Advanced options: Schema mapping, Object filter, Package and Additional scripts.
Schema mapping enables specifying how owners/schemas are handled during the SQL databases comparison. By default, schemas of the same name are automatically mapped to each other:
The Object filter allows users comparison and synchronization of only specific SQL objects, a useful option when updating a live SQL database from a backup or test script on a daily basis. It is an invaluable feature when it comes to saving time, especially in cases of a large number of database objects:
Once all the options are set, click the Compare button and the process leads to the main grid window containing the comparison result:
Here, ApexSQL Diff allows users further synchronization tuning by selecting SQL objects needed to update a live SQL database from a script folder as a source. It is even possible to track differences per individual object on a script level by using the Script difference view panel:
After finishing the SQL objects synchronization fine tuning, the synchronization of the scripts and the live SQL database can commence with the click on the Synchronize button from the Home tab:
After the first step, in which direction of synchronization is set, in the second step, ApexSQL Diff will check dependencies:
The next step before a synchronization script is generated offers a possibility to save a copy of the synchronization script. Saving a copy of the synchronization script is a good way to keep track of updates and deployments over a SQL database, and post-synchronization analysis, if necessary:
In the last step, ApexSQL Diff generates Summary and warnings, a step showing synchronization summary and potential problems or objects that cannot be scripted and/or synchronized, and allowing to preview the impact of the synchronization script before executing it:
The process described here is a synchronization using a script folder as a data source and a live SQL database as a destination, offering an efficient solution to build a SQL database and synchronize its structure and objects to another database through the highly customizable synchronization procedure.
Useful resources:
How to: Generate a Script (SQL Server Management Studio)
Generate and Publish Scripts Wizard
How to: Deploy Changes to New or Existing Databases
March 2, 2015
