Cách tạo môi trường Database Mirroring trên SQL Server bằng T-SQL đơn giản và hiệu quả
Database mirroring là một trong các giải pháp thông thường của Microsoft SQL Server cho tính sẵn sàng cao (nhóm cluster, log shipping, backup và restore cơ sở dữ liệu, sao chép, v.v.). Microsoft cho biết rằng nó sẽ bị loại bỏ trong các phiên bản SQL Server tương lai và đề xuất sử dụng Always On Availability Groups thay thế.
Tuy nhiên, vẫn có một số trường hợp mà database mirroring vẫn hợp lý, chẳng hạn như khi cần giải pháp sẵn có cao qua các miền. Không thể triển khai Always On Availability Groups qua các miền (với Windows Server 2016, có thể vượt qua rào cản này bằng cách cấu hình Domain-Independent Availability Groups), do đó database mirroring vẫn là sự lựa chọn tốt nhất trong trường hợp này và dễ dàng triển khai hơn nhiều.
Giới thiệu về Database Mirroring trong SQL Server
Database mirroring là một giải pháp cho tính sẵn sàng cao trong SQL Server, cho phép tạo một bản sao của cơ sở dữ liệu Product trên một máy chủ khác (máy chủ mirror). Cấu hình database mirroring sao cho cơ sở dữ liệu mirror được đồng bộ hoàn toàn với cơ sở dữ liệu Product (cơ sở dữ liệu chính), đảm bảo không mất dữ liệu trong trường hợp xảy ra failover.
Lưu ý rằng database mirroring chỉ áp dụng giữa các cặp cơ sở dữ liệu chính và mirror (không thể có nhiều hơn một cơ sở dữ liệu mirror) và cơ sở dữ liệu phải ở chế độ phục hồi đầy đủ (full recovery model). Database mirroring có thể là bất đồng bộ (asynchronous) hoặc đồng bộ (synchronous).
Ở chế độ bất đồng bộ, còn được gọi là chế độ hiệu suất cao, các Transaction được chuyển từ cơ sở dữ liệu chính sang mirror một cách bất đồng bộ, do đó trong trường hợp failover có thể mất dữ liệu.
Ở chế độ đồng bộ, còn được gọi là chế độ an toàn cao, các Transaction được commit trước tiên trên cơ sở dữ liệu mirror, sau đó mới commit trên cơ sở dữ liệu chính. Do đó, dữ liệu giữa hai cơ sở dữ liệu này được đồng bộ hoàn toàn. Tuy nhiên, chế độ đồng bộ hoạt động chậm hơn chế độ bất đồng bộ.
Bằng cách thêm một witness server vào chế độ an toàn cao, chúng ta sẽ có chế độ an toàn cao với failover tự động và chế độ này cung cấp tính sẵn có cao nhất. Cũng có thể thêm một witness server vào chế độ hiệu suất cao, nhưng không được khuyến nghị vì không có lợi ích và có thể gây ra vấn đề.
Database mirroring tăng tính sẵn sàng của cơ sở dữ liệu bằng cách chuyển sang cơ sở dữ liệu mirror trong trường hợp máy chủ chính gặp sự cố. Trong chế độ an toàn cao, khi xảy ra failover tự động, thời gian tạm ngừng hoạt động sẽ được giảm thiểu.
Database mirroring cũng có thể được sử dụng cho mục đích phục hồi sau thảm họa, nhưng trong trường hợp này, máy chủ mirror nên được đặt ở một vị trí khác nhau để giảm thiểu rủi ro trong trường hợp xảy ra thảm họa.
Một lợi ích khác của database mirroring là giảm thiểu thời gian không hoạt động trong quá trình nâng cấp. Chúng ta có thể chuyển đổi máy chủ và nâng cấp chúng theo trình tự, vì vậy cơ sở dữ liệu sẽ sẵn có cho khách hàng trong suốt quá trình vận hành.
Cấu hình Database Mirroring đồng bộ trong SQL Server
Mặc dù có thể cấu hình database mirroring bằng giao diện đồ họa của SQL Server Management Studio (SSMS), nhưng công việc này cũng có thể được thực hiện bằng mã T-SQL. Bài viết này nhằm mô tả cấu hình database mirroring thông qua T-SQL.
Trong ví dụ của chúng ta, tôi sẽ cấu hình database mirroring đồng bộ giữa hai máy chủ cơ sở dữ liệu nằm trong cùng một miền. Trong miền của chúng ta - testdomain.com, chúng ta có hai máy chủ thử nghiệm - DBSERVER1 (sẽ hoạt động như cơ sở dữ liệu chính) và DBSERVER2 (mirror). Hãy kết nối đến các máy chủ này thông qua SSMS và thực thi các lệnh T-SQL theo từng bước bên dưới.
Đầu tiên, chúng ta cần tạo một điểm cuối (endpoint) database mirroring được sử dụng để thiết lập kết nối giữa các phiên database mirroring. Điểm cuối sử dụng giao thức TCP và lắng nghe trên một số cổng duy nhất.
Chúng ta nên tạo một điểm cuối trên cả máy chủ chính và máy chủ mirror. Sau đó, nếu tài khoản đăng nhập SQL Server khác nhau trên máy chủ chính và máy chủ mirror, chúng ta nên tạo tài khoản tương ứng của máy chủ khác trên mỗi máy chủ. Trong môi trường của chúng ta,
TESTDOMAIN\DBServer1_sqluser và TESTDOMAIN\DBServer2_sqluser là tài khoản người dùng SQL Server của DBSERVER1 và DBSERVER2 tương ứng. Sau đó, chúng ta nên cấp quyền CONNECT trên điểm cuối cho những người dùng này. Để thực hiện các bước đã nêu trên trên máy chủ chính, chúng ta chạy mã sau trên DBSERVER1:
---------Kết nối tới DBSERVER1(primary server) thông qua SSMS
USE master
GO
-- Step-1. (PRIMARY)
-- Tạo một điểm cuối database mirroring
CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO
-- Step-2. (PRIMARY)
-- Tạo tài khoản đăng nhập Windows trên máy chủ chính cho tài khoản SQL Server của máy chủ mirror (vì chúng chạy dưới các tài khoản khác nhau).
CREATE LOGIN [TESTDOMAIN\DBServer2_sqluser] FROM WINDOWS
GO
-- Step-3. (PRIMARY)
-- Cấp quyền kết nối (CONNECT) cho tài khoản đăng nhập của tài khoản SQL Server trên máy chủ mirror trên điểm cuối (endpoint).
GRANT CONNECT ON ENDPOINT::Mirroring TO [TESTDOMAIN\DBServer2_sqluser]
Tiếp theo, chúng ta cần thực hiện các bước tương tự trên máy chủ mirror. Do đó, trên DBSERVER2, chúng ta thực thi mã sau đây:
---------Connect to DBSERVER2(mirror server) via SSMS
USE master
GO
-- Step-4. (MIRROR)
-- Tạo một điểm cuối database mirroring
CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO
-- Step-5. (MIRROR)
-- Tạo tài khoản đăng nhập Windows trên máy chủ mirror cho tài khoản SQL Server của máy chủ chính (vì chúng chạy dưới các tài khoản khác nhau).
CREATE LOGIN [TESTDOMAIN\DBServer1_sqluser] FROM WINDOWS
GO
-- Step-6. (MIRROR)
-- Cấp quyền kết nối (CONNECT) cho tài khoản đăng nhập của tài khoản SQL Server trên máy chủ chính trên điểm cuối (endpoint).
GRANT CONNECT ON ENDPOINT::Mirroring TO [TESTDOMAIN\DBServer1_sqluser]
Bây giờ, chúng ta chuyển lại sang cửa sổ truy vấn của máy chủ chính và chạy đoạn mã tiếp theo để sao lưu toàn bộ cơ sở dữ liệu (TestDB) và sao lưu transaction log từ cơ sở dữ liệu chính. Như đã đề cập ở trên, cơ sở dữ liệu phải ở chế độ phục hồi đầy đủ để tham gia vào phiên database mirroring, do đó chúng ta đặt chế độ phục hồi của cơ sở dữ liệu chính thành FULL.
---------Connect to DBSERVER1(primary server) via SSMS
USE master
GO
-- Step-7. (PRIMARY)
-- Thay đổi chế độ phục hồi của cơ sở dữ liệu thành "full" (vì hiện tại nó đang ở chế độ "simple").
ALTER DATABASE TestDB SET RECOVERY FULL
-- Step-8. (PRIMARY)
-- Thực hiện sao lưu toàn bộ và transaction log của cơ sở dữ liệu.
BACKUP DATABASE TestDB TO DISK='\\Backups\TestDB\TestDB.bak'
GO
BACKUP LOG TestDB TO DISK='\\Backups\TestDB\TestDB.trn'
GO
Sau khi đã sao lưu dữ liệu, chúng ta chuyển sang session mirror để khôi phục các bản sao lưu này.
---------Connect to DBSERVER2(mirror server) via SSMS
USE master
GO
-- Step-9. (MIRROR)
-- Khôi phục bản sao lưu trên máy chủ mirror với tùy chọn NORECOVERY.
RESTORE DATABASE [TestDB] FROM DISK = N'\\Backups\TestDB\TestDB.bak'
WITH FILE = 1,
MOVE N'TestDB' TO N'D:\TestDB\TestDB.mdf',
MOVE N'TestDB2' TO N'D:\TestDB\TestDB2.ndf',
MOVE N'TestDB3' TO N'D:\TestDB\TestDB3.ndf',
MOVE N'TestDB_log' TO N'D:\TestDB\TestDB.ldf',
NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
GO
RESTORE LOG [TestDB] FROM DISK = '\\Backups\TestDB\TestDB.trn'
WITH NORECOVERY
GO
-- Step-10. (MIRROR)
-- Đặt partner cho session mirror trên máy chủ mirror.
ALTER DATABASE TestDB SET PARTNER ='TCP://DBSERVER1.testdomain.com:5022'
GO
Chúng ta có thể thấy, chúng ta đã khôi phục các bản sao lưu bằng cách sử dụng NORECOVERY, cho phép áp dụng transaction logs lên cơ sở dữ liệu mirror. Bước cuối cùng trong mã trên là định nghĩa đối tác mirroring cho máy chủ mirror (DBSERVER1). Hãy di chuyển đến máy chủ chính một lần nữa và định nghĩa đối tác mirroring (DBSERVER2):
---------Connect to DBSERVER1(primary server) via SSMS
USE master
GO
-- Step-11. (PRIMARY)
-- Set the partner instance on the primary server
ALTER DATABASE TestDB SET PARTNER = 'TCP://DBSERVER2.testdomain.com:5022'
GO
Nếu chúng ta muốn thiết lập chế độ bất đồng bộ, chúng ta chạy câu lệnh sau ngay sau mã ở trên trên máy chủ chính:
ALTER DATABASE TestDB SET PARTNER SAFETY OFF
Mặc định, SAFETY được bật (ON), điều này có nghĩa là chế độ đồng bộ. Bây giờ, chúng ta đã thiết lập phiên mirroring thành công. Để tạo thuận lợi cho quá trình chuyển đổi giữa các máy chủ chính và mirror, tôi đã kết nối với cả hai phiên bản bằng SSMS và sắp xếp cửa sổ truy vấn theo chiều dọc để có thể chạy các bước theo đúng trình tự.
Giám sát Database Mirroring
Để giám sát trạng thái đồng bộ hóa cơ sở dữ liệu, chúng ta có thể sử dụng mã sau:
USE master
GO
SELECT *
FROM sys.database_mirroring
Trong kết quả trả về, nếu "mirroring_state_desc" là "SYNCHRONIZED" thì chúng ta có thể nói rằng chúng ta đã hoàn thành tác vụ thành công. Lưu ý rằng với truy vấn này, chúng ta có thể xác định vai trò của máy chủ qua "mirroring_role_desc". Nếu chúng ta chạy truy vấn trên máy chủ mirror, giá trị của "mirroring_role_desc" sẽ là "MIRROR" và nếu chúng ta chạy trên máy chủ chính, giá trị sẽ là "PRINCIPAL".
Kết luận
Trong bài viết này, chúng ta đã tìm hiểu về cách thiết lập database mirroring và cấu hình phiên database mirroring giữa các máy chủ trong cùng một miền bằng cách sử dụng mã T-SQL. Hiểu mã T-SQL của cấu hình database mirroring có thể rất hữu ích trong việc giảm thiểu công việc thủ công trong triển khai database mirroring cho các môi trường khác nhau. Điều này bởi vì nó có thể được tham số hóa và áp dụng cho các máy chủ khác nhau với sự thay đổi tối thiểu.
All rights reserved
