March 8, 2005 at 3:43 pm
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
March 9, 2005 at 2:49 am
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?
March 9, 2005 at 4:38 am
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.
March 9, 2005 at 5:18 am
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?
March 9, 2005 at 10:44 am
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
March 9, 2005 at 10:47 am
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