1 / 40

SQL Server High Availability Concepts & Solution Guidance (2008 R2 & 2012)

May 08 – 09 2012, Kongresshaus Berchtesgaden. SQL Server High Availability Concepts & Solution Guidance (2008 R2 & 2012). Satya SK Jayanty Director & Principal Architect D BI A Solutions. consulting@dbiasolutions.co.uk. About me. IT Experience

jethro
Download Presentation

SQL Server High Availability Concepts & Solution Guidance (2008 R2 & 2012)

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. May 08 – 09 2012, KongresshausBerchtesgaden SQL Server High Availability Concepts & Solution Guidance (2008 R2 & 2012) Satya SK Jayanty Director & Principal Architect D BI A Solutions • consulting@dbiasolutions.co.uk

  2. About me • IT Experience • Principal Architect & Consultant – D Bi A Solutions : Europe (consulting@dbiasolutions.co.uk) • Been in the IT field over 20+ years (using SQL Server ver.4.2 onwards) • Publications • Author: Microsoft SQL Server 2008 R2 Administration cookbook – Packt Publishers (May 2011) • Co-author for MVP Deep Dives Volume II – Manning Publications (October 2011) • Community Contributions • SQL Server MVP since 2006 • Founder (SQLMaster) & blogs at www.sqlserver-qa.net(SQL Server Knowledge Sharing Network) • Contributing Editor & Moderator - www.sql-server-performance.com[SSP] • Quiz Master & Blogger: www.beyondrelational.com & www.sqlservergeeks.com • Active participation in assorted forums such as SSP, SQL Server Central, MSDN, SQL Server magazine, dbforums etc. www.sqlserver-qa.net@sqlmaster

  3. www.packtpub.com www.sqlserver-qa.net @sqlmaster

  4. Agenda • Understanding High Availability • Common terms • Planned and Unplanned Downtime • High Availability vs. Disaster Recovery • Specific SQL Server 2008 + R2 features for High Availability • SQL Server 2012 – What’s New • AlwaysOn

  5. Describing High Availability

  6. IT Lifecycle Database Service Management Operate Support Deliver IT Operations

  7. High Availability Definition Database Service Management Time between failure Time to repair IT Operations Uptime Downtime • MTBF – Mean Time Between Failures, MTTR – Mean Time To Repair

  8. Database Server Availability Total Availability = product of the availability of each component

  9. Downtime • Consider: • RTO – Duration of Outage (Recovery Time Objective) • RPO – Measure of acceptable data loss (Recovery Point Objective) • Justify ROI • Avoid downtime • Automating recovery • Resource utilization

  10. Disaster recovery Business Continuity Plan Business Function Database Service Management IT Operations Recovery Time Objective Data loss Disaster recovery plan Recovery Point Objective

  11. High Availability vs. Disaster Recovery • High Availability (HA) – prevent an outage • Disaster Recovery (DR) – address & re-establish HA after outage • HA is feature and DR is implementation (must be tested) • RCA is highly essential in both aspects.

  12. SQL SERVER 2008 R2 HIGH AVAILABILITY FEATURESFailover clustering Windows Failover Cluster Windows Failover Cluster Node 2 Node 1 Node 1 Node 2 Direct Attached Storage Storage Area Network

  13. HIGH AVAILABILITY FEATURESGeographically dispersed failover clustering Storage Replication (Synchronous) London Zurich

  14. HIGH AVAILABILITY FEATURESDatabase mirroring Mirror Site B Principal Site A Witness (Optional) Site C

  15. HIGH AVAILABILITY FEATURESLog shipping Secondary Site B Primary Site A Transaction Log backup

  16. HIGH AVAILABILITY FEATURESTransactional Replication Secondary Site B Transaction Log records Primary Site A

  17. HIGH AVAILABILITY FEATURESPeer-to-peer transactional replication Node B Site B Node A Site A Node C Site C

  18. WINDOWS HIGH AVAILABILITY FEATURESOther features Edition specific • Online Indexing • Hot Add CPU • Hot Add Memory • Adjust memory online without restart of SQL Services

  19. COMMON CONFIGURATION SCENARIOSFailover clustering SQL Instance A SQL Instance A SQL Instance B SQL Instance B SQL Instance A SQL Instance A Node B Node A Node B Node A mscs mscs Storage Area Network Storage Area Network

  20. COMMON CONFIGURATION SCENARIOSGeographically dispersed failover clustering SQL Instance A SQL Instance A SQL Instance A SQL Instance A mscs Node B Node A Node D Node C Storage Replication Site A Site B

  21. COMMON CONFIGURATION SCENARIOSDatabase mirroring and log shipping mscs mscs DBM/LS Site A Site B

  22. COMMON CONFIGURATION SCENARIOSPeer-to-peer transactional replication mscs mscs Site A Site B Site C mscs

  23. COMPARING HIGH AVAILABILITY SOLUTIONSFailover

  24. COMPARING HIGH AVAILABILITY SOLUTIONSSecondary server

  25. COMPARING HIGH AVAILABILITY SOLUTIONSData considerations

  26. COMPARING HIGH AVAILABILITY SOLUTIONSNetwork considerations

  27. High Availability Solution Application/Web Servers Storage replication Data Center - B Data Center - A

  28. High Availability Solution Application/Web Servers Database mirroring/Log shipped Data Center - B Data Center - A

  29. High Availability Solution Application/Web Servers Site B Site C Site A Interoperability: Database mirroring and Log shipping combination

  30. High Availability Solution Application/Web Servers Peer-to-peer transactional replication Site A Site C Site B

  31. SQL Server 2012:: HA What’s new AlwaysOn :: Configuring availability at both database & instance level. • AlwaysOn Availability Groups (AG) • Log based data movement without shared disks • Zero data loss • Automatic & manual failover of a logical group of databases • Support upto 4 secondary replicas • Automatic page repair (continuing from SQL2008 R2) • AlwaysOn Failover Cluster Instances (FCI) • Multi-site clustering across subnets • Enables cross data-center failover of SQL instances • Faster failover for application availability

  32. What’s new:: Reduce Downtime Helps reduced planned downtime! (Comes with a cost) • Windows Server Core • Online operations (SQL Server) • Rolling upgrade & patches (AlwaysOn) • SQL Server on Hyper-V (benefit of Live Migration) • migrate virtual machines between hosts with zero downtime. • Easy deployment • Configuration Wizard, Windows PowerShell command-line interface, dashboards, dynamic management views (DMVs), policy-based management, and System Center integration help simplify deployment and management of availability groups.

  33. AlwaysOn: RPO & RTO Capabilities • An AlwaysOn Availability Group can have no more than a total of four secondary replicas, regardless of type. • This feature will be removed in a future version of Microsoft SQL Server. Use AlwaysOn Availability Groups instead. • Backup, Copy, Restore is appropriate for disaster recovery, but not for high availability. • Automatic failover of an availability group is not supported to or from a failover cluster instance. • The FCI itself doesn’t provide data protection; data loss is dependent upon the storage system implementation. • Highly dependent upon the workload, data volume, and failover procedures.

  34. AlwaysOn Layers of Protection Provides fault tolerance and disaster recovery across several logical and physical layers of infrastructure and application components • Infrastructure level • Windows Server Failover Clustering (WSFC): Server-level-fault-tolerance & intra-node network • SQL Server instance level • FCI: attached to symmetric shared storage • Database level • AG – Availability Groups: Primary replica & 4 Secondary replica • Each replica is hosted by an instance (FCI or non-FCI) on different node of WSFC • Client Connectivity • connect directly to a SQL Server instance network name, or • they may connect to a virtual network name (VNN) that is bound to an availability group listener • Logical redirection to appropriate SQL Server instance and database replica

  35. AlwaysOn: Storage Considerations Direct-attached vs. remote • HBA • SAN – iSCSI or Fibre channel • SMB (Server Messaging Block) Symmetric or Assymetric • Storage devices are considered symmetric • SSDs are good Dedicated vs Shared • Dedicated reserved for use and assigned to a single node in the cluster • Shared storage is accessible to multiple nodes in the cluster • WSFC supports cluster shared volumes – file sharing • SQL Server does not support to a shared volume

  36. Availability Improvements • Flexible Failover Policy • sp_server_diagnostics uses FailureConditionLevel • Failover Policy for Failover Cluster Instances (http://msdn.microsoft.com/en-us/library/ff878664(SQL.110).aspx) • Enhanced logging and instrumentation • Specific system configuration views, DMVs, performance counters, and an extended event health session • AlwaysOn Availability Groups Dynamic Management Views and Functions (http://msdn.microsoft.com/en-us/library/ff877943(SQL.110).aspx), & • sys.dm_os_cluster_nodes (http://msdn.microsoft.com/en-us/library/ms187341(SQL.110).aspx) • SMB file share support: • SQL Databases on File Shares - It's time to reconsider the scenario(http://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/10/18/sql-databases-on-file-shares-it-s-time-to-reconsider-the-scenario.aspx)

  37. Using storage replication technologies effectively • Test the solution prior to deployment • Test the database failover and ensure they can be brought online every single time • Test the entire solution to ensure that the required operation processes and documents are in-place • Understand the performance impact of the solution implemented • Synchronous replication can reduce RPO to zero but impacts performance based on the network latency • Asynchronous replication can reduce performance impact, but increases RPO • Benchmark the solution performance prior to the deployment • Implement vendor specified best practices

  38. Using storage replication technologies effectively • Data growth • Understand the impact of dynamically increasing the size of the LUNS if available • Follow SQL Server best practices • Keep each database data and log files on it’s own devices • Avoid replicating the TempDB • Simple user databases physical layout help simplify maintenance

  39. www.packtpub.com www.sqlserver-qa.net @sqlmaster

  40. End slide if you need oneAny Questions?

More Related