Keith Burns Microsoft UK

1. Mission Critical Database Keith Burns Microsoft UK Keith.burns@microsoft.com

2. Microsoft SQL Server® 2008 R2 HA/DR Technologies

3. Business Continuity SolutionsPoints to consider • Automatic or manual detection of errors • Automatic or manual failover • Acceptable data loss • Recovery Point Objective (RPO) • Time it takes to failover • Recovery Time Objective (RTO) • Number of failures to survive • Recovery Granularity: • Instance, Database, Table, Page or Row • Cost • Complexity

4. Backup • Permanent copy of data • Online restore • Bring database online as soon as primary file • group is restored • Access restored portion of database while remainder is restored • Backup compression in SQL Server 2008 R2 • Reduce volume significantly • Faster restore and backup • Compress UCS-2 Unicode data • Cost-effective ─ no specialized hardware is required • Straightforward setup and administration • RPO can be 100% • RTO: Time to backup txn log &restore all backup(s)

5. Backup Media Mirroring • Mirroring a media set increases backup reliability by reducing the impact of backup-device malfunctions • Creating checksums on backup media enables verifying subsequent restore operations • Having a mirror can resolve some restore errors quickly by substituting the mirrored media for damaged backup media 1100101001010010 1100101001010010 1100101001010010 1100101001010010 1100101001010010 1100101001010010 1100101001010010 1100101001010010 1100101001010010 “The backup media mirroring feature of SQL Server enables you to perform a mirrored backup of a database to multiple backup devices, which greatly increases the reliability of backups in case of faulty media or a lost backup device.” —High Availability White Paper

6. Online, Piecemeal restore • Almost any component (page, file, filegroup) can be restored – ONLINE • If a page is damaged • restore only that page from a file, filegroup or database backup • If a file is damaged • restore only that file from a file, filegroup or database backup • If a filegroup is damaged • restore only that filegroup from a filegroup or database backup • Readonlyfilegroups can be restored without rolling forward log changes • Users can access the database during the restore

7. Log Shipping • Protect data against user errors on the primary server • Pros • Good DR solution • Multiple Secondary databases • Time delay for replaying txn logs • May not require an additional lic. • Cons • Exclusive access required to apply log • Manual failover • Different server name. • How to restore secondary? • RPO: Probably not 100% • RTO: Not automatic

8. Multi-Instance Clustering • More than one passive node is available to host instances from multiple failovers on active nodes • Having multiple failover nodes provides greater availability • Multiple instances can share the same failover node, which reduces hardware costs • Simplified setup reduces administrative costs • 1 copy of data => disk(s) need to be “fault tolerant” • Stretch clusters often used for D.R. • RTO: ~ 1min (automatic detection & failover) • RPO: 100% Applications & Business Logic 110010100101110010100101 110010 110010100101 110010100101 110010 110010100101 110010100101 110010 Active Failover Active Offline Active Active

9. Database Mirroring • Hot standby protects against database or server failure • Two separate copies of data • SQL Server 2008 R2 compresses the log stream from principal server to mirror server • Automatic recovery from page corruption • SQL Server 2008 R2 supports • rolling upgrades of mirrored • database configurations • High Availability (HA Soln.) • RPO: 100% • RTO: Seconds (with witness) • High Performance (DR Soln.) • RPO: Seconds • RTO: Manual Client Witness Principal Mirror

10. Enhanced Database Mirroring High Performance Mirroring • Increase performance through asynchronous mirroring Automatic Page Repair • Automatically detects page corruption and retrieves data from the mirror • Reduces downtime and management costs • Minimizes application changes to correctly handle I/O errors Reporting from Mirror • Increase utilization of mirror server • Reduce need for reporting servers Applications & Business Logic Principal Mirror “This is a really powerful enhancement because prior to this… you would have to run DBCC CHECKDB... and that would likely mean taking downtime… With SQL Server 2008 Database Mirroring you can avoid the effort and downtime.” — Glenn Berry, Database Architect, NewsGator Technologies

11. Peer-to-Peer Replication • Increases reliability by replicating data to multiple servers • Provides higher availability in case of failure or to allow maintenance at any of the participating nodes • Offers improved performance for each node with geo-scale architecture • Add and remove servers easily without taking replication offline, by using the new topology wizard • You deal with conflicts. • Consider issues with triggers etc Applications & Business Logic 110010100101 110010100101 110010 110010100101 110010100101 110010 110010100101100101100101100101 110010100101 110010100101 110010 “[Microsoft] SQL Server 2008 replication proved to be very predictable and reliable in our testing. This helps us to create flexible and scalable replication solutions. Reliability must be at the foundation of all that we do.” — Sergey Elchinsky, Leading System Engineer, Baltika Breweries

12. Live Migration in Hyper-V™ • Move running instances of virtual machines between host servers • Virtual machines can be moved for maintenance or to balance workloads on host servers • Perform maintenance on physical machines without any downtime • Requires Windows Server 2008 R2 Hyper-V • In itself not an HA solution but could be combined with clustering Applications & Business Logic 110010100101 110010100101 110010 110010100101 110010100101 110010 110010100101 110010100101 110010 110010100101 110010100101 110010 110010100101 110010100101 110010 110010100101 110010100101 110010 110010100101 110010100101 110010 110010100101 110010100101 110010 Virtual Servers Virtual Server Host Server Host Server “This server already runs on our cluster solution with high availability, but after we have tested live migration on the new hardware, we’ll move it over to ensure optimal performance and reliability.” —Rodrigo Immaginario, IT Manager, Universidade Vila Velha

13. Online Operations • Allow routine maintenance without downtime • Online index operations • Online page and file restoration • Online configuration of peer-to-peer nodes • Users and applications can access data while the table, key, or index is being updated 0 5 1 1 Applications & Business Logic 2 2 3 3 4 4 0 5 110010100101 110010100101 110010 110010100101 110010100101 110010 Index Table 5 Index Table Deleted 0 4 Deleted 2 Deleted Deleted 3 7 5 0 4 6 5 3 7 6 7 “We recommend performing online index operations for business environments that operate 24 hours a day, seven days a week, in which the need for concurrent user activity during index operations is vital.” — SQL Server Books Online

14. Database Snapshots • Provide a read-only static view of the database at a point in time • Revert databases to a point in time before user error • Data loss is limited to changes after the snapshot • Run reports from a snapshot created on the mirror server in a database mirroring session to better utilize resources Applications & Business Logic 110010100101 110010100101 110010 110010100101 110010100101 110010 110010100101 110010100101 110010 Snapshot Source “Database snapshots allow you to create read-only databases for reporting and can also be useful in your data recovery efforts in the event of a disaster.” —Tim Chapman, SQL Server Database Administrator

15. Logical recovery standby Putting It All Together Database Scale- outfor queries • Database Mirroring • Primary disaster site for databases • Failover Clustering • Local server redundancy • Replication • Database reporting and read scale-out with redundancy • Log Shipping • Additional disaster sites for databases • Logical recovery Replication Database mirroring Hot standby Clustering Production database Log shipping Warm standby Log shipping with restore delay Backup

16. SQL Server “Denali” AlwaysOn Increase flexibility with new, integrated HADR solution • Faster failover, easier administration with Availability Groups • Identify databases to failover as a unit to reduce unplanned downtime • Faster application failover using virtual name • Increase application uptime using flexible failover policy • Enable better data redundancy and protection with up to four secondaries and up to two synchronous secondaries • Limited downtime with enhanced online operations • Run Microsoft SQL Server® on Windows Server® Core to reduce planned downtime (50-60% fewer OS patch reboots) Shared Storage A A A A A A A Non-Shared Storage A A Disaster Recovery

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