Cơ sở dữ liệu - Chương 4: Mirronring và log shipping

Cơ sở dữ liệu - Chương 4: Mirronring và log shipping CSDL chính và mirror nên có cùng cẫu trúc thư mục Nêu cấu trúc thư mục khác nhau thì việc thme hay xõa trên CSDL chính sẽ không thực hiện được nếu không tạm ngừng

75 trang | Chia sẻ: huyhoang44 | Lượt xem: 1092 | Lượt tải: 0

Bạn đang xem trước 20 trang tài liệu Cơ sở dữ liệu - Chương 4: Mirronring và log shipping, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên

GV Phi Loan – FIT - UIH 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̂ǹg 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. 6 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. 7 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. 8 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. 9 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. 10 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) 11 GV Phi Loan – FIT - UIH 12 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. 13 GV Phi Loan – FIT - UIH 14 GV Phi Loan – FIT - UIH Scenario 1 15 GV Phi Loan – FIT - UIH Scenario 2 16 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) 17 GV Phi Loan – FIT - UIH 18 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. 19 GV Phi Loan – FIT - UIH Trình tự thực hiê ̣n cu ̉a SAFETY FULL 1. 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. 20 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. 21 GV Phi Loan – FIT - UIH 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. 23 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̂ǹg bô ̣. GV Phi Loan – FIT - UIH 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. 26 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 27 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. 29 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 30 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 31 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 32 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 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 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 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 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. 2. 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 1. 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 GV Phi Loan – FIT - UIH Configuring log shipping using Management Studio 5. 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 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ăǹg 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

Các file đính kèm theo tài liệu này:

chuong_4_ktcddbms_1476.pdf