Snapshot replication vs. dts
-
We are tasked with attempting to copy about 70 tables every night that equates to roughly 50 gigs of data from one server to another. It is not the full database so a backup and restore is not an option (the entire database is about 400 gigs). The data can be updated and modified on the 2nd server and the changes must not come back over to the primary. Every night the data needs to be completely refreshed. Anyone have experience on which is faster snapshot replication or dts?
Thanks for your input
-
Can changes occur simulatenously on Server 1 and Server 2. Or is this a situation where Server 1 is finished for the day and Server 2 takes over and when Server 2 is finished and Server 1 takes over again in 12 hour shifts?
-
Changes can occur simultaneously on both servers but, we do not want the changes replicated at all. Once the data is copied they operate as 2 independant databases.
-
DTS is twice as fast as snapshot replication only if you have a single subscriber.
I would use bcp for this with a larger commit batch size. You will find that this may work faster than the bulk insert that DTS will use as you can control transaction log growth.
--
Hilary Cotter
Looking for a SQL Server replication book?
-
If you refresh every day overwriting the end server you can:
1. Truncate all tables at destination
2. Put the Destination recovery mode in BULKLOGGED
3. Use bcp to pump data in
3. Change Recovery mode back to your needs
Good Luck
* Noel -
Since the developers are stil making database changes I am planning on dropping and recreating the tables so that any changes made will come over automatically. Thanks for the advice. I will try it out and see how it goes.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply