SQL Server 2005 & High Availability

1. SQL Server 2005& High Availability By George SquillaceNew Horizons of Michigan MCT, MCSE, MCDBA CompTIA A+, Network+ MCITP – Database Administration MCITP – Enterprise Messaging v2.1

2. What Does a Database ServerContain? • System Databases • Created Automatically • Contain the Configuration of the Database Server Installation • User Databases • Databases that Serve the Purpose of the Database Server Installation

3. Description of the Files that Support a Microsoft SQL Server 2005 Database • Primary Data File • .mdf extension • .ndf extension if I have more than 1 Data File • Log File • .ldf extension

4. What is a Transaction? • A Transaction is a Change Made to Data or to the Database System • Insert • Update • Delete • Creation/Deletion/Alteration of a Defined Object in the Database • SQL Server Ensures that Transactions Complete Successfully or are not Applied Whatsoever

5. Description of the Transaction Logging Process • Data Changes Are Made in SQL Server Memory FIRST! • Periodically Changed Data is Written SEQUENTIALLY to the Log File (.ldf) • Changes in the Log File are Rolled Forward into the Database File or Files (.mdf, possibly .ndf files)

6. What Does a DatabaseContain? • Tables • Rows & Columns • Other Objects that are defined: • Views • Stored Procedures • Triggers • …other stuff

7. How Do I Define a Database? • Demo creation of a Database • Demo creation of a Table • Insert a Row of Data • Query the Table

8. How Does Someone Interact with a SQL Server, Retrieving and Changing Data? • Description of the Client/Server Interaction • Client Submits Request to Database Server • Database Server Responds to Request • Very Different from Interacting with a File-Based Database like Microsoft Access • Microsoft Office Application, such as Access, Excel or Word • Custom WindowsApplication, i.e., an app created with Visual Studio using a programming language like VB .NET • Custom Web Applicationthrough a Web Browser (ASP Pages) See examples of Windows & Web Applications on Subsequent Slides

9. Example of a Database-Oriented Custom Windows Application

10. Example of a Database-OrientedWeb Application

11. How Many Databases Might a SQL Server Contain? • Insert the Standard Answer Posed to a Consultant… • …”It depends!” • One of Our Recent Customers Had Six Clustered SQL Servers Each Hosting Around 200 - 260 Databases!

12. How Many Tables Might a SQL Server Database Contain? • Insert the Standard Answer Posed to a Consultant… • …”It depends!” • Some of Our Recent Customers Had Databases of 3,000 And 6,000 Tables!

13. What is Availability , What is High Availability& Why Do I Care? • Availability = Whether Or Not Clients/Users Can Connect to a Resource or Service That They Require. A Resource is Available, or Not. • High Availability = Building Redundancy into a System or Service So As To MAINTAIN AVAILABILITY in the Event of a Certain Failure or Failures

14. How is Availability Measured? • One way Availability is Measured is According to “the Nines” • 99% Available • 99.9% Available • Less than nine hours downtime per year • 99.99% Available • Less than 53 minutes downtime per year • 99.999% Available (a.k.a. “Five 9s”) • Less than six minutes downtime per year, barely attainable See the Wikipedia High Availability webpage referenced elsewhere in this presentation.

15. The Trend in the role SQL Server Database Servers play is increasingly MISSION CRITICAL • Sales • Manufacturing • Health Care • Banking & Finance

16. How are Availability & High Availability Achieved in SQL Server? • Backup & Restore • Database Copy • Failover Clustering • Replication • Log Shipping • Database Mirroring

17. Backup & Restore • Involves Copying a Database(s) to Tape or Another Disk • Strengths • Portability of Backup • Can Restore a Backup onto Another Server • Weaknesses • Lengthy…Much Potential Downtime • Restore Isn’t Automated • Not a HIGH Availability Solution

18. Database Copy • Involves Copying a Database(s) to Another Server • Strengths • Can Copy to a Database in Another Location • Weaknesses • Configured on a Per Database Basis • Doesn’t Protect Against a Complete Site Failure • Not a HIGH availability Solution

19. Diagram Resources & Web References • www.e-Squillace.com • /tech/techdiagrams/SQL_High_Availability_Technologies.htm • /tech/techreference/sql • http://en.wikipedia.org/wiki/High_availability • Description of the “Five 9s”

20. Failover Clustering • Configuration Basics… • Strengths • Provides Protection Against a Node Failure, Protects the Entire SQL Instance • Automatic Failover Supported • Weaknesses • Generally Expensive, Requires Specialty Hardware • Specialty Hardware Requirements • Not Trivial to Configure and Manage • Doesn’t Protect Against a Complete Site Failure

21. Log Shipping • Configuration Basics… • Strengths • Can Ship Logs Across WAN (Wide-Area Network) • Protects an Entire Database • Weaknesses • Configured Per Database • NO AUTOMATIC FAILOVER

22. Database Mirroring • Configuration Basics… • Strengths • Can Mirror Across WAN • Automatic Failover, and Nearly Instantaneous, Better than Failover Clustering • Protects an Entire Database • Weaknesses • Requires Enterprise Edition • Must be Configured Per Database

23. Replication • Configuration Basics.. • Push • Pull • Strengths • Perpetual or on-demand replication of data, local or remote • Protects (duplicates or merges) the exact portion of the database I want • Weaknesses • Configured per database, even per table • Generally does not protect or duplicate an entire Database

24. Does it Appear Trivial to Design and Architect a High Availability Solution? • Answer = No! • Now what???

25. Microsoft Course 2788Designing High Availability Database Solutions Using SQL Server 2005 (3 days) • Note: this is not really a hands-on course, it is a design course(Geo, read p. xv in the 2788 courseware Introduction module) • Pay close attention to the pre-requisites • This course uses Virtual Server as opposed to Virtual PC

26. Thank You!