Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Disaster recovery best practices - system databases Expand / Collapse
Author
Message
Posted Wednesday, June 12, 2013 3:04 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 9:58 AM
Points: 151, Visits: 648
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?
Post #1462511
Posted Wednesday, June 12, 2013 4:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:17 AM
Points: 1,379, Visits: 2,687
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
Post #1462539
Posted Thursday, June 13, 2013 2:49 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 9:58 AM
Points: 151, Visits: 648
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?
Post #1462941
Posted Thursday, June 13, 2013 2:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:17 AM
Points: 1,379, Visits: 2,687
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.
Post #1462944
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse