420 likes | 596 Views
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
E N D
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
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
www.packtpub.com www.sqlserver-qa.net @sqlmaster
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
IT Lifecycle Database Service Management Operate Support Deliver IT Operations
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
Database Server Availability Total Availability = product of the availability of each component
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
Disaster recovery Business Continuity Plan Business Function Database Service Management IT Operations Recovery Time Objective Data loss Disaster recovery plan Recovery Point Objective
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.
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
HIGH AVAILABILITY FEATURESGeographically dispersed failover clustering Storage Replication (Synchronous) London Zurich
HIGH AVAILABILITY FEATURESDatabase mirroring Mirror Site B Principal Site A Witness (Optional) Site C
HIGH AVAILABILITY FEATURESLog shipping Secondary Site B Primary Site A Transaction Log backup
HIGH AVAILABILITY FEATURESTransactional Replication Secondary Site B Transaction Log records Primary Site A
HIGH AVAILABILITY FEATURESPeer-to-peer transactional replication Node B Site B Node A Site A Node C Site C
WINDOWS HIGH AVAILABILITY FEATURESOther features Edition specific • Online Indexing • Hot Add CPU • Hot Add Memory • Adjust memory online without restart of SQL Services
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
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
COMMON CONFIGURATION SCENARIOSDatabase mirroring and log shipping mscs mscs DBM/LS Site A Site B
COMMON CONFIGURATION SCENARIOSPeer-to-peer transactional replication mscs mscs Site A Site B Site C mscs
High Availability Solution Application/Web Servers Storage replication Data Center - B Data Center - A
High Availability Solution Application/Web Servers Database mirroring/Log shipped Data Center - B Data Center - A
High Availability Solution Application/Web Servers Site B Site C Site A Interoperability: Database mirroring and Log shipping combination
High Availability Solution Application/Web Servers Peer-to-peer transactional replication Site A Site C Site B
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
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.
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.
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
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
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)
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
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
www.packtpub.com www.sqlserver-qa.net @sqlmaster