Transcript SQL Server & High Availability - e

SQL Server 2005
& High Availability
By George Squillace
New Horizons of Michigan
MCT, MCSE, MCDBA
CompTIA A+, Network+
MCITP – Database Administration
MCITP – Enterprise Messaging
v2.1
What Does a Database Server Contain?
• System Databases
• Created Automatically
• Contain the Configuration of
the Database Server
Installation
• User Databases
• Databases that Serve the
Purpose of the Database Server
Installation
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
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
Description of the Transaction
Logging Process
1. Data Changes Are Made in SQL Server
Memory FIRST!
2. Periodically Changed Data is Written
SEQUENTIALLY to the Log File (.ldf)
3. Changes in the Log File are Rolled
Forward into the Database File or Files
(.mdf, possibly .ndf files)
What Does a Database Contain?
• Tables
• Rows & Columns
• Other Objects that
are defined:
•
•
•
•
Views
Stored Procedures
Triggers
…other stuff
How Do I Define a Database?
• Demo creation of a
Database
• Demo creation of a
Table
• Insert a Row of Data
• Query the Table
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 Windows Application, i.e., an app created with
Visual Studio using a programming language like VB
.NET
• Custom Web Application through a Web Browser (ASP
Pages)
See examples of Windows & Web Applications on Subsequent Slides
Example of a Database-Oriented
Custom Windows Application
Example of a Database-Oriented
Web Application
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!
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!
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
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.
The Trend in the role
SQL Server Database Servers play
is increasingly MISSION CRITICAL
•
•
•
•
Sales
Manufacturing
Health Care
Banking & Finance
How are Availability &
High Availability Achieved in
SQL Server?
•
•
•
•
•
•
Backup & Restore
Database Copy
Failover Clustering
Replication
Log Shipping
Database Mirroring
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
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
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”
External Storage Cabinet or SAN
Failover Clustering
W: (Database Files)
X: (Log Files)
Q:
(Quorum
Resource)
10.99.99.0/24
PRIVATE LAN
Special Host Bus Adapter
• Configuration Basics…
• Strengths
Special Host Bus Adapter
IP2
\\Srv1
\\Srv2
(Local OS Name)
(Local OS Name)
Local
Operating
System
(RAID
Protected)
(Optional,
boot from
SAN)

IP1
IP3
IP4
Host Bus Adapter
Host Bus Adapter
192.168.1.0/24
Public LAN
Clients connect to:
\\SQLClust1
IP5
(Portable Failover Cluster Name,
which “sits” on only one node at a time)
• 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
\\SQLSrv1
Schedule 1,
Log Shipping
Agent Job Backs
up log file on
primary database
to local or remote
shared folder
\\SQLSrv2
(Same or
different
subnet,
Same or
different
location)
Shared
Folder
Shared
Folder
(Stored
locally, or
on a
remote
share)
(Stored
locally, or
on a
remote
share)
Client connects originally to:
• Configuration Basics…
• Strengths
\\SQLSrv1
Schedule 3,
Agent Job
restores log files
to database
Schedule 2,
Agent Job copies
from Shared
folder related to
the Primary to
destination (local
or remote) folder
\\SQLSrv3
(optional
Monitor Server)
(Same or
different subnet,
Same or different
location)
Client machine
• Can Ship Logs Across WAN (Wide-Area Network)
• Protects an Entire Database
• Weaknesses
• Configured Per Database
• NO AUTOMATIC FAILOVER
Made by George Squillace
Database
Mirroring
\\SQLSrv1
\\SQLSrv2
(Hosts the Principal Database)
(Hosts the Mirrored Database)
(Endpoint: TCP Port 5022)
(Endpoint: TCP Port 5023)
the same subnet as
(Witness Server role)
Principal or anywhere
(Endpoint: TCP Port 5024)
TCP/IP “reachable”
Client connects originally to:
• Configuration Basics…
• Strengths
May be located on
\\SQLSrv3
\\SQLSrv1
Made by George Squillace
Client machine
• 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
Client machine
\\SQLSrv1
Replication
(Publisher/
\\SQLSrv2
Distributor of
(Subscriber
Snapshot
To Publicaton1)
Publicaton1 and
Clinic1DB
Publication1
Merge
Article1
Table1
Table2
View1
TVF1
Article2
Table6
Publicaton2)
HealthCareDB
Publication1
Article1
Table1
Table2
View1
TVF1
Article2
Table6
\\SQLSrv3
OLAP_DB
(Subscriber
Publication3
Article1
Table9
Table10
Table11
To Publicaton2 &
Publication3)
Publication2
WAN
Article1
Table8
Clinic2DB
• Configuration Basics..
• Push
• Pull
Publication2
Article1
Table8
Client machine
Client machine
Publication3
Article1
Table9
Table10
Table11
Client machine
• 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
Does it Appear Trivial
to Design and Architect
a High Availability Solution?
• Answer = No!
• Now what???
Microsoft Course 2788
Designing 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
Thank You!