DTS or Replication?

I need help deciding whether to use DTS or Replication:

We have four SQL Server databases on four separate servers with the same data schema, one for the north east, south west, mid atlantic, and mid west. We also have one "global" database on a separate server. First, and only this one time, we need to copy data from the four regional databases to the global database. Then, at least once a day we will need to copy changed and new data from the four regional SQL databases to the global database.

I have researched DTS and Replication and am still unsure how to proceed. DTS doesn't seem to have a mechanism to copy over only the changed data. Can somebody please advise me as to which method to use?

Thank you.

This sounds like a candidate for transactional replication to me.

Transactional replication bulk copies the data on first use but then only updates are applied, exactly what you are asking for. You will have to be careful about handling updates on the "global" database if that is a requirement, your key must be unique within all of the publication tables. Also, when setting up the subscriptions ensure replication does not try to drop the table first or replicate to seperate databases and join it together with a view. Using this view approach would be slightly less efficient but less prone to people pushing new subscriptions that drop all target tables.

DTS has no mechanism for doing this, you would have to write the logic yourself based on some sort of changed data marker/date flag. Why go to the hassle when SQL Server has a well tested system in place already?

Hope that helps

Nick
DBA
www.comoni.co.uk

|||

Do you know of any texts or resources I can find to teach me how to do this? I am not familiar with how to run replication or create a replication job, or whatever you call it. Thanks

Karen

|||

Merge replication is what you need. It is designed for the occasionally connected scenarios.

The following are some resources to get you started:

Planning Merge Replication

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replplan_2ipa.asp

How merge replication works

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/repltypes_30z7.asp

|||

Merge replication would not be my choice here. Merge is best suited to 2 way data changes, not one way as in this case. The management overhead is higher with merge than transactional too. It is fine to run the distribution agent for transactional replication only once a day, providing there is space in the distribution database to store that day's changes.

As for documentation on this, books online is not particularly helpful on the basics. I found a quick Google for transactional replication brought up plenty of how-to articles though. It is worth spending the time reading the onlie resources as replication is a reasonably complex process and has a habit of causing problems if not planned correctly.

Nick

|||

The main difference between merge and transactional replication is not two-way or one-way. There is a "download-only" option in SQL Server 2005 merge replication to allow you to setup one-way replication and you can setup bi-directional transactional replication. The main difference is how soon you will see the change. Giving you only want to sync once a day, I would go with merge replication.

Here is an overview of different replication types and how to choose between them:

http://msdn2.microsoft.com/en-us/library/ms152531(SQL.90).aspx

Here is an example of using merge reaplication to integrate data from multiple branch offices, which is similar to the original scenario in this post:

http://msdn2.microsoft.com/en-us/library/ms151790.aspx

|||Thank you all for your help! I have enough to move me in the right direction now. I'm sure I'll have problems along the way.....