MS SQL - Migrating from Prep to Production
Posted on 2005-04-21
We have two seperate Servers hosting the same DB - Server1 + Server2.
Server1 purpose is to receive all updates and compile all reports.
Server2 is a read only database, thus heavily indexed.
We are looking for the best way of migrating all data/updates from Server 1 to Server 2. The migrate brings it challenges as there are tables that need updated with new and there are tables that need deleted and then all records added (all the tables that contain report data).
The options that we have identified are:
1) Prepare everything in Server 1 & migrate DB Files
2) Extract data in Server 1 & upload into existing tables in Server 2
3) Extract data in Server 1 & upload into new tables in Server 2, the drop existing and rename new.
Option (1) requires DB to be offline for a short while. We have no issue in automating the file transfer, but how to automate the Start & Stop of the server is a challenge.
Option (2) requires 2 hours when the DB is unavailable, can be scheduled during the night.
Option (3) requires no time when the DB is unavailable, but unsure about the solution of uploading into say table1temp then delete table1 and then rename table1temp to table1.
The volume of data is rather large. On any give batch of updates the size is approx 4GB. But this is data only. Indexes are another 20GB.
In short we are looking for the most reliable solution that requires least effort & less risk. Also with the volume fragmentation is an issue as well.
I would like to ask your prespective. Just before I sign off - it may be worth mentioning that we have rulled out Replication as an option due to the fact that we dont presently have sufficient bandwidth between Server1 and Server2. The options about we can use compression of extracts & files to minimize the hit on bandwidth.
Thanks in advance for your help.