Hướng dẫn cấu hình SQL Server Replication hoàn chỉnh

Cơ sở dữ liệu rất quan trọng đối với mọi công ty và nhiều giải pháp phần mềm sử dụng cơ sở dữ liệu có thể được tập trung và phân phối. Tính sẵn sàng cao (High Availability – HA) của cơ sở dữ liệu và mức độ liên quan của dữ liệu là rất quan trọng đối với các doanh nghiệp, việc sao lưu và nhân rộng cơ sở dữ liệu là điều cần thiết.

Microsoft SQL Server là phần mềm quản lý cơ sở dữ liệu có thể cài đặt trên hệ điều hành Windows, Linux và Mac OS. Với SQL Server Replication, có thể tạo một bản sao giống hệt cơ sở dữ liệu chính của bạn hoặc đồng bộ hóa các thay đổi giữa nhiều cơ sở dữ liệu và duy trì tính nhất quán và toàn vẹn của dữ liệu.

Bài viết này liệt kê các kiểu nhân rộng trong SQL Server, giải thích cách hoạt động và hướng dẫn cách thực hiện nhân rộng với SQL Server Replication.

Thuật ngữ được sử dụng cho SQL Server Replication

Trước khi đi sâu vào cấu hình và thiết lập nhân rộng với SQL Server Replication, trước tiên chúng ta hãy xem qua các thuật ngữ chính và các mô hình nhân rộng.

Article là các đơn vị cơ bản cần được nhân rộng, chẳng hạn như table, stored procedure, function và view. Articles có thể được scale theo chiều dọc hoặc chiều ngang bằng cách sử dụng các bộ lọc (filter). Có thể tạo nhiều articles cho cùng một đối tượng.

Publication là một tập hợp logic của các articles. Đây là tập thực thể cuối cùng từ cơ sở dữ liệu được chỉ định để nhân rộng.

Filter (bộ lọc)là một tập hợp các điều kiện cho một article. SQL Server Replication cho phép bạn sử dụng bộ lọc và chọn các thực thể tùy chỉnh để nhân rộng, do đó làm giảm lưu lượng dư thừa và lượng dữ liệu được lưu trữ trong bản sao cơ sở dữ liệu.

Ví dụ: bạn có thể chỉ chọn các bảng và trường quan trọng nhất bằng cách sử dụng bộ lọc và chỉ nhân rộng những dữ liệu này.

Có ba thành phần chính trong nhân rộng cơ sở dữ liệu SQL Server: nhà phân phối, nhà xuất bản và người đăng ký.

Nhà phân phối (distributor) là một instance SQL Server được cấu hình để thu thập các giao dịch từ nhà xuất bản và phân phối chúng cho người đăng ký. Một nhà phân phối hoạt động như một cơ sở dữ liệu để lưu trữ các giao dịch nhân rộng.

Cơ sở dữ liệu của nhà phân phối có thể được coi là Nhà xuất bản và Nhà phân phối cùng một lúc. Trong mô hình nhà phân phối cục bộ, một phiên bản MS SQL Server chạy cả Nhà xuất bản và Nhà phân phối.

Mô hình nhà phân phối từ xa có thể được sử dụng khi bạn muốn người đăng ký được cấu hình để sử dụng một instance SQL Server duy nhất để nhận các ấn phẩm khác nhau (phân phối tập trung).Trong mô hình này, Nhà xuất bản và Nhà phân phối chạy trên các máy chủ khác nhau.

Nhà xuất bản (publisher) là bản chính của cơ sở dữ liệu mà article được cấu hình, cung cấp dữ liệu cho các máy chủ MS SQL khác được cấu hình để sử dụng trong quá trình nhân rộng. Nhà xuất bản có thể có nhiều hơn một article.

Người đăng ký (subcriber) là một cơ sở dữ liệu nhận dữ liệu sao chép từ một article. Một Người đăng ký có thể nhận dữ liệu từ nhiều nhà xuất bản. Mô hình một người đăng ký được sử dụng khi có một người đăng ký được kết nối với một article duy nhất. Mô hình nhiều người đăng ký được sử dụng khi nhiều người đăng ký được kết nối với một article duy nhất.

Agent là các thành phần của SQL Server có thể hoạt động như các dịch vụ nền cho hệ quản trị cơ sở dữ liệu quan hệ và được sử dụng để lập lịch thực hiện tự động các công việc, chẳng hạn như sao lưu và nhân rộng cơ sở dữ liệu SQL Server. Có năm loại agent: Snapshot Agent, Log Reader Agent, Distribution Agent, Merge Agent và Queue Reader Agent.

Subscription là yêu cầu một bản sao của article phải được chuyển đến Người đăng ký. Subscription được sử dụng để xác định dữ liệu xuất bản phải được nhận và dữ liệu này sẽ được nhận ở đâu và khi nào. Có hai loại đăng ký – push và pull.

Push subscription: dữ liệu đã thay đổi được truyền từ Nhà phân phối đến cơ sở dữ liệu của Người đăng ký. Không cần yêu cầu từ Người đăng ký.

Pull subscription: dữ liệu đã thay đổi được thực hiện trên Nhà xuất bản do Người đăng ký yêu cầu. Agent chạy ở phía Người đăng ký.

Cơ sở dữ liệu của người đăng ký là cơ sở dữ liệu đích trong mô hình nhân rộng SQL Server Replication.

Metadata là dữ liệu được sử dụng để mô tả các thực thể của cơ sở dữ liệu. Có một loạt các metadata function được tích hợp sẵn cho phép bạn trả về thông tin về phiên bản SQL Server, instance cơ sở dữ liệu và các thực thể cơ sở dữ liệu.

Trong mô hình nhiều nhà xuất bản–nhiều người đăng ký, Nhà xuất bản có thể hoạt động như Người đăng ký trên một trong các máy chủ SQL Server. Đảm bảo rằng bạn tránh mọi xung đột cập nhật tiềm ẩn khi sử dụng mô hình nhân rộng SQL Server Replication này.

Các kiểu nhân rộng SQL Server Replication

SQL Server Replication là công nghệ sao chép và đồng bộ hóa dữ liệu giữa các cơ sở dữ liệu một cách liên tục hoặc thường xuyên theo các khoảng thời gian đã định. Đối với hướng sao chép, SQL Server Replication có thể là: một chiều, một-nhiều, hai chiều và nhiều-một. Có bốn kiểu nhân rộng trong SQL Server Replication: snapshot, transactional, peer-to-peer và merge.

Snapshot Replication

Nhân rộng ảnh chụp nhanh (Snapshot Replication) được sử dụng để nhân rộng dữ liệu chính xác như khi nó xuất hiện tại thời điểm tạo ảnh chụp nhanh cơ sở dữ liệu.

Kiểu nhân rộng này có thể được sử dụng khi dữ liệu được thay đổi không thường xuyên; khi không cần thiết phải có một bản sao cơ sở dữ liệu cũ hơn một cơ sở dữ liệu chính; hoặc một lượng lớn các thay đổi được thực hiện trong một khoảng thời gian ngắn.

Không có theo dõi thay đổi nào được thực hiện khi nhân rộng ảnh chụp nhanh. Ví dụ, nhân rộng ảnh chụp nhanh có thể được sử dụng khi tỷ giá hối đoái hoặc bảng giá được cập nhật một lần mỗi ngày và phải được phân phối từ máy chủ chính đến các máy chủ trong văn phòng chi nhánh.

Transactional Replication

Nhân rộng giao dịch (Transactional Replication) là nhân rộng tự động khi dữ liệu được phân phối từ cơ sở dữ liệu chính sang bản sao cơ sở dữ liệu trong thời gian thực (hoặc gần thời gian thực).

Nhân rộng giao dịch phức tạp hơn nhân rộng ảnh chụp nhanh. Không chỉ trạng thái cuối cùng của cơ sở dữ liệu được sao chép, tất cả các giao dịch đã thực hiện cũng được sao chép, giúp bạn có thể theo dõi toàn bộ lịch sử giao dịch trên bản sao cơ sở dữ liệu.

Khi bắt đầu quá trình nhân rộng giao dịch, một ảnh chụp nhanh được áp dụng cho Người đăng ký và sau đó dữ liệu liên tục được chuyển từ cơ sở dữ liệu chính sang bản sao cơ sở dữ liệu sau khi được thay đổi.

Nhân rộng giao dịch được sử dụng rộng rãi như là nhân rộng một chiều.

Ví dụ và các trường hợp sử dụng nhân rộng giao dịch:

• Tạo một máy chủ cơ sở dữ liệu với một bản sao cơ sở dữ liệu có thể được sử dụng để thực hiện chuyển đổi dự phòng nếu một máy chủ cơ sở dữ liệu chính bị lỗi.
• Nhận báo cáo về các hoạt động được thực hiện tại các văn phòng chi nhánh bằng cách sử dụng nhiều nhà xuất bản ở các văn phòng chi nhánh và một người đăng ký tại văn phòng chính.
• Các thay đổi phải được sao chép càng sớm càng tốt sau khi chúng xảy ra.
• Dữ liệu trên cơ sở dữ liệu nguồn được thay đổi thường xuyên.

Peer-to-peer Replication

Nhân rộng ngang hàng (Peer-to-peer Replication) được sử dụng để nhân rộng cơ sở dữ liệu cho nhiều người đăng ký cùng một lúc. Loại nhân rộng SQL Server Replication này có thể được sử dụng khi các máy chủ cơ sở dữ liệu của bạn được phân phối trên toàn cầu.

Các thay đổi có thể được thực hiện trên bất kỳ máy chủ cơ sở dữ liệu nào. Các thay đổi được truyền tới tất cả các máy chủ cơ sở dữ liệu khác. Nhân rộng ngang hàng có thể giúp mở rộng ứng dụng sử dụng cơ sở dữ liệu. Nguyên tắc làm việc chính dựa trên sự nhân rộng giao dịch.

Dưới đây, bạn có thể thấy cách nhân rộng ngang hàng trong SQL Server Replication có thể được sử dụng giữa các máy chủ cơ sở dữ liệu được phân phối trên toàn cầu.

Merge Replication

Nhân rộng hợp nhất (Merge Replication) là một loại nhân rộng hai chiều thường được sử dụng trong môi trường máy chủ-máy khách để đồng bộ hóa dữ liệu giữa các máy chủ cơ sở dữ liệu khi chúng không thể được kết nối liên tục.

Khi kết nối mạng được thiết lập giữa cả hai máy chủ cơ sở dữ liệu, các agent nhân rộng hợp nhất sẽ phát hiện những thay đổi được thực hiện trên cả hai cơ sở dữ liệu và sửa đổi cơ sở dữ liệu để đồng bộ hóa và cập nhật trạng thái của chúng.

Nhân rộng hợp nhất tương tự như nhân rộng giao dịch, nhưng dữ liệu được sao chép từ Nhà xuất bản đến Người đăng ký và ngược lại.

Kiểu nhân rộng cơ sở dữ liệu này là phức tạp nhất trong tất cả các kiểu nhân rộng SQL Server Replication và hiếm khi được sử dụng.

Ví dụ: nhân rộng hợp nhất có thể được sử dụng bởi nhiều cửa hàng ngang hàng hoạt động với cơ sở dữ liệu kho dùng chung. Mỗi cửa hàng được phép thay đổi thông tin trong cơ sở dữ liệu kho, đồng thời tất cả các cửa hàng phải cập nhật trạng thái cơ sở dữ liệu của họ sau khi xuất hàng hoặc chuyển vật tư đến kho.

Nhân rộng hợp nhất có thể được sử dụng trong trường hợp thông tin cập nhật phải có sẵn cho cơ sở dữ liệu chính (hoặc trung tâm) và cơ sở dữ liệu chi nhánh đồng thời.

Yêu cầu đối với SQL Server Replication

Các cổng sau phải được mở cho lưu lượng đến:

• TCP: 1433, 1434, 2383, 2382, 135, 80, 443
• UDP: 1434

Đừng quên cấu hình tường lửa của Windows và kích hoạt các cổng thích hợp cho lưu lượng đến trên mỗi máy chủ lưu trữ trước khi cài đặt SQL Server. Các máy chủ được tham dự trong MS SQL Replication phải phân giải nhau bằng tên máy chủ.

Trước khi thực hiện cấu hình SQL Server Replication, các phần mềm sau phải được cài đặt cho SQL Server:

• .NET Framework – một bộ thư viện.
• SQL Server – máy chủ cơ sở dữ liệu.
• SQL Server Management Studio (SSMS) – phần mềm quản lý cơ sở dữ liệu SQL Server với giao diện người dùng đồ họa (GUI).

Lưu ý rằng nếu bạn cài đặt SQL Server 2016 trên máy đầu tiên chứa cơ sở dữ liệu nguồn, giải pháp tốt nhất là cài đặt SQL Server 2016 trên máy thứ hai để cơ sở dữ liệu hoạt động bình thường. Nhân rộng sẽ không hoạt động nếu bạn cài đặt SQL Server 2008 trên máy thứ hai.

Ví dụ: nếu bạn muốn cấu hình nhân rộng giao dịch SQL Server, bạn có thể sử dụng máy chủ cơ sở dữ liệu thứ hai (nơi Người đăng ký được cấu hình) với một trong hai phiên bản của máy chủ cơ sở dữ liệu nguồn mà Nhà xuất bản được cấu hình.

Chính xác hơn, nếu phiên bản SQL Server trên Publisher là SQL Server 2016, thì Nhà phân phối có thể được cấu hình trên các phiên bản SQL Server 2016, 2017 và 2019 và Người đăng ký có thể được cấu hình trên phiên bản SQL Server 2012, 2014, 2016, 2017 và 2019.

Các khuyến nghị cơ bản về nhân rộng cơ sở dữ liệu SQL Server

Trước khi cấu hình môi trường cho SQL Server, dưới đây là một số yếu tố cần xem xét:

• Có những hạn chế đối với trường định danh (identity field) và trigger.
• Article chỉ có thể chứa các bảng có khóa chính.
• Chúng tôi khuyến nghị rằng không nên sử dụng lập lịch tạo ảnh chụp nhanh cho cơ sở dữ liệu lớn do tiêu tốn nhiều tài nguyên máy tính.
• Hãy cẩn thận khi thay đổi dữ liệu trong bản sao cơ sở dữ liệu nằm trên Người đăng ký, vì khi sắp có một giao dịch sửa đổi dữ liệu và dữ liệu đó đã được chỉnh sửa hoặc xóa, việc sao chép có thể dừng cho đến khi bạn giải quyết được vấn đề này.

Cấu hình môi trường

Nếu bạn lần đầu tiên cấu hình nhân rộng cho SQL Server, bạn nên thực hành trong môi trường thử nghiệm, ví dụ, cấu hình nhân rộng trong máy chủ SQL Server chạy trên máy ảo.

Hai máy chủ chạy Windows Server 2016 và SQL Server 2016 được sử dụng trong hướng dẫn này để giải thích nhân rộng trong SQL Server. Chúng ta hãy xem cấu hình của môi trường thử nghiệm được sử dụng trong bài viết này để có ý tưởng tốt hơn về cấu hình nhân rộng trong SQL Server.

Máy chủ 1

• Địa chỉ IP: 192.168.101.101
• Tên máy chủ: MSSQL01
• ID phiên bản máy chủ MS SQL: MSSQLSERVER1

Máy chủ 2

• Địa chỉ IP: 192.168.101.102
• Tên máy chủ: MSSQL02
• ID phiên bản máy chủ MS SQL: MSSQLSERVER2

Cả hai máy đều có 2 ổ đĩa là C: và D: trong cấu hình đĩa của chúng.

Bạn có thể tạm thời vô hiệu hóa tường lửa của Windows khi cài đặt SQL Server để thực hành cấu hình nhân rộng cho SQL Server.

Bài viết này không đi sâu vào cách cài đặt SQL Server vì hướng dẫn này tập trung vào cấu hình nhân rộng cho SQL Server. Trong ví dụ này, cả hai máy chủ SQL Server đều được cài đặt mà không có PolyBase.

Kiểm tra để đảm bảo rằng bạn đã cài đặt các tính năng cần thiết để nhân rộng SQL Server sau khi quá trình cài đặt SQL Server hoàn tất. Các dịch vụ công cụ cơ sở dữ liệu, chẳng hạn như SQL Server replication và R-Services, phải được chọn trong quá trình cài đặt MS SQL Server. Đường dẫn cài đặt mặc định được sử dụng trong ví dụ này (C:Program FilesMicrosoft SQL Server).

Các thiết lập khác:

• Chế độ xác thực hỗn hợp (xác thực Windows và xác thực MS SQL Server)
• Thư mục gốc dữ liệu: D:MSSQL_Server
• Thư mục cơ sở dữ liệu hệ thống: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Thư mục cơ sở dữ liệu người dùng: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Thư mục nhật ký cơ sở dữ liệu người dùng: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Thư mục sao lưu: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackup

Sau khi SQL server 2016 và SQL Server Management Studio được cài đặt trên máy, bạn có thể chuẩn bị máy chủ SQL Server của mình để nhân rộng cơ sở dữ liệu.

Chuẩn bị cho SQL Server Replication

Bạn phải cấu hình các máy chủ trước khi có thể bắt đầu nhân rộng cơ sở dữ liệu. Trong ví dụ của chúng tôi, một tài khoản Windows sẽ được sử dụng cho các agent nhân rộng SQL Server.

Tạo người dùng mssql trên cả hai máy chủ và đặt cùng một mật khẩu.

Người dùng mssql là thành viên của các nhóm sau:

• Administrators (quản trị viên cục bộ trên máy cục bộ, không phải quản trị viên miền)
• SQLRUserGroupMSSQLSERVER1
• SQLServer2005SQLBrowserUser$MSSQL01

Bạn có thể chỉnh sửa người dùng và gom nhóm bằng cách nhấn Win + R, mở CMD lên và chạy lệnh lusrmgr.msc.

Hai máy Windows Server được sử dụng trong ví dụ này không có trong Active Directory. Nếu bạn sử dụng Active Directory, bạn có thể tạo người dùng mssql trên bộ điều khiển miền.

Chạy SQL Server Management Studio.

Đăng nhập (xem ảnh chụp màn hình) bằng tài khoản sa sử dụng chứng thực SQL Server Authentication.

• MSSQL01MSSQLSERVER1 là tên máy chủ và tên instance SQL Server trên máy chủ đầu tiên.
• MSSQL02MSSQLSERVER2 là tên máy chủ và tên instance SQL Server trên máy chủ thứ hai.

Tương tự, bạn có thể kết nối trên máy chủ thứ hai (MSSQL02) với instance SQL Server thứ hai (MSSQLSERVER2).

Bạn cũng có thể kết nối với instance SQL Server thứ hai (MSSQLSERVER2) từ máy chủ thứ nhất (MSSQL01) bằng cách nhập thông tin đăng nhập thích hợp vào SQL Server Management Studio.

Bạn có thể kết nối với cả hai instance SQL Server (MSSQL01 và MSSQL02) trong cùng một SQL Server Management Studio. Để thực hiện việc này, trong đối tượng Explorer, bấm Connect > Database engine.

Trong hướng dẫn này, chúng tôi sẽ kết nối với MSSQLSERVER1 từ MSSQL01 và với MSSQLSERVER2 từ MSSQL02 bằng cách sử dụng SQL Server Management Studio để cấu hình máy chủ SQL Server.

Khi bạn đăng nhập vào instance SQL Server, bạn sẽ thấy rằng Agent không chạy. Theo mặc định, SQL Server Agent không tự động khởi động. Bạn có thể khởi động dịch vụ này theo cách thủ công, nhưng tốt hơn hết bạn nên cấu hình dịch vụ này để tự động khởi động sau khi khởi động Windows.

Cấu hình dịch vụ Agent để bắt đầu tự động.

Nhấn Win + R, mở cmd và chạ ylệnh services.msc.

Mở thuộc tính dịch vụ SQL Server Agent và đặt Startup Type thành Automatic.