TableSync ^command

Giới thiệu

Lệnh TableSync dùng để đồng bộ dữ liệu từ các bảng được chỉ định tới những bảng tương ứng ở hai Database khác nhau.

Cấu hình đồng bộ được định nghĩa tại tính năng Table Synchronize

Nếu bảng được chỉ định không tồn tại ở database đích, TableSync sẽ tạo mới bảng với cấu trúc giống như bảng nguồn.

Dữ liệu ở bảng đích sẽ giống với bảng nguồn trừ một số trường hợp đặc biệt.

Lệnh này được dùng trong các trường hợp sau (ví dụ):

Đồng bộ dữ liệu của các bảng được chỉ định ở 2 Instance SQL Server khác nhau: Instance Sql tại local và remote.
Copy dữ liệu từ SQL server của khách hàng về test server thay vì dùng Backup/Restore của SQL.
Chuyển dữ liệu của một số bảng sau khi build sang server của khách hàng.

Lưu ý:

TableSync chỉ hỗ trợ SQL Server.
Các cột với loại dữ liệu là TimeStamp/RowVersion sẽ không giống nhau giữa bảng nguồn và bảng đích, nguyên nhân là do những cột dữ liệu này được quản lý bởi SQL Server và không cho phép chỉnh sửa.
TableSync sẽ bỏ qua những cột có kiểu dữ liệu là timestamp.
Ở chế độ sync Tính năng này sẽ không kích hoạt Trigger tại Database đích (nếu có).

Usage Syntax:

pbs.BO.DB.TableSync?TSYNCode=ABC

Trong đó TSYNCode là mã profile sync được định nghĩa ở tính năng Table Synchronize

Dành cho consultant:

Khi được gọi, Runnable TableSync hoạt động theo các bước sau:

Kết nối vào Server nguồn
Đọc dữ liệu từ bảng nguồn. Nếu có nhiều bảng được chọn tại SqlTableSync, TableSync sẽ lần lượt đọc dữ liệu từ từng bảng và thực hiện các bước bên dưới thay vì đọc dữ liệu từ tất cả các bảng rồi mới thực hiện.
Kết nối vào server đích
Tạo bảng tạm ở server đích. Bảng tạm được tạo với tên: <Tên bảng hiện tại><Một số ngẫu nhiên kiểu Guid>. Bảng tạm này sẽ bị xóa tự động sau khi hoàn tất đồng bộ dữ liệu
Copy dữ liệu vào bảng tạm. Copy dữ liệu từ bảng nguồn vào bảng tạm bằng Bulk Copy
Merge dữ liệu ở bảng tạm vào bảng chính thức ở server đích. Tạo Merge script từ bảng đích và bảng tạm. Merge dữ liệu từ bảng tạm vào bảng đích.

Nếu bảng đích không tồn tại, TableSync sẽ tạo bảng đích tự động.

Lý do tạo bảng tạm là vì Bulk copy không hỗ trợ validate dữ liệu khi copy. Do đó, nếu có lỗi phát sinh trong quá trình copy dữ liệu (thường là trùng khóa chính), Bulk copy sẽ bị dừng ngay lập tức và runnable TableSync cũng sẽ bị ngừng theo.

Để tránh trường hợp này, ta phải dùng thêm bảng tạm của Sql để copy dữ liệu từ nguồn sang đích. Bảng tạm sẽ bị xóa khi ngắt kết nối, do đó mỗi lần copy dữ liệu ta không cần bận tâm về việc validate dữ liệu.

Sau khi copy hoàn tất, TableSync sẽ tạo lệnh Merge để đồng bộ dữ liệu giữa bảng tạm và bảng đích. Lệnh Merge sẽ hoạt động theo logic sau:

Nếu tìm thấy record ở bảng đích, Merge sẽ cập nhật lại record này bằng dữ liệu ở bảng tạm (bảng tạm lúc này đóng vai trò như bảng nguồn)
Nếu không tìm thấy record ở bảng đích, Merge sẽ thêm mới record với dữ liệu được lấy từ bảng tạm
Nếu record có ở bảng đích nhưng không có ở bảng tạm, record ở bảng đích sẽ bị xóa.

Câu lệnh Merge có dạng (T-SQL):

MERGE pbs_CUSTOMER AS t
USING pbs_CUSTOMERF9168C5E-CEB2-4faa-B6BF-329BF39FA1E4 AS s
ON t.CUSTOMER_CODE = s.CUSTOMER_CODE AND t.DTB = s.DTB

WHEN MATCHED THEN
    UPDATE SET t.DTB = s.DTB, t.CUSTOMER_CODE = s.CUSTOMER_CODE…

WHEN NOT MATCHED BY TARGET THEN
    INSERT (DTB, CUSTOMER_CODE, CUSTOMER_GROUP, …)  VALUES (s.DTB, s.CUSTOMER_CODE, s.CUSTOMER_GROUP, …)

WHEN NOT MATCHED BY SOURCE THEN
    DELETE
;

Thiết lập ví dụ :

Ví dụ sau mô tả cách đồng bộ dữ liệu giữa 2 bảng dữ liệu trong 2 Database khác nhau chi tiết như bảng sau:

Source	Target
Sql server	Instance Sql tại PC DESKTOP-KVQR979	Instance Sql tại Laptop DESKTOP-T5FKF7O
IPv4	10.0.0.36	10.0.0.24
Database	DEVELOPMENTDB	TSTDB
Tables	pbs_CUSTOMER, pbs_HR_EMPL

Tạo connection tới 2 Source và Target tại DBC
Tạo các connection tại DBC để TableSync có thể đọc và ghi dữ liệu.

Tạo connection tới database nguồn

Tạo connection tới database đích

Tạo cấu hình các bảng cần đồng bộ tại TSYN

Cấu hình bao gồm các thông tin cần thiết giúp TableSync có thể xác định bảng nguồn, các kết nối nguồn và đích

Sau khi định nghĩa xong, người dùng nhấn Save để lưu lại.

Đồng bộ dữ liệu

Kiểm tra các table tại database nguồn trước khi thực hiện đồng bộ dữ liệu.

Thao tác này giúp kiểm tra dữ liệu trước và sau khi thực hiện đồng bộ, trong thực tế có thể bỏ qua bước này.

Dữ liệu tại các bảng ở database đích.

Tiến hành đồng bộ dữ liệu bằng cách chạy câu lệnh sau:

pbs.BO.DB.TableSync?TSYNCode=STT2

Câu lệnh trên sẽ gọi pbs.BO.DB.TableSync với cấu hình STT2 vừa được định nghĩa ở bước trên.

Người dùng cũng có thể chạy runnable này ngay trong giao diện của tính năng TSYN bằng cách mở cấu hình TSYN > click vào Transfer như hình dưới

Kiểm tra lại các bảng dữ liệu tại database đích

Troubleshoot

Các lỗi sau đây có thể xảy ra trong quá trình đồng bộ dữ liệu:

Received an invalid column length from the bcp client for colid 57.

Lỗi này phát sinh khi bảng đích có cấu trúc khác bảng nguồn. VD: cột ở bảng nguồn là CONTACT nvarchar(30) nhưng ở bảng đích là CONTACT nvarchar(50)

Giải quyết vấn đề này bằng cách sửa cấu trúc 2 bảng dữ liệu nguồn và đích giống nhau

Xem thêm

Document	Link
Merge Statement Tài liệu giới thiệu nhanh và cách dùng lệnh Merge trong SQL	https://medium.com/@naibedya.kar/https-medium-com-naibedya-kar-synchronizing-two-ms-sql-tables-using-merge-statement-91bc1c287c34
Merge Statement Tài liệu về lệnh Merge của Microsoft	https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017
Bulk Copy (for Dev) Cách dùng Bulk copy để chuyển dữ liệu hàng loạt	https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/single-bulk-copy-operations

_{Updated on : 2020-09-04 07:05:48}. _{by : . at T470-01}.

_{Topic : . pbs.bo.db.tablesync}

TableSync command