xSQL Data Compare for SQL Server - new build available

We just published a new build of the xSQL Data Compare for SQL Server. Following is the list of enhancements and fixes included in this build:

1. Allows session naming by clicking on the name without launching it
2. Adds tooltip on session panel that shows the left/right databases, useful when the names of Sql Server/Database are too long and can't be fully seen in the UI
3. Fixes an issue with screen resizing
4. Fixes an issue with print wrapping for windows that support printing.
5. Adds a UI formatter for XML data types
6. Improves the process of comparing select tables

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

How to deploy a SQL Server database to a remote host

CASE 1: you have direct access to both the SQL Server where the source database is and the SQL Server where the target database is.

1. First time deployment
• Backup / restore
1. Backup the database on the source
2. Copy the backup file to the target machine
3. Restore the database on the target
4. Create logins and set permissions as needed
• Compare and Synchronize
1. Create database on the target machine (blank)
2. Use xSQL Object to compare and synchronize the database schemas of the source and the target. 
3. xSQL Data Compare to populate the remote database with whatever data you might have on the source that you want to publish (lookup tables etc.)
2. Database exists in the target server
• Compare and Synchronize
1. Use xSQL Object to compare and synchronize the database schemas of the source and the target. 
2. Use xSQL Data Compare to push any data you need to push from the source to the target. Caution: be careful not to affect any data that exists on the target already.

CASE 2: You can not directly access the target server but you have a way to deploy SQL scripts on that server. As is indeed the case in most scenarios you also should have a way to get a backup of your database from that remote host. In this case follow those simple steps:

1. Restore the remote database on your local environment
2. Use xSQL Object to compare your source database with the restored database. Generate the schema synchronization script and save it.
3. Use xSQL Data Compare to compare your source database with the restored database. Carefully make your selections to ensure you push only the data you want to push from the source to the target. Generate the data synchronization script and save it. 
4. Deploy your schema synchronization script to the target machine. 
5. Deploy your data synchronization script to the target machine.

Both xSQL Object and xSQL Data Compare are completely free for SQL Server Express with no restrictions or limitations. Furthermore, for other editions of SQL Server the tools are free if the database has under a certain number of objects in it (current limitations are listed here).

Comparing 1TB database taking too long!

We got a call yesterday from a customer who was using our xSQL Data Compare to compare a 1TB database. He was concerned that the comparison was taking a very long time – when he called it had been running for about 10 hours and was still going! While we don’t think there are many users out there comparing 1TB or larger databases we thought it might be helpful to those few out there if we explained why such compare may take 10 hours or more depending on the environment. Here are the factors to consider:

1. Connection speed - in a typical scenario xSQL Data Compare is running on a client machine let’s call that Client1 and the databases being compared reside on let's say Server1 and Server2. In order to compare those databases the whole 1TB worth of data from Server1 and another 1TB worth of data from Server2 will have to be "brought" over to Client1. So you are gradually transferring 2TB of data over the wire (or over the air) and depending on how fast the connections Client1 – Server1 and Client1-Server2 are this process alone may take not just 10 but 30 or 40 hours.
2. Processing power – xSQL Data Compare running on Client1 needs to pair those millions of rows and compare them one by one. If you have a slow machine, even if the data is readily available it will take a long time to process all that data.
3. I/O and local disk speed – 2TB worth of data is being temporarily stored on the Client1 hard drive and that process alone may take a long time.

In short, when you are comparing large and very large databases don't be shocked if the process takes many hours to complete. The important thing is that, provided you have sufficient disk space on the machine where xSQL Data Compare is running, the databases will be compared successfully and you will be able to generate the synchronization script. 

xSQL Data Compare new build

New build of xSQL Data Compare that fixes an issue with the sync script serialization is available for download. Under certain conditions the serialization of the synchronization script would fail – while that failure is mostly transparent to the end user as the process will continue normally, it would be exposed in cases when the synchronization script is very large.

xSQL Data Compare provides for comparing and synchronizing data in two SQL Server databases. The current version supports SQL Server 2008/2005/2000 - all editions. xSQL Data Compare is free for SQL Server Express with no restrictions. For other editions of SQL Server it is only free if the number of objects does not exceed certain limits listed here: http://www.xsql.com/LiteEdition.aspx. You can download your free copy from here: http://www.xsql.com/download/sql_server_data_compare/

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/

Compare and Synchronize SQL Server Databases

Why compare databases?

Whether you are a developer that uses SQL Server on the back end to support your application or a database administrator in charge of monitoring and maintaining those SQL Server databases you often have to deal with different versions of the same database. For example, you may have a development environment, a testing or QA environment and a production environment and at any given time your database is in different "states" in each of those environment. As you work on implementing changes and additions to your applications you routinely make changes on the development database like adding tables, adding columns, changing types, creating indexes, adding new and modifying existing views, stored procedures, user defined functions etc.

At different points in the development you may need to promote all the work you have done on to the QA environment so that the QA team can start "pocking holes" in the work you have done. As soon as you do that you go back to your development World and continue working on your application and making database changes as the need arises so, it won't be long before the version of the database you are working on does not match the version the QA team is testing and that does not match the version of the SQL Server database that is running on your production servers.

Any professional in this position will keep meticulous records on each version of the database so that at any given time he would be able to tell what is different from one version to the other. That's well and good but it does not take much to realize that, especially if the database is relatively big and changes relatively many, sorting through such records to figure out what changes when (let alone how to change it back if necessary) is like looking for the needle in the haystack. That's why the need to have a tool that compares the SQL Server databases and tells you what is different.
 
How do I compare two databases?

xSQL Object which you can download from this site allows you to compare the schemas of two SQL Server databases with a few clicks, regardless of the location of those databases (as long as you can access those databases). The results of the comparison are displayed on an easy to use grid that shows the objects on the top section and the differences for the selected object on the bottom section. In addition of the side by side display of the selecte objects' scripts it also provides two change scripts - one to make the object on the second database in the comparison the same as the corresponding object in the first database in the comparison and abother change script to go the other way.
 
What do I do after comparing the two databases?

After you have compared the SQL Server databases in question, knowing on which direction you wish to transfer changes (example: if you were comparing the development version of your SQL Server database with the QA version it is likely that you would want to transfer changes from the development version to the QA version) you simply click on the right button (link) and xSQL Object automatically generates the change script required to transfer those changes. The script generated is safe and version specific (this is great for cases when you have different versions of SQL Server running on different environments - for example: you have upgraded the development environment to SQL Server 2005 or SQL Server 2008 but your QA and production environments are still on SQL Server 2000). At this point you can simply review the script to ensure that you do indeed intend to make those changes on the target database and then execute the script directly from xSQL Object's interface.
 
How about the data - the content of two SQL Server databases?

So, what if you have some sort of a distributed SQL Server database or a replicated database or some other scenario where you need to compare the actual data between two SQL Server databases to make sure that the replication has worked correctly or to see what records were changes since the last backup if you are trying to perform some auditing or for some other reason? Well, we thought of that to: xSQL Data Compare allows you to compare the data, the content, of two SQL Server databases, see where the differences are and then transfer all or some of those changes from one database to the other. xSQL Data Compare comes together with xSQL Object and the corresponding command line utilities as part of the xSQL Bundle that you can download from this site.

How much am I going to have to pay for those tools?

Not much at all - in fact it is likely that you may not have to pay anything - xSQL Bundle which includes both xSQL Object and xSQL Data Compare is completely free for SQL Server Express and comes in a free Lite Edition for other editions of SQL Server.

New build of xSQL Bundle available

A new build of xSQL Bundle that includes xSQL Object for database schema comparison and synchronization and xSQL Data Compare for comparing and synchronizing data is available for download from: http://www.xsql.com/download/sql_server_comparison_bundle/

The new build adds the ability to include and exclude object level permissions from the synchronization. Permissions are now shown under each object they're granted to or denied on. Statement permissions are listed under the database node in the comparison grid.

No dependency on SP1 of .NET framework anymore

Since we received a few messages from users having trouble with xSQL Data Compare because of the SP1 dependency, instead of "forcing" the users to install SP1 for .NET Framework we decided to remove that dependency altogether. You can simply download the latest build of xSQL Bundle from our site and install it over the current version you have on your machine.

You can download the latest build of xSQL Bundle from: http://www.xsql.com/download/sql_server_comparison_bundle/

xSQL Data Compare 3.0 requires .NET Framework SP1

We have receives a couple of reports from users who are not seeing the INSERT statements when scripting a table (schema and data) from xSQL Data Compare or when generating a synchronization script. The issue is related to a structure that we are using in version 3.0 that was apparently introduced with the SP1 of .NET Framework.

If you are experiencing this issue with xSQL Data Compare either install the SP1 for .NET Framework which you can get from: http://www.microsoft.com/downloads/details.aspx?familyid=79BC3B77-E02C-4AD3-AACF-A7633F706BA5&displaylang=en or email us if for some reason you do not want to install SP1 and would rather just go back to xSQL Data Compare version 2.5 which does not require SP1.

Synchronize with bulk insert – xSQL Data Compare

By default the database synchronization script that xSQL Data Compare generates contains one INSERT statement for every row that exists in the source database but does not exist on the target (destination) database. When the number of such rows is extremely large you can imagine that the synchronization script will likely get to be very large also and such script on a transaction will make for an extremely large transaction that is bound to create performance issues.

It is for that reason that xSQL Data Compare provides a little known “switch” called “Synchronize with bulk insert” which will change the default behavior and instead of generating one INSERT statement for each row will generate only one bulk insert statement for each table in the comparison making for a much more efficient synchronization script.

The “Synchronize with bulk insert” option can be found under the main menu Compare / Data Options.

In case you are not familiar with xSQL Data Compare – it is a simple tool that allows you to compare and synchronize the data in two SQL Server databases and supports SQL Server 2008, SQL Server 2005 and SQL Server 2000. It is free for SQL Server Express databases with no limitations and it is also free for other editions of SQL Server but with some limitations. You can read more and download your copy of xSQL Data Compare from our website at: http://www.xsql.com

xSQL Data Compare custom comparison keys

A conversation with a customer yesterday brought to light the potential confusion with the custom comparison keys that xSQL Data Compare allows the user to define "on the fly". The customer was worried that defining a custom unique key would change the structure of the table - so here is some clarification:

• when you define a custom comparison key for a table in xSQL Data Compare the database schema (structure) is not affected - no changes are made to the schema;
• the custom comparison key can include multiple columns from the table in question;
• the key must be unique - that is the combination of the values of the columns included in the key should uniquely identify a row in that table.
• the comparison key defined on one of the tables in the comparison should match the comparison key defined on the other table - the rows from both tables in the comparison are paired based on the key values.

xSQL Data Compare provides for comparing and synchronizing the content (the data) of two SQL Server databases. It supports SQL Server 2000, SQL Server 2005 and SQL Server 2008 and it is completely free for SQL Server Express and its predecessor MSDE. xSQL Data Compare can be downloaded as part of xSQL Bundle from: http://www.xsql.com/download/sql_server_comparison_bundle/

Support for SQL Server 2008 – new release

We are very excited to announce a major new release of the following tools which now provide full support for SQL Server 2008:

• xSQL Object V3 (free for SQL Server Express)
• xSQL Data Compare V3 (free for SQL Server Express)
• xSQL SDK V3
• xSQL Builder V2
• RSS Reporter V3 (free for one SQL Server instance)
• xSQL Object Search V2 (free tool)
• xSQL Script Executor V2 (free tool)

What's new - a detailed description of changes and enhancements on this new release can be found at: <>. Following are some highlights:

• Support for all new or enhanced data types such as geography, geometry, hierarchyID etc.
  Support for the table level change tracking feature introduced by SQL Server 2008.
• Data compression support for tables, primary key constraints, unique constraints and indexes.
• Support for the enhanced full-text catalogs and indexes.
• Support for Remote Service bindings for Service Broker.
• Improved object dependency handling.

You can download those tools from: http://www.xsql.com/download/

xSQL Bundle receives highest marks and praise from SSWUG

Just realized that we never published a link to this review of our database comparison tools xSLQ Object and xSQL Data Compare. The review was conducted by Stephen Wynkoop, Microsoft SQL Server MVP and SSWUG founder who was duly impressed with our products and gave them the highest rating possible in all aspects of the review, overall score, installation, usage and real-World usefulness. You can read Stephen's review here:
http://www.xsqlsoftware.com/reviews/sswug_xsql_compare_review.pdf