Synchronization tools for SQL Server Express

Most of the tutorials/case studies I have presented in this blog have had SQL Server Enterprise edition as their primary focus. However, the reality is that, this version, if you didn't get the hint from its name, is mostly for big enterprises that need advanced features like SQL Server Agent or Analysis Services. In any other case this version is a bit of an overkill. In these cases, SQL Server Express is a very good starting point because it's free and offers a lot to work with, such as a full database engine, full T-SQL support, full text search, native XML, and some very useful built in tools like SQL Server Management Studio. A few classic examples where SQL Server Express is a very good option are:

• Deploying desktop windows solutions that use SQL Server to users who do not own a SQL Server. Given that it's free, its setup can be embedded in the application's setup so that Windows application can enjoy most of SQL Server's functionality.
• Lightweight web applications that only need to use small SQL Server databases such as websites using a CMS like Umbraco.
• Testing and developing applications with a local database.

Although it has some limitations regarding the processing power it can use, computer memory available and database size, it is suitable for a wide range of applications. Now, this might be something that one does not think of right away, but at some point in time, it's certain that there will be the need to perform some kind of database synchronization. This is where SQL Server Express has another advantage. Some of the synchronization tools, xSQL Schema Compare and xSQL Data Compare included, follow the same 'no charge' pattern set by Microsoft for this version of SQL Server. The Lite version of these two tools, when used with SQL Server Express offers the full functionality of the licensed version. Let's first have a look at the windows desktop solution case which, in my opinion is a very interesting case where schema synchronization is useful.

Suppose you have created and deployed a desktop Windows application that uses a SQL Server database. A good example here might be an app that does some sort of system monitoring and stores any data it finds in the database. SQL Server Express is a clear choice for the database because there is no complex functionality required and the database size is very likely to be small. Let's also suppose that you have just developed a major update for the app that includes a lot of database changes. How would you go about transferring those changes in the clients' databases? One option would be to generate the full schema script from SSMS and during the update's installation on the client, run that script to recreate the database. As you can imagine this is not an optimal choice because it would delete all the data that are already saved in the database. Another option is to manually generate the script but, in most cases, simply mentioning this, would get you a few "are you kidding?" looks as it is a very tedious and error prone process. Buying a comparison tool seems like a big waste of money if you're going to use it just for this one thing, so here is where the Lite version of xSQL Schema Compare comes to the rescue. For absolutely no cost you get a tool that, on SQL Server Express, will compare the 2 database versions and generate a synchronization script that can be run during the installation process and perform all the necessary changes in the client database.

A scenario in which data comparison on SQL Server Express might be necessary is the development of websites with a CMS. Content Management Systems store all the content management-related data in a database. Take Umbraco for example. It uses an element called Document Type to provide the structure of different webpages. This element stores all the info it needs in a database which can very well be a SQL Server Express database as this type provides the necessary functionality and more. It's always a good idea to develop these sites locally or in a development server and once the changes are fully tested, upload those on the live server. Of course along side this upload, the data on the database need to be transferred as well so that the site admin won't have to recreate the new content structures. Backing up and restoring the database is not a good idea because there are some data (like the actual content of the site) that do not need to be transferred, and if the live site is on a Cloud service like Azure, restoring is not possible. Again, the best option here would be a synchronization tool and a very good choice is xSQL Data Compare. Since the synchronization will be performed on SQL Server Express, the FREE Lite version of xSQL Data Compare can be used to transfer the data. Also, the synchronization process is highly customizable (refer to the online help), so you can manually determine which tables and what data in those tables you want to synchronize. This way, only the data regarding the content structure will be transferred.
In conclusion, I think it's safe to say that the two scenarios mentioned here are a very small portion of the huge number of cases where the application of SQL Server Express is enough to fulfill a software's database needs. Keeping in mind that database synchronization is a process that sooner or later will take place in most of these cases, the extension of the 0 cost principle of SQL Server Express to its synchronization tools like in the case of the Lite versions of xSQL Data Compare and Schema Compare is something that can greatly reduce the development and maintenance costs of software.


   

Read More

Database synchronization in the Software Development Life Cycle

Reading the title, most of you are probably thinking: What does database synchronization have to do with the Software Development Life Cycle (SDLC)? Stick around, because I'll get to that in just a minute. First, let me give a quick introduction to SDLC for those one or two developers out there who, by an impossible twist of fate, may have never heard of SDLC in their working experience. SDLC is a multi-phase process which ensures that good software is supplied to the customers. The term 'good software' has quite a broad definition but generally, 'good software' implies software that meets the requirements, has been tested thoroughly and has minimal errors. SDLC has a number of variations, but usually the software will go through these 5 stages:

1. Requirement analysis 
2. Design
3. Implementation / Development
4. Testing
5. Maintenance

Since, the focus of this article is database synchronization, I'll skip the first three stages and go straight to the stages which deal with database synchronization, which are testing and maintenance.

In the context of testing, database synchronization is useful in preparing the testing environment. For example, usually when a web application or a new feature is completed it will first be published on a test domain which is made available only to some chosen individuals / groups. This test site will probably need its own database that will have to be created or updated every time a change is made. You could backup the development database and restore it in the test environment or generate the database's script and execute it on the test environment but this would require the database to be recreated every time which can be a very time consuming process for large databases. Also, this is not possible if the databases are in the Azure cloud environment. The best option here is to use a synchronization tool like xSQL Schema Compare or xSQL Data Compare. So, you could use xSQL Schema Compare to transfer any schema changes from the development database to the testing environment and xSQL Data Compare to transfer any changes in lookup data. As a side benefit, xSQL Schema Compare can also be used to take periodic snapshots of the database in order to keep track of the changes made to it and rollback any of them is necessary.

Since the synchronization process is fairly straightforward and more or less the same in most of the scenarios, I'll first explain the other scenario in which database synchronization is necessary and then demonstrate how it's done and how to automate it.

The other, very important stage where database synchronization might take place in, is maintenance. In this stage, this process is done in both directions. First of all, for every bug fix or new feature, schema changes and lookup data are transferred from the development environment to the testing and then the live one. This process is the same as the one in the testing stage. Secondly, to make sure that the software is working correctly, quality assurance is performed. Quality assurance can have its own environment or it can use the testing environment. One thing is for certain though, it needs to have data that is as close as possible to the live data. Obviously, no type of data satisfies this condition better than the live data itself. By using xSQL Data Compare, these live data can be transferred to the quality assurance environment where they can then be used as needed.
Without further ado, let's proceed to a demonstration of schema and data synchronization. I'll be using the AdventureWorks2012 database in this demo. I made a copy of this database (AdventureWorksCopy) which will be used as the testing environment's database. Suppose this database belongs to a company that has decided to extend its medical and dental benefits to the employee families. Obviously, they will want to have some data in the system for the employee's family members. To do this, a table called EmployeeDependant is added to the HumanResources schema in the development database which has records for the employee's families. Of course, before this feature can be published it needs to be tested, which means that the schema changes need to be transferred to AdventureWorksCopy. There are 4 very simple steps to this process.

1. Add the databases in xSQL Schema Compare.
2. Compare AdventureWorks2012 with AdventureWorksCopy. The comparison reveals that there is a table EmployeeDependant in AdventureWorks2012 that does not exist in AdventureWorksCopy (note the one-way arrow to the right).
   
3. Generate the synchronization script.
4. Execute the script. By opening the database in SSMS I can see that the EmployeeDependant table is added to the AdventureWorksCopy database.

The new EmployeeDependant table has a column named 'RelationshipWithEmployee'. Instead of it being a simple VARCHAR column it's better to have it as a foreign key to a 'Relationships' table. The records in this table will probably never change (there is a fixed number of relationship types in a family), so it can be used as an example of the lookup data that may need to be transferred from the development environment to the testing one. Doing this synchronization with xSQL Data Compare is, again, a very straightforward process. Before I show this process' steps, here is a screen shot of the data in the Relationships table that will be transferred to the AdventureWorksCopy database:

To add these data to the Relationships table in AdventureWorksCopy database do the following:

1. Add the databases to xSQL Data Compare.
2. Choose the objects that need to be compared. In this case, only the Relationships table will be synchronized, so only that table will be selected from the objects to be compared.
   
   
3. Compare the tables
4. Generate the synchronization script for the AdventureWorksCopy database, Here is the script that is generated:
   

   SET XACT_ABORT ON;
   SET ARITHABORT ON;
   SET NUMERIC_ROUNDABORT OFF;
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   
   
   BEGIN TRANSACTION;
   
   
   /*-- -Insert(s): [HumanResources].[Relationships] */
   SET IDENTITY_INSERT [HumanResources].[Relationships] ON;
   INSERT INTO [HumanResources].[Relationships] ([Id],[RelationshipName]) VALUES(1,'Spouse');
   INSERT INTO [HumanResources].[Relationships] ([Id],[RelationshipName]) VALUES(2,'Sibling');
   INSERT INTO [HumanResources].[Relationships] ([Id],[RelationshipName]) VALUES(3,'Parent');
   INSERT INTO [HumanResources].[Relationships] ([Id],[RelationshipName]) VALUES(4,'Child');
   SET IDENTITY_INSERT [HumanResources].[Relationships] OFF;
   
   
   
   COMMIT TRANSACTION;
   

5. Excecute the script. After execution, if a select query is run on the Relationships table AdventureWorksCopy database, it will display the following data: 

Using a similar process to the one in this demo you can copy the data in the live database in the Quality Assurance database and run any tests that you need.

In most cases, these tasks are repetitive. For example, an organization might have the practice of synchronizing the live data with quality assurance every 3 days. Instead of doing this manually every time, you can just generate a XML configuration file for xSQL Data Compare Command Line by clicking the button in the picture below and then, using Windows Scheduler, schedule the comparison to be run periodically with that XML file as an argument.

Automation of the schema comparison is exactly the same as automation of data comparison. The only difference is that xSQL Schema Compare Command Line needs to be used.

In conclusion, the benefits of using synchronization tools like xSQL Schema and Data compare are quite obvious. They can simplify and automate parts of the SDLC that are trivial and boring and would otherwise, take a relatively long time to complete.

Read More

Comparing two tables with millions of rows

Nowadays the number of the users of our SQL Server tools dealing with very large data sets is growing rapidly and that has been reflected in a significant increase of queries regarding comparing the data in two large databases or even just two very large tables. As a result we decided to explain once more how our SQL Data Compare handles the comparison and what the user can do to speed the process up.

First the basics - here is what happens when you say compare ServerS.DatabaseS.TableS with ServerT.DatabaseT.TableT (I am using S to denote the Source server /database / table and T to denote the Target server /database/ table):

1. xSQL Data Compare  will connect to both servers and read the table definitions for both sides
2. It will then try to identify a common unique key (primary key is the first choice) that will be used to pair the rows. At this point it gives the user the opportunity to change the comparison key if the user thinks a different existing key would be better, or even create a custom comparison key. 
3. Once a comparison key has been selected it's time to compare the two tables:
1. Read rows from TableS and from TableT and bring them to the machine that is performing the comparison
2. Pair the rows based on the comparison key
3. Compare the values for each column row by row
4. Display the comparison results
4. Next step is to generate the synchronization script that will make TableT same as TableS
5. Last step is to execute the synchronization script on the target server.

Now, steps 1 and 2 above are very quick and not taxing at all for any of the systems involved, however, when you reach step 3 is where the "trouble" starts. Let's suppose that tableS and tableT contain around 100 million rows each and each of them is over 10GB in size. You can easily imagine how expensive this operation will be - the performance will depend on the parameters of the servers hosting those databases, parameters of the machine running the comparison and the speed of the connection between the machine running the compare to each of the servers. Here are the things that xSQL Data Compare does to make this kind of comparison possible (note that a lot of the tools in the market will not be able to do this at all):

1. carefully controls memory usage on the machine running the comparison by processing the data in chunks and serializing the data to disk
2. dynamically managing the comparison results grid to only load portions of the data at time, on demand. 

No matter how large the tables may be, the xSQL Data Compare will succeed in comparing and synchronizing those tables as long as there is sufficient free disk space on the machine doing the compare. However, understandably, the operation may take a very long time. So what can the user do to speed up the operation? There are two simple but very important steps that the user can take:

1. exclude from the comparison the columns that may not need to be compared, especially the blob type columns. 
2. use the "Where Clause" to exclude the rows that do not need to be compared. For example if your table contains a "date updated" time stamp and you know that you synchronized those tables a week ago then you can use the where clause to instruct the Data Compare tool to only compare rows where the "DateUpdated" is > than the last time you synced the tables. This will significantly reduce the amount of work and the process may only take a few minutes instead of a few hours. 

 In order to exclude certain columns and set a "where clause" you would need to drill down the the table pair that is being compared (see the screen shot below) CodeProject

Read More

How to move your SQL Server database to SQL Azure – easy as 1, 2, 3

Are you preparing to move your SQL Server databases to SQL Azure? In the words of one of our customers that just completed his transition "using xSQL's compare tools the process was as easy as 1,2,3": 

1. Download and install the xSQL Comparison Bundle (1 minute)
2. Run xSQL Schema Compare – add your SQL Server / Database to the workspace; Add your SQL Azure / Database to the workspace; Compare, Generate Sync Script, Execute (4-8 minutes*)
3. Run xSQL Data Compare – add both servers/databases to the workspace; Compare, Generate Sync Script, Execute (5-20 minutes*)

All done, now your database is on SQL Azure. 

* time estimates for steps 2 and 3 depend on the database size and are ranges from this one customer's experience with about 12 databases that they transferred. 

CodeProject

Read More

xSQL Compare Tools with Azure support released

We just released version 7 of our SQL Server Schema and Data Compare tools with full support for SQL Azure v11 and SQL Azure v12. You can now compare and synchronize SQL Azure databases with on premise or on the cloud SQL Server databases. Platform differences are handled automatically and are transparent to the end user. Details of the new release are listed here http://www.xsql.com/products/sql_server_comparison_bundle/release_notes/v7/

Download the new version now: http://www.xsql.com/download/sql_server_comparison_bundle/

Read More

xSQL Compare Bundle V5 with support for SQL Server 2014 released

We just released version 5 of our SQL Server comparison and synchronization tools, xSQL Schema Compare and xSQL Data Compare, with full support for SQL Server 2014. The release notes provide a more detailed description of what's new in this version. Here is a summary:

• SQL Server 2014 support: full support for SQL Server 2014 including all new and improved SQL Server 2014 objects such as memory-optimized filegroups, memory-optimized tables, hash and range indexes, columnstore indexes, memory-optimized table types, natively-compiled stored procedures, primary and secondary selective xml indexes, Xml index and namespace paths, etc.
• New comparison options:  in xSQL Schema Compare you can now choose to include/exclude database filegroups and memory-optimized tables from the comparison and synchronization, whereas in xSQL Data Compare you can now choose to include/exclude from the comparison memory-optimized tables and columnstore tables.
• Filegroup mapping: xSQL Schema Compare version 5 introduces filegroup mappings feature that allows you to easily handle complex database comparison scenarios involving databases with different filegroup structures.
• Improved workspace: consistent look, feel and functionality in both comparison tools the xSQL Schema Compare and the xSQL Data Compare; new coloring scheme for comparison sessions allowing you to better organize the comparison sessions in the workspace; separate tab for schema snapshots etc.

xSQL Schema Compare V5 supports SQL Server 2014, SQL Server 2012, SQL Server 2008/2008R2, and SQL Server 2005.

 

xSQL Data Compare V5 supports SQL Server 2014, SQL Server 2012, SQL Server 2008/2008R2, SQL Server 2005, and SQL Server 2000.

 

You can download the new version of the comparison tools as one package from: http://www.xsql.com/download/sql_server_comparison_bundle/

Read More

SQL Comparison Bundle new build - multiple workspaces

We just published a new build of our SQL Server Comparison Bundle tools with support for multiple workspaces. The new build also contains the following fixes:

• Fixes an error that occurs when the database name contains unicode characters.
• Fixes an error related to the columns that participate in an index.
• Fixes a scripting issue related to database compatibility level.
• Fixes an issue related to replicated tables.
• Fixes a refreshing problem with schema comparison grid.
• Improves the handling of SQL Server multi-line comments.
• Fixes a scripting error related to "EXECUTE AS" clause of CLR database triggers.

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

Read More

A better way to license xSQL database deployment tools

Today we are introducing a new, more efficient and much more affordable way to license our database deployment tools, SQL Schema Compare, SQL Data Compare, xSQL Builder and Script Executor - instead of purchasing a perpetual license for each tool you can now purchase a one year Silver Subscription that includes all four tools.

How is the subscription better? The answer is simple - it is a lot cheaper initially, $399 versus $1,497, and the cost differential gets better every year, subscription renewal is only $199 versus the yearly maintenance cost of $250 for the 4 tools.

I have a Comparison Bundle license, can I switch to a subscription? Yes, if you have a Comparison Bundle license you can purchase a Subscription Renewal for only $199. You will need to provide the Comparison Bundle license.

Read More

Installing xSQL Comparison Bundle on Windows 8

When installing our comparison tools (SQL Schema Compare and SQL Data Compare) on a Windows 8 machine the installation may fail with the message “SQL Server Comparison Bundle v4 Setup Wizard ended prematurely because of an error” (see screen shot below).

Cause: by default on a Windows 8 machine only .NET Framework 4.5 is enabled whereas previous versions of the .NET Framework are not enabled.  

Solution: enable .NET Framework 3.5 (this includes .NET 3 and .NET 2) on your machine. To do this go to Control Panel / Programs / Programs and Features / Turn Windows Features on or off and then check the “.NET Framework 3.5.1” checkbox and click OK (the machine needs to be connected to the Internet while this is being done. After enabling .NET Framework 3.5.1 re-run the installation of xSQL Comparison Bundle and it should install without a problem.

Read More

SQL Server Comparison bundle - new build available

A new build of the SQL Server Comparison Bundle is available for download. The changes implemented are relatively minor however, we recommend that if you are using version 4.x of the tools you download and install this new build, which:

• Fixes an issue with the GUI scaling on different resolution/DPI settings (both, schema and data compare).
• Fixes an issue with user membership to roles (schema compare).
• Fixes a bug related to the order of the included columns in an index (schema compare).
• Adds two new command line options for the data compare where clauses, allowing you to set different filters on each table/view on the pair (data compare).

Read More

Bidirectional synchronization using xSQL Data Compare

xSQL Data Compare is designed to compare the data in two SQL Server databases and generate the change script to make database A the same as database B or vice versa - in other words it is designed to go in only one direction at a time. However, while this one directional synchronization is very helpful in various scenarios it does not address the need of the users who are in charge of managing distributed databases and that for one reason or another are not able to implement a SQL replication solution. While not an ideal solution, with a bit of effort those users can use xSQL Data Compare to address this issue.

So, let's say you have a copy of database DB1 on Server S1 and a copy of it on server S2. Both servers/databases are active so they are both being modified simultaneously. Now, let’s say that you are in charge of synchronizing those two copies of the database every night. Before you can synchronize let's say table T1 on S1.DB1 with table T1 on S2.DB1 you will need to make a couple of structural changes:

1. First let's assume that table T1 has a TimeStamp column that will be used to decide which record overrides which.
2. Now, add column S2_TimeStamp on table S1.T1 and add column S1_TimeStamp on table S2.T1. You will need to make sure that the new timestamp columns are automatically updates anytime the row is modified. 
3. Create a view for S1.T1 and a view for S2.T1 naming the timestamp columns in such a way that the name of S1.T1.TimeStamp matches S2.T1.S1_TimeStamp and S2.T1.TimeStamp matches S1.T1.S2_TimeStamp. The view on S1 will contain the following columns: T1.ID, T1.TimeStamp as TimeStamp1, T1.S2_TimeStamp as TimeStamp2 whereas the view on S2 will contain: T1.ID, T1.TimeStamp as TimeStamp2, T1.S1_TimeStamp as TimeStamp1. This will ensure that the columns of S1.T1 are automatically mapped with columns of S2.T1.

You will only need to do the above once for each table you wish to do a bidirectional data synchronization for. At this point everything is ready to set up the comparison and synchronization. Here are the steps:

1. First, run a compare of the two views you created above and then:
1. Exclude wT1.TimeStamp2 column and generate the script that makes S2.wT1 the same as S1.wT1. Execute the script – this will update the S2.T1 table with the timestamp from S1 without touching the S2 timestamp. 
2. Now include wT1.TimeStamp2 and exclude wT1.TimeStamp1 and generate the script that makes S1.wT1 the same as S2.wT1. Execute the script – this will update the S1.T1 table with the timestamp from S1 without touching the S1 timestamp.
2. Now you are ready for the final step – compare table S1.T1 with S2.T1 adding a WHERE clause for each side to exclude the rows where the timestamps are the same. You will need to repeat this process twice: first, you only include rows where S1.T1.TimeStamp > S1.T1.S2_TimeStamp – you will need to "push" those rows from S1 to S2; then, you include only the rows where S2.T1.TimeStamp > S2.T1.S1_TimeStamp – you will need to push those rows from S2 to S1.

 You can use the SQL Data Compare command line utility to automate all those comparison steps.

 

xSQL Software's Data Compare is free for SQL Server Express Edition.

Download Data Compare for SQL Server now. 

Read More

xSQL Comparison Bundle – new build available

A new build of xSQL Comparison bundle containing a few minor fixes is available for download: http://www.xsql.com/download/package.aspx?packageid=10

Issues addressed by this build include:

1. Schema Compare tool (xSQL Object)  - fixes an issue with check constraints that depend on user-defined functions. Symptom:  database schema comparison fails with the error “Failed to generate check constraints synchronization script. Object reference not set to an instance of an object”; 
2. Schema Compare tool (xSQL Object)  - fixes an issue with default constraints that depend on user-defined functions. Symptom: database schema comparison fails with the error “Failed to generate check constraints synchronization script. Object reference not set to an instance of an object”;
3. SQL Data Compare tool - fixes a problem that occurs when the first column of the chosen data comparison key is created as a descending column. Symptom: SQL Data Compare tool fails to identify the rows that exist in both tables being compared. 
4. SQL Data Compare tool - fixes a problem with the data candidate index defined on columns that allow NULL values. Symptom: Data Compare prepares update queries with where clause as “and fieldname = null” in cases when the key used includes a nullable column.

Read More

Lookup table changes

Whenever you publish a new version of your application chances are that in addition of the database schema changes you also need to publish changes you have made on your lookup tables. xSQL Object makes the publishing of the schema changes a breeze but how about the values on those lookup tables? The answer is xSQL Data Compare which allows you to:

• Select the tables you need to compare and sync
• Filter out the rows that you might not want to sync
• Review the differences 
• Generate the sync script 
• Execute the sync script (this step in the process will publish all the data changes you made on development into the production database).

In addition of the granular control over the process there are three features that make xSQL Data Compare the ideal sql tool for the job:

1.  “Configure once”: xSQL Data Compare saves all your comparison configuration settings in a comparison “session” so that next time you can launch the comparison with one click and complete the whole data synchronization process with two more clicks (one to generate the sync script and one to execute the sync script).
2. Command line utility: allows you to run the comparison and synchronization in one step from the command line. 
3. It is completely free for SQL Server Express without any limitations and it is also free for other editions of SQL Server as long as the number of objects in your database does not exceed certain limits.

Download your copy today and discover for yourself why thousands of users around the world swear by this simple but extremely helpful sql tool.

Read More

SQL Data Compare mapping rules explained

By default xSQL Data Compare will automatically "map" tables from the two databases being compared based on the full object name, that is [schemaname].[objectname]. For example table [database1].[dbo].[Customer] will be mapped to table [database2].[dbo].[customer]. For most of the real life scenarios out there this automatic mapping is sufficient however, there are many cases when the tables on both sides cannot be mapped using this approach because:

1. The same tables maybe owned by different schemas on different databases;
2. Table names might not be exactly the same – for example, on one database the customer's table might be named dev_customer whereas on another database might be named prod_customer.

One utility that xSQL Data Compare provides to allow you to map tables regardless of the name is the "compare tables" functionality. This allows you to manually map a table from database1 to any table from database2 regardless of schema name or table name. This is perfect when you only have a few tables that you need to compare, but when you have hundreds or even thousands of tables this manual mapping is not practical. Here is where xSQL Data Compare "mapping rules" come in. There are two name based mapping rules that you can control:

1. Schema name mapping rules. Using the schema mapping rules you can instruct xSQL Data Compare to:
1.  Do an exact match on schema names (default behavior). In this case [database1].[dbo].[customer] will be mapped to [database2].[dbo].[customer] but [database1].[dev].[customer] will not be mapped to [database2].[prod].[customer]
2. Completely ignore the schema name. In this case [database1].[dev].[customer] will be mapped to [database2].[prod].[customer]. 
3. Manually map schema names. You could, for example map the schema “dev” to the schema ‘prod” in which case then xSQL Data Compare will automatically map [database1].[dev].[customer] to [database2].[prod].[customer]
2. Table name mapping rules. Using this rule you can instruct xSQL Data Compare to:
1. Ignore a certain name prefix on the left database and a certain name prefix on the right database when performing the object mapping. For example, you could say ignore the prefix “dev” on the left and the prefix “prod” on the right. In this case, the table [database1].[dbo].[dev_customer] will be mapped to [database2].[dbo].[prod_customer]  
2. Ignore a certain name postfix on the left database and a certain name postfix on the right database.

Download your copy of xSQL Data Compare and see for yourself why this is the best sql data compare tool in the market, and on top of that you might not have to pay anything for it.

Read More

One-click SQL Data Compare

Comparing and synchronizing data in two SQL Server databases is most of the time a fairly straight forward process: you select the databases you want to compare; xSQL Data Compare maps the tables and views automatically, identifies and selects the comparison keys and performs the comparison at the end of which it shows you the results on the screen. At that point you can click a button and generate the synchronization script which, if executed on the target will make the target the same as the source. So, the interaction required is minimal.

However, there are often times when the process is not very simple. Here are some possible complications:

• Two identical tables might be owned by different schemas in both databases so with the default settings xSQL Data Compare will not map those two tables with each other. So, you either have to change the mapping rules to ignore the schema or you have to manually map those two tables with each other;
• You might have tables that have no primary key defined on them and no unique constraints that can be used as comparison keys. In these cases you will need to drill down to those object pairs and manually select a comparison key that can be a combination of columns from this table;
• You might want to completely exclude some tables from the comparison;
• You might want to tweak the behavior of the comparison engine by adjusting the options to your needs;

For a relatively large database you might spend hours preparing the comparison. That is where the xSQL Data Compare’s “comparison session” saves the day – once you go through the comparison configuration process every "bit" of the configuration is stored in a session. Next time you launch xSQL Data Compare you will see a box on the main window for that session with a "Launch" action link – one click on that link and the comparison process will be done for you - many hours saved.

xSQL Data Compare supports SQL Server 2008 R2/2008/2005/2000 and it is free for SQL Server Express – download your copy from: http://www.xsql.com/download/package.aspx?packageid=10

Read More