Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Simple DR Solution

By TJay Belt,

From the DBA’s perspective, a Disaster Recovery solution may be daunting.  There are a lot of pieces of the puzzle to apply to have a successful DR solution.  It’s important to be involved from the beginning of the project, and help to understand and define the scope. 

This is my version of a DR project that was handed to me one afternoon as an afterthought.  I was tasked with getting the main production instance of each of your 15+ clients up on a DR site within a few weeks.  The first one showed up at my workspace and wanted to be able to test the DR solution that afternoon; I panicked.  No work had been done yet.  Emails had been sent out asking for a DR solution, that’s all at that point.

Feeling backed into a corner, I started spouting out database jargon and list of things that needed to be gathered, prior to a successful test.  I started saying that we needed to get to the Business Owner a list of DTS packages, Jobs and Users that are wanted to be in existence for this DR database.  We obviously had to copy the database down to the DR server, since nothing was setup to replicate or any other HA solution currently in place.  So, I hurriedly created spreadsheets for all the DTS packages, Jobs, and Users, and forwarded them onto the Business Owners with the plea that they let me know which ones of each were imperative for this solution, from their perspective.  I placed myself at their disposal as a Subject Matter Expert to assist in any of the discovery necessary.  I started the process of copying down too our DR server in Mexico, thru a small bandwidth pipe that was already overloaded.  The first database for the first client in the queue was on its way to a fully functional DR site.  I was so excited….  While I was waiting for the database backup to complete its copy process, the Business Owner stopped by and asked if we could test the site now?  I laughed… explained, and continued working.

Once the database was copied down and restored, I went back the Business Owner, and asked for the answers to the DTS, Job and User info I had previously requested.  He had not looked at the email, and had no idea what I was requesting.  After a bit more discussion, it was further realized that this was a first attempt at a DR site.  A very simple implementation of DR would occur.  A basic copy of the database, a couple DTS packages, some users, and a copy of the website, with some images of documents.  We were to plug this website into the db, and login, pull up on document, see the image, and call it good.  A single IP address would be used as we rolled each client into the DR site, and the websites would not be functional beyond the initial test.

A few things were still learned, even for this simple version of a DR site.  I will enumerate those items here, in the hopes that they will help others that attempt even the simplest DR implementation.

  • Copy backups of databases to DR server
    • Ensure that you actually have valid backups being processed on Production server.
    • Determine the proper time frame when it is acceptable to copy file down to the DR server.
    • Ensure that there is enough bandwidth to copy the backups.
    • Realize how backups are performed, 3rd party applications vs standard backup.  Make sure that you have the same process on DR side
  • Configuration/Installation of DR System
    • SQL Server needs to be installed on DR site
    • Configuration of SQL Server on DR site
    • Permissions granted on DR site
  • Information gathering
    • Run scripts to gather users
    • Run scripts to gather jobs and info about jobs
    • Run script to gather DTS information
    • Present this information to business owners in an understanding way
    • Review information and decide on subset of items to implement
  • Implement the information gathered in DR site
    • Restore the database
    • Add the users, and/or attach the orphaned users
    • Create any needed jobs
    • Copy any DTS packages needed
    • Alter any configurations inside the DB that are hardcoded to a server. (Don’t laugh.  Make sure you look for them)
  • Testing system
    • Be ready and able to dig into the system for configurations, hard coded issues, and be nimble enough to alter them to get it working
    • Be available when testing occurs, make this a priority

With the DBA, Network Admins, Business Analysts, Production Support individuals, Business Owners and other’s help, most of the hurdles you will see will be overcome to allow the system to function properly enough to be tested as a DR instance.  You may want to emphasis the importance and timeframe needed to get each client up and running.  We ran into problems where the Business Owner was expecting a quicker response than we were prepared to provide.  This friction did not go over well, and caused more issues.  Other clients were more laid back, and allowed deviation and slowness to occur.  We initially didn’t think of this project as a priority, and felt overwhelmed with our day jobs, along with this added task.  When we reprioritized this project, things became much smoother. I wish these concerns had been brought up to the group at the beginning and resolved up front. 

When you attempt to create your first DR site, make sure you plan well.  Get buy-in on the list of tasks and expected results. Get all the parties involved that need to be involved to succeed.  Plan, plan and plan.  Make room for errors and resolution time.  I hope that your first, second or whatever attempt at a DR site is more successful than the last one.

Total article views: 8027 | Views in the last 30 days: 2
 
Related Articles
FORUM

Database Owner

Database Owner and db_owner

FORUM

Database owner

Database owner

SCRIPT

Check owners, collations and backups

A script to check the owners, collations and backups on the server.

FORUM

How to change table owner when restoring database?

Changing table owner during restoring database

FORUM

database backup

database backup

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones