1 / 25

Disaster Recovery in SQL Server

Disaster Recovery in SQL Server. Vinod Kumar Technology Evangelist | Microsoft http://www.ExtremeExperts.com http://bogs.sqlxml.org/vinodkumar. Agenda. Definitions Disaster Recovery Planning Database Snapshots Log shipping Database Mirroring What to do when system databases crash

daniel-gentry
Download Presentation

Disaster Recovery in SQL Server

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. Disaster Recovery in SQL Server Vinod Kumar Technology Evangelist | Microsoft http://www.ExtremeExperts.com http://bogs.sqlxml.org/vinodkumar

  2. Agenda • Definitions • Disaster Recovery Planning • Database Snapshots • Log shipping • Database Mirroring • What to do when system databases crash • Rebuilding System Databases

  3. Definitions • For clarity, we’ll use the following definitions today: • Disaster: an event that results in serious loss of data or service • Disaster Recovery: A process that allows continuation of business following a disaster, including manual methods • Offsite Disaster Recovery: A process that allows disaster recovery at a remote location (usually entire site) • Business Continuity: A process that includes disaster recovery and offsite disaster recovery as well as using systems to avert disasters, such as fault-tolerant hardware and software

  4. Definitions • As per http://support.microsoft.com/kb/822400/en-us “Disaster recovery planning is the work that is devoted to preparing all the actions that must occur in response to a disaster. The planning includes the selection of a strategy to help recover valuable data. The selection of the appropriate disaster recovery strategy depends on your business requirements.”

  5. Type of disasters Legal issues Wars Strikes Human error Viruses, etc. • Natural Disasters: Tsunami, Katrina, Rita, etc • Fire • Power outage/failure • Organized disruptions • Theft • System failures

  6. Disaster Recovery Drivers Symantec Annual IT Survey Results Published in IT News 9/9/2008 – Symantec Annual IT Survey

  7. Prepare a DRP document • Include every possible information: • System architecture (How the system/application works) • How many systems are involved and what their names are. • Their IP Addresses, drive information, file locations • Software installed, Contact information of DBA’s, or other key people. • Know your SLAs and choose appropriate technology.

  8. Prepare a DRP document(cont.) • Include every possible information… • Step by step guide on how to recover each of your system based on different disaster scenarios (Including timelines for recovery) • Make sure you discuss DRP guide with all the parties involved. • Security information, jobs/schedule information, etc. • Make it a reminder for yourself that any system changes should be updated in this guide. • Test, test and test!!!

  9. Database Snapshots • Read-only, consistent view of a database • Specified point-in-time • Modifying data • Copy-on-write of affected pages • Reading data • Accesses snapshot if data has changed • Redirected to original database otherwise Page Page 12:00 Snapshot

  10. DEMO Database snapshot 1. Recovering modified data 2. Recovering dropped object 3. Restoring from Snapshot

  11. Log Shipping • An automated method of maintaining a warm standby server • Based on SQL Server's backup and restore architecture. Uses the transaction log to track changes • Relatively low-tech and inexpensive • ‘Ships' (copies and restores) a production server's transaction logs to a standby server

  12. Log Shipping (Key terms) • Primary Server: • Contains your primary database. • SQL Server Agent makes periodic transaction log backups to capture changes. • Secondary Server • Contain an unrecovered copy of the production database. • One standby server can contain standby databases from multiple primary servers.

  13. Log Shipping (Key terms) cont… • Monitor Server (Optional) • Monitors the status of the log-shipping jobs on the primary and each standby server. • One monitoring server can monitor multiple primary-standby server pairs. • Should use a server other than the primary or the standby to detect problems on either server.

  14. Log Shipping

  15. Database Mirroring • Newly introduced with SQL Server 2005. • Maintains a copy of the principal database as a mirror. • Transfers log records from principal to mirror server instance. • Works with all hardware that supports SQL Server 2005. • Automatic client redirection (using .NET 2.0) • Can have a third optional server called Witness server for Auto Failover.

  16. Database Mirroring -Synchronous 1 Acknowledge Commit 7 Acknowledge 6 Constantly redoing on mirror 2 Transmit to mirror 2 4 Write to local log Committed in log Write to remote log 3 5 DB Log Log DB

  17. Database Mirroring Enhancements • Enhancements in SQL 2008 • Compression of stream data for which at least a 12.5 percent compression ratio can be achieved. • Automatic Recovery from Corrupted Pages. • Page read-ahead during the undo phase. • Improved use of log send buffers.

  18. DEMO Database Mirroring Automatic Failover Auto page repair (SQL 2008)

  19. Rebuilding System Databases • SQL 2000 • Use RebuildM.exe (UI based) • Need setup media for Database files • SQL 2005 • Use Setup.exe (Command Prompt based) • Need setup media for setup.exe and database files. • SQL 2008 • Use Setup.exe (Command Prompt based) • Setup media not required. Files copied during initial installation. • Will not Rebuild Resource database.

  20. Rebuilding System Databases • Setup.exe file located under %Program Files%\Microsoft SQL Server\100\Setup Bootstrap\Release • Database Files used by setup are located under %Program Files%\Microsoft SQL Server\MSSQL10.<INSTANCENAME>\MSSQL\Binn\Templates • Files Missing? • Copy from setup media • Apply service packs/patches after rebuilding

  21. System databases disaster • Master database loss • SQL Server won’t start • Rebuild system databases • Start SQL in single user mode and restore master • Msdb database loss • SQL Agent won’t start. • Restore msdb • Model database loss • SQL Server won’t start. • Rebuild system databases • Restore master, msdb and model

  22. Best Practices • Backup your system databases after modifications. • Test if backups are restorable. • Practice / Test your disaster recovery plans. • Documentation is not only for you. • Keep dedicated DR Server ready. • Use BACKUP CHECKSUM features. • Run DBCC CHECKDB regularly. • Don’t ignore any runtime errors.

  23. Summary • Murphy’s Law on Disaster… • If there is a possibility of several things going wrong, the one that will cause the most damage will be the one to go wrong. • If you fail to plan, you are planning to fail. • Off-site backups always help. • Auto page repair is a band-aid.

  24. References • SQL Server Books online http://msdn.microsoft.com/en-us/sqlserver/default.aspx • Planning for SQL Disaster Recovery http://technet.microsoft.com/en-us/library/ms178094.aspx • Database Mirroring Whitepaper http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

More Related