xSQL Database Compare tools V3.5 released

We just released version 3.5 of our database comparison and synchronization engine xSQL SDK, the schema comparison and synchronization tool xSQL Object, and the data compare and synchronization tool xSQL Data Compare.

The new version includes:

• Support for SQL Server 2008 R2.
• The passwords for SQL Server connections are now encrypted and stored in the workspace file thus eliminating the need to enter the password each time the xSQL comparison tools are launched.
• Fixing the discrepancies between the name in the object definition and the name in the system catalog. This enhancement addresses an issue caused when the sp_rename procedure is used to rename objects with sql definition such as procedures, views, functions and triggers. The procedure sp_rename in these cases changes only the name in the sys catalog, but it does not change the name in the object's definition. This particular problem manifests itself mostly on SQL Server 2000 databases.
• A new schema options named "Include objects with invalid dependencies" replaces the previous option named "Include views with invalid dependencies"; in addition to views this option now affects stored procedures, functions and triggers.
• Trial expiration issue on Windows Vista and Windows 7 with UAC on. The enhancement addresses an issue with early expiration of xSQL Object and xSQL Data Compare trial period on Windows Vista and Windows 7 when the UAC is turned on - it guarantees that both tools will be fully functional with no restriction for the duration of the trial period. After the trial, the products will revert to Lite edition.
• Some enhancements and bug fixing in the command line utilities including a newly added error code that tracks script execution errors.
• A fix to stored procedure multi-line comments.
• An enhancement in the way xSQL Data Compare handles identity columns. Explicit updates of the identity columns are no longer supported and will trigger data warnings.

You can download the new version from: http://www.xsql.com/download/

Can a simple Alter Trigger statement cause serious blocking?

Most of the time it would not as it is a extremely quick operation but it certainly has the potential to. I ran across this the other day: an admin user was running a simple, “innocent”, Alter Trigger statement on a production SQL Server, he had done this many a times without any trouble so he had no reason to worry. Unfortunately, this time something went wrong – the Alter Trigger statement which usually executes instantaneously wasn’t completing and all of a sudden blocking alerts started firing left and right! He killed the Alter Trigger and all went back to normal, but he was puzzled: what had just happened?

After a quick investigation here is what I found: the Alter Trigger had blocked virtually everyone (it was a trigger on a critical, heavily used table) but it wasn’t the head blocker! Instead, a “long” query that appeared to be doing some sort of data aggregation had blocked the Alter Trigger statement. But wait how can that be, the query was written with the NOLOCK option - apparently the person who wrote it knew that this query could potentially wreck-havoc on the production system so he tried to make sure he wouldn’t block anyone. And yes, most of the time his query would not cause any trouble to “others” except… while the nolock directive ensures that the query takes no data locks the query still takes what’s called a schema stability lock (in other words it’s saying: I will read un-committed data but don’t change the schema on me while I am reading) which prevents the Alter Trigger from taking the schema modification lock it needs. So, while the Alter Trigger is patiently waiting in line to get the schema modification lock it needs the rest of the processes that desperately need access to that table start piling up!