Transcript MCITP Administrator: Microsoft SQL Server 2005 Database

MCITP Administrator: Microsoft
SQL Server 2005 Database Server
Infrastructure Design Study
Guide (70-443)
Chapter 10 : Designing a SQL
Server 2005 Solution for High
Availability
SQL Server 2005 High
Availability
• Dependence on databases is
growing.
• More applications used 24x7
• Maintenance periods shrinking or
being eliminated.
• High availability can help
© Wiley Inc. 2006. All Rights Reserved.
SQL Server 2005
Technologies
• SQL Server 2005 has four main
technologies
– Failover clustering
– Database mirroring
– Log shipping
– Replication
• Each meets different goals and
solves separate problems
© Wiley Inc. 2006. All Rights Reserved.
Availability
• Measured in different ways
(application, database, network)
• Holy Grail - 5’s (99.999%)
– Equates to 5 minutes of downtime a
year.
– Single server can’t get here
• Need some technology to distribute
load
© Wiley Inc. 2006. All Rights Reserved.
Availability – cont’d
• Issues affecting availability
– Maintenance
– Upgrades
• Multiple servers can mitigate issues
© Wiley Inc. 2006. All Rights Reserved.
Examining HA Technologies
• Trying to increase availability
• Identify Single Points of Failure
– CPU
– Disk drive
– Power Supply
– Network
– OS
– SQL Server
– Others
© Wiley Inc. 2006. All Rights Reserved.
Setting HA Goals
• Since each technology solves
different problems, need to identify
which ones you need to solve.
– Hardware failures
– Geological disasters
– Application problems.
– Automated failover
© Wiley Inc. 2006. All Rights Reserved.
HA System Limitations
• High availability <> Scalability
• Data Loss potential
• Delays during failover
© Wiley Inc. 2006. All Rights Reserved.
Failover Clustering
• Most common HA technology
• Improved from SQL Server 2000
• Entire instance is protected from
disaster
• SQL Server Agent, Notification
Services, Analysis Services, and
replication failed over.
• Virtual node presented to clients
© Wiley Inc. 2006. All Rights Reserved.
Failover Clustering Limitations
•
•
•
•
•
Shared Disk Resources
Cost
Hardware limitations
Complex network configuration
Employee training (DBA and system
administrator)
© Wiley Inc. 2006. All Rights Reserved.
Designing Clustering
• Node count limited by MSCS
resources
– 8 nodes with Windows 2003
Datacenter and SQL Enterprise
– SQL Standard limited to 2 nodes
• Active/Active v Active/Passive
• N+1 configurations
© Wiley Inc. 2006. All Rights Reserved.
Designing Clustering – cont’d
• Geographic considerations
– Protect against isolated disaster (fire,
bomb, etc.)
– Hardware and networking can be very
expensive.
• Hardware decisions
– Must be on the HCL
– Shared disk subsystem required
© Wiley Inc. 2006. All Rights Reserved.
Designing Clustering – cont’d
• Licensing
– Only active nodes must be licensed.
© Wiley Inc. 2006. All Rights Reserved.
Database Mirroring
• New technology introduced in SQL
Server 2005.
• Not supported in RTM, need SP1
• Addresses high cost and overkill of
clustering in some situations.
© Wiley Inc. 2006. All Rights Reserved.
Database Mirroring – cont’d
• Automatic Failover
• Uses any Windows hardware, even
disparate hardware between servers
• Protects against disk failure
• Very fast failover
• Operates at the database level
© Wiley Inc. 2006. All Rights Reserved.
Database Mirroring Limitations
• Database Level means that logins
must be manually setup on both
servers.
• Master, msdb, model not protected
• SQL Agent jobs not failed over.
• ADO.NET 2.0 clients required for
automatic failover.
© Wiley Inc. 2006. All Rights Reserved.
Database Mirroring - Roles
• Principal – database to be protected
• Secondary – database that receives
the updates from the principal
• Witness – optional server used to
initiate automatic failover
• Quorum of two servers required to
initiate failover.
© Wiley Inc. 2006. All Rights Reserved.
Database Mirroring Protection
Levels
• High Performance Mode
– No confirmation from secondary
– No automatic failover
– Some data loss possible
• High Protection Mode
– Secondary acknowledges updates
– Manual failover required
– No witness
© Wiley Inc. 2006. All Rights Reserved.
Database Mirroring Protection
Levels – cont’d
• High Availability Mode
– Automatic failover supported
– Updates acknowledged
– Witness required
© Wiley Inc. 2006. All Rights Reserved.
Configuring Database
Mirroring
• Endpoints needed on each server to
transfer updates.
• Initialize mirror database with a
backup.
• ALTER DATABASE SET PARTNER
to begin updates.
• Be sure you test failover and
failback.
© Wiley Inc. 2006. All Rights Reserved.
Log Shipping
• Based on normal backup and
restore procedures
• Enterprise edition required in
previous versions. Supported in
Standard in 2005.
• Supports multiple secondary servers
• Secondary databases can be used
for reporting.
© Wiley Inc. 2006. All Rights Reserved.
Log Shipping Limitations
• No automatic failover
• Application changes may be
required to failover
• Secondary server name must be
different.
• Operates at the database level
© Wiley Inc. 2006. All Rights Reserved.
Log Shipping Roles
• Primary server
– Normal server for clients
• Secondary server
– One or more servers receiving log
backups from the primary
• Monitor server
– Stores tracking information about
backups and restores.
© Wiley Inc. 2006. All Rights Reserved.
Failover with Log Shipping
• Must be performed by DBA
– Restore remaining logs on secondary
using the NORECOVERY option
– Backup tail from primary server if
possible
– Restore tail on secondary
– Bring secondary online with the
RECOVERY option.
– Configure clients to connect to the
secondary
© Wiley Inc. 2006. All Rights Reserved.
Replication
• Not specifically a high availability
technology.
• Can be adapted for HA situations
• Use Transactional or Merge
replication for HA
© Wiley Inc. 2006. All Rights Reserved.
Transactional Replication for
High Availability
• Use low latency batches to move
transactions
• Similar to log shipping, but can be
faster
• Secondary system is full live for
queries.
• Disparate hardware can be used
• Operates at the publication level, not
database level.
© Wiley Inc. 2006. All Rights Reserved.
Merge Replication with High
Availability
• Similar to transactional replication.
• Updates can be made to either the
primary or secondary nodes
• Can scale the system when both
nodes are active
© Wiley Inc. 2006. All Rights Reserved.
Highly Available Storage
• Any HA solution requires highly
available storage
• Two main choices
– RAID
– SAN
© Wiley Inc. 2006. All Rights Reserved.
RAID Technology
• Redundant Array of Inexpensive
Disks
– RAID 0 - Striping
– RAID 1 – Mirroring
– RAID 5 – Striping with Parity
– RAID 1 + 0 – Combine RAID 0 + 1
• RAID 0 not suitable for
databases.
© Wiley Inc. 2006. All Rights Reserved.
SAN Storage
• Large set of disks attached to
multiple servers.
• May use RAID technology
• Specialized installations with
proprietary hardware
• Work with vendors to design
database storage
© Wiley Inc. 2006. All Rights Reserved.
Designing an HA Solution
• Consider more than the database
server
• Consider Failover times
• Application failover issues
• Cost of solutions
© Wiley Inc. 2006. All Rights Reserved.
Nontechnical Issues
• In a disaster situation, consider
other issues
– Staffing
– Geographic connections
– Load on secondary server
© Wiley Inc. 2006. All Rights Reserved.
Preparing for Migration
• Develop a plan to move to the HA
solution
– Minimize downtime
– Train staff
– Network Abstraction
– Test
© Wiley Inc. 2006. All Rights Reserved.
Summary
• Each HA technology has different
benefits and costs
• Each solves different problems
• Match up needs with the
technology
• Test the solution to be prepared for
a real disaster
© Wiley Inc. 2006. All Rights Reserved.