June 12, 2013 at 3:04 am
I'm currently working on a new design for DR for my SQL Servers; I have four servers and a total of seven instances, three servers only have the default instance, whilst one has three instances.
In my DR environment, I only have two servers availabe, so I'll be squeezing the seven instances onto those two servers.
At this point, I'm wondering what the best practices are for the system databases, in particular master and msdb, and restoring those onto a different server and possible from a default instance to a named instance (I don't know whether this makes a difference at all?)
I'm aware that the server builds need to be the same to restore the master dbs; is there anything else I need to be aware of?
What I'm wondering is whether it's worth performing the restores, or whether I'm better generating scripts to recreate the server level objects that are held in the master db, and the SQL Server Agent jobs in msdb etc and then running those in the event of a DR switch?
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 12, 2013 at 4:19 am
System databases can be restored as user databases on your DR servers - Just keep track of which ones belong to which servers.
When you have downtime on the Prd servers, copy out the data and log files for each of the system DB's -Keep these on your DR server somewhere
Backup your system DB's regularly - Store the backups somewhere safe - Possibly on the DR server
June 13, 2013 at 2:49 am
I'm not sure what good having them restored as user databases does, unless I'm missing something?
All my databases, including system, are backed up regularly and stored in several secure locations (one of which is the DR server itself) so I have that covered.
I'm mostly concerned with ensuring that either the following items exist or can be easily created in the event of a need to failover to DR:-
Logins with relevant permissions
Keys (the database master keys are encrypted by the service master key) - these are all backed up so can be restored
Certificates (some logins are owned by a cert) - all backed up so can be restored
Server scoped triggers
SQL Agent Jobs
If I restore the master and msdb databases onto my DR server as the master and msdb system databases, will I be covered, or will it be fiddly to get it all working? If the latter, I guess having a script regularly generated that will create them all is the way forward?
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 13, 2013 at 2:59 am
I've dealt with corrupter maser databases before - Instead of rebuilding all system databses, which is the fix for master db corruption, I did the following:
Took a previous backup of the master DB, restored is as a user DB on another server, took the MDF and LDF from the restored master DB and replaced the corrupted master's MDF and LDF with those - That worked quite well.
That way, you don't end up having to rebuild all system DB's and then restoring.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply