Sync two SQL Server databases

Question

I have two databases: the source is a database from SQL Server Express by client and the target is a database from SQL Server 2005 database as backup initially. What I need is to sync the source to the target db if there is any difference between them and the sync is one-way from source to target.

I am not sure what tools are available. I tried to google this issue and found MS VS Team Edition (2005) has a tool to sync database, which can generate T-SQL scripts as well. Not sure if this one is good or not. Can I use the script as a scheduled job on SQL Server (target server)? By the way, I don't have Team Edition right now but I do have VS 2005 Prof. Any suggestions?

Answer 1

Pay to play: http://www.red-gate.com/products/SQL%5FCompare/index.htm

Free, open source: http://www.codeplex.com/OpenDBiff

Answer 2

You should into the SQL Server tools produced by Red-Gate. I've found them to be the best around.

Answer 3

If you have SQL Server 05, you can use replication services(this comes with SQL Server). If you open up your management studio, under your server folders you should see one titled "Replication". From here you can setup subscriptions or publications with push or pull syncs.

Here's MSDN's take: http://msdn.microsoft.com/en-us/library/ms151198.aspx

Answer 4

IMHO by far the easiest and fastest way to sync the two databases one-way (A to B) is to backup database on A and restore it on B. This could be done via T-SQL, let me know if you would like me to post SQL statements

Answer 5

If this is a one time / once-in-a-while thing, you can use SnapShot Replication.

If you need the databases to be in sync all the time, you can use Transactional Replication.

http://msdn.microsoft.com/en-us/library/ms151847.aspx

Answer 6

Ideally you would set up Transactional Replication from your source to your target(s). However, since your source is Express edition and Replication does not work with Express as a publisher (source) but only as a subscriber (target), you cannot use it.

The best solution would be to upgrade your Express edition to SKU that supports Replication publishing (ie. Standard Edition).

Log shipping, or manual backup/restore, will not work because it will create an absolutely identical copy of the source db at the target, overwritting any changes made by the target (you mention 'some differences' may exist). Same goes for File/Copy.

SQL Compare tools are OK for a one time manual operation, but they fail at automated operations because they always compare the two databases from scratch, ei. are not capable of synching just what changed. As soon as data grows to a sufficient size, the comparison approach is doomed as it has to ship over the entire database for purpose of comparison alone.

Other solutions are to set up pro-active real-time ETL, but the time/cost investment into this is prohibitive compared with the cost of a SE license and deploying Replication.

Answer 7

In addition to Red Gate tools you can try DB Ghost as well http://www.innovartis.co.uk/. It's most useful as a automated build tool, but does also have an user interface to diff and sync databases. It costs ~$350.00