Business Continuity Solutions for SQL Database* applications on Windows Azure

1. DBI334 Business Continuity Solutions for SQL Database* applications on Windows Azure Alexander (Sasha) Nosov Principal Program Manager Microsoft Corporation 425.922.9081 * Formerly known as SQL Azure

2. Business Continuity problems

3. Foundation of Business Continuity • Physical security • Geo distribution • Platform security • Compliance

4. Terminology

5. Availability challenges in cloud systems • Hardware and software failures are inevitable • People make operational errors that cause failures • At cloud scale low frequency failures happen every day • Need: automated fault tolerance to maintain local availability

6. High Availability solution for SQL databases Single Logical Database Multiple PhysicalReplicas • Reads are completed at the primary • Writes are replicated to secondaries • Each replica is backed up independently DB Ack Read Value Write P P Ack Ack S S S Write Write S

7. High Availability under the hood • Critical capabilities: • Create new replica • Synchronize data • Stay consistent • Detect failures • Fail over Primary Manager Node Partition Manager Which replica lost? Global Partition Map Promote to primary Node down Reconfigure Data Node 101 Data Node 102 Data Node 103 Data Node 104 Data Node 105 P P P P S S S S S P S P S S S S S S S Fabric

8. Customer benefits of High Availability • No additional administration cost • ACID properties are maintained by the system • Failovers are fully automated • Dynamic routing of connections • No additional charges for database redundancy • RPO=0, RTO=30sec • 99.9% availability SLA

10. Recovery from human errors • Humans make mistakes • The system cannot detect them • Human is needed to recover • Need: ability to return to previous application state

11. Database copy as extension of Create replica

12. Database copy as extension of Create replica • Extends replica creation and expose to user • Transactional consistency after completion • Can copy to any server in the same region • Can copy from live database • Can cancel in flight operation • Can monitor status • Only billed if copy is successful P S S P’ S S

13. Using database copy to recover from human error

14. Using database copy to recover from human error AzureDNS http://www.contoso.com/ • Minimal charge is prorated day use • Copies entire database • You are responsible for retention and cleanup • Support database federations is coming soon • RTO = time to recognize the error + time to rename the database + rename delays End user App admin Oops! ALTER DATABASE DB_MAIN MODIFY NAME = DB_MAIN_OLD GO WAITFOR DELAY '00:00:30' GO ALTER DATABASE DB_06_01_2012 MODIFY NAME = DB_MAIN GO South Central US Contoso service Recover from backup Create backup copy DB_MAIN DB_MAIN_OLD DBCopy DB_06_11_2012 DB_06_12_2012 DB_06_13_2012 DB_MAIN

15. Using database copy and Azure storage Leverages import/export service and Azure storage Reduces storage cost but increases backup time More complex backup/restore workflow RTO = time to recognize the error + time to import database + time to rename the database South Central US Contoso service DB_MAIN BP_06_11_2012 BP_06_12_2012 BP_06_13_2012 DBCopy Export DB_06_01_2012

17. Recovery from widespread loss of facility • Happen more often than you think • Often caused by human error • Recovery will cause data and uptime loss • Need: geographic redundancy and ability to fail over

18. Using export to Azure storage to recover from widespread failure

19. Using Azure storage for geo redundancy AzureDNS Export http://www.contoso.com/ North Central US • Copies entire database • You are responsible for cleanup • Minimal database charge is prorated day use • Support for database federations is coming soon • RTO = time to import + time to start app + DNS change • RPO = export interval DNS update End user App admin Activate and verify application Import from blob Create backup copy South Central US North Central US Contoso V1 (primary instance) Contoso V1 (secondary instance) Export to blob BP_06_11_2012 DB_MAIN BP_06_12_2012 BP_06_13_2012 DBCopy Import DB_MAIN DB_06_11_2012 DB_06_12_2012 DB_06_13_2012

20. demo Using SQL Database import/export service for disaster recovery of your application

21. Using Azure storage automatic geo-replication South Central US North Central US Contoso V1 (primary instance) Geo-replication BP_06_11_2012 BP_06_11_2012 DBCopy BP_06_12_2012 BP_06_12_2012 BP_06_13_2012 BP_06_13_2012 DB_06_01_2012 DB_05_25_2012 DB_05_18_2012

22. Using Azure storage automatic geo-replication • Quicker export to local blob • RPO = export interval (potentially shorter) • RTO = Azure storage RT(24 hrs) + time to import + time to start app + DNS change

24. Quick survey • I am fully satisfied with the existing Azure solutions • I am OK for now but the achievable RPO/RTO will be a problem down the road • The achievable RPO/RTO do not meet my SLA requirements

25. Roadmap: Geo-DR

26. Roadmap: Geo-DR • Extends replica creation and data synchronization to geo • User initiated • Automatic data replication and synchronization • Optional RPO enforcement • Read-only geo-secondary • Multiple geo-secondaries • User-controlled failover P’ P S’ S S’ S

27. Roadmap: Point in time recovery

28. Roadmap: Point in time recovery • Exposes internal backups to the user • Backups are highly available • Restores to new database • Any point in time within retention period P S S P’

29. Recap and call to action • Azure provides a solid foundation of business continuity • A range of solutions available • User participation is required: • Implement re-try logic in the application • Enable PITR using dbcopy • Convert to BACPAC and blobs to minimize cost • Enable geo-redundancy by exporting BACPAC(s) into multiple datacenters • Stay tuned for future announcement of the new BC features

30. Track Resources Hands-On Labs @sqlserver @ms_teched SQL Server 2012 Eval Copy Get Certified! mva • Microsoft Virtual Academy

31. Additional resources • Business Continuity in SQL Azure http://msdn.microsoft.com/en-us/library/windowsazure/hh852669.aspx • Business Continuity for Windows Azure http://msdn.microsoft.com/en-us/library/windowsazure/hh873027.aspx • Inside SQL Azure http://social.technet.microsoft.com/wiki/contents/articles/1695.inside-sql-azure.aspx • SQL Azure Import/Export Service has hit Production http://dacguy.wordpress.com/

32. Related Content • Breakout Session: • AZR203 Business Continuity in the Windows Azure Cloud • AZR204 Hybrid Will Rule: Options to Connect, Extend and Integrate Applications in Your Data Center and Windows Azure Find Me Later At SQL Server booth or e-mail sashan@microsoft.com

33. Q & A

34. Using Data Sync as a DR solution Goals of Data Sync Challenges Preservation of transaction boundaries Some schemas are not supported No support for multiple versions • Synchronization of data between SQL Server databases and Azure SQL databases. • Synchronization of data between two or more Azure SQL databases

35. Track Resources Hands-On Labs @sqlserver @ms_teched SQL Server 2012 Eval Copy Get Certified! mva Microsoft Virtual Academy

36. Resources Learning TechNet • Connect. Share. Discuss. • Microsoft Certification & Training Resources http://northamerica.msteched.com www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet http://microsoft.com/msdn

37. Required Slide Complete an evaluation on CommNet and enter to win!

38. MS Tag Scan the Tag to evaluate this session now on myTechEd Mobile

39. Session Objectives and Takeaways • Session Objectives: • Scope of business continuity failures • Business continuity solutions for SQL database application in Windows Azure • Implementation trade-offs • Key Takeaways: • Azure provides strong support to enable business continuity of your application • You can choose from a range of solutions depending on your SLA requirements and budget constraints

40. © 2012 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.