SQL Server Performance Forum – Threads Archive

Use Replication or Log Shipping?

I have a client with 7 databases that range from 300M to 15G in size. These databases do very little transactions (less than 100M total in transaction log backups per day) They have a production server in the UK and a test server in the US. About every 2 weeks, they update the test server by restoring new backups from the production databases. They would like some way to automate this. The current copy of the backup files (~50G) takes more than 6 hours. I did consider log shipping because of the small amount of data changes per day, but the test databases need to be writable. The client has said they could just backup the standby database on the test server and restore it as another name (this would cut out the US to UK copy time). Anyone know if this will work? Also, any other suggestions? Snapshot replication sounds interesting, but I would guess I would have to transfer the entire 50G every 2 week instead of the 100M nightly updates. I am worried about transactional replication because I can see them making "test" changes which could create replication conflicts. Any help is greatly appreciated…

I would go with log shipping rather than using replication, to ease up the maintenance of setup. We have similar setup where the standy database are captured in a periodic basis for testing purposes without any issues in using log shipping. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>