Bài giảng Cơ sở dữ liệu nâng cao - Chapter 7: Maintaining high availability. Chương 7 gồm có những nội dung chính sau đây: Availability, log shipping, data mirroring, clustering. Mời các bạn cùng tham khảo.
AMBIENT/
Chủ đề:
Nội dung Text: Bài giảng Cơ sở dữ liệu nâng cao - Chapter 7: Maintaining high availability
- Maintaining High Availability
- Agenda
1. Availability
2. Log shipping
3. Data mirroring
4. Clustering
- 1. Availability
High availability isn’t always about fulltime operations,
but, about services being accessible to your users when
they need them
- 2. Log shipping
Log shipping is:
To send transaction logs from the primary database to the
secondary database.
To back up the transaction logs from a primary database and
then copy and restore them to a secondary database, keep
the secondary database nearly synchronized with the
primary database.
The destination server acts as a backup server.
Log shipping can be used with databases using the full or
bulklogged recovery models.
- 2. Log shipping
Log shipping architecture:
- 2. Log shipping
Log shipping architecture:
Primary server:
The SQL instance that needs to be protected in case of
hardware failure, software error, ...
The primary server is configured to execute a SQL Agent
job to back up the transaction log to a file
Secondary server:
The backup SQL Server 2008 instance that maintains a
copy of the primary server database.
The secondary server is configured with two SQL Agent
jobs: one to copy the transactionlog file from the shared
backup folder and the other to restore the transaction log
- 2. Log shipping
Log shipping architecture:
Monitor server:
Having a monitor server as part of log shipping is
optional but recommended.
When the monitor server participates in log shipping, it
manages the jobs that produce monitoring information
(such as the last time the transaction log was backed up
on the primary server, the last transaction log that was
restored on the secondary server, …)
- 2. Log shipping
Log shipping process:
Back up the transaction log on the primary server. A SQL
Agent job on the primary server backs up the transaction log
at a userconfigurable time interval to a file in a backup
folder.
Copy the transaction log to the secondary server. A SQL
Agent job on the secondary server accesses the backup
folder on the primary server to copy the transactionlog file
to a local folder on the secondary server.
Restore the transaction log on secondary server. A SQL
Agent job on the secondary server restores the transaction
log on the secondary server.
- 2. Log shipping
Log shipping deployment:
Before beginning the log shipping process, need to do some
initial configuration first.
Then, choose regarding how to deploy: using the SQL
Server 2008 Management Studio or using TSQL scripts.
Typically, a DBA uses SQL Server 2008 Management
Studio to configure log shipping and then generates SQL
scripts for future redeployment.
- 2. Log shipping
Log shipping deployment:
Initial configuration:
Create a backup folder that the primary server can
access; share it, and ensure that it is accessible by the
secondary server.
Create a destination folder on the secondary server, The
secondary server’s SQL Agent account or the proxy
account executing the job must have read and write
permission to this folder.
The recovery model for the logshipped database must be
set to either Full or bulk_logged
- 2. Log shipping
Enabling log shipping involves the following basic steps:
Choose servers for your primary server, secondary server,
and optional monitor server.
Chose the backup compression behavior of log backups if
neccessary.
Create a file share for the transaction log backups.
Choose a backup schedule for the primary database.
Create a folder for each secondary server into which the
transaction log backup files will be copied. (These folders
are usually located on the secondary servers.)
Configure one or more secondary databases.
- 3. Database mirroring
Database mirroring is a primarily software solution for
increasing database availability.
Mirroring is implemented on a perdatabase basis and
works only with databases that use the full recovery
model.
Database mirroring maintains two copies of a single
database that must reside on different server instances of
SQL Server Database Engine.
- 3. Database mirroring
One server instance serves the database to clients (the
principal server).
The other instance acts as a server (the mirror server),
depending on the configuration and state of the mirroring
session.
When a database mirroring session is synchronized,
database mirroring provides a hot standby server that
supports rapid failover without a loss of data from
committed transactions.
When the session is not synchronized, the mirror server is
typically available as a warm standby server (with
possible data loss).
- 3. Database mirroring
Database mirroring architecture:
- 3. Database mirroring
Database mirroring architecture:
Principal Server—The principal server hosts the copy of the
database that clients connect to and interact with. As
transactions occur on the principal database, the transaction
log records are forwarded to the mirror database.
Mirror Server—The mirror server hosts a copy of the
principal database and applies the transaction log records
sent by the principal database to keep the mirrored database
in sync with the principal database.
- 3. Database mirroring
Database mirroring architecture:
Witness Server (optional):
Needed if automatic failover to the mirror server is required in the
case of a principal database failure.
The witness server monitors the status of the principal and mirror
servers in a highavailability configuration
- 3. Database mirroring
How database mirroring works:
The principal and mirror servers communicate and
cooperate as partners in a database mirroring session. The
two partners perform complementary roles in the session:
the principal role and the mirror role.
Database mirroring involves redoing every insert, update,
and delete operation that occurs on the principal database
onto the mirror database as quickly as possible.
- 3. Database mirroring
How database mirroring works:
Redoing is accomplished by sending every active
transaction log record to the mirror server, which applies
log records to the mirror database, in sequence, as quickly
as possible.
Unlike replication, which works at the logical level,
database mirroring works at the level of the physical log
record.
- 3. Database mirroring
Database mirroring modes:
The database can be configured in 3 operating modes:
Highperformance mode
Highsafety without automatic failover mode
Highsafety with automatic failover mode.
- 3. Database mirroring
Database mirroring modes:
Highperformance mode:
Use asynchronous processing.
The principal server sends an acknowledgment to the client
application of a successful transaction as soon as it sends the
corresponding log record to the mirror server, but it does not wait
for acknowledgment from the mirror server that the log record
was received.
Under normal workload conditions, the latency between the
principal and the mirror is relatively small. However, if the
principal server is under heavy workload, this can increasethe gap
between the two partners.
Thêm vào BST
Báo xấu
Download
Vui lòng tải xuống để xem tài liệu đầy đủ
