1 / 131

Preparation for Disaster

The World’s Largest Community of SQL Server Professionals. Preparation for Disaster. Steve Jones SQLServerCentral Red Gate Sofware. Be prepared I will do my best. Why do we prepare for disasters?. Failure is inevitable. The “Whoops” Disaster. Who is a parent?. Be prepared

alaire
Download Presentation

Preparation for Disaster

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. The World’s Largest Communityof SQL Server Professionals Preparation for Disaster • Steve Jones • SQLServerCentral • Red Gate Sofware

  2. Be prepared • I will do my best The World’s Largest Community of SQL Server Professionals

  3. The World’s Largest Community of SQL Server Professionals

  4. Why do we prepare for disasters? The World’s Largest Community of SQL Server Professionals

  5. Failure is inevitable The World’s Largest Community of SQL Server Professionals

  6. The World’s Largest Community of SQL Server Professionals

  7. The World’s Largest Community of SQL Server Professionals

  8. The “Whoops” Disaster The World’s Largest Community of SQL Server Professionals

  9. The World’s Largest Community of SQL Server Professionals

  10. The World’s Largest Community of SQL Server Professionals

  11. Who is a parent? The World’s Largest Community of SQL Server Professionals

  12. The World’s Largest Community of SQL Server Professionals

  13. The World’s Largest Community of SQL Server Professionals

  14. The World’s Largest Community of SQL Server Professionals

  15. The World’s Largest Community of SQL Server Professionals

  16. Be prepared • I will do my best The World’s Largest Community of SQL Server Professionals

  17. 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 The World’s Largest Community of SQL Server Professionals

  18. The World’s Largest Community of SQL Server Professionals

  19. The World’s Largest Community of SQL Server Professionals

  20. insurance The World’s Largest Community of SQL Server Professionals

  21. backups are insurance The World’s Largest Community of SQL Server Professionals

  22. How often do you back up? The World’s Largest Community of SQL Server Professionals

  23. It depends The World’s Largest Community of SQL Server Professionals

  24. Recovery Time Objective (RTO) Recovery Point Objective (RPO) The World’s Largest Community of SQL Server Professionals

  25. 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 The World’s Largest Community of SQL Server Professionals

  26. 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 The World’s Largest Community of SQL Server Professionals

  27. RTO Examples Time Disaster Occurs System Restored Someone notices Clients Connect The World’s Largest Community of SQL Server Professionals

  28. RTO Examples Time Disaster Occurs System Restored Someone notices Clients Connect RTO The World’s Largest Community of SQL Server Professionals

  29. RTO Examples Time Disaster Occurs System Restored Someone notices Clients Connect RTO The World’s Largest Community of SQL Server Professionals

  30. RTO Examples Time Disaster Occurs System Restored Someone notices Clients Connect RTO The World’s Largest Community of SQL Server Professionals

  31. RTO Examples The World’s Largest Community of SQL Server Professionals

  32. Recovery Point Objective (RPO) The World’s Largest Community of SQL Server Professionals

  33. Recovery Point Objective (RPO) describes the acceptable amount of data loss measured in time. - Wikipedia, http://en.wikipedia.org/wiki/Recovery_point_objective 0% data loss is possible The World’s Largest Community of SQL Server Professionals

  34. 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 The World’s Largest Community of SQL Server Professionals

  35. 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? The World’s Largest Community of SQL Server Professionals

  36. 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 The World’s Largest Community of SQL Server Professionals

  37. 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 c RPO With Tail Log The World’s Largest Community of SQL Server Professionals

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

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

  40. 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 The World’s Largest Community of SQL Server Professionals

  41. RPO Examples The World’s Largest Community of SQL Server Professionals

  42. 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 The World’s Largest Community of SQL Server Professionals

  43. A transaction is not committed until the user gets an acknowledgement in the application. The World’s Largest Community of SQL Server Professionals

  44. Building an RTO/RPO • SQLServerCentral • 4 databases (3GB, 1.9GB, 260MB, 220MB) • Full backups nightly at midnight • Log backups every half hour • Servers clustered • Backups files are stored on separate physical drives from the data and log files. • RTO is 30 minutes • RPO is 10 min The World’s Largest Community of SQL Server Professionals

  45. Building an RTO/RPO • Can I meet my RTO? (30 min) • Full restore is 12 min • 18 min allows for 9 logs, or a restore from midnight through 4:30am. • Any failures after this time requiring all logs will result in RTO being exceeded. The World’s Largest Community of SQL Server Professionals

  46. Building an RTO/RPO • Can I meet my RPO? (10 min) • Logs backed up every 30 minutes • If a failure is within 10 minutes of a log backup, I can meet the RPO • If the tail log backup is available, I can meet the RPO. The World’s Largest Community of SQL Server Professionals

  47. Mitigations • RPO • Move log backups to every 5 minutes (or anything < 10 minutes) • RTO • Differentials may help reduce the recovery time, but not likely enough to meet the RTO in all situations. • Most likely a standby server is needed to ensure the RTO can be met in all circumstances. Another server will be $5k + $400/mo • Without another server, RTO will likely be exceeded (max restore time is 284 min + response time. (8 min restore + 276 logs through 11:55pm). • Increase acceptable RTO to 300 min. The World’s Largest Community of SQL Server Professionals

  48. Remediation - Zero Cost • RPO • Log backups can be scheduled more often • Mirror to a spare database • Add auditing/logging of transactions • RTO • utilize spare hardware for a warm database • have scripts ready to eliminate restores (whoops! Disasters) • Implement Backup Compression (if supported in your edition) The World’s Largest Community of SQL Server Professionals

  49. Remediation - Hard Costs • RPO • Hot standby servers in a remote location • Third party auditing tools • RTO • Hot standby servers • Third party tools for object level restores (SQL Virtual Restore, Data Compare, SQL Compare) • Backup Compression (third party tools such as SQL Backup Pro) The World’s Largest Community of SQL Server Professionals

  50. talk to clients The World’s Largest Community of SQL Server Professionals

More Related