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.

Remise

Answer 1

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).

Sasin

The way I see it's pretty simple, the structure of the tables won't change at all and duplicate keys issues and all that are already handled prior to being inserted. Basically all I need is take the data and place it on the other DB. – Remise

Replication does sound like a good solution then. But, you'll have to decide if you're comfortable with it. Good luck. – Sasin

I tried it with different local databases, and it worked well, jobs are simple enough to create for this and the data is properly synchronized. I used snapshot replication though.. not really sure if this could be bad? Also, I need to set up a subscription on the server DB and have the local DB act as publisher, correct? – Remise

The subscription identifies the target database, which is your server. You can make this a push subscription, which means you run the add-subscription on the source (your local DB), or a pull subscription, which is run on your server. For your purposes, it's not too important to distinguish push and pull. So, finally, the brief answer is Yes, that is correct. :) – Sasin

@Eton B: Snapshot replication pushes the entire database every time, not just the deltas. For a very small database, this might not matter, but for anything substantial, you probably don't want to use snapshot. Transactional replication gets you only the changes since the last data push. – Calmas

@Joe: Thanks for that clarification, I'll go transactional since that'll reduce the load on both sides. – Remise

@bobs: Thanks for your responses, this answer was the most adequate to my needs and pretty simple. Also helped me make a decision which is always a plus. – Remise

Answer 2

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.

Seal

Yes, I'm afraid that (for now, at least) the MERGE statement solution is the path I need to take. I'll take a look at that example and some others and get back at ya. – Remise

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

Calmas

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.

Sumption

I guess we have different interpretations of the OP's requirements. I didn't see a bi-directional need based on "data from the local DB should be synchronized to the server DB." That sounded one way (from local to server) to me. – Calmas

Answer 5

There are some excellent third party tools out there. For me, xSQL Data Compare has always done the trick. And because the comparisons are highly modifiable it is suitable for almost every data compare or data-synchronization scenario. Hope this helps!

Pocked