Tuesday, November 27, 2012

SQL Schema Compare – include dependant objects

The ultimate goal of a database schema comparison and synchronization tool, like xSQL Schema Compare for SQL Server (aka xSQL Object), is to allow database administrators and database developers to safely and efficiently propagate database schema changes from one environment to another, most commonly from development to staging to production.

By default, xSQL Schema Compare for SQL Server includes all database objects in the comparison and synchronization and arranges the synchronization script in such a way as to respect the dependencies between the database objects, so that the script can be successfully executed on the target.

However, often the user needs to run a partial comparison and synchronization including only certain objects. Let's say the user chooses to synchronize the database view vw_01 but neglects to include table tbl_01 on which this database view depends. In a scenario like this there is a good chance that the synchronization script will fail to execute successfully on the target (table tbl_01 might not exist in the target or might be different). The user will then have to identify the cause of the failure and go back and include table tbl_01 in the synchronization, but that is not the end of the story, there may be many similar situations involved and the script will keep failing. Going through this painful and expensive process of identifying the object dependencies through trial and error, would defeat the purpose of a schema comparison and synchronization tool.
xSQL Schema Compare for SQL Server provides a critical comparison option "Include dependant objects" which is ON by default. After the user has selected the database objects that he wants to include in the synchronization, xSQL Schema Compare will automatically identify all the additional objects that must be included in the synchronization for the script to execute successfully on the target, and will include those objects in the proper order regardless of the user selection.
We often get support requests from users with the following question: "I have specifically excluded those database objects from the synchronization – why am I still seeing them in the synchronization script?!"  The answer is the "include dependant objects" option the purpose of which was explained above. You can however easily turn the option off by un-checking the corresponding checkbox under comparison options (see the screen shot below).
xSQL Schema Compare for SQL Server is the best database schema compare tool in the market available to you at an un-beatable price – free for SQL Server Express with no limitations and also free for other editions of SQL Server but with limitations based on number of objects in the database (see editions here: http://www.xsql.com/products/sql_server_schema_compare/editions.aspx). You can download your copy today from: http://www.xsql.com/download/package.aspx?packageid=10

No comments:

Post a Comment