Transcript Slide 1

High Availability & Scalability
in SQL Server 2005
Rick Brewis, Microsoft
Technology Specialist
[email protected]
Brian Madden, NuSoft Solutions
Senior Consultant
[email protected]
High Availability and
Scalability: Agenda
High Availability
Also known as business continuance
Refers to technologies to ensure
continued availability – a matter of kind
Primary new technology: database
mirroring
Scalability
Keeping a database available as you scale
up or out
Primary new technology: partitioning
High Availability Overview
People
Process
HA
Technology
High Availability – Overview
Blend of people, process, and
technology
Process enforces “best practices”
Technology enables people and
processes to come together in
providing maximum uptime
SQL Server 2005 HA
Technologies
Cold Standby
No failover with potential data loss
Backup / restore
Detach / copy / attach
Warm Standby
Manual failover with potential data loss
Log Shipping
Transactional Replication
Database Mirroring - high performance mode
Hot Standby
Automatic failover and zero data loss
Failover Clustering
Database Mirroring - high availability mode
Cold Standby Solutions
Backup / Restore
Backup / Restore
Data files may be smaller – only used
pages are backed up
Log backups allow restore to point in time
Generally a longer restore time
Detach / Copy / Attach
Copies entire files
No possibility of rolling forward
subsequent logs
For a cold standby:
Manually detect failure and fail over
Some data loss
Per database only, not per server
Works on standard servers
Client must know how to re-connect
Slowest failover with most downtime
Detach / Copy / Attach
Warm Standby 1: Log
Shipping
Log Shipping
Provide Multiple copies and
Manual failover
Log Shipping
Basic idea: Automated Backup,
Copy, Restore Log
Database scope
Secondary database is accessible
but read-only
Users must exit for next log to be
applied
Database backups no longer block
log backups
Log Shipping
Monitoring Server
Secondary Server(s)
Primary Server
1. Transaction
log backed up
Log
Backup
2. Transaction
log copied
Transfer Logins
3. Transaction log
restored
Log
Backup
Log Shipping Notes
Automatically copies and restores
transaction log files from the primary to
one or more secondary databases
Transfers all database objects, all changes
Managing large log files can be a problem
SQL Server 2005 log shipping
Revised from SQL Server 2000
Available on EE, Std, and WG
Warm Standby 2: Peer-to-peer
Transactional Replication
Primarily designed for
availability
Often used for scale out of read
activity
Only data is copied, not system
tables
Failover must be
customized, but can be
automatic
Only copies data
Can work with a subset of a
database
Replicated data is accessible for
read activity (i.e. reporting)
Very low latency: seconds
Replication
Peer-to-peer Transactional
Replication
“West”
“East”
Logreader
Agent
Logreader
Agent
Dist
DB
Dist
DB
Distribution
Agent
“South”
Logreader
Agent
Dist
DB
Distribution
Agent
Distribution
Agent
Peer-to-Peer Transactional
Replication Notes
New in SQL Server 2005
All nodes are peers of each other
Table schema must be identical at all sites
Each node publishes updates made on its data
Each node subscribes to other nodes and receives their
changes
A given set of data (row) can be updated at only
one site at a time
Data range ownership is application-defined
No conflict detection
SQL Server prevents an update from cascading
Enables load-balancing and high availability
Can be used for a warm or hot standby
Small possibility of data loss on failure,
depending on latency
Hot Standby 1: Clustering
Automatic failover
At the SQL Server instance level,
not database level
Preserves server and database
dependencies
No committed work loss
Based on Microsoft Cluster
Server (MSCS)
Multiple nodes provide
availability
Maximum of 2, 4, or 8 nodes
depending on OS edition
Automatic detection and failover,
transparent to client
Requires Cluster-certified
hardware:
See the Windows Catalog:
Clustered
Failover Cluster
Clustering
Client PCs
Node A
Node B
SQL Server
SQL Server
Heartbeat
Shared Disk Array
Clustering Notes (SQL
Server 2005)
More nodes are now supported
Matches the Windows OS limits
Two-node Failover Clustering is available in Standard
Unattended setup now available
Support for mounted volumes (Mount Points)
Full support for Windows Majority Node Set quorum
All SQL Server data services participate
Database Engine, SQL Server Agent,
Full-Text Search
Analysis Services – Now has multiple instances
Managing and troubleshooting a cluster can be
complex
Hot Standby 2: Database
Mirroring
Databases are mirrored, not
instances
Does not account for database and
server dependencies
Automatic Failover
Very fast failover
Less than five seconds in many
cases
No loss of committed work
Manual failover also possible
New mirror can automatically re-sync
after failover
Potential for automatic,
transparent client redirect
Client must use ADO.NET or SQL
Native Client
Database Mirroring
Database Mirroring: HA
mode
Witness
Principal
Mirror
Application
1
5
2
SQL Server
SQL Server
2
4
3
Database Mirroring Modes
High-Availability Mode
Safety set to FULL: synchronous operation
Database is available whenever a quorum exists
Witness is required for automatic failover
High-Protection Mode
Safety FULL: synchronous operation
No witness – quorum provided by partners
If the principal loses quorum, it stops servicing the
database
Ensures high protection; database is never in ‘exposed’
state
Manual failover only, no automatic failover
Designed as a transition mode
High-Performance Mode
Safety set to OFF: asynchronous operation
Manual failover only: forced service with potential data loss
Database Mirroring Notes 1
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
Database Mirroring Notes 2
Role of the Witness is to verify principal
and mirror for producing a quorum
To automatically survive the loss of one
server you must have at least three
Prevents a “split brain”
Two out of three vote required for failover
To become a new principal, the mirror
must talk to at least one other server
The witness settles the issue of whether
the principal is down
Witness
The witness must be an instance of
SQL Server 2005
Can be a SQL Server Express instance
A server can be a witness for multiple
mirroring sessions
Witness consumes very little resources
The witness is not a single point of
failure
The mirroring partners can form a quorum
on their own
Safety versus Performance
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
Transparent Client Redirect
Client automatically redirected if the
principal is down
Upon initial connection to a principal,
the client library caches the mirror
name from the principal
If the client attempts to reconnect
because the connection is lost
If the old principal is not available, the
client library automatically redirects
connection to mirror (new principal)
Comparing SQL Server
2005 HA Technologies
HA Technology
Enterprise
Standard Workgroup Express
Log Shipping
Yes
Yes
Yes
N/A
Peer-to-Peer
Replication
Yes
?
?
N/A
Failover Clustering Yes
2-node
only
N/A
N/A
Database Mirroring Yes
Safety
Full
Witness
only
Witness
only
Scalability/Availability
Enhancements
Availability Enhancements
Database Snapshots
Table Partitioning
Availability Enhancements
Online Operations
Checksum verification
Fast recovery
Online restore
Backup to mirrored media
Instant file initialization
Full-text backup and restore
Online Index Operations
Snapshot Isolation
Readers don’t block Writers, Writers don’t block
readers
Database Snapshots
Table partitioning
Database Snapshots
User Error
Users, applications, and DBAs do
make errors
Database Snapshots solve the problem
by allowing the database to go back in
time
Data lost as database goes back in time
Must be created before the error
Snapshots
General
Snapshot of a database at a point in
time
Created instantly
Read only
Base database continues to change
Snapshot does not restrict the base
database
Snapshot requires a different database
name from base database
Revert to previously created Snapshot
to recover from errors
Snapshot
Technology
Extremely space efficient
Does not require a complete copy of
the data
Shares unchanged pages of the database
Only requires extra storage for changed
pages
Uses a “copy on write” mechanism
Snapshot does affect performance on
the base database
Snapshot (Copy on Write)
Command
Northwind
Northwind_SS
Create Northwind_SS
Update Northwind
Read Northwind_SS
Value
Result:
DD
Space Used 12.5%
0%
R
D
X
B
H
J
L
Y
M
Value
D
Snapshots
Database Mirroring
Multiple Snapshots are allowed on the
Mirror
Each Snapshot is taken at a different time
Each Snapshot has a different name
Snapshot on Mirror may affect
performance on Principal
Snapshots can exist forever
Constrained by resources
Reporting on a Mirror
Use database Snapshots on Mirror
Database Mirroring
OLTP Clients
Witness
Principal
Mirror
Snapshot2
@ 2PM
Snapshot
@ Noon
Reporting
Client
Reporting on the Mirror
Limitations
Schema of Snapshot cannot change
Snapshot is static
New data is not available
Snapshot may affect performance
Consider Replication for a dedicated,
scale out reporting solution
Table Partitioning
Table Partitioning
Scale-up:
Allow easy management of very large tables and
indexes (data scale-up)
For example Fast Insert or Delete of large
quantities of data
SQL Server Enterprise Edition feature
Take advantage of large machines
Better scaling of some operators
Take advantage of collocation
Joins of large tables
Improve concurrency
Lock escalation only within a partition
How Partitioning Works
Data in a Table is separated into
different pieces instead of a single heap
or B-Tree
Data still logically belongs to a single
table
Non-Partitioned
Partitioned
A, B, C, D, E, F, G, H, I, J, K, L
A, B, C
D, E, F
G, H, I
J, K, L
What Can Be Partitioned?
Objects that may be partitioned
Base Tables
Indexes (Secondary or Clustered)
Indexed Views
Row is the smallest unit of partitioning
(only “horizontal” partitioning)
Partitioned Table
Table
ID
1
2
3
4
5
6
7
c1
30
50
20
10
50
50
20
c2 c3 c4
A
B
B
L
Y
A
F
Part. Function
Part. Scheme
30
Filegroup3
50
Filegroup5
20
10
Filegroup2
Filegroup1
50
50
Filegroup5
Filegroup5
20
Filegroup2
Partition Function Object
CREATE PARTITION FUNCTION
PFR (smallint)
AS RANGE LEFT
FOR VALUES (10,20,30,40,50,60)
RANGE partition function specifies the boundaries
of the ranges
LEFT or RIGHT keyword specifies to which of the intervals
the boundary value belongs
There are no holes in the partitioned domain even if
not all values are attainable
Discourages use of the PF as a constraint
Partition Scheme Object
Assigns partitions to physical storage
(Filegroups)
Partitions can be mapped to the same or
different Filegroups
In SQL Server 2000, Tables and Indexes
could be mapped to Filegroups – now they
can also be mapped to partition schemes
A Partition Function can participate in
multiple Partition Schemes
Partitioning Key Restrictions
Must be based on columns in the table
Restrictions similar to index key
limitations
Only “Native Types” – no User-Defined
Types
Column values must be deterministic
Deterministic imprecise columns must be
persisted
New Varchar(Max) is supported as
partitioning key
Other Partitioning
Restrictions
Maximum of 1000 Partitions per table in
SQL Server 2005
Partitions must all be on a single
database
Distributed Partitioned Views can be used
in conjunction with table partitioning
Other Query Enhancements
Many other operations can be
performed “per-partition”
Grouping, Filtering, Projection
Inserts, Updates, Deletes
Create Index, Bulk Insert
SQL Server tries to find large groups of
operations that can be performed perpartition to improve query performance
Adding/Dropping Partitions
ALTER PARTITION FUNCTION pfr()
{SPLIT|MERGE} [RANGE (boundary_value)]
Ranges are added by specifying a new boundary
point – this SPLITs the existing partition
Note that ALL tables and indexes using the
partition function are affected by this
operation
Example: Add a new month to a partitioned
sales table (partitioned by month)
Partition Operations
SPLIT - new partition is automatically
populated
For RANGE - by moving some rows from the
partition that is split
MERGE – data is automatically moved
RANGE – to the neighboring “remaining”
partition
SWITCH
SWITCH Partition
Method to move a whole partition into
or out of a table efficiently
Enables important manageability
scenarios
All indexes on table must be “aligned”
(plus other restrictions)
SWITCHing a Partition in
Table1
Q1
Q2
Table2
Q3
Q4 =
Empty
Q4
ALTER TABLE Table2 SWITCH PARTITION TO Table1 PARTITION 4
SWITCH Benefits
Performance - allow building new and
removing old partition fast
Availability - allow adding new and
removing old partition with minimal
downtime
Per-partition manageability – support
as many utilities as possible to work on
a per-partition basis
Large Table Challenge
Load and Delete from a large table
Table has indexes => load is slow; about
10+ times slower than loading into a heap
and creating indexes afterwards
Delete is slow – deleting rows is orders of
magnitude slower than truncating a multiGB table
Cost of running a utility usually grows
linearly with the table size
Large Table Solution
Q: How to populate empty partition?
A: Build it outside the partitioned table
(BCP into a heap, create indexes), and then
make it a new partition (SWITCH in)!
Q: How to delete all data in one partition?
A: SWITCH partition out of the partitioned
table into another table then back it up,
drop it or truncate it
SWITCH Solution
Allows you to add and remove whole
partition
Advantage
Performance
Disadvantages
New command
Restrictions – tables must satisfy certain
conditions to allow SWITCH
SWITCH Solution
Allows a separate table to become a
partition of existing partitioned table
(move partition “IN”)
Allows a partition of a table to become
a new separate table (move partition
“OUT”)
In most general solution it allows to
“SWITCH” a partition between two
partitioned tables
SWITCH Requirements
Both the source and target tables must
exist
Target table or partition must be empty
Metadata only operation – no data
scans, copies. Therefore
+ SWITCH is perfectly scalable and fast (1
second or so)
- SWITCH has some restrictions
SWITCH Restrictions
Restrictions are derived from the
principles of no data movement, scans
Columns must have same names
All target indexes must exist in the
source
All indexes must be aligned
Corresponding target index is in the
same filegroup as the source
Sliding Window Scenario
Large Database
Each hour/day/week/month… add new
partition and remove the oldest one
New partition – may need load, scrub,
transformation before becoming a part
of the whole table; or it starts as empty
and is populated by transactions
Old partition – may need backup,
archive, restore
Sliding Window Scenario
Think through your scenario
Ensure minimal data movement for
SPLIT
MERGE
Empty partition should be the one that
is “SPLIT”
Watch how filegroups are assigned to
partitions
MERGE may cause data movement even if
one partition is empty
Partitioning Summary
Partitioning in SQL Server 2005 is
primarily for
Data Management
SCALE UP on large (>8 way) machines
Plan for it
Understand the benefits and costs
Identify the problem that you want to solve
with partitioning
Resources
SQL Server 2005 Mission Critical High Availability
http://www.microsoft.com/technet/prodtechnol/sql/themes/highavailability.mspx
Database Mirroring Best Practices and Performance
Considerations
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologi
es/dbm_best_pract.mspx
SQL Server 2005 Failover Clustering White Paper
http://www.microsoft.com/downloads/details.aspx?familyid=81823
4dc-a17b-4f09-b282-c6830fead499&displaylang=en
SQL Server 2005: Upgrade Your Skills
http://www.microsoft.com/events/series/technetsqlserver2005.msp
x
SQL Server 2005 Upgrade Technical Reference Guide
http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e
96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
SQL Server 2005: Virtual Labs
http://msdn.microsoft.com/virtuallabs/sql/default.aspx
SQL Server 2005 Understanding Database Pricing
http://www.microsoft.com/sql/howtobuy/understdbpricing.mspx
Customer Examples
Mediterranean Shipping Company – MSC runs it
5 TB SQL database and 15 billions transacions on Database
Mirroring
http://www.microsoft.com/casestudies/casestudy.aspx?cas
estudyid=49075
Austrian Broadcast Corporation (ORF) – with
SQL Server 2005, ORF generates results 70% faster and
also runs Database Mirroring
http://www.microsoft.com/casestudies/casestudy.aspx?casestudyi
d=48607
ServiceU Corporation – ServiceU runs Database
Mirroring between two data centers 300 miles apart
http://www.microsoft.com/casestudies/casestudy.aspx?casestudyi
d=49683
Tuev Nord - one of Germany's largest technical service
providers) runs its SAP Business Warehouse (BW) on SQL
Server 2005
http://www.microsoft.com/casestudies/casestudy.aspx?casestudyi
d=49607
© 2005 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.