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

Monday, November 26, 2012

Schema Compare - now it's different, now it's not

When comparing two SQL Server database objects, like comparing two tables, comparing two stored procedures, comparing two triggers, comparing two functions etc., the result of the comparison is not a clear cut "equal" or "different". The result depends on what you care to compare. The simplest example that demonstrates this point is the issue of spaces in the object definition: let's say you are comparing [DB1].[SP1] with [DB2].[SP1] and  further let's assume that both stored procedures are identical except for the fact that [DB2].[SP1] contains an extra blank line at the end of the definition. The question is: are the stored procedures [DB1].[SP1] and [DB2].[SP1] equal or are they different? Well, in this particular case if you only care about the functionality you would say that they are equal but if you also want those stored procedures to appear visually identical then you would say they are different.

Like the example of "spaces in the definition" above, there are many other elements that affect the result of the comparison of different types of database objects. xSQL Schema Compare for SQL Server exposes over 40 comparison options that drive the comparison engine and affect the results of the comparison between two SQL Server database schemas giving you total control over the comparison and synchronization process.

While this level of flexibility and control is very helpful for many users it is also often the cause of confusion for others. You compare table1 to table2 and they show as equal but a minute later when you run a re-compare they show as different even though you did not make any changes to the structure of those two tables! After a closer look you realize that in the first compare you did not consider the "lock escalation property" whereas on the second run you did and that caused those two tables to appear as different.

So, does the user need to spend time understanding all those options in the new xSQL Schema Compare for SQL Server? No, absolutely not! In over 95% of database schema comparison and synchronization cases the user does not need to look at or even be aware of the existence of those comparison options – just compare, generate synchronization script and execute it on target.

xSQL Schema Compare for SQL Server is free for SQL Server Express – no limitations. It is also free for other editions of SQL Server but with some limitations on the number of objects in the database.
Download your copy of xSQL Schema Compare for SQL Server here…

Wednesday, November 14, 2012

xSQL Schema Compare for SQL Server v4 released

Faster, safer, free (lite edition only) – publishing database schema changes to production has never been easier! The just released version 4 of xSQL Schema Compare for SQL Server (previously known as xSQL Object) is built on top of a completely redesigned, faster, scalable database schema compare engine, and brings a new streamlined, easier to use and much more efficient interface. Here are the highlights:
  • Full support for SQL Server 2012.
  • Enhanced support for SQL Server 2008 and SQL Server 2005 (some objects that were partially supported or not supported in previous versions are now fully supported).
  • Support for SQL Server 2000 has been dropped. If you still need to compare and synchronize SQL Server 2000 databases you can contact us to obtain the older version of the schema compare tool.
  • Over 100 options (object type inclusion/exclusion, comparison options, scripting options,  and execution options give you total control over the comparison and synchronization process.
More details on the new release can be found here: http://www.xsql.com/products/sql_server_schema_compare/release_notes/v4/
The new version will continue to be free for SQL Server Express with no limitations and also free for other editions of SQL Server with some limitations - details here:  http://www.xsql.com/products/sql_server_schema_compare/editions.aspx
Download your copy now and see what you have been missing: http://www.xsql.com/download/package.aspx?packageid=10