How to synchronize databases in different servers in SQL Server 2008?

Question

I have 2 databases that have the same structure, one on a local machine and one on the company's server. Every determined amount of time, the data from the local DB should be synchronized to the server DB.

I have a general idea on how to do this - create a script that somehow "merges" the information that is not on the server DB, then make this script run as a scheduled job for the server. However, my problem lies in the fact that I am not very well experienced with this.

Does SQL Server Management Studio provide an easy way to do this (some kind of wizard) and generates this kind of script? Is this something I'll have to build from scratch?

I've done some basic google searches and came across the term 'Replication' but I don't fully understand it. I would rather hear some input from people who have actually done this or who are good with explaining this kind of stuff.

Thanks.

Answer 1

SQL Server Management Studio unfortunately doesn't offer much in this way.

You should have a serious look at some of the excellent commercial offerings out there:

• Red Gate Software's SQL Compare and SQL Data Compare - excellent tools, highly recommended! You can even compare a live database against a backup from another database and synchronize the data - pretty nifty!

• ApexSQL's SQL Diff and SQL Data Diff

They all cost money - but if you're serious about it, and you use them in your daily routine, they're paid for in no time at all - well worth every dime.

The only "free" option you have in SQL Server 2008 would be to create a link between the two servers and then use something like the MERGE statement (new in SQL Server 2008) to transfer the data. That doesn't work for structural changes, and it's limited only to having a live connection between the two servers.

Answer 2

Replication sounds like a good option for this, but there would be some overhead (not technical overhead, but the knowledge need to support it).

Another SQL Server option is SSIS. SSIS provides graphical tools to design what you're trying to do. The SSIS package can also run SQL statements, if appropriate. An SSIS package can be started, and therefore scheduled, from a SQL Server job.

You should consider the complexity of the synchronization rules when choosing your solution. For example, would it be difficult to resolve conflicts, such as a duplicate key, when merging the data. A SQL script may be easy to create if the rules are simple. But, complex conflict rules may be more difficult to implement in a script (or, replication).

Answer 3

You should definitely read up on transactional replication. It sounds like a good fit for the situation you've described. Here are a few links to get you started.

• How Transactional Replication Works
• How do I... Configure
  transactional replication between two SQL Server 2005 systems?
• Performance Tuning SQL Server
  Transactional Replication

Answer 4

What you want is Peer-to-Peer Transactional Replication, which allows data to be updated at both databases yet keep them in sync through a contiguous merge of changes. This is the closes match to what you want, but is a fairly costly option (requires Enterprise Edition on both sites). Another option is Bidirectional Transactional Replication, but since this requires also two EE licenses, I say that peer-to-peer is easier to deploy for the same money.

A more budget friendly option is Updatable Subscriptions for Transactional Replication, but updatable subscriptions are being deprecated and you'd bet your money on a loosing horse.

Another option is to use Merge Replication. And finally, for the cases when the 'local' database is quite mobile there is Sync Framework.

Note that all these options require some configuration and cooperation from the Company's server DB.