Transcript Mecanismos de alta disponibilidad con Microsoft SQL Server 2008

Mecanismos de alta disponibilidad
con Microsoft SQL Server 2008
Por: ISC Lenin López Fernández de Lara
Temario





Database Mirroring
Log Shipping
Replication
Failover Clustering
Demo
1.1 Database Mirroring Roles
1.2 Operating Modes
High
Availability
High
Performance
High Safety
1.2.1 High Availability Operating
Mode
Automatic failover
Uses simple ping
Performance impact
Writes a
transaction to
th transaction
log
synchronous
The transaction
is first
committed on
the mirror
database
1.2.2 High Performance Operating Mode
No automatic failover
Asynchronous
1.2.3 High Safety Operating
Mode
Manually failover
Performance impact
Writes a
transaction to
th transaction
log
Synchronous
The transaction
is first
committed on
the mirror
database
Log Shipping
¿What is?


Provides a means to maintain a secondary server on an
automated basis using a chain of transaction log
backups
Also allows you to configure a monitor server that can
verify the health
1.1 Log Shipping Scenarios
Offloading
Report
Activity
Initialization
for Database
Mirroring
Upgrading
Versions or
Migrating to
a New
Platform
Primary or
Secondary
Availability
Solution
1.1.1 Offloading Report
Activity
Reporting
server
Standby
Mode
SELECT
statements
1.1.2 Initialization for Database Mirroring
backups of the
principal
minimizes
the time
principal and
mirror are
synchronized
1.1.3 Upgrading Versions or
Migrating to a New Platform
build the new
instance
move the
databases
a brief
outage on
the
applications
1.1.4 Primary or Secondary
Availability Solution
Secondary
databases
that
applications
can switch
an outage
of the
primary
database
1.2 Log Shipping Components
Replication
¿What is?



Replication is designed as a data-distribution
mechanism.
The core replication engine is designed for very flexible
implementation
The core architecture can be used to provide
availability for a database because a redundant copy of
data is maintained in synchronization with a master
copy
1.1 Replication Components
Articles
• The basic building block of replication
• Can be defined against a table, view, stored
procedure, or function
Publications
• Publications are groupings of articles that define
the replication set
Filters
• You can apply one or more filters to each article
that restrict the set of data that is replicated.
• You can fi lter articles by rows or by columns
1.2 Replication Roles
publisher
• Maintains the master copy of the data within a
replication architecture
subscriber
• Is the database that is receiving changes from the
replication engine defined by the publication to
which it is subscribing
distributor
• Is the main engine within a replication architecture
• The distribution database is stored on the instance
that is configured as the distributor (An instance of
SQL).
1.3 Replication Topologies
Central
Publisher
Central
Subscriber
Other
1.3.1 Central Publisher
Topology
1.3.2 Central Subscriber
Topology
1.6 Replication Methods
Snapshot
Replication
Transactional
Replication
Merge
Replication
1.6.1 Snapshot Replication
It is not
normally
used for high
availability
1. Snapshot Agent
extracts the schema
and BCPs the data
2. Distribution Agent then
picks up and applies the
snapshot to each subscriber
(tables are dropped and recreated, then the data is
copied using BCP)
full replace of
data
1.6.2 Transactional
Replication


Begins with an initial snapshot being applied to the
subscriber to ensure that the two databases are
synchronized
As subsequent transactions are issued against the
publisher, the replication engine applies them to the
subscriber.
2.2 Transactional Options
Merge Replication
¿What is?



Merge replication is another alternative that can be
applied to high-availability systems.
Merge replication was primarily designed for mobile,
disconnected users.
By translation, the mechanisms are already built in for
changes to occur at any location and get synchronized,
as well as to be able to withstand failures and continue
processing
3.1 Change Tracking
(synchronize process)
MSmerge
_genhistor
y
MSmerge
_genhistor
y
MSmerge_to
mbstone
MSmerge_to
mbstone
Windows Clustering
¿What is?

Windows clustering enables multiple pieces of hardware
to act as a single platform for running applications.
1.1 Windows Cluster
Components
1.2 Types of Clusters
Standard
Windows
Cluster
Majority
Node Set
Cluster
2.2 Failover Cluster Instance Components
The components that you need to configure for a SQL
Server failover clustered instance are the following:





IP addresses
Network names
Disk drives on the shared drive array
SQL Server services
Service accounts
Demo

Mirroring High Availability
¿######?