Cách cấu hình tính năng sao chép dữ liệu trên MS SQL Server

Microsoft SQL Server là phần mềm quản lý cơ sở dữ liệu có thể được cài đặt trên các hệ điều hành Windows Server. Cơ sở dữ liệu được các doanh nghiệp trong mọi ngành nghề sử dụng, và nhiều giải pháp phần mềm đều dựa trên cơ sở dữ liệu – bao gồm cả cơ sở dữ liệu tập trung và phân tán. Tính sẵn sàng của cơ sở dữ liệu và tính nhất quán của dữ liệu là những yếu tố then chốt đối với các doanh nghiệp, do đó việc sao lưu và nhân bản cơ sở dữ liệu trở thành một yêu cầu thiết yếu.
Tìm hiểu về các loại nhân bản trong SQL Server, cách thức hoạt động của nhân bản trong SQL Server, cũng như cách thực hiện nhân bản trong SQL Server.

NAKIVO cho sao lưu Windows

Sao lưu nhanh các máy chủ và máy trạm Windows vào các vị trí tại chỗ, ngoài văn phòng và trên đám mây. Khôi phục toàn bộ hệ thống và các đối tượng chỉ trong vài phút, giúp giảm thiểu thời gian khôi phục (RTO) và tối đa hóa thời gian hoạt động.

KHÁM PHÁ GIẢI PHÁP

Replication trong SQL Server là gì?

Replication trong MS SQL Server là quá trình sao chép dữ liệu từ một cơ sở dữ liệu sang cơ sở dữ liệu khác, bao gồm các đối tượng cơ sở dữ liệu cụ thể, đồng thời duy trì bản sao đồng bộ của dữ liệu này giữa cơ sở dữ liệu nguồn và cơ sở dữ liệu đích. Với tính năng replication trong SQL Server, bạn có thể tạo ra một bản sao giống hệt cơ sở dữ liệu chính và đồng bộ hóa các thay đổi giữa hai cơ sở dữ liệu, đồng thời duy trì tính nhất quán và tính toàn vẹn của dữ liệu.

Các thuật ngữ được sử dụng cho sao chép MS SQL Server

Trước khi đi sâu vào cách cấu hình và thiết lập sao chép MS SQL Server, trước tiên chúng ta hãy điểm qua ngắn gọn các thuật ngữ chính và các mô hình sao chép.
Các bài viết là các đơn vị cơ bản cần được sao chép, chẳng hạn như bảng, thủ tục, hàm và chế độ xem. Các bài viết có thể được mở rộng theo chiều dọc hoặc chiều ngang bằng cách sử dụng bộ lọc. Có thể tạo nhiều bài viết cho cùng một đối tượng.
Một bản phát hành là tập hợp logic của các bài viết. Đây là tập hợp cuối cùng các thực thể từ cơ sở dữ liệu được chỉ định để sao chép.
Một bộ lọc là tập hợp các điều kiện cho một bài viết. Tính năng sao chép của MS SQL Server cho phép bạn sử dụng bộ lọc và chọn các thực thể tùy chỉnh để sao chép, từ đó giúp giảm lưu lượng, sự trùng lặp 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, sau đó chỉ sao chép dữ liệu này.
Các tác nhân là các thành phần của MS SQL Server có thể hoạt động như các dịch vụ nền cho hệ thống quản lý cơ sở dữ liệu quan hệ và được sử dụng để lên lịch thực thi tự động các tác vụ, chẳng hạn như sao lưu và sao chép cơ sở dữ liệu MS SQL. Có năm loại tác nhân: Tác nhân ảnh chụp nhanh (Snapshot Agent), Tác nhân đọc nhật ký (Log Reader Agent), Tác nhân phân phối (Distribution Agent), Tác nhân hợp nhất (Merge Agent) và Tác nhân đọc hàng đợi (Queue Reader Agent).
Siêu dữ liệu (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 hàm siêu dữ liệu tích hợp sẵn cho phép bạn trả về thông tin về phiên bản MS SQL Server, các phiên bản cơ sở dữ liệu và các thực thể cơ sở dữ liệu.

Các vai trò trong sao chép cơ sở dữ liệu SQL

Có ba vai trò chính trong sao chép cơ sở dữ liệu MS SQL: Distributor, Publisher và Subscriber.

• Một Distributor là một phiên bản cơ sở dữ liệu MS SQL được cấu hình để thu thập các giao dịch từ các bản phát hành và phân phối chúng cho các subscriber. Distributor đóng vai trò là cơ sở dữ liệu để lưu trữ các giao dịch được sao chép.

  Một cơ sở dữ liệu Distributor có thể được coi là cả Publisher và Distributor 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 duy nhất chạy cả Nhà xuất bản (Publisher) và Nhà phân phối (Distributor). Mô hình nhà phân phối từ xa có thể được sử dụng khi bạn muốn các thuê bao được cấu hình để sử dụng một phiên bản MS SQL Server duy nhất nhằm 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.

• Máy chủ xuất bản (Publisher) là bản sao cơ sở dữ liệu chính mà trên đó bản phát hành được cấu hình, giúp cung cấp dữ liệu cho các máy chủ MS SQL Server khác được cấu hình để sử dụng trong quá trình sao chép. Máy chủ xuất bản có thể có nhiều hơn một bản phát hành.
• Người đăng ký (Subscriber) là cơ sở dữ liệu nhận dữ liệu được sao chép từ một bản phát hành. Một người đăng ký có thể nhận dữ liệu từ nhiều máy chủ xuất bản và bản phát hành. Mô hình một người đăng ký được sử dụng khi chỉ có một người đăng ký. Mô hình nhiều người đăng ký được sử dụng khi có nhiều người đăng ký kết nối với một ấn phẩm duy nhất.

  Đăng ký là yêu cầu nhận bản sao của một ấn phẩm phải được gửi đến người đăng ký. Đăng ký được sử dụng để xác định dữ liệu ấn phẩm cần nhận cũng như vị trí và thời điểm nhận dữ liệu này. Có hai loại đăng ký:

  • Đăng ký đẩy : Dữ liệu đã thay đổi được truyền bắt buộc từ máy 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ý.
  • Đăng ký kiểu Pull : Dữ liệu đã thay đổi trên Nhà xuất bản được Người đăng ký yêu cầu. Trình đại lý (Agent) chạy trên phía Người đăng ký.

  Cơ sở dữ liệu đăng ký là cơ sở dữ liệu đích trong mô hình sao chép MS SQL.

  

Trong mô hình nhiều Nhà xuất bản – nhiều Người đăng ký , Nhà xuất bản có thể đóng vai trò là Người đăng ký trên một trong các máy chủ MS SQL. Hãy đảm bảo tránh mọi xung đột cập nhật tiềm ẩn khi sử dụng mô hình sao chép MS SQL Server này.

Các loại sao chép MS SQL Server

Sao chép MS SQL Server là công nghệ dùng để 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 định kỳ theo lịch trình. Về hướng sao chép, sao chép MS SQL Server có thể là một chiều, một-đến-nhiều, hai chiều và nhiều-đến-một. Có bốn loại sao chép MS SQL Server: sao chép ảnh chụp nhanh (snapshot replication), sao chép giao dịch (transactional replication), sao chép ngang hàng (peer-to-peer replication) và sao chép hợp nhất (merge replication).

Sao chép bản chụp nhanh

Sao chép bản chụp nhanh được sử dụng để sao chép dữ liệu chính xác như trạng thái tại thời điểm tạo bản chụp nhanh cơ sở dữ liệu. Loại sao chép này phù hợp với dữ liệu không thay đổi thường xuyên, khi việc bản sao cơ sở dữ liệu cũ hơn cơ sở dữ liệu chính không phải là vấn đề nghiêm trọng, hoặc khi có một lượng lớn thay đổi được thực hiện trong một khoảng thời gian ngắn. Tính năng theo dõi thay đổi không được sử dụng với sao chép bản chụp.
Ví dụ, sao chép bản chụp 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ủ tại các chi nhánh.

Sao chép giao dịch

Sao chép giao dịch là quá trình sao chép tự động định kỳ, trong đó 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 theo thời gian thực (hoặc gần thời gian thực). Sao chép giao dịch phức tạp hơn so với sao chép bản chụp. Tất cả các giao dịch được thực hiện cũng như trạng thái cuối cùng của cơ sở dữ liệu đều được sao chép, điều này cho phép theo dõi toàn bộ lịch sử giao dịch trên bản sao.
Khi bắt đầu quá trình sao chép giao dịch, một bản chụp sẽ được áp dụng cho máy chủ đăng ký (Subscriber), sau đó dữ liệu được chuyển liên tục từ cơ sở dữ liệu chính sang bản sao cơ sở dữ liệu khi có thay đổi đối với dữ liệu này. Sao chép giao dịch được sử dụng rộng rãi dưới dạng sao chép một chiều.

Các trường hợp sử dụng cho sao chép giao dịch:

• Tạo máy chủ cơ sở dữ liệu có bản sao cơ sở dữ liệu để sử dụng cho việc chuyển đổi dự phòng nếu máy chủ cơ sở dữ liệu chính gặp sự cố.
• Nhận báo cáo về các hoạt động được thực hiện tại các chi nhánh bằng cách sử dụng nhiều máy chủ phát (Publisher) tại các chi nhánh và một máy chủ nhận (Subscriber) tại văn phòng chính.
• Đảm bảo các thay đổi được sao chép ngay khi chúng xảy ra.
• Dữ liệu trên cơ sở dữ liệu nguồn thay đổi thường xuyên.

Sao chép ngang hàng

Sao chép ngang hàng được sử dụng để sao chép dữ liệu cơ sở dữ liệu đến nhiều máy nhận cùng lúc. Loại sao chép này của MS SQL Server có thể được sử dụng khi các máy chủ cơ sở dữ liệu của bạn được phân tán 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 sẽ được truyền đến tất cả các máy chủ cơ sở dữ liệu. Sao chép ngang hàng có thể giúp mở rộng quy mô ứng dụng sử dụng cơ sở dữ liệu. Nguyên lý hoạt động chính dựa trên sao chép giao dịch.

Dưới đây, bạn có thể xem cách sử dụng sao chép ngang hàng của MS SQL Server giữa các máy chủ cơ sở dữ liệu được phân tán trên toàn cầu.

Sao chép hợp nhất

Sao chép hợp nhất là một loại sao chép 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ể kết nối liên tục. Khi kết nối mạng được thiết lập giữa hai máy chủ cơ sở dữ liệu, các tác nhân sao chép hợp nhất sẽ phát hiện các thay đổi được thực hiện trên cả hai cơ sở dữ liệu và điều chỉnh cơ sở dữ liệu để đồng bộ hóa và cập nhật trạng thái của chúng. Sao chép hợp nhất tương tự như sao chép giao dịch, nhưng dữ liệu được sao chép từ Nhà xuất bản sang Người đăng ký và ngược lại.

Loại sao chép cơ sở dữ liệu này là phức tạp nhất trong tất cả các loại sao chép của MS SQL Server và hiếm khi được sử dụng. Ví dụ, sao chép hợp nhất có thể được sử dụng bởi nhiều cửa hàng ngang hàng làm việc với một kho hàng chung. Mỗi cửa hàng được phép thay đổi thông tin trong cơ sở dữ liệu kho hàng và đồng thời, tất cả các cửa hàng phải có trạng thái cập nhật của cơ sở dữ liệu sau khi vận chuyển hàng hóa hoặc giao hàng đến kho. Sao chép hợp nhất có thể được sử dụng trong các trường hợp mà 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ác cơ sở dữ liệu chi nhánh cùng lúc.

Yêu cầu đối với sao chép MS SQL Server

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

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

Đảm bảo cấu hình tường lửa Windows và kích hoạt các cổng thích hợp cho lưu lượng truy cập đến trên mỗi máy chủ trước khi cài đặt MS SQL Server. Các máy chủ tham gia vào quá trình sao chép (replication) của MS SQL phải giải quyết tên máy chủ của nhau bằng tên máy chủ.
Trước khi cấu hình sao chép (replication) cho MS SQL Server, các phần mềm sau đây phải được cài đặt cho MS SQL Server:

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

LƯU Ý: Trong bài viết này, chúng tôi sử dụng MS SQL Server 2016 để cấu hình. Bạn có thể áp dụng nguyên tắc tương tự để cấu hình tính năng Replication trên các phiên bản SQL Server mới hơn.
Hãy lưu ý rằng nếu bạn cài đặt MS SQL Server 2016 trên máy chủ đầu tiên chứa cơ sở dữ liệu nguồn, bạn cũng cần cài đặt MS SQL Server 2016 trên máy chủ thứ hai để cơ sở dữ liệu hoạt động đúng cách. Ví dụ: nếu bạn muốn cấu hình sao chép giao dịch MS SQL, bạn có thể sử dụng máy chủ cơ sở dữ liệu thứ hai (nơi đã cấu hình Máy nhận) có phiên bản nằm trong khoảng hai phiên bản so với máy chủ cơ sở dữ liệu nguồn, nơi đã cấu hình Máy phát. Nếu phiên bản Publisher trên MS SQL Server là 2016, thì Distributor có thể được cấu hình trên các phiên bản 2016, 2017, 2019 và 2022, còn Subscriber có thể được cấu hình trên MS SQL Server 2012, 2014, 2016, 2017 và 2019. Phiên bản của máy phân phối (Distributor) không được thấp hơn phiên bản của máy xuất bản (Publisher). Ví dụ: quá trình sao chép sẽ không hoạt động nếu bạn cài đặt MS SQL Server 2008 trên máy thứ hai.

Các khuyến nghị cơ bản về sao chép cơ sở dữ liệu MS SQL

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

• Có những hạn chế đối với các trường định danh (identity fields) và trình kích hoạt (triggers).
• Các bản sao (Publications) chỉ có thể chứa các bảng có khóa chính.
• Khuyến nghị không nên sử dụng lịch trình tạo bản sao (snapshot) cho các cơ sở dữ liệu lớn để tránh tiêu tốn quá nhiều tài nguyên tính toán.
• 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 máy đăng ký (Subscriber). Khi một giao dịch sửa đổi dữ liệu đang diễn ra và dữ liệu đó đã bị chỉnh sửa hoặc xóa, quá trình sao chép có thể bị dừng cho đến khi vấn đề này được giải quyết.

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

Khi cấu hình sao chép MS SQL lần đầu tiên, bạn nên thực hiện trong môi trường thử nghiệm trước. Ví dụ: chúng tôi cấu hình sao chép trên các máy chủ SQL chạy trên máy ảo. Trong hướng dẫn này, chúng tôi sử dụng hai máy chủ chạy Windows Server 2016 và MS SQL Server 2016 để giải thích về sao chép MS SQL Server.
Hãy cùng xem qua cấu hình của môi trường thử nghiệm được sử dụng để viết bài blog này nhằm hiểu rõ hơn về cấu hình sao chép MS SQL Server.
Máy chủ 1

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

Máy chủ 2

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

Cả hai máy đều có ổ đĩa C: và ổ đĩa D: trong cấu hình ổ đĩa của chúng.
Bạn có thể tạm thời tắt tường lửa Windows khi cài đặt MS SQL Server để thực hành cấu hình sao chép MS SQL Server. Bài viết trên blog này không đi sâu vào cách cài đặt MS SQL Server vì hướng dẫn này tập trung vào việc cấu hình tính năng sao chép (replication) của MS SQL Server. Trong ví dụ này, cả hai máy chủ MS SQL Server đều được cài đặt mà không có PolyBase.
Sau khi hoàn tất cài đặt MS SQL Server, hãy kiểm tra xem bạn đã cài đặt các tính năng cần thiết cho tính năng sao chép của MS SQL Server hay chưa. Lưu ý rằng các dịch vụ của cơ sở dữ liệu, chẳng hạn như sao chép SQL Server và R-Services, phải được chọn trong quá trình cài đặt MS SQL Server. Trong ví dụ này, đường dẫn cài đặt mặc định được sử dụng (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 cài đặt MS SQL Server 2016 và SQL Server Management Studio trên các máy, bạn có thể chuẩn bị các máy chủ MS SQL để thực hiện sao chép cơ sở dữ liệu.

Chuẩn bị cho việc sao chép MS SQL Server

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

1. Tạo người dùng mssql trên cả hai máy chủ và đặt cùng một mật khẩu.
2. Tài khoản người dùng mssql trong ví dụ này là thành viên của các nhóm sau:
   • Administrators (quản trị viên cục bộ trên các máy cục bộ, không phải quản trị viên miền)
   • SQLRUserGroupMSSQLSERVER1
   • SQLServer2005SQLBrowserUser$MSSQL01
3. Bạn có thể chỉnh sửa người dùng và nhóm bằng cách nhấn Win+R , mở CMD , và chạy lệnh lusrmgr.msc .

Hai máy chủ Windows Server được sử dụng trong ví dụ này không nằm 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 máy chủ miền.

Kết nối với MS SQL Server

1. Chạy SQL Server Management Studio.
2. Đăng nhập (xem ảnh chụp màn hình) với tư cách sa bằng cách sử dụng xác thực SQL Server.
   • MSSQL01MSSQLSERVER1 là tên máy chủ và tên phiên bản MS SQL trên máy chủ thứ nhất.
   • MSSQL02MSSQLSERVER2 là tên máy chủ và tên phiên bản MS SQL trên máy chủ thứ hai.

   

Tương tự, bạn có thể kết nối từ máy chủ thứ hai (MSSQL02) đến phiên bản máy chủ MS SQL thứ hai (MSSQLSERVER2). Bạn cũng có thể kết nối đến phiên bản máy chủ MS SQL thứ hai (MSSQLSERVER2) từ máy chủ MS SQL đầu tiên (MSSQL01) bằng cách nhập thông tin đăng nhập trong SQL Server Management Studio. Bạn có thể kết nối đến cả hai phiên bản máy chủ MS SQL (MSSQL01 và MSSQL02) trong cùng một phiên bản của SQL Server Management Studio.
Để thực hiện điều này, trong Object Explorer, nhấp vào Connect > Database engine . Trong hướng dẫn này, chúng ta sẽ kết nối từ MSSQL01 đến MSSQLSERVER1 và từ MSSQL02 đến MSSQLSERVER2 bằng cách sử dụng SQL Server Management Studio để cấu hình các máy chủ MS SQL.

Khởi động dịch vụ Agent

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

Để cấu hình dịch vụ SQL Server Agent khởi động tự động:

1. Nhấn Win+R , chạy cmd, và chạy lệnh services.msc .
2. Mở thuộc tính dịch vụ SQL Server Agent và đặt loại khởi động thành Tự động .

   

Cấu hình người dùng cho MS SQL Server

Sau khi kết nối với phiên bản MSSQLSERVER1 trong SQL Server Management Studio, chúng ta cần cấu hình người dùng:

1. Chuyển đến Object Explorer và mở Security > Logins .
2. Nhấp chuột phải vào Logins và chọn New Login . Chọn Windows authentication .
3. Nhập tên đăng nhập mssql vào phần General .
4. Nhấp vào Tìm kiếm , sau đó nhấp vào Kiểm tra tên để xác nhận, và nhấp vào OK hai lần để lưu cài đặt.

   

5. Bây giờ, người dùng Windows MSSQL01mssql đã được thêm vào danh sách những người dùng có thể đăng nhập vào cơ sở dữ liệu (tương tự, hãy thêm người dùng mssql vào mục logins trên máy thứ hai MSSQL02 trong SQL Server Management Studio).
6. Thêm người dùng mssql vào vai trò máy chủ sysadmins trong phần cấu hình Security của cơ sở dữ liệu trong SQL Server Management Studio.
7. Truy cập MSSQL01MSSQLSERVER1 > Vai trò máy chủ , nhấp chuột phải vào sysadmin , và mở Thuộc tính .
8. Trong trang Thành viên , nhấp vào Thêm , nhập tên người dùng của bạn mssql, và nhấp vào Kiểm tra tên .
9. Chọn hộp kiểm của tên người dùng MSSQL01mssql và nhấp vào OK .

   

10. Thực hiện cấu hình tương tự trên máy thứ hai (trong trường hợp này là MSSQL02).
11. Khởi động lại cả hai máy chủ.

    Bây giờ bạn có thể đăng nhập bằng xác thực Windows trên cả hai máy chủ.

    

Nhập cơ sở dữ liệu từ bản sao lưu

Hãy nhập một cơ sở dữ liệu mẫu từ bản sao lưu và sau đó sao chép cơ sở dữ liệu từ máy chủ đầu tiên sang máy chủ thứ hai. Cơ sở dữ liệu AdventureWorks2016 được sử dụng làm cơ sở dữ liệu mẫu trong ví dụ này.

1. Sao chép tệp sao lưu cơ sở dữ liệu AdventureWorks2016.bak vào thư mục sao lưu MSSQL của bạn. Trong trường hợp của chúng tôi, thư mục này trên máy chủ đầu tiên là D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackup
2. Nhập cơ sở dữ liệu mẫu. Trên máy chủ đầu tiên trong SQL Server Management Studio, truy cập MSSQL01MSSQLSERVER1 , nhấp chuột phải vào Cơ sở dữ liệu, và chọn Khôi phục cơ sở dữ liệu trong menu ngữ cảnh.

   

3. Trong cửa sổ Khôi phục cơ sở dữ liệu , hãy chọn các thông số cần thiết:
   • Nguồn: Thiết bị .
   • Nhấp vào ba chấm để duyệt tệp sao lưu cơ sở dữ liệu.
     • Trong cửa sổ Chọn thiết bị sao lưu , hãy chọn loại phương tiện sao lưu: tệp .
     • Nhấp vào Thêm .
   • Chọn tệp .bak cần thiết – D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackupAdventureWorks2016.bak
   • Nhấn OK , sau đó nhấn OK một lần nữa.
4. Cơ sở dữ liệu AdventureWorks2016 đã được khôi phục thành công.

   

Bạn có thể nhập cơ sở dữ liệu từ bản sao lưu trên máy chủ thứ hai, nơi bản sao cơ sở dữ liệu sẽ được chạy. Cách làm này giúp giảm lưu lượng mạng vì quá trình sao chép sẽ bắt đầu bằng việc sao chép các thay đổi xảy ra kể từ khi bản sao lưu được tạo, thay vì sao chép toàn bộ dữ liệu cơ sở dữ liệu vào một cơ sở dữ liệu trống.
Khôi phục cơ sở dữ liệu từ bản sao lưu trên máy chủ thứ hai và đổi tên cơ sở dữ liệu thành AdventureWorks2016r , trong đó “r” có nghĩa là “bản sao”.
Cuối cùng, chúng ta có:

Sau khi nhập cơ sở dữ liệu, bạn cần thực hiện một số điều chỉnh để chuẩn bị cho các máy chủ MS SQL

1. Trên máy MSSQL01 , truy cập MSSQL01MSSQLSERVER1 > Security > Logins , chọn MSSQL01mssql . Nhấp chuột phải (hoặc nhấp đúp) vào mssql người dùng và chọn Thuộc tính .
2. Trong phần Vai trò máy chủ , chọn hộp kiểm bên cạnh vai trò dbcreator .

   

3. Trên trang Ánh xạ người dùng , chọn các người dùng được ánh xạ đến tài khoản đăng nhập này và đánh dấu vào hộp kiểm cơ sở dữ liệu AdventureWorks2016 (chọn AdventureWorks2016r trên máy chủ thứ hai tương ứng).
4. Trong phần thành viên vai trò cơ sở dữ liệu , hãy đánh dấu vào ô chọn db_owner .

   

5. Nhấp vào OK để lưu cài đặt.

Thực hiện cấu hình tương tự trên máy MSSQL02. Sau đó, bạn có thể cấu hình các thành phần MS SQL Server cần thiết cho việc sao chép cơ sở dữ liệu.

Tìm gói dịch vụ phù hợp với môi trường của bạn

Khám phá các phiên bản và mô hình cấp phép linh hoạt của NAKIVO, được thiết kế để mang lại giải pháp bảo vệ dữ liệu cấp doanh nghiệp với chi phí phù hợp với ngân sách của bạn.

XEM GIÁ

Cấu hình sao chép cơ sở dữ liệu

Cấu hình sao chép ở chế độ đồ họa là phương pháp thuận tiện nhất. Quá trình cấu hình tiếp theo được thực hiện trong SQL Server Management Studio. Ví dụ này sẽ giải thích về sao chép cơ sở dữ liệu theo giao dịch vì đây là một trong những loại sao chép được sử dụng phổ biến nhất trên MS SQL Server.
Giao diện trên máy chủ cơ sở dữ liệu chính (MSSQL01MSSQLSERVER1) và giao diện trên máy chủ thứ hai (MSSQL02MSSQLSERVER2) trong SQL Server Management Studio được hiển thị trong ảnh chụp màn hình bên dưới.

Cấu hình Phân phối

Phân phối có thể được sử dụng cho nhiều Nhà xuất bản và Người đăng ký. Trong ví dụ này, Phân phối được cấu hình trên máy chủ chính nơi lưu trữ cơ sở dữ liệu nguồn. Trên máy chủ chính (MSSQL01MSSQLSERVER1), nhấp chuột phải vào Replication và trong menu ngữ cảnh, chọn Configure Distribution .

Trình hướng dẫn Configure Distribution Wizard sẽ mở ra.

1. Distributor . Trong ví dụ này, hãy chọn phiên bản cơ sở dữ liệu hiện tại đang chạy trên máy chủ chính (MSSQL01MSSQLSERVER1) để đóng vai trò là Distributor. Nhấp vào Tiếp theo mỗi lần để chuyển sang bước tiếp theo trong trình hướng dẫn.
2. Khởi động SQL Server Agent . Nếu bạn chưa cấu hình SQL Server Agent để khởi động tự động, như đã giải thích ở trên, thông báo sau sẽ được hiển thị. Chọn Có, cấu hình dịch vụ SQL Server Agent để khởi động tự động .

   

3. Thư mục bản chụp nhanh . Bạn có thể giữ nguyên đường dẫn mặc định tại đây. Bản chụp nhanh là cần thiết để khởi tạo quá trình sao chép. Đảm bảo có đủ dung lượng trống trên đĩa nơi chứa thư mục snapshot. Dung lượng trống phải tương ứng với ít nhất kích thước của cơ sở dữ liệu được sao chép.
4. Cơ sở dữ liệu phân phối . Nhập tên cơ sở dữ liệu phân phối. Bạn có thể giữ nguyên tên mặc định ( distribution ) và các thư mục chứa tệp cơ sở dữ liệu phân phối và tệp nhật ký.

   

5. Nhà xuất bản . Xác định các Nhà xuất bản sao chép MS SQL Server có thể truy cập Nhà phân phối. Chọn hộp kiểm bên cạnh tên cơ sở dữ liệu phân phối trên phiên bản MS SQL Server chính (nơi lưu trữ cơ sở dữ liệu nguồn sẽ được sao chép). Trong ví dụ này, đó là phiên bản MSSQL01MSSQLSERVER1 và tên cơ sở dữ liệu phân phối là distribution .
6. Wizard Actions . Chọn Cấu hình phân phối để cấu hình phân phối trong bước cuối cùng của trình hướng dẫn. Trong ví dụ này, chúng ta sẽ không tạo tệp tập lệnh để thực thi sau này.

   

7. Hoàn tất trình hướng dẫn . Kiểm tra tóm tắt cấu hình phân phối và nhấp vào Hoàn tất để tạo Distributor.

   

8. Trạng thái Thành công sẽ xuất hiện nếu Distributor đã được tạo và cấu hình thành công.

   

Nếu bạn thấy có lỗi xảy ra khi cấu hình SQL Server Agent để khởi động tự động, hãy chuyển đến phần cấu hình dịch vụ và kiểm tra chế độ khởi động của SQL Server Agent (xem cách cấu hình khởi động Agent ở phần trên trong bài viết này).
Bạn cũng có thể mở thuộc tính của SQL Server Agent trong SQL Server Management Studio và kiểm tra trạng thái dịch vụ cùng các tùy chọn khởi động lại. Nhấp chuột phải vào SQL Server Agent ở cuối danh sách trong Object Explorer và chọn Properties để xem hoặc chỉnh sửa thuộc tính của Agent.

Cấu hình Nhà xuất bản

Sau khi đã cấu hình Phân phối, bạn có thể cấu hình Nhà xuất bản. Nhà xuất bản nên được cấu hình trên máy chủ chính (MSSQL01MSSQLSERVER1) nơi cơ sở dữ liệu master cần được sao chép được lưu trữ. Chọn Sao chép , nhấp chuột phải vào Các bản phát hành cục bộ và, trong menu ngữ cảnh, chọn Bản phát hành mới .

Trình hướng dẫn Tạo bản phát hành mới sẽ mở ra.

1. Cơ sở dữ liệu phát hành . Chọn cơ sở dữ liệu bạn muốn sao chép ( AdventureWorks2016 trong trường hợp này). Nhấn Tiếp theo tại mỗi bước trong trình hướng dẫn để tiếp tục.

   

2. Loại bản phát hành . Ở bước này, bạn có thể chọn các loại sao chép của MS SQL Server cho cơ sở dữ liệu. Hãy chọn bản phát hành giao dịch (transactional publication), đây là loại sao chép được sử dụng rộng rãi.
3. Bài viết (Articles) . Chọn các đối tượng cần thiết, chẳng hạn như bảng, thủ tục, view, view có chỉ mục và hàm do người dùng định nghĩa để phát hành dưới dạng bài viết. Bạn có thể chọn sao chép các trường tùy chỉnh trong bảng và thiết lập thuộc tính bài viết nếu cần. Trong ví dụ này, một số bảng đã được chọn.

   

4. Lọc hàng bảng . Không có bộ lọc nào được thêm vào trong ví dụ này (đây là cấu hình mặc định của bộ lọc). Bạn có thể thêm bộ lọc nếu cần.
5. Trình đại lý chụp nhanh . Chỉ định thời điểm chạy Trình đại lý chụp nhanh. Hãy cấu hình Trình đại lý (Agent) để chạy ngay lập tức. Chọn Tạo bản chụp ngay lập tức và giữ bản chụp sẵn sàng để khởi tạo các đăng ký .

   

6. Bảo mật Trình đại lý . Chọn Sử dụng cài đặt bảo mật từ Trình đại lý bản chụp . Nhấp vào nút Cài đặt bảo mật để chọn tài khoản mà Trình đại lý sẽ chạy dưới quyền đó.

   Trong cửa sổ Bảo mật Trình đại lý bản chụp mới mở ra, nhập thông tin đăng nhập của mssql tài khoản người dùng Windows mà bạn đã tạo trước đó. Chọn kết nối với Nhà xuất bản (Publisher) bằng cách giả mạo tài khoản quy trình (process account) . Nhấp vào OK để lưu cài đặt và quay lại trình hướng dẫn.

   

   Sau khi định nghĩa người dùng cần thiết, bạn có thể thấy người dùng này trong các phần Snapshot Agent và Log Reader Agent .

   

7. Các hành động của trình hướng dẫn (Wizard Actions) . Chọn ô kiểm phía trên để tạo bản xuất bản (publication) trong bước cuối cùng của trình hướng dẫn.
8. Hoàn tất trình hướng dẫn (Complete the Wizard) . Kiểm tra cấu hình bản phát hành và nhấp vào Hoàn tất để tạo bản phát hành mới.

   

Trong cửa sổ Tạo bản phát hành , bạn có thể theo dõi tiến trình tạo bản phát hành mới. Chờ một lát và bạn sẽ thấy trạng thái thành công nếu mọi thứ đã được thực hiện chính xác.

Bản phát hành hiện đã được tạo và bạn có thể xem bản phát hành trong Object Explorer bằng cách truy cập Sao chép > Bản phát hành cục bộ .

Cấu hình người đăng ký

Như bạn đã biết, sao chép trong MS SQL Server có thể là sao chép kiểu pull hoặc push. Nếu bạn cấu hình sao chép kiểu push, bạn nên cấu hình máy đăng ký để chạy các tác nhân trên máy chủ cơ sở dữ liệu chính (trong trường hợp này là MSSQL01). Nếu bạn cấu hình sao chép kiểu pull, máy đăng ký phải được cấu hình để chạy các tác nhân trên máy thứ hai (MSSQL02), tức là máy mà bản sao cơ sở dữ liệu sẽ được tạo ra.
Hãy cấu hình sao chép kiểu push và tạo một đăng ký mới trên máy chủ MS SQL Server đầu tiên (MSSQL01MSSQLSERVER1) nơi cơ sở dữ liệu master được lưu trữ.
Trong Object Explorer, hãy chuyển đến Replication , nhấp chuột phải vào Local Subscriptions và, trong menu ngữ cảnh, chọn New Subscriptions .

Trình hướng dẫn tạo đăng ký mới ( ) sẽ mở ra.

1. Publication . Chọn bản phát hành (publication) mà bạn muốn tạo đăng ký mới. Trong ví dụ của chúng ta, tên của Nhà xuất bản là MSSQL01MSSQLSERVER1 và tên bản xuất bản (đã được tạo trước đó) là AdvWorks_Pub . Nhấp vào Tiếp theo tại mỗi bước trong trình hướng dẫn để tiếp tục.
2. Vị trí của Đại lý phân phối . Chọn loại sao chép bằng cách chọn đăng ký đẩy (push subscription) hoặc đăng ký kéo (pull subscription). Trong ví dụ của chúng ta, chúng ta muốn tất cả các đại lý chạy trên phía máy chủ nguồn, do đó, tùy chọn đầu tiên được chọn để tạo đăng ký đẩy. Điều này cho phép bạn quản lý sao chép MS SQL Server một cách tập trung.

   

3. Người đăng ký . Theo mặc định, máy chủ mà bạn chạy trình hướng dẫn (trong trường hợp này là MSSQL01MSSQLSERVER1) được hiển thị là Người đăng ký, và cơ sở dữ liệu đăng ký chưa được xác định. Hãy thêm một người đăng ký mới và chọn cơ sở dữ liệu đăng ký nằm trên máy chủ cơ sở dữ liệu thứ hai (MSSQL01MSSQLSERVER2). Nhấp vào Thêm người đăng ký và, trong menu ngữ cảnh, chọn Thêm người đăng ký SQL Server .
   • Trong cửa sổ bật lên, nhập thông tin đăng nhập cho phiên bản máy chủ MSSQL thứ hai (trong trường hợp này là MSSQL01MSSQLSERVER2) và nhấp vào Kết nối .

     

   • Chọn hộp kiểm của máy chủ thứ hai nơi bản sao cơ sở dữ liệu của bạn sẽ được lưu trữ (MSSQL02MSSQLSERVER2) và trong menu thả xuống Cơ sở dữ liệu đăng ký , chọn một cơ sở dữ liệu mới hoặc một cơ sở dữ liệu hiện có được khôi phục từ bản sao lưu để sử dụng làm bản sao cơ sở dữ liệu.

     Trong ví dụ của chúng ta, cơ sở dữ liệu AdventureWorks2016r đã được tạo trên máy chủ thứ hai bằng cách khôi phục cơ sở dữ liệu chính (nguồn) AdventureWorks2016 từ bản sao lưu để bắt đầu quá trình sao chép. Quá trình sao chép được khởi động bằng cách chỉ sao chép dữ liệu mới chứ không sao chép toàn bộ cơ sở dữ liệu sau khi bắt đầu quá trình này. Do đó, AdventureWorks2016r được chọn làm cơ sở dữ liệu đăng ký trong ví dụ hiện tại.

     

4. Bảo mật Đại lý Phân phối . Nhấp vào nút có ba chấm (…), sau đó chọn người dùng và các tùy chọn bảo mật khác cho Trình phân phối.

   Trong cửa sổ Bảo mật Trình phân phối mới mở ra, thiết lập Trình phân phối để chạy trên máy chủ MSSQL01 dưới tài khoản người dùng mssql . Nhập mật khẩu cho người dùng Windows mssql . Chọn Kết nối với Máy phân phối bằng cách mạo danh tài khoản quy trình và chọn Kết nối với Người đăng ký bằng cách mạo danh tài khoản quy trình . Nhấn OK để lưu cài đặt.

   

   Bây giờ các thuộc tính đăng ký của bạn đã được thiết lập.

   

5. Lịch trình đồng bộ hóa . Chọn Trình đại lý nằm trên Máy phân phối để Chạy liên tục cho Người đăng ký hiện tại.
6. Khởi tạo đăng ký . Chọn hộp kiểm Khởi tạo và trong menu thả xuống, chọn Ngay lập tức để xác định thời điểm khởi tạo đăng ký. Bạn cũng có thể chọn tùy chọn Tối ưu hóa bộ nhớ nếu cần.

   

7. Các hành động của Trình hướng dẫn . Chọn hộp kiểm phía trên để tạo (các) đăng ký vào cuối trình hướng dẫn.
8. Hoàn tất Trình hướng dẫn . Bạn có thể kiểm tra cài đặt đăng ký và nhấp vào Hoàn tất để tạo đăng ký.

   

9. Chờ cho đến khi đăng ký được tạo. Nếu bạn thấy trạng thái Thành công , điều đó có nghĩa là đăng ký đã được tạo thành công.

   

10. Sau khi cấu hình sao chép trong SQL Server, ba tác vụ sẽ được hiển thị trong Object Explorer, và bạn có thể xem chúng bằng cách truy cập SQL Server Agent > Tác vụ .

    

Hoàn tất cấu hình sao chép

Sau khi đã cấu hình Máy chủ phân phối, Máy chủ xuất bản và Máy chủ đăng ký, bạn có thể kiểm tra trạng thái sao chép của MS SQL Server.

1. Trên máy chủ đầu tiên (MSSQL01MSSQLSERVER1), khởi chạy Trình giám sát sao chép để xem trạng thái sao chép của MS SQL Server. Trong SQL Server Management Studio, chọn phiên bản MS SQL Server của bạn (MSSQLSERVER1), chuyển đến Sao chép , nhấp chuột phải vào Các bản phát hành cục bộ và trong menu ngữ cảnh, chọn Khởi chạy Trình giám sát sao chép .

   

2. Trong trường hợp của chúng ta, có một lỗi Trình đọc nhật ký . Để xem chi tiết lỗi, hãy chọn cơ sở dữ liệu nguồn (Publisher) trong khung bên trái, chọn tab Agents trong khung bên phải, sau đó nhấp đúp vào tên lỗi.

   

3. Trong cửa sổ hiện ra, bạn có thể xem lịch sử tác vụ và thông báo lỗi. Các thông báo lỗi là:
   • Quá trình không thể thực thi sp_replcmds trên MSSQL01MSSQLSERVER1. Nguồn: MSSQL_REPL. Số lỗi: MSSQL_REPL20011).
   • Không thể thực thi với tư cách chủ thể cơ sở dữ liệu vì chủ thể “dbo” không tồn tại, không thể mạo danh loại chủ thể này hoặc bạn không có quyền. (Nguồn: MSSQLServer, Mã lỗi: 15517).

   

   Thông báo lỗi thứ hai cho thấy thiếu một số quyền nhất định. Hãy khắc phục lỗi này.

4. Tạo một truy vấn mới trong MS SQL Management Studio và thực thi truy vấn này. Trong cửa sổ chính, nhấp vào nút New Query .
5. Trong phần truy vấn SQL của cửa sổ chính, nhập truy vấn sau:

   USE AdventureWorks2016

   GO

   EXEC sp_changedbowner 'sa'

   GO

   Nhấp vào nút Execute .

   

   Các lệnh đã được thực thi thành công.

6. Tiếp theo, truy cập MSSQL01MSSQLSERVER1 > Replication > Local Publications > [AdventureWorks2016]: AdvWorks_Pub . Nhấp chuột phải vào tên bản phát hành và trong menu ngữ cảnh, chọn View Snapshot Agent Status . Bạn có thể nhấp vào Action > Refresh để làm mới trạng thái và Reinitialize All Subscriptions để áp dụng bản chụp nhanh cho từng người đăng ký.

   Giờ đây mọi vấn đề đã được giải quyết, không còn lỗi nào được hiển thị và tính năng sao chép của MS SQL Server sẽ hoạt động bình thường.

   

Kiểm tra cách thức hoạt động của tính năng sao chép

Hãy cùng xem tính năng sao chép của MS SQL Server hoạt động như thế nào. Xem nội dung của một bảng trong cơ sở dữ liệu AdventureWorks2016 được lưu trữ trên máy chủ MS SQL đầu tiên ( MSSQL01MSQLSERVER1 ). Trong ví dụ này, chúng ta sẽ chọn toàn bộ dữ liệu từ bảng Person.AddressType . Để thực hiện điều này, hãy thực thi truy vấn:
USE AdventureWorks2016;
GO
SELECT *
FROM Person.AddressType
;
Kết quả của việc thực thi truy vấn được hiển thị trong ảnh chụp màn hình bên dưới:

Thực thi một truy vấn tương tự trên máy chủ thứ hai để hiển thị toàn bộ dữ liệu của bảng Person.AddressType thuộc cơ sở dữ liệu AdventureWorks2016r được lưu trữ trên MSSQL02MSSQLSERVER2.
USE AdventureWorks2016r;
GO
SELECT *
FROM Person.AddressType
;
Nếu so sánh các ảnh chụp màn hình ở trên và dưới đây, nội dung của bảng Person.AddressType là giống hệt nhau trên cả hai cơ sở dữ liệu (cơ sở dữ liệu nguồn trên máy chủ đầu tiên và cơ sở dữ liệu đích là bản sao cơ sở dữ liệu trên máy chủ thứ hai).

Hãy xóa một hàng trong bảng PersonAddressType thuộc cơ sở dữ liệu AdventureWorks2016 (nguồn) trên máy chủ đầu tiên (MSSQL01MSSQLSERVER1). Chạy truy vấn để xóa một hàng có chứa ‘Billing’ trong trường tên và hiển thị nội dung của bảng sau đó:
DELETE FROM Person.AddressType WHERE Name='Billing';
SELECT * FROM Person.AddressType;

Như bạn có thể thấy, hàng đầu tiên có AddressTypeID là 1 và tên là ‘Billing’ đã bị xóa khỏi bảng Person.AddressType trong cơ sở dữ liệu AdventureWorks2016 trên máy chủ MSSQL01 .
Sao chép giao dịch đang hoạt động. Hãy kiểm tra nội dung của bảng Person.AddressType trong cơ sở dữ liệu AdventureWorks2016r trên máy chủ MSSQL02 . Thực thi lại một truy vấn tương tự như trên để xem nội dung của bảng:
USE AdventureWorks2016r;
GO
SELECT *
FROM Person.AddressType
;
Do quá trình sao chép, dòng đầu tiên cũng đã bị xóa khỏi bảng Person.AddressType trong cơ sở dữ liệu thứ cấp đóng vai trò là bản sao cơ sở dữ liệu ( AdventureWorks2016r ). Bạn có thể xem kết quả trong ảnh chụp màn hình bên dưới.

Quá trình sao chép cơ sở dữ liệu trong SQL Server đang hoạt động bình thường.

Kết luận

Có bốn loại sao chép trong MS SQL Server — sao chép bản chụp nhanh (snapshot), sao chép giao dịch (transactional), sao chép ngang hàng (peer-to-peer) và sao chép hợp nhất (merge). Do sao chép giao dịch được sử dụng rộng rãi, chúng tôi đã cấu hình loại sao chép này trong bài viết blog này. Phải cấu hình Distributor, Publisher và Subscriber để sao chép cơ sở dữ liệu hoạt động. Subscriber có thể được cấu hình trên máy chủ nguồn (sao chép đẩy) và máy chủ đích (sao chép kéo).
Tuy nhiên, bạn nên cân nhắc sử dụng cả sao chép và sao lưu cơ sở dữ liệu MS SQL để tăng khả năng thành công của phục hồi dữ liệu cơ sở dữ liệu.

Dùng thử NAKIVO Backup & Replication

Đăng ký dùng thử miễn phí để khám phá toàn bộ các tính năng bảo vệ dữ liệu của giải pháp này. 15 ngày miễn phí. Không có bất kỳ giới hạn nào về tính năng hay dung lượng. Không cần thẻ tín dụng.

DÙNG THỬ MIỄN PHÍ