Help on DTS vs. Replication

Question

Help on DTS vs. Replication

miguelmodrigo

Grasshopper

Points: 12
More actions
January 24, 2010 at 4:09 pm

#140907

Hi Guys,
Hope to get some feedback from you. Actually I am planning to migrate our SQL 2000 database to SQL 2005 using attach/detach (detach from SQL 2K and attach DB to SQL 2K5). However, I have some confusion on how I will have to sync in two databases. Assuming I have successfully migrated my databases to 2005.
[Problem/Environment]
--------------------
1 Primary SQL 2K5 Server (data would be 3 months for the application to run fast) - example current month is January 2010, so data I have are for months of Dec., Nov., and Oct.
1 Backup SQL 2k5 Server (this will copy the data from Primary, so it can accumulate all data 1-7 years) - this is for reporting purposes by our team and not to intervene with the primary sql server. In this case, from September backwards, it will hold the data.
[Solution]
--------------------
I can only think of DTS and Replication. I wanted to grab/sync' in my data from Primary Server going to Backup server. But I hope it will not override whatever data I have in primary (I am thinking that if this will get in sync, it will remove the data of my backup since previous months are not existing in the primary). In this case, we can query and create reports even up to 7 years (maximum data retention).
Any possible recommendations will be greatly appreciated.
Thanks,
Migz

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55553 More actions · Answer 1

With DTS, wouldn't you have to delete the previously migrated data & reload ?

I would think replication would be easier since it can push just data changes since the last push. And you can add a filter to select data >= 3 months ago. Does all your data have a way of being identified as 3 months old ?

miguelmodrigo Grasshopper Points: 12 More actions · Answer 2

Thanks mate!

Yes actually, what the backup server should do is to accept all the changes from the primary SQL server (except deletion of data), whatever data added to the primary server, it will also be reflected in the backup server + its previous data. There is no way to filter/know the newest data except of course the date.

Cheers,

Migz

homebrew01 SSC Guru Points: 55553 More actions · Answer 3

I think you can configure replication to not replicate the deletes when you purge the old data from the source server. (It's been I while since I used it)