Mission Critical Database Keith Burns Microsoft UK Keith
Mission Critical Database Keith Burns Microsoft UK Keith. burns@microsoft. com
Microsoft SQL Server 2008 R 2 HA/DR Technologies ® Backup and Restore Log Shipping Failover Clustering Database Mirroring Transactional and peer-to-peer replication
Business Continuity Solutions Points 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
Backup • • Permanent copy of data Online restore • Bring database online as soon as primary file group is restored • • Backup compression in SQL Server 2008 R 2 • • Access restored portion of database while remainder is restored 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)
Backup Media Mirroring 1100 1010 0101 0010 1100 1010 0101 0010 § 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 1100 1010 0101 0010 “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
Online, Piecemeal restore • Almost any component (page, 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 • Readonly filegroups can be restored without rolling forward log changes • Users can access the database during the restore
Log Shipping • • Protect data against user errors on the primary server Pros • • • Cons • • • Good DR solution Multiple Secondary databases Time delay for replaying txn logs May not require an additional lic. Exclusive access required to apply log Manual failover Different server name. How to restore secondary? RPO: Probably not 100% RTO: Not automatic
Multi-Instance Clustering Applications & Business Logic Active Failover Active 110010111 110010100 00101 110010 Offline Active 1100101 110010 • 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. • RPO: 100% Active RTO: ~ 1 min (automatic detection & failover)
Database Mirroring • • • Hot standby protects against database or server failure Two separate copies of data SQL Server 2008 R 2 compresses the log stream from principal server to mirror server Automatic recovery from page corruption SQL Server 2008 R 2 supports • • • High Availability (HA Soln. ) • • • rolling upgrades of mirrored database configurations RPO: 100% RTO: Seconds (with witness) High Performance (DR Soln. ) • • RPO: Seconds RTO: Manual Client Witness Principal Mirror
Enhanced Database Mirroring High Performance Mirroring § Automatic Page Repair Applications & Business Logic Principal Increase performance through asynchronous mirroring Mirror § 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 “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, News. Gator Technologies
Peer-to-Peer Replication Applications & Business Logic 11001010 0101100101 11001010 100101 1100101 00101 110010 • 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 11001010 0101 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
Live Migration in Hyper-V™ Applications & Business Logic • 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 R 2 Hyper-V • In itself not an HA solution but could be combined with clustering 110010100 110010100 101 101 110010 110010100 101 101 110010 Virtual Servers 110010100 101 101 110010 Virtual 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. ”
Online Operations 11001010 0101 110010 • Applications & Business Logic Table Index 05 Deleted 1 Deleted 24 Deleted 233 74 505 6 37 • 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 “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
Database Snapshots 11001010 0101 110010 Applications & Business Logic Snapshot • 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 Source 11001010 0101 110010 “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
Putting It All Together Database Scaleout for queries Database Mirroring Replication § Primary disaster site for databases Failover Clustering § Local server redundancy Replication § Database reporting and read scale-out with redundancy Database mirroring Clustering Production database Log shipping Log Shipping § Additional disaster sites for databases Log shipping with restore delay § Logical recovery Backup Hot standby Warm standby Logical recovery standby
SQL Server “Denali” Always. On Increase flexibility with new, integrated HADR solution A A 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 A A A Non-Shared Storage • Limited downtime with enhanced online operations ® A Shared Storage • Faster failover, easier administration with Availability Groups • A ® • Run Microsoft SQL Server on Windows Server Core to reduce planned downtime (50 -60% fewer OS patch reboots) A A Disaster Recovery
© 2008 Microsoft Corporation. All rights reserved. Microsoft, 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.
- Slides: 17