April 14, 2008 at 6:10 am
I have SQL2005ent on 2 node cluster.
Both mdf and ldf files are on one LUN.
Storage is working very slow, i need to change database location from one lun(raid 5) to another (raid 10).
What can you suggest, with minimal downtime?
April 14, 2008 at 3:38 pm
Log-ship the database to the new LUN:
1. Restore a recent backup on the server as a database with a different name.
2. Restore all log backups.
3. Add a step to your log backup job to take each successive log backup from the current database and restore it to the new database.
4. Schedule the downtime to do the switch. The job will keep the databases in near-sync until you can take the original database offline to complete the switch to the new database.
To complete the switch to the new database:
1. Disable all jobs against the source database
2. Prevent further modification of the source database: ALTER DATABASE [DB Name Goes Here] SET READ_ONLY. Note: You can back up read-only databases.
3. Run the backup log / restore log job one last time and disable it. All transactions from the source are now applied to the target.
4. Complete the transfer by recovering the database target: RESTORE DATABASE [DB Name Goes Here] WITH RECOVERY
5. Detach source database.
6. Rename your new database to the original database name: ALTER DATABASE [new name] MODIFY NAME = [original name]. Your database is now online and ready for connections.
7. Change the backup job to no longer restore log backups
8. Enable all the jobs for the database. (You should take a fresh backup)
Steps 2, 4, 5, and 6 can be completely scripted (1, 7, and 8 can be scripted as well, if you're really motivated) and ready to run when the actual time comes. Using the technique, you can perform the move with less than 30 seconds of perceived downtime.
* Practice a few times (!!!)
- You can start and stop your own manual log shipping without affecting the source database. Pick a few smaller databases you can quickly restore, and run through the process (without taking the source DB offline, of course): Restore DB, run log-shipping for a while, recover DB.
-Eddie
Eddie Wuerch
MCM: SQL
April 15, 2008 at 1:32 am
Thanks, its an interesting solution.
I'll try it.
April 18, 2008 at 12:58 am
It is working, thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply