SQL Server High Availability Tim Beamer Technology Evangelist

SQL Server High Availability Tim Beamer, Technology Evangelist, Dell Services timothy_beamer@dell. com

Why Do You Need High Availability? Downtime causes lost customer revenue and decreased staff productivity Availability during Planned Downtime Patching & Service Pack Installation Hardware and Software Upgrade System Reconfiguration Database Maintenance Application Upgrade Protection against Unplanned Downtime Human Error #1 Cause of Failure Site Disasters Hardware Malfunction Data Corruption Software Crash Global Marketing

Conceptual Design, SQL 2005 3 Confidential Global Marketing

Conceptual Design, SQL 2008 4 Confidential Global Marketing

SQL Server 2008 High Availability Redundancy at server, database and data page levels reduces costs associated with downtime Highly credible, cost effective high availability solutions Reduced downtime for database maintenance and hardware upgrades Reduced contention for resources Increased availability of data in the event of a partial failure Industry leading manageability tools Global Marketing

Agenda • Increasing Availability • Decreasing Downtime • Improving Manageability Global Marketing

SQL Server 2008 High Availability Features Database Mirroring Transactional Replication Log Shipping Backup/Restore Windows Clustering Database Snapshot Global Marketing

Database Mirroring Overview • Hot Standby protects against database or server failure • Provides a fault-tolerant database • Cost effective as no specialist hardware is required • Straightforward setup and administration • Automatic or manual failover – SQL Server 2008 does not require a database restart after manual failover • Automatic, transparent client redirect • No shared components; two separate copies of data • SQL Server 2008 compresses the log stream from Principal to Mirror Global Marketing

Database Mirroring Synchronous, high availability configuration § Data is mirrored synchronously as part of a transaction § Automatic failover if Principal server fails § Automatic Client redirection Client Witness Principal Mirror Global Marketing

Database Mirroring Synchronous, high protection configuration § Data is mirrored synchronously as part of a transaction § Manual failover if Principal server fails § Automatic Client redirection Client Principal Mirror Global Marketing

Database Mirroring Asynchronous, high performance configuration § Data is mirrored asynchronously outside of transactions § Manual failover if Principal server fails § Automatic Client redirection Client Principal Mirror Global Marketing

Database Mirroring Recovering suspect pages • SQL Server 2008 uses a checksum to validate page writes • Inconsistant pages can be recovered automatically from the mirror server Principal Mirror Global Marketing

Transactional Replication • High performance – latency measured in seconds • Minimal load on the server • Cost effective as no specialist hardware is required • Straightforward setup and administration • Can be implemented at database or table level • Two types – Standard transactional replication › Easy to design, setup & manage › Subscriber (standby) can be used for reporting – Peer-to-peer transactional replication › Multi-master model; schema is identical on all sites › Supports distributed applications with data partitioning; enables load balancing › Does not handle conflicts; design to avoid/prevent conflicts Global Marketing

Transactional Replication Options Transactional Replication Reporting + Redundancy Peer-to-Peer Replication Query Scale Out + Redundancy London New York Boston Shanghai New Jersey Seattle Tokyo Global Marketing

Peer-To-Peer Transactional Replication How does it work? “West” “East” Logreader Agent Dist DB Distribution Agent Logreader Agent “South” Logreader Agent Dist DB Distribution Agent Global Marketing

Peer-To-Peer Transactional Replication SQL Server 2008 Enhancements • Configure replication easily with the new graphical topology viewer • Add new nodes without taking replication offline • Protect against accidental conflicts with SQL Server 2008 conflict detection Global Marketing

Log Shipping • Provides database redundancy • Cost effective as no specialist hardware is required • Straightforward setup and administration • Running read operations such as reports on secondary server is permitted – Users are disconnected when log restore occurs • Can maintain multiple secondary servers • Optional Monitor server – Records history and status of backup/restore jobs – May be setup to raise alerts when jobs fail Global Marketing

Backup • Permanent copy of data • Online restore – Perform a restore operation while an instance of SQL Server is running • Backup compression in SQL Server 2008 – Reduce volume by up to 50% – Allows reduced latency and faster restore – Applied per instance and per backup • Cost effective - no specialist hardware is required • Straightforward setup and administration Global Marketing

Backup Media Mirroring • Protection against lost or damaged backup media – All backup types can be mirrored (database, log, etc. ) – Each device must be the same type – All devices must be present during Backup, but only one backup set is required for Restore Global Marketing

Microsoft Clustering • Server hardware redundancy – Using a shared disk subsystem – Entire instance virtualized and fails over as a unit – Can include non-SQL Server resources – SQL Server 2008 no longer requires a drive letter for each instance • Clustering can be combined with Database Mirroring, Log Shipping, or Replication • Geographically Dispersed Failover Clustering provides protection even if the disk array fails Global Marketing

Microsoft Clustering SQL Server Editions Standard Edition 2 -node clusters only Enterprise Edition As many nodes as the operating system supports Global Marketing

Windows Server 2008 Clustering Support for 16 node clusters Nodes can reside on different subnets Support for OR dependencies • E. g. Network Name resource is available if either of two IP Address resources is available Cluster Validation tool • Verifies adequate hardware resources for clustering Global Marketing

Database Snapshot • Provides a read-only, consistent copy of database • Snapshot remains unchanged while database is modified – Protects against user error and data corruption • Very fast to create • Preserves disk space – • Client “Copy-on-Write” technology Can be created for any database • Cost effective as no special hardware is required • Straightforward setup and administration Global Marketing

Putting it All Together § Database Mirroring § Primary disaster site for databases § Replication Log Shipping § Additional disaster sites for databases § Logical Recovery § Replication § Database Mirroring Clustering § Database reporting and read scale out with redundancy Clustering Database Scale Out For Queries Production Database Log Shipping § Local server redundancy § Always On Storage Partner Solutions Log Shipping with Restore Delay § Site storage HA § Highest hardware reliability Backup Global Marketing Hot Standby Warm Standby Logical Recovery Standby

SQL Server High Availability Licensing License only active servers • Passive servers do not require a license • If a failover occurs a license is not required for 30 days Most cost effective high availability model amongst leading database vendors Global Marketing

Agenda • Increasing Availability • Decreasing Downtime • Improving Manageability Global Marketing

Enhanced Restore Operations Online Restore • Access restored data even when the rest of the database is not yet available Piecemeal Restore • Recover the database in stages – bring critical filegroups online first, then restore remaining filegroups Page-Level Restore • Restore individual pages from a backup to resolve page corruption issues quickly Global Marketing

Fast Recovery During Restart or Restore • SQL Server 2000 – Database is available after Undo completes Redo Undo Time • DB is Available SQL Server 2008 – Database is available when Undo begins Redo Undo DB is Available Global Marketing

Online Index Operations • Create, Rebuild, and Drop indexes online CREATE CLUSTERED INDEX idx_Prd ON Products WITH (ONLINE = ON) Scan Sort/Merge Bulk Insert Target (new index) Source (Existing Products table) INSERT UPDATE DELETE SELECT Idx_Prd Global Marketing

Partial Database Availability • Database remains partially available when secondary files are damaged Primary Filegroup Additional Data Filegroups Global Marketing

Locking and Concurrency Row versioning support • READ_COMMITTED_SNAPSHOT isolation level • ALLOW_SNAPSHOT_ISOLATION database option Benefits of row versioning • Read operations retrieve a consistent snapshot of the database. • SELECT statements do not lock data during a read operation (readers do not block writers, and vice versa) • SELECT statements can access the last committed value of the row, while other transactions are updating the row without getting blocked • The number of deadlocks is reduced • The number of locks required by a transaction is reduced, which reduces the system overhead required to manage locks • Fewer lock escalations take place Enhanced locking in SQL Server 2008 • Optimized lock escalation for partitioned tables and indexes Global Marketing

Dynamic Configuration Add hardware resources without taking the database server offline Hot-Add Memory Hot-Add CPU Global Marketing

Agenda • Increasing Availability • Decreasing Downtime • Improving Manageability Global Marketing

Table and Index Partitioning • Partition tables and indexes across multiple physical files – Reduce the impact of IO intensive operations – Manage backup and archival based on partitions – Control lock escalation at the partition level to increase concurrency Global Marketing

Dedicated Administrator Connection Special diagnostic connection for administrators • Available when other connections are not possible • Use to troubleshoot or shut down cleanly • Only available from clients running on the server by default Global Marketing

Resource Governor SQL Server Backup OLTP Activity Admin Tasks Ad-hoc Reports High Admin Workload OLTP Workload Min Memory 10% Max Memory 20% Max CPU 20% Admin Pool Executive Reports Report Workload Max CPU 90% • Ability to differentiate workloads –e. g. app_name, login, etc. • Per-request limits –Max memory % –Max CPU time –Grant timeout –Max Requests • Resource monitoring Application Pool Global Marketing

Tim’s Axioms of DR • A failure to plan is the plan for failure • If you’re building the plan while recovering, make sure your resume is updated • The failure to plan on someone else’s part will almost always constitute an emergency on your part • Plan for the worst, hope for the best, and know that reality is always somewhere in the middle • Commitment = $ • Every plan has a weak link, identify early and strengthen • Regular review is critical • Always have a “Plan B” ready to go 37 Confidential Global Marketing

Planning Considerations • Plan development will require input from every business unit • RPO/RTO needs to be well-defined and well-aligned – If the RTO for SQL is 2 hours and an application using SQL has an RTO of 10 minutes, your alignment is off – “A week” is NOT a well-defined RTO • RPO/RTO define the solution, NOT the other way around • Develop your Application Matrix (communicate it too) • Document dependencies (Dynamics CRM requires SQL, Exchange, Scribe, and LOB App X) • Each additional place to the right of the decimal for uptime is an exponential increase in the cost of the solution (99. 9% vs. 99%) • Designate alternates for each role/responsibility 38 Confidential Global Marketing

Can Virtualization help? • Of course! • HW virtualization (Live Migration, V-Motion) • Storage Virtualization (Quick Storage Migration , Storage VMotion) • Storage Solutions with SQL “intelligence” (Like Equallogic and Compellant) to reduce admin involvement • COMBINE your D/R and Prod. Environments • Don’t forget about the End-User environment – Desktop – Applications • Have you thought about “cloud”? 39 Confidential Global Marketing

Q&A