Post reply

Consolidating Servers for Disaster Recovery - Advice Needed

  • February 23, 2007 at 2:00 pm

    #172399

    We want to consolidate some production SQL servers when we perform our disaster recovery tests.  An issue I am trying to address is how to handle situations where common file paths are referenced by multiple instances on the same physical server?  For example, a SQL job backing up the master database to G:\MSSQL\BACKUP works fine when each instance of SQL Server has its own physical server, however when consolidating servers this presents a problem of duplicate file names. 

    Here's an example:  

     

    Production Server Names:              SQL1, SQL2, SQL3

    Disaster Recovery Server Name:     DRSQLFARM1

    Disaster Recovery Instance NamesRSQLFARM1\SQL1

                                                    DRSQLFARM1\SQL2

                                                    DRSQLFARM1\SQL3

     

    Data files will be stored on the F drive so I will see F:\MSSQL$SQL1, F:\MSSQL$SQL2 and F:\MSSQL$SQL3.

     

    Backups are performed to the G drive so I need to create G:\MSSQL$SQL1\BACKUP, G:\MSSQL$SQL2\BACKUP and G:\MSSQL$SQL3\BACKUP.

     

    The problem comes when I restore msdb for each DR test and the corresponding SQL jobs used for database and transaction log backups reference the backup drive used on the production server, such as G:\MSSQL.  Since this drive does not exist the backups will fail.  If the drive did exist the backups would fail for any database that exists on more then one instance, such as the master database.

     

    I could create recovery procedures specifying that following the restore of msdb I must script out all jobs and perform a “find and replace” to specify the new backup location.  I could also update sysjobsteps directly, although that approach is less desirable.

     

    Thanks,   Dave

     

  • February 26, 2007 at 4:20 am

    #691694

    If you script your jobs out, and script your DTS packages as Structured Storage files, then there might not be any reason to restore the msdb. Your DTS packages will need amending probably but if they are created using global variables to create the connections the amendments will be minimal. The jobs scripts will just need amending to refer to the correct server instances and databases.

     

     

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply