SQL Server 2005 Business Continuity Measures - PowerPoint PPT Presentation


PPT – SQL Server 2005 Business Continuity Measures PowerPoint presentation | free to view - id: 1f638-MjhhM


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation

SQL Server 2005 Business Continuity Measures


Disaster recovery of systems and data, and all other necessities to return to ... For instance an application data recovery can be achieved from yesterday or even ... – PowerPoint PPT presentation

Number of Views:62
Avg rating:3.0/5.0
Slides: 28
Provided by: SQLO


Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: SQL Server 2005 Business Continuity Measures

SQL Server 2005 Business Continuity Measures
  • by
  • Satya Shyam K Jayanty
  • SQL Server MVP
  • www.sqlserver-qa.net
  • sqlmaster_at_sqlserver-qa.net

Lalandia, Denmark - March 09th 2007
  • Introduction
  • Challenges in Disaster Recovery
  • Business Continuity terminology concepts
  • Documenting Disaster Preparedness
  • Training Planning with Change Management.
  • Measuring Metrics - Effectiveness
  • SQL Server 2005 features
  • Best practices Industry standards SQL Server
  • Useful Links
  • Q A

Introduction - Speaker
  • Webmaster of www.sqlserver-qa.net
  • Been in the IT field over 15 years
  • SQL Server DBA for over 10 years (since ver. 4.2)
  • Contributing Editor, Writer Moderator for
  • Currently, working full-time as Sr. DBA at a
    large Financial Enterprise focusing on SQL Server
    high availability, clustering, disaster recovery,
    and performance tuning.
  • SQL Server MVP (www.microsoft.com/mvp/)
  • Participation in assorted forums such as MSDN,
    SQL Server magazine, dbforums and so on

Challenges in Disaster Recovery
  • Backup of your data how frequently?
  • How much data would you lose in a disaster
    between the backups?
  • When disaster strikes?
  • How quickly you can recover from a disaster?
  • How long would you be without access to
    applications and data?
  • Have you tested Disaster Recovery plan?
  • Disaster Recovery strategy hhmmss.
  • Availability metrics 99.99.

Business Continuity terminology concepts
  • No enterprise IT infrastructure is immune from
    the disasters.
  • If the availability is lost then reputation will
    be at stake.
  • Risks
  • Disk Crashes (storage)
  • Power Failures
  • Human Error
  • Natural Disasters
  • Stops the flow of data on IT facilities
  • Unplanned longer outages to the services
  • Recovery
  • Documented recovery plans
  • Restore from last good know backup
  • Are you ready?

Business Continuity terminology concepts
  • High Availability Business
    Continuity Disaster Recovery
  • High Availability HA Business Continuity
    BC Disaster Recovery - DR
  • IT Service Management is most important to cover
    range of planning and preparation strategies that
    will define your system design, backups and
    recovery process.
  • High Availability reducing downtime of
  • Business Continuity forethought to prevent loss
    of operational capability
  • Disaster Recovery process of restoring systems
    to an operational state
  • A process that does not eliminate the causes of
    the events but prevents many effects of the
    events and assures recovery from the disaster.
  • Despite considerable advances in equipment and
    telecommunications design and recovery services,
    IT disaster recovery is becoming increasingly

Business Continuity terminology concepts
  • 100 availability means that all users have the
    ability to do all their work, when they want to
    do their work. This is perfect availability.
  • Since 100 availability is virtually impossible,
    high availability (HA) is all that is possible,
    business continuity.
  • The higher you get toward attaining perfect
    availability, the more it costs.
  • With a regular practice the cost will gradually
    decrease in downtime.
  • Your organization must decide for itself how much
    it can afford towards paying for HA.

Business Continuity terminology concepts
  • Disaster Recovery plan is referred as Business
    Continuity .
  • A Disaster Recovery business continuity plan
    covers more than just computer systems and data
    at a few physical sites.
  • Critical areas such as employee safety,
    relocation plans, communication systems and
    others are covered in a business continuity plan.
  • Once we look at these impediments to HA, we begin
    our look at the best practices we can implement
    to help ensure HA.

Business Continuity terminology concepts
  • Plan
  • A complete business continuity plan should
    account for your employees first and foremost
    with an evacuation plan that ensures everyone's
  • Recovery
  • Disaster recovery of systems and data, and all
    other necessities to return to business as usual,
    even in the event that an entire location is not
  • Combination
  • Mixing both Business Continuity Disaster
    Recovery can fetch better results with the help
    of available technology hardware.

Business Continuity terminology concepts
  • Changing Times
  • There has been a marked shift in disaster
    recovery from a reactive approach, to a proactive
    approach, which prepares organizations to avoid
    any interruptions and render the smooth
    functioning of their systems.
  • Business continuity essentially brings together
    various planning methodologies, fitting the parts
    together to create a complete plan.
  • Risk management, contingency planning and
    disaster recovery are the major pieces of the
    planner's jigsaw.
  • It is important to choose the tools and
    techniques to react quickly in case of any
    disaster practice makes perfect.

Documenting Disaster Preparedness
  • The need for a Disaster Recovery plan- Why you
    are at risk?
  • Natural disasters floods/volcano/earth quake
  • Terrorist atrocities bombings
  • IT Infrastructure virus attacks, blue screen
  • Human errors poor query design, outdated
  • Disaster Recovery is nothing but providing
    Business Continuity.
  • What do you think about Disaster Recovery plan
  • Tape backup
  • Disk backup
  • Offsite backup
  • Disaster Recovery planning is not for later, it
    is for that moment when it strikes to simplify
    process of data recovery within no time.

Documenting Disaster Preparedness
  • Getting Started
  • Breaking things down into less formidable steps
    can simplify the process.
  • Important resource to achieve is identifying
    right people.
  • Property can be replaced if damaged, but not
    people (employees).
  • Understand what keeps your business going.
  • Arrangements to store data offsite from live
  • Calculate the cost of downtime right from 01st
  • Return of Recovery - RPO RTO are important

Documenting Disaster Preparedness
  • List of lines
  • The plan should encompass the who/how/where/when
    of "emergency response, back operations and
    post-disaster procedures.
  • Make a note of applications that are important to
    Service Delivery
  • Continue to build and test the plan ongoing
  • Strategy
  • Effective disaster recovery planning requires
    updating hard-copy backup strategies combined
    with continuous data replication.
  • Unfortunately unplanned outages will happen, but
    with a short-term DR planning you can achieve
    long-term solution.

Documenting Disaster Preparedness
  • Return of Recovery
  • Recovery Point Objective (RPO)
  • RPO defines the amount of data, in time, which
    your business can afford to lose for a particular
    system for a rapid return of recovery.
  • Non-urgent
  • For instance an application data recovery can be
    achieved from yesterday or even last week may be
  • In this case RPO factor would be days or weeks.
  • Urgent
  • Other applications and data, for which any loss
    is not acceptable.
  • In this case RPO factor would be minutes.

Documenting Disaster Preparedness
  • Return of Recovery
  • Recovery Time Objective (RTO)
  • RTO is the amount of time the application can be
    down and not available to users or customers.
  • Can your business survive without a particular
    application for a few minutes?
  • How about a few days?
  • These factors will help to set appropriate
    priorities for recovering systems and data and
    determine the right solution for each.
  • RTO is totally dependant on the RPO factor, most
    efficient factor to choose these 2 options is to
    decide which application is core to your
    Enterprise reputation.

Documenting Disaster Preparedness
  • Put into work
  • Testing is equally important to make sure the
    documented plan is working.
  • Improve resiliency against outage to maintain
    user and business productivity.
  • Even the most thorough business continuity plan
    is no substitute for an equally thorough disaster
    recovery plan until it has been implemented.
  • Planning for Disaster Recovery is synonymous with
    contingency planning it is "a plan for backup
    procedures, emergency response and post-disaster

Training Planning with Change Management
  • Training Planning
  • Time is precious - creating a BC plan could take
    up to months.
  • A project plan allows for the breakdown of tasks
    into more manageable chunks so that the overall
    project is not as overwhelming.
  • A documented project plan can help to expedite
    the implementation .
  • Then identifying and coordinating correct
    resources would help to pull the power.
  • What can we do differently?
  • Consider your options and be sure to account for
    the protection of your applications and systems
    as well as infrastructure and human resources.
  • Every step in the process of Business Continuity
    must be documented and recorded in a log book.
  • Allocate a resource to manage the process
    efficiently, rotate the same privilege within the
    Service Delivery team for a better practice.

Training Planning with Change Management
  • Why don't we achieve what we want to achieve?
  • Lack of Change Management and the end-result of
    this will be
  • do not achieve their objectives, or
  • do not deliver the promised results, or
  • sacrifice the predefined quality, or
  • are not completed in the given time schedule, or
  • use more resources than originally planned.
  • Risk Analysis
  • Originally this factor is not conceived as a
    change management tool.
  • This kind of risk analysis can really help teams
    to get a breakthrough.
  • It helps to identify communication gaps and risks
    that have not been recognized.
  • Testing the businesses continuity and disaster
    recovery plans provides an excellent training
    vehicle for everyone in your company.

Measuring Metrics - Effectiveness
  • Ready to get-started
  • A plan is only as good as it is when it is
  • Practice makes process perfect
  • Operational Procedures to perform the work
    necessary to conduct business.
  • Controls to ensure that the business is
    conducted as expected.
  • Advancement to higher category to ensure
    operational effectiveness does not decrease as a
    result of changes made. 
  • Removal of redundant controls time to time.
  • The DR plan is a piece of the overall BC plan.

Measuring Metrics - Effectiveness
  • It is much better for your business to be
    proactive with its DR plan rather than reactive
    during or after an outage or catastrophe.
  • Roles and Responsibilities
  • The role of DBA is undoubtedly an important one,
    but many DBAs tend to be somewhat blasé about
    backup and recovery.
  • Important to bridge the knowledge gap and provide
    working scenarios, policy/procedure and best
    practice for database backup and recovery.
  • For instance tape-based disaster protection can
    only restore data to the point of the last
    backup, which was most likely the prior night.
  • Consider the facts and figures from RPO RTO.
  • Set appropriate milestones that account for
    delays and setbacks so as not to have to rush to
    meet unrealistic deadlines.

SQL Server 2005 features
  • Big new features
  • CLR integration many modern programming
    languages (assemblies)
  • Failover Clustering - during a hardware failure,
    operating system failure, or a planned upgrade.
  • Database mirroring - software solution for
    increasing database availability.
  • Database Snapshots view based reports
  • SQL Server Integration Services (SSIS) - building
    high performance data integration solutions (OLAP
  • Reporting Services with Report Builder server
    based reporting platform from relational and
    multi-dimensional data sources.
  • Native XML Web services and many more.

Best practices Industry standards SQL Server
  • After the initial plan is complete and
    successfully tested, schedule quarterly or
    semi-annual tests.
  • Continuous testing with up to date and
    knowledgeable on the disaster recovery
  • A simple and inexpensive solution to recovering
    from failure is to take backups of all your
  • Standby servers are a cost-effective and viable
    way for businesses to maintain SLA and business
    continuity efficiently.

Best practices Industry standards SQL Server
  • HIPAA, Sarbanes-Oxley is the most comprehensive
    financial regulatory law in US history.
    Applicable to most of financial organizations for
    their data security configuration.
  • SQL Server 2005 security features Surface Area
    Configuration tool.
  • Do not compromise performance for the sake of DR
    or HA provision, it is also a primary aspect for
  • Triggers for Logon Events (New in Service Pack 2)
  • Indexing made easier with SQL Server 2005 DMV
  • Scalable Shared Databases
  • Database Tuning Adivsor

Useful Links
  • Business Continuity Plan (template) high level
    plan for DR too.
  • Analyze your business critical systems and
    prioritize the order of restoration.
  • ? Which systems need to be highly available?
  • ? Which systems can be out for 24 hours?
  • ? Which systems can be down for a week or more?
  • Identify and rank critical risks in terms of most
    likely to occur
  • ? Power Failure
  • ? Whether related disasters.(Tornadoes,
    Hurricane, Blizzards)
  • ? Environmental related disasters.(Fire ,
    chemical, contamination)
  • Define personnel roles, responsibilities,
    functions and hierarchy
  • Is your data protected?
  • ? High Availability (hardware or application
  • ? Data redundancy (copy of offsite data)
  • ? Tape restoration (local or remote)
  • Evacuation Plan
  • ? Define Evacuation Routes (contact HR department
    or local fire officials)
  • ? Meeting Location ( where to meet after
  • ? Accounting for personnel (is everyone out?)
  • ? Post evacuation communication (2-way radio)

Useful Links
  • SQL Server 2005 Best Practices Analyzer (February
    2007 CTP) download
  • Choosing a Database for High Availability An
    Analysis of SQL Server and Oracle download
  • How to Attain SQL Server High Availability at
    Minimal Cost download
  • SQL Server Tech-Center on High Availability
  • SQL Server 2000 High Availability (ISBN
    9780735619203) Book

  • Ill do my best to answer your questions.
  • If you dont get your questions answered today,
    post them at SSP Forums MSDN Forums or view

Thank you and have a great day ahead.
  • Feedback
  • Please fill out evaluation forms
About PowerShow.com