Bài giảng Kiến trúc cài đặt cơ sở dữ liệu - Chương 4: Mirroring và log shipping

GV Phi Loan – FIT - UIH

Nội dung

• Khái quát về mirroring – Các mode vận hành – Cấu hình mirroring – Giám sát họat động của mirroring

• Log shipping • Kết hợp giữa log shipping và mirroring

GV Phi Loan – FIT - UIH

High Availability Solutions

• Các giải pháp high availability che giấu

ảnh hưởng của lỗi phần cứng hay phần mềm và duy trì tính sẵn sàng của ứng dụng sao cho downtime đối với người dùng là nhỏ nhất.

GV Phi Loan – FIT - UIH

Các giải pháp High Availability

• AlwaysOn Failover Cluster Instances (chỉ

có SQL Server 2012) • Database mirroring • Log shipping

GV Phi Loan – FIT - UIH

Chọn lựa phương pháp

• Một DB có thể được mirror hay log shipping, hay thực hiện đồng thời cả mirror và log shipping.

• Để chọn phương pháp nào tùy thuộc vào việc

cần bao nhiều destination servers – Nếu chỉ cần 1 destination DB, thì mirroring là giải

pháp tốt nhất.

– Nếu cần nhiều hơn 1 destination database, nên

dùng log shipping, hoặc một mình hoặc dùng cùng với database mirroring.

GV Phi Loan – FIT - UIH

Database mirroring

• Database mirroring là giải pháp để tăng tính sẵn sàng (availability) của CSDL, cho phép CSDL mirror thay thế CSDL chính (principal database) nếu CSDL chính không sẵn sàng.

• Database mirroring duy trì 1 CSDL dự trữ

nóng (hot standby) đồng bộ với CSDL chính (principal database) bằng cách chuyển các bản ghi của nhật k{ giao dịch (transaction log) từ CSDL chính sang CSDL mirror qua mạng.

GV Phi Loan – FIT - UIH

Lợi ích của mirroring

• Tăng khả năng bảo vệ CSDL nhờ duy trì 1

copy của CSDL.

• Cho phép chọn CSDL nào cần mirroring từ

điển hình SQL Server này sang điển hình SQL Server khác. Cho phép mirroring nhiều CSDL của cùng 1 điển hình

• Cải thiện tính sẵn sàng của CSDL trong lúc HD

và SW nâng cấp.

GV Phi Loan – FIT - UIH

Lợi ích của mirroring

• Có thể được thực thi mà không cần HD đặc biệt và có thể rẻ hơn đáng kể so với các giải pháp high-availability khác.

GV Phi Loan – FIT - UIH

Nhược điểm của mirroring • Chỉ CSDL ở mô hình full recovery mới có thể mirror. • Các CSDL hệ thống (master, model, msdb, tempdb)

không thể mirror

• Mirror không hổ trợ cac giao dịch phân tán ngang qua

các CSDL.

• Mirror chỉ áp dụng cho các thay đổi từ nhật k{ giao dịch

(transaction log) hay từ sao lưu đầy đủ đầu tiên của CSDL chính. Các đối tượng CSDL như logins, jobs, maintenance plans, SSIS packages cần phải tạo bằng tay trên CSDL mirror.

• Mirror không hổ trợ việc có nhiều bản copy cho cùng 1

CSDL chính. Mỗi CSDL chính chỉ có 1 CSDL mirror.

GV Phi Loan – FIT - UIH

Nhược điểm của mirroring

• CSDL mirror phải cùng tên với CSDL chính. • Khi CSDL mirror bị lỗi, không gian dành cho nhật k{ giao dịch trên CSDL chính cũng không thể sử dụng được.

GV Phi Loan – FIT - UIH

Định nghĩa mirror

• Có liên quan đến 3 SQL Servers:

– Principal server – Mirror server – Witness server ( tùy chọn)

GV Phi Loan – FIT - UIH

Quorum

• Quorum là mối quan hệ khi 2 hay nhiều

điển hình server trong 1 phiên mirror kết nối với nhau.

GV Phi Loan – FIT - UIH

Scenario 1

GV Phi Loan – FIT - UIH

Scenario 2

GV Phi Loan – FIT - UIH

Safety level

• Các bản ghi của Transaction log được chuyển từ server chính sang server mirror đồng bộ (synchronous) hay bất đồng bộ (asynchronous) tùy theo cấp độ an toàn (safety level) được chọn

• Có 2 cấp độ an toàn:

– SAFETY FULL (synchronous) – SAFETY OFF (asynchronous)

GV Phi Loan – FIT - UIH

High-safety mode with automatic failover.

• Nếu chọn SAFETY FULL và có 1 witness server trong cấu hình mirror, thì có thể thực thi automatic failover. – Nếu CSDL chính bị lỗi, các server witness và mirror sẽ tạo 1 quorum và mirror sẽ trở thành server chính mới khôi phục lại CSDL và bắt đầu phục vụ client.

GV Phi Loan – FIT - UIH

Trình tự thực hiện của SAFETY FULL

Client gửi 1 transaction đến CSDL chính. Server chính viết bản ghi của transaction log vào bộ đệm của transaction log

2. Bộ đệm của transaction log viết vào đĩa đồng thời chuyển các bản ghi của transaction log từ bộ đệm vào mirror server. Server chính sẽ đợi xác nhận từ mirror, do đó thời gian đáp ứng của ứng dụng sẽ tăng.

GV Phi Loan – FIT - UIH

Trình tự thực hiện của SAFETY FULL

3. Mirror server viết các bản ghi của transaction log vào bộ đệm transaction log. Từ bộ đệm được viết tiếp vào đĩa.

4. Mirror server thông báo cho server chính là

transaction đã được viết.

5. Một commit (xác nhận) sẽ được báo đến

client.

GV Phi Loan – FIT - UIH

High-safety mode without automatic failover

• Nếu chọn SAFETY FULL và không có

witness server , thì không thể thực hiện automatic failover – Khi CSDL chính bị lỗi, mirror server không

thể tạo 1 quorum.

– Người dùng phải thực hiện failover bằng

tay.

GV Phi Loan – FIT - UIH

High-performance mode

• SAFETY OFF còn được gọi là asynchronous database mirroring hay high-performance mode.

• Sự tương tác giữa CSDL chính và mirror là bất

đồng bộ.

GV Phi Loan – FIT - UIH

High-performance mode

• Các bản ghi của transaction log được ghi vào nhật k{ transaction của CSDL chính đồng thời gửi cho nhật k{ transaction của CSDL mirror (tương tự như trong mode đồng bộ)

• Điểm khác biệt chính là CSDL chính không đợi phản hồi từ mirror mà giao dịch trên CSDL chính được commit ngay khi nó được viết vào nhật k{ transaction của CSDL chính. Nhờ đó tăng tốc độ thực thi nhưng cũng tăng tải ( heavy load) cho CSDL chính.

GV Phi Loan – FIT - UIH

High-performance mode

• Nếu mạng bị lỗi (trễ) có thể làm cho CSDL chính phải đợi cho đến khi gửi nhật k{ transaction đến CSDL mirror, và nếu CSDL chính bị lỗi thì các bản ghi của nhật k{ transaction chưa được gửi sẽ bị mất. • Mode này không cho phép automatic và

manual failover, chỉ cho phép dùng forced failover

GV Phi Loan – FIT - UIH

Xác định cấu hình mirroring

• Dùng 1 trong 2 phương pháp:

– Dùng SQL Server Management Studio – Dùng system-stored procedures.

GV Phi Loan – FIT - UIH

Tiền điều kiện của mirroring

• Các điển hình primary, mirror, và witness cần phải chạy trên cùng 1 phiên bản của SQL Server. – Các điển hình primary và mirror phải chạy cùng 1 edition, trong khi điển hình witness phải chạy trên 1 edition của SQL Server có hổ trợ witness.

GV Phi Loan – FIT - UIH

Các edition dùng cho mirror

• Enterprise : hổ trợ high-performance, high-

safety, và witness modes

• Business Intelligence: hổ trợ high-safety và

witness modes

• Standard : hổ trợ high-safety and witness

modes

• Web: chỉ hổ trợ witness mode only • Express (all versions) chỉ hổ trợ witness

mode

GV Phi Loan – FIT - UIH

Tiền điều kiện của mirroring

• CSDL chính phải có mô hình recovery là

full

• Phải bảo đảm đủ không gian đĩa cho

CSDL mirror trên server mirror.

• Tạo CSDL mirror sẵn

GV Phi Loan – FIT - UIH

Tạo CSDL mirror

• Tạo full backup của CSDL chính và các transaction log backup tiếp sau đó.

• Copy các backup và restore trên mirror server với tùy chọn WITH NORECOVERY để đặt CSDL ở trạng thái cho phép chèn các bản ghi nhật k{ của transaction.

• CSDL mirror phải cùng tên với CSDL chính

GV Phi Loan – FIT - UIH

Tạo CSDL mirror

• CSDL chính và mirror nên có cùng cấu

trúc thư mục.

• Nếu cấu trúc thư mục (directory

structure) khác nhau thì việc thêm hay xóa file trên CSDL chính sẽ không thực hiện được nếu không tạm ngưng (suspending ) việc mirroring CSDL.

GV Phi Loan – FIT - UIH

Trước khi bắt đầu mirroring

• Trước khi bắt đầu mirroring, hãy backup

transaction log trên CSDL chính và restore nó trên CSDL mirror với tùy chọn WITH NORECOVERY.

• Không được restore với tùy chọn WITH

STANDBY, CSDL mirror sẽ không làm việc.

GV Phi Loan – FIT - UIH

Định nghĩa mirroring dùng SQL Server Management Studio • Sau khi đã tạo CSDL mirror • Mở hộp thoại properties của CSDL chính: – Trang options: chọn recovery mode là full – Trang Mirroring: nhấp nút Configure Security

để mở hộp thoại Configure Database Mirroring Security Wizard

• Có 2 chọn lựa:

– Có Witness Server – Không có Witness Server

GV Phi Loan – FIT - UIH

Thực hiện mirroring dùng SQL Server Management Studio

• Lần lượt xác định điển hình server của

CSDL chính và mirror

GV Phi Loan – FIT - UIH

Kiểm tra cấu hình của CSDL đã mirror

• Ngay khi tạo xong cấu hình của CSDL

mirror, có nhiều cách để kiểm tra việc xác lập cấu hình này: – Xem trạng thái của CSDL chính và mirror: – Truy vấn view có tên

sys.database_mirroring của CSDL chính

GV Phi Loan – FIT - UIH

Trạng thái của CSDL chính

GV Phi Loan – FIT - UIH

Lệnh truy vấn view

SELECT DB_NAME(database_id) AS Database_Name, mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc, mirroring_partner_name, mirroring_witness_name, mirroring_witness_state_desc FROM sys.database_mirroring WHERE mirroring_state IS NOT NULL;

GV Phi Loan – FIT - UIH

View sys.database_mirroring

GV Phi Loan – FIT - UIH

Giám sát việc mirroring CSDL

• Có ba cách để giám sát việc mirroring

CSDL – Database Mirroring Monitor – System Monitor – SQL Server Profiler tool.

GV Phi Loan – FIT - UIH

Dùng Database Mirroring Monitor

• Để mở công cụ Database Mirroring

Monitor: – Mở Management Studio và kết nối đến

server của CSDL chính hay mirror. – Nhấp phải tại CSDL chính, chọn task\ Launch Database Mirroring Monitor.

GV Phi Loan – FIT - UIH

SQL Server Profiler tool

• Từ thực đơn start, chọn Programs/Microsoft SQL Server 2012/ Performance Tools/SQL Server Profiler.

• Chọn New Trace, xuất hiện hộp thoại

Connect to Server, chọn server chính để kết nối.

• Chọn tab Events Selection, chọn các event liên quan đến mirroing, nhấp nút run đề thực hiện.

GV Phi Loan – FIT - UIH

Thay đổi mode vận hành

• Để thay đổi mode vận hành, mở properties

của CSDL chính. – Chuyển đến trang Mirroring – Thay đổi mode vận hành:

GV Phi Loan – FIT - UIH

Chuyển đổi role và failover

• Có 3 cách chuyển đổi role:

– Manual failover – Automatic failover – Forced service (with possible data loss)

GV Phi Loan – FIT - UIH

Chuyển đổi role và failover

• Manual failover : role này luôn sẵn sàng nếu mirror đã được cấu hình ở mode high-safety mode. Có thể bắt đầu manual failover khi mirroring đang đồng bộ. DA thường thực hiện manual failover khi họ cần cập nhật hay khi các service packs yêu cầu khởi động lại.

GV Phi Loan – FIT - UIH

Chuyển đổi role và failover

• Automatic failover: nếu có dùng witness và mirroring được cấu hình ở mode high-safety mode. Automatic failover có thể xảy ra khi witness và mirror vẫn còn được kết nối, nhưng server chính không thể kết nối được.

GV Phi Loan – FIT - UIH

Chuyển đổi role và failover

• Forced service (with possible data loss): role này có thể được dùng ở mode high- safety khi không có witness hay ở mode high-performance. Người quản trị buộc mirror trở thành chính (principal) khi principal bị lỗi (unavailable).

GV Phi Loan – FIT - UIH

Automatic failover

• Automatic failover có thể xảy ra trong các

điều kiện sau: – Đã cấu hình mirroring session với mode high-

safety và có 1 witness.

– Mirror ở trạng thái đồng bộ (synchronized) – Instance chính bị lỗi (unavailable) trong khi các instance của mirror và witness vẫn duy trì 1 quorum. Nếu tất cả instance đều mất kết nối, thì automatic failover sẽ không xảy ra được thậm chí nếu witness và mirror đã kết nối trở lại.

GV Phi Loan – FIT - UIH

Manual failover

• Có thể kích khởi failover bằng tay bằng cách nhấp nút Failover trên trang Mirroring của hộp thoại properties của CSDL chính. • Hoặc dùng lệnh ALTER DATABASE với tùy

chọn SET PARTNER FAILOVER .

• Ví dụ:

USE master; ALTER DATABASE AdventureMirror SET

PARTNER FAILOVER;

GV Phi Loan – FIT - UIH

Log shipping

• Log shipping cho phép gửi 1 cách tự

động các sao lưu transaction log từ DB chính trên server chính đến 1 hay nhiều DB thứ cấp trên nhiều server thứ cấp khác.

GV Phi Loan – FIT - UIH

Các thuật ngữ liên quan đến log shipping • primary server (source server) • primary database • secondary server (warm standby server) • secondary database • monitor server • Backup job • Copy job • Restore job • Alert job

GV Phi Loan – FIT - UIH

Monitor server

• Một server instance thứ ba tùy chọn

được gọi là monitor server ghi lại các lịch sử và trạng thái của các thao tác backup và restore, xuất ra các cảnh báo (alert) nếu các thao tác này xảy ra không đúng lịch.

GV Phi Loan – FIT - UIH

Monitor server

• Theo dõi chi tiết của log shipping, bao gồm:

– When the transaction log on the primary database

was last backed up.

– When the secondary servers last copied and

restored the backup files.

– Information about any backup failure alerts. • Một monitor server có thể giám sát nhiều log

shipping configurations.

• Khi monitor server không được dùng, primary và secondary server sẽ tự lưu trữ thông tin giám sát.

GV Phi Loan – FIT - UIH

Log shipping plan

• Mỗi primary database chỉ có thể có 1 kế hoạch log shipping, mỗi kế hoạch (plan) chỉ có thể ship duy nhất 1 database. • Nhưng 1 plan có thể ship đến nhiều

secondary servers.

GV Phi Loan – FIT - UIH

Các thao tác của log shipping

1. Back up the transaction log at the primary

server instance. Copy the transaction log file to the secondary server instance.

3. Restore the log backup on the secondary

server instance.

• Log có thể được ship đến nhiều điển hình

server thứ cấp.

GV Phi Loan – FIT - UIH

Chuẩn bị trước khi lop shipping

• Chuẩn bị không gian đĩa để lưu trữ các transaction log backup trên primary server. Cấp quyền read và write trên phần share của mạng và quyền read cho account thực hiện việc copy trên secondary server.

• Tạo folder Destination trên secondary server. Hai

công việc (job) trên secondary server: – Copy các transaction log backup từ mạng vào folder

destination

– Load : khôi phục các transaction log backups from

destination folder. Tài khoản SQL Server service trên secondary server cần có quyền read and write trên folder này.

GV Phi Loan – FIT - UIH

Chuẩn bị trước khi lop shipping

• Mô hình recovery model của primary database phải

là full hay bulk-logged.

• Edition của SQL Server 2008 phải là Enterprise,

Standard, hay Workgroup

• Nếu các server primary và secondary trên các

domain khác nhau thì cần xác lập đường truyền hai chiều giữa hai domain này.

• Nếu DB khá lớn, nên backup đầy đủ, copy backup này vào secondary server, và khôi phục nó trên secondary server với tùy chọn NORECOVERY hay STANDBY (xác lập trạng thái cho phép restore transaction log)

GV Phi Loan – FIT - UIH

Configuring log shipping

• Hai phương pháp:

– Dùng SQL Server Management Studio – Dùng system stored procedures.

GV Phi Loan – FIT - UIH

Configuring log shipping using Management Studio

Trên primary server, mở hộp thoại Properties của DB cầnlog shipping. 2. Ở trang Options, kiểm tra mô hình

recovery của DB, phải là Full hay Bulk- logged

3. Ở trang Transaction Log Shipping, đánh

dấu checkbox Enable this as a primary database in a log shipping configuration.

GV Phi Loan – FIT - UIH

Configuring log shipping using Management Studio

4. Nhấp vào nút Backup Settings để định

cấu hình cho log shipping:

–

Đưa vào network share nơi mà transaction log backups được lưu trữ trước khi được copy sang secondary server.

– Nếu folder backup là local, thì đưa vào

đường dẫn của folder này.

GV Phi Loan – FIT - UIH

Configuring log shipping using Management Studio

–

Lập lịch cho việc back up nhật k{ transaction log : đặt tên, thời gian, tần số thực hiện ( mặc định 15 phút 1 lần) 6. Trong phần secondary instance, nhấp nút add để thêm các secondary server Chọn Server instance và secondary DB

GV Phi Loan – FIT - UIH

Các tùy chọn khởi tạo secondary DB

• Đối với DB nhỏ nên để Management Studio restore :

– Hoặc tạo full backup rồi restore nó trên secondary

server

– Hoặc sử dụng bảng backup sau cùng: cần xác định tên thư mục mà backup được lưu trữ, nhấp nút ‘‘Restore Options’’ và đưa vào path nơi chứa file data và log.

• Nếu DB lớn thì nên chọn tùy chọn thứ ba ‘‘No, the secondary database is initialized’’ bỏ qua wizard và backup bằng tay DB, rồi copy bảng backup vào DB secondary và khôi phục.

GV Phi Loan – FIT - UIH

Tạo monitor server

• Sau khi tạo secondary DB, có thể tùy

chọn tạo monitor server – Nếu không thêm monitor server ngay lúc định cấu hình thì sau này không thể thêm được.

– Nếu thêm monitor server lúc này cũng sẽ không thể thay đổi nếu không xóa log shipping trước.

GV Phi Loan – FIT - UIH

Configuring log shipping using Transact-SQL • Thực hiện lần lượt các thủ tục hệ thống sau

trên primary server: – master.dbo.sp_add_log_shipping_primary_dat

abase

– msdb.dbo.sp_add_schedule – msdb.dbo.sp_attach_schedule – msdb.dbo.sp_update_job – master.dbo.sp_add_log_shipping_primary_sec

ondary

GV Phi Loan – FIT - UIH

Configuring log shipping using Transact-SQL • Thực hiện các thủ tục hệ thống sau trên secondary server: – master.dbo.sp_add_log_shipping_secondary_primary – msdb.dbo.sp_add_schedule – msdb.dbo.sp_attach_schedule – msdb.dbo.sp_add_schedule – msdb.dbo.sp_attach_schedule – master.dbo.sp_and_log_shipping_secondary_database – msdb.dbo.sp_update_job – msdb.dbo.sp_update_job

GV Phi Loan – FIT - UIH

Ví dụ: Typical Log Shipping Configuration

GV Phi Loan – FIT - UIH

Ví dụ: Typical Log Shipping Configuration

• The primary server instance runs the backup job to

back up the transaction log on the primary database, then places the log backup into a primary log-backup file, which it sends to the shared backup folder.

• Each of the three secondary server instances runs its own copy job to copy the primary log-backup file to its own local destination folder.

• Each secondary server instance runs its own restore

job to restore the log backup from the local destination folder onto the local secondary database.

GV Phi Loan – FIT - UIH

Kết hợp Log shipping và Mirroring

• DB chính (principal) trong mirroring cũng có thể làm DB primary trong log shipping configuration, hay ngược lại nều phần backup share không bị ảnh hưởng tới.

• Mirroring có thể chạy ở bất kz mode nào: synchronous hay asynchronous

GV Phi Loan – FIT - UIH