none
SQL Server 2005 Noob Questions

    Question

  • Hi, I have been asked to provide some sort of disaster recovery for our payroll system which has databases running on SQL Server 2005. As I haven't looked at SQL Server since about 1998 things are a bit foggy!

     

    I'd really appreciate some advice on the best solution and how to go about implementing it.

    I have determined that all that is required is a daily copy of the databases to be copied from the primary SQL server to a secondary SQL server, and be accessible from there if required. Also obviously once the issue with the primary SQL server is resolved there will be a requirement to overwrite the existing databases on it with the newly updated ones on the from the secondary SQL server.

     

    I have looked at mirroring and think that's a bit above what I need, and at replication but don't know if that is the right solution either (I have tried setting replication up but doesn't seem to want to work). I then looked at just copying the databases but wasn't sure if the secondary SQL server setup had to be identical to the first.

     

    Basically any advice gratefully received and pointing me in the direction of any good reading material on the subject would also be of immense value.

     

    Thanks,

    Dave

    Wednesday, April 06, 2011 3:55 PM

Answers

  • I normally recommend defining a few things regarding your disaster recovery plan even before jumping in to which type of technology to implement. RPO and RTO are the two key things that you need to define prior to choosing the right technology
     
    If you say that only a daily copy of the database is needed, then, log shipping might fit the bill. I was about to recommend a custom log shipping and use SQL Server Express as your standby server but this edition has database size limitations

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn
    Wednesday, April 06, 2011 8:16 PM
    Moderator
  • I operate a similar DR strategy on some of my Production databases and simply use a Powershell job to copy over the backups. Of course, for the more critical databases I implement Mirroring/Log Shipping but for those in SIMPLE recovery, the copy works a treat. I don't actually restore the databases on the secondary server, but its possible to add this step.

    http://richbrownesq-sqlserver.blogspot.com/2011/03/powershell-copy-those-backups.html

    Just realised you're using SQL2005 which doesn't have powershell available in SQL Agent but you could write a similar job in vbscript or using XCOPY. An alternative may be to just share the Backups directory on the primary server and then have a scheduled RESTORE WITH NORECOVERY command on the secondary server.


    every day is a school day
    My Blog
    Thursday, April 07, 2011 11:22 AM
    Moderator

All replies

  • I normally recommend defining a few things regarding your disaster recovery plan even before jumping in to which type of technology to implement. RPO and RTO are the two key things that you need to define prior to choosing the right technology
     
    If you say that only a daily copy of the database is needed, then, log shipping might fit the bill. I was about to recommend a custom log shipping and use SQL Server Express as your standby server but this edition has database size limitations

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn
    Wednesday, April 06, 2011 8:16 PM
    Moderator
  • The databases recovery mode is set to simple so don't believe I can do this ?
    Thursday, April 07, 2011 10:18 AM
  • I operate a similar DR strategy on some of my Production databases and simply use a Powershell job to copy over the backups. Of course, for the more critical databases I implement Mirroring/Log Shipping but for those in SIMPLE recovery, the copy works a treat. I don't actually restore the databases on the secondary server, but its possible to add this step.

    http://richbrownesq-sqlserver.blogspot.com/2011/03/powershell-copy-those-backups.html

    Just realised you're using SQL2005 which doesn't have powershell available in SQL Agent but you could write a similar job in vbscript or using XCOPY. An alternative may be to just share the Backups directory on the primary server and then have a scheduled RESTORE WITH NORECOVERY command on the secondary server.


    every day is a school day
    My Blog
    Thursday, April 07, 2011 11:22 AM
    Moderator