1 / 95

Thanks for coming along to the webinar. Things will get started shortly…

SQL Server Central Webinar Series #13: Quick recovery techniques. Thanks for coming along to the webinar. Things will get started shortly…. SQL Server Central Webinar Series #13: Quick recovery techniques.

love
Download Presentation

Thanks for coming along to the webinar. Things will get started shortly…

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server Central Webinar Series #13: Quick recovery techniques Thanks for coming along to the webinar. Things will get started shortly…

  2. SQL Server Central Webinar Series #13: Quick recovery techniques This webinar is being recorded and the video will be available by Monday. Visit: http://www.red-gate.com/products/dba/backup-restore-bundle/webinarsor: www.SQLServerCentral.com/Training Steve Jones, SQL Server MVP and editor-in-chief of SQLServerCentral.com

  3. Why do we prepare for disasters?

  4. Failure is inevitable

  5. Be prepared • I will do my best

  6. 7

  7. Be prepared • I will do my best

  8. What’s a Disaster? • Earthquake that destroys your data center • Hard drive failure • Corruption in the database • Fire that closes your office (and server room) • Flooding in the city where your server is located • Bulldozer cuts the fiber cable to the office park • Water leak in the data center • Backup tape copied by competitor • Incorrect data load • Execute a DELETE without a WHERE • Deploy changes to production instead of dev server • Many, many more

  9. The “Whoops” Disaster

  10. Important Systems Inventory Accounting Critical Systems CRM Sales Less Important Systems Development Intranet

  11. Recovery Time Objective (RTO) Recovery Point Objective (RPO)

  12. The Recovery Time Objective (RTO) is the duration of time and a service level within which a business process must be restored after a disaster (or disruption) in order to avoid unacceptable consequences associated with a break in business continuity. - Wikipedia, http://en.wikipedia.org/wiki/Recovery_time_objective

  13. The time it takes for you to get things running to the point where someone can use them after someone notices that they aren't. RTO ~ Uptime* * 100% uptime is not possible for all clients

  14. RTO Examples Time Disaster Occurs System Restored Someone notices Clients Connect

  15. RTO Examples Time Disaster Occurs System Restored Someone notices Clients Connect RTO

  16. RTO Examples Time Disaster Occurs System Restored Someone notices Clients Connect RTO

  17. RTO Examples Time Disaster Occurs System Restored Someone notices Clients Connect RTO

  18. RTO Examples

  19. Recovery Point Objective (RPO)

  20. Recovery Point Objective (RPO) describes the acceptable amount of data loss measured in time. - Wikipedia, http://en.wikipedia.org/wiki/Recovery_point_objective Note: 0% data loss is possible

  21. RPO Examples Full Backup Log Backup Log Backup Time T1 Begin T2 Begin T3 Begin System Restored Disaster Occurs T1 Commit T2 Commit Someone notices Clients Connect

  22. RPO Examples Full Backup Log Backup Log Backup Time T1 Begin T2 Begin T3 Begin System Restored Disaster Occurs T1 Commit T2 Commit Someone notices Clients Connect RPO?

  23. RPO Examples Full Backup Log Backup Log Backup Time T1 Begin T2 Begin T3 Begin System Restored Disaster Occurs T4 Begin T1 Commit T2 Commit Someone notices Clients Connect RPO

  24. c RPO Examples Full Backup Log Backup Log Backup Time T1 Begin T2 Begin T3 Begin System Restored Disaster Occurs T4 Begin T1 Commit T2 Commit Someone notices Clients Connect RPO With Tail Log

  25. RPO Examples Full Backup Log Backup Log Backup Time T1 Begin T2 Begin T3 Begin System Restored Disaster Occurs T4 Begin T1 Commit T2 Commit Someone notices Clients Connect RPO Without Tail Log, with Log Backup 2

  26. RPO Examples Full Backup Log Backup Log Backup Time T1 Begin T2 Begin T3 Begin System Restored Disaster Occurs T4 Begin T1 Commit T2 Commit Someone notices Clients Connect RPO Without Tail Log, without Log Backup 2, with log backup 1

  27. RPO Examples Full Backup Log Backup Log Backup Time T1 Begin T2 Begin T3 Begin System Restored Disaster Occurs T4 Begin T1 Commit T2 Commit Someone notices Clients Connect ? RTO Full Backup Corrupt, deleted, etc.

  28. RPO Examples

  29. RPO - User Perspective Full Backup User starts T4 User starts T3 Log Backup Log Backup Time T1 Begin T2 Begin T3 Begin System Restored Disaster Occurs T4 Begin T1 Commit T2 Commit Someone notices Clients Connect ? RTO

  30. A transaction is not committed until the user gets an acknowledgement in the application.

  31. Everyone wants 100% uptime and 0 data loss

  32. Everyone wants 100% uptime and 0 data loss but no one wants to pay for it.

  33. RTO/RPO SLA Budget DR/BC Plan

  34. Issue detection time

  35. Issue detection time + reporting time

  36. Issue detection time + reporting time + response time

  37. Issue detection time + reporting time + response time + time to correct the issue

  38. Issue detection time + reporting time + response time + time to correct the issue Minimum RTO/RPO Time

  39. B C P S

  40. Backups Checks Practice and preparation Script and schedule

  41. Backups Checks Practice and preparation Script and schedule

  42. Backups Checks Practice and preparation Script and schedule

  43. Full Backups - Recommendations • Run as often as you can • Make at least two copies, one off the physical server • Make sure full backups files are physically separate from the data files. • If you must, co-locate these with log files (.ldf) • Be aware of your SAN/LUN structures • Monitor the backup file size growth over time • Restoring a full backup will often exceed your RTO, so be prepared to do this in advance on warm servers • Use COPY_ONLY for ad hoc backups • The mirrored backup option will fail both backups if one fails. DO NOT USE this. (SQL Backup does not fail the primary backup) • Compress Backups to save space/time • Do not append backups to one file. Use INIT and new files

  44. Full Backups - Recommendations • Run as often as you can • Make at least two copies, one off the physical server • Make sure full backups files are physically separate from the data files. • If you must, co-locate these with log files (.ldf) • Be aware of your SAN/LUN structures • Monitor the backup file size growth over time • Restoring a full backup will often exceed your RTO, so be prepared to do this in advance on warm servers • Use COPY_ONLY for ad hoc backups • The mirrored backup option will fail both backups if one fails. DO NOT USE this. (SQL Backup does not fail the primary backup) • Compress backups to save space/time • Do not append backups to one file. Use INIT and new files

  45. 200GB File Size Database Size

  46. 200GB File Size 100GB

  47. Database Size Data Size 100GB Compressed Data Size 54GB

  48. Database Size Data Size 54:13 Compressed Data Size 40:35

More Related