SQL Server 2005 Business Continuity Measures

1
SQL Server 2005 Business Continuity Measures
Practices

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

Lalandia, Denmark - March 09th 2007
2
Topics

• 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

3
Introduction - Speaker

• Webmaster of www.sqlserver-qa.net
  www.sqloogle.co.uk
• Been in the IT field over 15 years
• SQL Server DBA for over 10 years (since ver. 4.2)
• Contributing Editor, Writer Moderator for
  www.sql-server-performance.com
• 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

4
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.

5
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?

6
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
  application.
• 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
  challenging.

7
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.

8
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.

9
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
  safety.
• 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
  accessible.
• Combination
• Mixing both Business Continuity Disaster
  Recovery can fetch better results with the help
  of available technology hardware.

10
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.

11
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
  statistics
• Disaster Recovery is nothing but providing
  Business Continuity.
• What do you think about Disaster Recovery plan
  source?
• 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.

12
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
  location.
• Calculate the cost of downtime right from 01st
  second.
• Return of Recovery - RPO RTO are important
  factors.

13
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
  process
• 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.

14
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
  suitable.
• 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.

15
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.

16
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
  recovery".

17
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.

18
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.

19
Measuring Metrics - Effectiveness

• Ready to get-started
• A plan is only as good as it is when it is
  executed.
• 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.

20
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.

21
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
  OLTP)
• Reporting Services with Report Builder server
  based reporting platform from relational and
  multi-dimensional data sources.
• Native XML Web services and many more.

22
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
  procedures.
• A simple and inexpensive solution to recovering
  from failure is to take backups of all your
  databases.
• Standby servers are a cost-effective and viable
  way for businesses to maintain SLA and business
  continuity efficiently.

23
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
  BC.
• Triggers for Logon Events (New in Service Pack 2)
• Indexing made easier with SQL Server 2005 DMV
• Scalable Shared Databases
• Database Tuning Adivsor

24
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?
  (Minutes)
• ? 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
  redundancy)
• ? 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
  evacuation)
• ? Accounting for personnel (is everyone out?)
• ? Post evacuation communication (2-way radio)

25
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
  Review
• SQL Server 2000 High Availability (ISBN
  9780735619203) Book

26
Q A

• 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
  Blogs

27
Thank you and have a great day ahead.

• Feedback
• Please fill out evaluation forms