Sql Explore Hebrew

  • Published on

  • View

  • Download

Embed Size (px)


Sql Server Code Name \'Denali\' HADR (High Availability Desaster Recovery)


<ul><li> 1. | "| " ()DBCS</li></ul><p> 2. - DBA + 3 01 PRO Sql Server -Oracle CTO " DBCS - ", ", , , storenext ,galcomm. 3. Introduction to High Availability in SQL Server:Hardware and software solutions Features and techniques comparison Log Shipping Database Mirroring Replication Database Snapshots Backup improvements Online operations HADR deep dive: How to implement the nextgeneration of high availability and disasterrecovery solution with SQL Server 4. Introduction to High Availabilityand Disaster Recovery Definitions Introduce key terms and concepts Business Continuity Planning Overview of the BCP process SQL Server High Availability Planning How does BCP apply to SQL Server availability? 5. High Availability and Disaster Recovery: Definition High Availability Disaster Recovery High availability is a system designprotocol and associated Processes and proceduresimplementation that ensures a designed to restore businesscertain absolute degree ofoperational continuity during a given operations due to a natural ormeasurement periodhuman-induced disaster Typically involves providing Availability defined in terms ofredundancy spanning multipleservice level agreements (SLA) Recovery Timesites or across geographic Data loss during unplanned regions downtime A highly available application shouldbe accessible by users x% of thetime 6. Defining x and SLAAvailability Acceptable Acceptable Data Recovery Time Objective (RTO)ClassDowntime (hrs/yr)Loss (time of last OR RTO copy) OR RPO guided by availability requirements How much downtime can you tolerate?Tier 1 &gt;99.99%5 min or less (1 hr or less)Tier 2 99.9% - 99.99% (1- 5 mins to 8.5 hrs Recovery Point Objective (RPO) 8.5 hrs)guided by criticality ofTier 3 ( 100-200 miles Site Failures Location Redundancy City, CountyLocal HA &lt; 100-200 miles 8. Business Continuity Planning Impact Analysis Critical FunctionsAnalysis Threat Identification Recovery Objectives Solution Design Achieve recovery objectives for Solutionrelevant threats within specifiedMaintenanceDesign constraints like budget, human resources etc CostBenefit analysis of solutions Implementation Deploy the recommended solution Testing Implementati Test to see if the solution meets theTestingon recovery requirements Maintenance Yearly testing and review of procedures 9. SQL Server High Availability Planning Analysis Application tiers serviced by the databases Causes of database downtime Protection levels: Local HA, Regional DR, Geographic DRAnalysis Solution Design Need to understand what solutions exists? What are the characteristics and Maintenance Solution Design cost of the solution? Implementation What are the deployment steps and best practices? Testing TestingImplementation How do I test my implementation? Maintenance How do I monitor and maintain the solution? 10. Database Downtime DriversAnalysis FailureProtectionUnplannedDowntime User Errors Database Downtime OnlineAdministration PlannedDowntime Predictable Resourcing 11. Solution DesignSolutionDesign Understand the Solution Architecturesolutions andchoices beforeHA Capabilitiesmaking a decisionLimitations and Caveats Cost Vector 12. SQL ServerSolutionDesignAlways On Technologies 13. Always On TechnologiesSolutionDesign Provides a fullrange of options to Backup and Restore Log Shippingminimize downtimeIncreases Database Mirroringand maintainAvailability Failover Clustering Peer-Peer Replicationappropriate levelsof applicationavailability Online Index Operations Table PartitioningDecreased Enhanced Locking Resource GovernorDowntime Database Snapshot Dedicated Admin Connection Dynamic Configuration 14. Always On Technology OverviewSolution Architecture OverviewDesign How does it work? Backup and Restore Solution CharacteristicsIncreases Log Shipping Database Mirroring Data Loss Guarantees Availability Failover Clustering Failover Characteristics Peer-Peer Replication Redundancy Levelsand Utilization Cost Limitations and Caveats 15. Whats New in SQL Server 2008 New Features Feature Enhancements Resource Governor Database Mirroring Automatic recovery from Manage SQL Serverpage corruption workloads and resources Log stream compression by specifying limits on Faster recovery on failover resource consumption Log Shipping Sub-Minute Log Shipping Backup compression Backup Compression Failover Clustering Reduce backup and restore 16 nodestime Rolling upgrade Peer-Peer Replication Hot add new nodes 16. Backup &amp; restore 17. Backup and RestoreSolutionDesign Base availability technology for any solution Protects against failures and recovery from errors Provides Local HA and Site DR Need to ensure the backups are accessible if site goes down High RTO due to restore time RPO=0 can never be guaranteed Types: Full, Differential, and Transaction Log File-group backup/restore for large databases Backup Compression provides faster andsmaller backups in SQL Server 2008 18. Enhanced Error Detection In SQL Server 2000 RESTOREVERIFYONLY does not guarantee that thebackup is good Data may be corrupt In SQL Server 2005 RESTOREVERIFYONLY checks everything Ensures that the data is correct 19. Database Checksums SQL Server 2000 had TornPageDetection todetect incomplete I/O Operations by powerfailures SQL Server 2005 adds checksums to datapages Header of every page contains a checksum value When reading page, it re-computes checksum andcompares with checksum stored Returns error (824) if difference found Detects errors not reported by I/O Subsystem 20. Backup Checksums Detect errors introduced by backup hardware butnot reported by hardware or operating system Backup media error detection Backup devices do not always detect errors Works with RESTORE RESTORE VERIFYONLY Restore also checks page checksums, if present Disk error detection on data pages prior to backup Can continue past errors if desired 21. Backup Compression Common questions: We saw an 85 percent How much compression will I see? reduction in file size using Will it be comparable to, say, SQL Litespeed? SQL Server 2008 Backup Compression, says Colin One simple answer: Neller, Senior SoftwareIt depends!Engineer at ServiceU and part of the companys SQL All data compressesServer 2008 implementationdifferently the compressionteam. A backup file that wasratio achieved depends on: previously over 300 GB is The type of data in the database Whether the data in the database is now only 40 GB, and the job already compressedruns in about half the time. Whether the data/database is encrypted 22. Backup Compression: BackupPerformance Backup of a 322 MB Adventureworks database Uncompressed Compressed Hardly any CPU used (avg 5%),A LOT more CPU used (avg 25%) runtime = 39.5s, compression BUT runtime = 21.6s (45% ratio of 0.improvement) and backup stored in76.7MB (4.2x compression ratio) 23. DEMO 24. DATABASE SNAPSHOTS 25. Database Snapshots Read-only, consistent view of a Pagedatabase Specified point-in-time Modifying data Copy-on-write of affected pages Reading dataPage Accesses snapshot if data haschanged Redirected to original database 12:00 Snapshototherwise 26. Using Database Snapshot to Recover DataScenarioExample Code / StepsUndeleting INSERT INTO Production.WorkOrderRoutingrows SELECT * FROM AdventureWorks_dbsnapshot_1800.Prod.WorkOrderRoutingUndoingUPDATE HR.Departmentan updateSET Name = ( SELECT Name FROM AdventureWorks_dbsnapshot_1800.HR.Department WHERE DepartmentID = 1) WHERE DepartmentID = 1Recovering 1 Script the object in the database snapshota droppedobject 2 Execute the script in the source database 3 Repopulate the object (if appropriate) Caution: Not a substitute for a comprehensive backup and restore strategy 27. DEMO 28. Log Shipping 29. Log ShippingSolutionDesign Automated transaction log backup andrestore provides redundancy at thedatabase level SQLLogship.exe provides the underlyingframework for doing automated backup,copy and restore Backup on primary instance Restore on secondary instance(s) Scheduling is done throughSQL Server Agent jobs SQL Server 2008 provides sub-minute scheduling interval providing the ability to do quick backup and restores No automatic failover capabilities 30. Log Shipping (Key terms) Primary Server: Contains your primary database. SQL Server Agent makes periodic transaction logbackups to capture changes. Secondary Server Contain an unrecovered copy of the productiondatabase. One standby server can contain standby databasesfrom multiple primary servers. 31. Log Shipping (Key terms) cont Monitor Server (Optional) Monitors the status of the log-shipping jobs on theprimary and each standby server. One monitoring server can monitor multiple primary-standby server pairs. Should use a server other than the primary or thestandby to detect problems on either server. 32. Log ShippingCopy and RestoreBackupsPerformBackupsCopy Secondary DatabaseCopy and RestoreBackups CopyCopy Secondary DatabasePrimary DatabaseCopy and RestoreBackups Raise Secondary Database AlertsMonitor Database 33. Strength &amp; weakness Strengths Can Ship Logs Across WAN (Wide-Area Network) Protects an Entire Database Weaknesses Configured Per Database NO AUTOMATIC FAILOVER 34. DEMO 35. Mirroring 36. Database Mirroring Solution Design A database level high availability solutionthat provides complete protectionagainst data loss and fast recoverythrough automatic failover Maintains a redundant database byshipping log blocks when thetransactions are committed on theprincipal Synchronous and Asynchronousmodes provide the spectrum ofoptions to choose betweenavailability and performance Automatic failover when usingwitness server 37. Database Mirroring Modes High-Availability Mode Safety Full; Synchronous operation Database is available whenever a quorum exists Automatic failover High-Protection Mode Safety Full; Synchronous operation No witness quorum provided by partners If Principal loses quorum, it stops servicing the database Ensures high protection; database is never in exposed state Manual failover only; no automatic failover A transition mode; should not be in this mode for long High-Performance Mode Safety Off; Asynchronous operation Manual failover only Supports only one form of role switching: forced service (with possible data loss) 38. Database Mirroring How it works Mirror is alwaysApplication Witnessredoing it remains current CommitPrincipal Mirror1 52SQL Server SQL Server 2&gt;243 &gt;3LogDataLogData 39. DBM Automatic Page RecoveryWitnessClient 2. Request page 3. Find page6. Write5. Transfer page 1. Bad Page Page DetectedLog XData DataLog Principal 4. Retrieve page Mirror 40. Database Mirroring Enhancements Enhancements in SQL 2008 Compression of stream data for which at least a 12.5percent compression ratio can be achieved. Automatic Recovery from Corrupted Pages. Page read-ahead during the undo phase. Improved use of log send buffers. 41. Strength &amp; Weakness Strengths Can Mirror Across WAN Automatic Failover, and Nearly Instantaneous, Betterthan Failover Clustering Protects an Entire Database Weaknesses Requires Enterprise Edition Must be Configured Per Database 42. DEMO 43. Replication 44. Replication Primarily used whereavailability is required inconjunction with scale out ofread activity Failover possible; a customsolution Not limited to entiredatabase; Can define subsetof source database or tables Copy of database iscontinuously accessible forread activity Latency between sourceand copy can be as low asseconds 45. Transactional ReplicationSolution Design A high performance data replication solution that providesgranular table level replication Logical data movement provides flexibility and better hardware utilization Key scenarios: Customized application-specific DR Real-time reporting on secondary server that be used for Site DR Scale out application queries with ability to use any one database copy for Site DR Two types relevant for HA and DR Transactional and Peer-to-Peer 46. Peer-to-Peer Replication Provides high availabilityPeer Node Peer Nodeand read scalability Builds redundancy byeliminating single point offailure Enable online upgrades ofserversPeer Node Peer Node Maximize ApplicationUptime Support for both Ring andGrid Topology Centralized Managementusing Management Studio 47. New Features Replicated Data Write Load Balancing ReadApplication ServerUserRequests 48. Strength &amp; Weakness Strengths Perpetual or on-demand replication of data, local orremote Protects (duplicates or merges) the exact portion of thedatabase I want Weaknesses Configured per database, even per table Generally does not protect or duplicate an entireDatabase 49. DEMO 50. FailOver Custering 51. Failover ClusteringSolutionDesign Instance level protection built on WindowsFailover Clustering shared disk model Cluster nodes typically co-located within thesame site to provide local HA Regional DR possible using VLAN and stretchstorage level replication No built in data redundancy like databasemirroring and log shipping Data protection has to be provided at thestorage level or by combining with other solutions 52. Failover ClusteringNode 2Node 1 Virtual Node 3 ServerShared Disk 53. SQL Server Cluster Topologies Supports many scenarios:Failover Cluster Single Instance Multiple Instance * Inst1 Multiple Active Nodes N+1 N+M Multiple Active NodesN+1: N Active, 1 Inactive N+M: N Active, M InactiveNodes Nodes* Inst1* Inst1 Inst3 *Inst2 *Inst2 * 54. Failover Clustering (Facts) Redundancy at database instance level All databases fail over together Shared copy of system databases Single data copy on shared storage device No I/O overhead reducing throughput Storage unit is single point of failure for cluster All database services are clustered SQL Agent; Analysis Services; Full-Text engine, MS DTC Automatic failover (up to minutes) DBMS accessed over virtual IP Storage is controlled by one cluster node at a time Requires hardware certified by Microsoft for MicrosoftCluster Service 55. Strength &amp; Weakness Strengths Provides Protection Against a Node Failure, Protectsthe Entire SQL Instance Automatic Failover Supported Weaknesses Generally Expensive, Requires Specialty Hardware Specialty Hardware Requirements Not Trivial to Configure and Manage Doesnt Protect Against a Complete Site Failure 56. DEMO 57. Best Practices Backup your system databases aftermodifications. 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. Dont ignore any runtime errors. 58. What Solution Is Best For US ? 59. Always On SolutionSolution DesignCharacteristicsRedundancy andRPOFailover Utilization CostSolutions No Data Failover Unit Auto ReadMult- Write Hard- App PerfManag-Lo...</p>