Designing 24 x 7 SQL Server Deployments Vineet

Designing 24 x 7 SQL Server Deployments Vineet Gupta Evangelist – Database and Integration Microsoft Corporation http: //spaces. msn. com/members/vineetgupta/

Barriers To Availability Database Server Failure or Disaster User or Application Error Data Access Concurrency Limitations Database Maintenance and Operations Upgrades Availability at Scale Tuning

Barriers To Availability As addressed in SQL Server 2005 Database Server Failure or Disaster Failover Clustering, Database Mirroring, Log Shipping User or Application Error Data Access Concurrency Limitations Database Maintenance and Operations Upgrades Availability at Scale Tuning

Database Server Failure Mitigation Attribute Failure detection Automatic failover Perceived downtime Potential data loss Masking of storage failure Special hardware Clustering Log shipping Database mirroring Yes No No Yes N/A 3 seconds Yes – latest transaction log Yes – in some configurations 30 seconds + recovery Yes – one copy of data No – shared disk Yes Certified storage No and servers No Distance 100 miles Unlimited Scope System and user User databases Unlimited User databases

Cold Standby Solutions Backup / Restore and Detach / Copy / Attach Both Provide Manual detection and failover Potential for some work loss Whole-database scope Standard servers Limited reporting on standby Duplicate copy of database Client must know where to re-connect Slowest failover – Most downtime Backup / Restore Smaller size – only used pages are copied Log backups allow restore to point in time Longer restore time Detach / Copy / Attach Copies entire files No possibility of rolling forward subsequent logs Detach / Copy / Attach

Warm Standby Solutions Replication and Log Shipping Both Provide Multiple copies and Manual failover Replication – since SQL Server 6. 0 Primarily used where availability is required in conjunction with scale out of read activity Failover possible; a custom solution Not limited to entire database; Can define subset of source database or tables Copy of database is continuously accessible for read activity Latency between source and copy can be as low as seconds Log Shipping Basic idea: Backup, Copy, Restore Log will always be supported But no more investment in the scripts Database scope Database accessible but read-only Users must exit for next log to be applied Data backups no longer block log backups Log Shipping

Hot Standby Failover Solutions Failover Clustering and Database Mirroring Both Provide Failover Cluster Automatic detection Automatic, fast failover Manual failover Transparent client redirect Zero work loss Database Mirroring

Failover Clustering Hot Standby Failover Cluster * Inst 1 Zero work loss, zero impact on throughput Instance Failover – entire instance works as a unit Single copy of instance databases Available since SQL Server 7. 0 Standby is not available for reporting, queries, etc. May support other instances

Failover Clustering Failover Cluster SQL Server 2005 More nodes Match operating system limits Two-node Failover Clustering is available in SQL Server 2005 Standard Edition Unattended setup Support for mounted volumes (Mount Points) Full support for Majority Node Set quorum All SQL Server data services participate Database Engine, SQL Server Agent, Full-Text Search Analysis Services – Now has multiple instances

Database Mirroring New for SQL Server 2005 Hot Standby Provides a fault-tolerant database Building block for complex topologies Database Failover Very fast failover Less than five seconds in most cases Zero data loss Automatic or manual failover Automatic re-sync after failover Automatic, transparent client redirect

Architectural Blueprint Database Mirroring Clients Witness Log Records Principal Mirror

Witness and Quorum Witness Sole purpose of the Witness is to provide automatic failover To survive the loss of one server you must have at least three Prevents “split brain” Does a lost connection mean the partner is down or is the network down? To become the Principal, a server must talk to at least one other server The Witness does not direct the Mirror to become Principal It simply answers the question “Who do you see? ”

Witness is an instance of SQL Server 2005 Perhaps even SQL Server Express Can be witness for multiple sessions Consumes very little resources Not a single point of failure Partners can form quorum on their own

Database Mirroring How it works Application Mirror is always redoing – it remains current Witness Commit Principal Mirror 1 5 2 SQL Server 2 Log >2 Data SQL Server 4 3 Log >3 Data

Safety / Performance There is a trade-off between performance and safety Database Mirroring has two safety levels FULL – commit when logged on Mirror Allows automatic failover No data loss OFF – commit when logged on Principal System does its best to keep up Prevents failover; to make mirror available Must ‘force’ service Or terminate Database Mirroring session

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)

Transparent Client Redirect No changes to application code Client automatically redirected if session is dropped Client library is aware of Principal and Mirror servers Upon initial connect to Principal, library caches Mirror name When client attempts to reconnect If Principal is available, connects If not, client library automatically redirects connection to Mirror

Database Mirroring Application Impact Configuration All setup is within SQL Server; no OS-level setup DDL using ALTER DATABASE or Management Studio Application Server-side Some monitoring of Database Mirroring session Application Client-side Transparent to client; reconnect on dropped connection May specify principal and mirror servers in connection string

Database Mirroring Advantages Hardware Works with standard computers, storage, and networks No shared storage components, virtually no distance limitations Impact to transaction throughput Zero to minimal, depending on environment / workload

Summary SQL Server 2005 HA Technology Database Server Failure or Disaster Failover Clustering Database Mirroring Peer-to-Peer Replication User or Application Error Log Shipping Database Snapshot Data Access Concurrency Limitations Snapshot Isolation Online Index Operations Replication Upgrade Software and Hardware Database Maintenance and Operations Fast Recovery Partial Availability Online Restore Media Reliability Dedicated Administration Connection Dynamic Configuration Availability at Scale Data Partitioning Replication Tuning Database Tuning Advisor

Questions?

Free Online Training http: //www. microsoft. com/technet/prodtechnol/sql/2005/learning /default. mspx 2936: Installing and Securing Microsoft SQL Server 2005 2937: Administering and Monitoring Microsoft SQL Server 2005 2938: Data Availability Features in Microsoft SQL Server 2005 2939: Programming Microsoft SQL Server 2005 2940: Building Services and Notifications Using Microsoft SQL Server 2005 2941: Creating the Data Access Tier Using Microsoft SQL Server 2005 2942: New Features of Microsoft SQL Server 2005 Analysis Services 2943: Updating Your Data ETL Skills to Microsoft SQL Server 2005 Integration Services 2944: Updating Your Reporting Skills to Microsoft SQL Server 2005 Reporting Services Worth $ 99 Each Free till Nov 1, 2006 90 Day Subscription from time of activation!

More Info SQL Server Community sites http: //www. microsoft. com/sql/community/default. mspx List of newsgroups http: //www. microsoft. com/sql/community/newsgroups/default. mspx Locate Local User Groups http: //www. microsoft. com/communities/usergroups/default. mspx Attend a free chat or web cast http: //www. microsoft. com/communities/chats/default. mspx http: //www. microsoft. com/usa/webcasts/default. asp

Your Feedback is Important! Please Fill Out the feedback form

© 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.