Mở đầu

Dạo gần đây mình có "va chạm" phải với một loại Database hoàn toàn mới đối với bản thân là SQL Server của nhà Microsoft. Một cách gọi khác của loại database này là Microsoft SQL (MSSQL). Công việc chính của mình là migrate dữ liệu của những database này sang một môi trường khác, quá nhiều thứ mới và nhiều ý tưởng đã được thực hiện, thành công có thất bại có. Bài viết này mình sẽ hệ thống hóa lại toàn bộ các cách mình biết để có thể dịch chuyển (Migrate) dữ liệu cho SQL Server. Bài viết này mình sẽ tập trung vào SQL Server On-premise nhé, các phiên bản trên Cloud thì mình chỉ thêm 1 chút với kinh nghiệm cá nhân.

Chắc hẳn những giải pháp dưới vẫn có thể còn thiếu, bạn có đóng góp thêm giải pháp nào có thể comment góp ý thêm ở dưới nhé.

Trước khi migrate database

Có một vài điều bạn sẽ cần để biết trước khi migrate một SQL Server database. Đây là các câu hỏi bạn sẽ cần phải trả lời cho trường hợp của bạn:

• Quá trình migrate có yêu cầu Zero Downtime cho hệ thống hay không?

=> Hiểu đơn giản là khi chuyển endpoint database sang database mới thì hệ thống có bị 'chết' không? Tất nhiên là dữ liệu phải đảm bảo không mất mát (consistency) nhé. Đối với một vài hệ thống không yêu cầu SLA cao hay một số hệ thống có người dùng cực thấp (~0) vào nửa đêm thì việc migrate có downtime hoàn toàn chấp nhận được Ta không nên cố đấm ăn xôi thực hiện phương án Zero Downtime nếu tốn nhiều nỗ lực và chi phí.

Đây là câu hỏi quan trọng mà bạn phải trả lời để chọn được cách migrate phù hợp. Trong phần sau mình sẽ chia các cách migrate làm 2 phần: Zero Downtime và có Downtine.

• Những cái gì sẽ cần chuyển?

=> Đối với SQL Server khi dịch chuyển sẽ có khá nhiều thông tin cần chuyển như: schema (Tables, Indexs,...), Data (Dữ liệu trong các bảng), Store Procedures, View, Database's users. Bạn cần làm rõ với Dev hoặc người quản lý những gì cần dịch chuyển. Rất nhiều trường hợp bạn chỉ cần dịch chuyển schema và data hoặc 1 vài dữ liệu không còn sử dụng cũng sẽ không cần chuyển.

Đây là câu hỏi cũng không kém quan trọng, nó có thể tiết kiệm cho bạn rất nhiều thời gian.

Trong bài viết mình sẽ dùng 1 vài từ tiếng anh cho dễ diễn tả:

• Migrate: Dịch chuyển dữ liệu nói chung
• Source database: SQL Server nguồn cần sao chép dữ liệu
• Destination database: SQL Server đích được sao chép dữ liệu đến

Giải pháp dịch chuyển zero downtime

Trong hầu hết các trường hợp việc migrate không có downtime luôn được ưu tiên vì nhiều ưu điểm hơn so với có downtime.

Tính năng replication

Đây là một tính năng có trong các bản SQL Server giúp cấu hình Destination SQL Server như một Subscriber sẽ theo dõi và sao chép sự thay đổi dữ liệu từ Source Database (Publisher). Có thêm 1 vai trò thứ 3 là Distributor đứng giữ Publisher và Subscriber, tuy nhiên mình thấy đa số Distributor được cài trực tiếp trên Source Database chạy cùng Instance với Publisher luôn.

Tutorial từ Microsoft: https://learn.microsoft.com/en-us/sql/relational-databases/replication/tutorial-replicating-data-between-continuously-connected-servers?view=sql-server-ver16

Cách thức hoạt động:

1. Sau khi cấu hình Publication cho 1 database, Source database sẽ tiến hành tạo 1 bản snapshot chứa dữ liêu hiện tại của database.
2. Bản snapshot này sẽ được đẩy lên 1 shared folder. Share folder này cần đọc được từ cả 2 database, đối với Azure SQL Managed Instance thì bạn có thể cấu hình File Share để lưu trữ snapshot file này.
3. Tiếp theo sau khi cấu hình Subscription cho destination database. Database này sẽ kéo bản snapshot trên shared folder về để khởi tạo schema và dữ liệu ban đầu.
4. Sau khi khởi tạo xong, Destination database sẽ liên tục giám sát và kéo về transaction logs (Log file lưu những câu SQL được áp dụng trên SQL Server) từ Source Database và áp dụng những sự thay đổi đó vào database hiện tại.

Ưu điểm:

• Quá trình phản ánh dữ liệu sang destination database rất nhanh (vài giây)
• Setup khá dễ dàng với công cụ Server Management Studio (SSMS)
• Có thể dịch chuyển được tất cả các loại dữ liệu: Schema, data, store procedures, View, User.
• Có thể phát hiện được sự thay đổi schema.
• Quá trình tạo snapshot ban đầu khá lâu kể cả database nhỏ (~20 phút)

Nhược điểm:

• 2 Database cần có network thông với nhau (Do 2 database sẽ trực tiếp giao tiếp để chuyển transaction log files cho nhau)
• Để sao chép được sự thay đổi schema cần tạo lại snapshot 1 cách thủ công

Replication thông qua Full Backup và transaction log files

Trước hết bạn cần hiểu 2 loại file backup và transaction log File trong SQL Server:

• Full Backup File: Đây là bản sao lưu dữ liệu của một database tại một thời điểm nhất định. Backup giúp bảo vệ dữ liệu, cho phép khôi phục lại database trong trường hợp có sự cố như lỗi hệ thống, mất mát dữ liệu, hoặc tấn công từ bên ngoài. File Fullback thường sẽ có đuôi là .bak
• Transaction Log File: Đây là file ghi lại mọi thay đổi xảy ra trong database, bao gồm các giao dịch như INSERT, UPDATE, và DELETE. Mỗi giao dịch trong SQL Server đều được ghi lại trong log file trước khi được thực thi để đảm bảo tính toàn vẹn của dữ liệu. File transaction log thường có đuôi là .trn

Ngoài ra còn có Differential backup file. Bạn đọc thêm ở đây: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server?view=sql-server-ver16

Với 2 loại file này bạn có thể sử dụng với các dịch vụ trên Cloud như Database Migration Service của GCP để sao chép dữ liệu lên Cloud SQL Server. Hoặc hoàn toàn bạn có thể viết script để thực hiện định kỳ backup từ source database rồi restore sang destination database.

Cũng tương tự như phương pháp replication bên trên, tuy nhiên ở phương pháp này thì bạn sẽ phải làm bằng tay

Cách thức hoạt động:

1. Bạn thực hiện** tạo bản Full Backup lần đầu** từ source database dùng câu lệnh BACKUP DATABASE SQLTestDB TO DISK = 'c:\tmp\SQLTestDB.bak'
2. Restore bản này sang source database sử dụng câu lệnh RESTORE DATABASE SQLTestDB FROM DISK = c:\tmp\SQLTestDB.bak';
3. Thực hiện lấy transaction log file định kỳ bằng command BACKUP LOG SQLTestDB TO ...;
4. Định kỳ lại restore transaction log file này sang source database bằng command RESTORE LOG SQLTestDB FROM ... WITH RECOVERY;

Ưu điểm:

• Với khả năng backup lên S3 sử dụng TO URL nên khá dễ dàng có thể ship log file qua destination database.
• Dễ dàng sao chép dữ liệu qua destination database thông qua các script chạy định kỳ
• Việc lưu lại các file transaction giúp bạn có thể restore lại database ở bất cứ thời điểm nào

Nhược điểm:

• Bạn sẽ phải tắt encryption nếu database đang bật chế độ encryption
• Một số dịch vụ SQL Server trên Cloud không cho phép chạy command BACKUP DATABASE một cách thủ công (Ví dụ như Azure Managed Instance, Azure SQL Database )

Always On Availability Group

Microsoft Document: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups?view=sql-server-ver16

Cách này mình chưa có cơ hội thử nhưng trông cũng khá hay. Về cơ bản sau khi tạo Availability Group thì Destination server sẽ hoạt động như replica. Khi có sự cố xảy ra thì sẽ tự động failover qua replica server.

Change Data Capture (CDC) và Change Tracking

Change Data Capture (CDC) và Change Tracking cơ bản có cơ chế hoạt động giống nhau, đều bắt sự thay đổi trong 1 table và lưu lại thành các records để từ đó có thể sao chép qua destination database. Tuy nhiên Change Tracking thì là phiên bản đơn giản hơn của tính năng Change Data Capture, dành cho các mục đích đơn giản.

Change Tracking chỉ lưu lại 3 hành động U (Update), I (Insert), D (Delete) và cột thay đổi dữ liệu chứ không lưu lại dữ liệu trước thay đổi. Change Data Capture lưu lại nhiều trường với thời gian chi tiết cùng với phiên bản dữ liệu trước thay đổi.

Hình dưới thể hiện sự thay đổi được ghi nhận lại qua tính năng Change Data Capture

Để kích hoạt tính năng CDC cho 1 table sử dụng câu lệnh

-- Enable CDC at the database level
USE master;
GO
EXEC sys.sp_cdc_enable_db;
GO -- Enable CDC on a specific table (replace 'SchemaName', 'TableName', and 'PrimaryKeyColumn' as appropriate)
USE YourDatabaseName;
GO
EXEC sys.sp_cdc_enable_table @source_schema = N'SchemaName', @source_name = N'TableName', @role_name = NULL, -- Use NULL if you don't need role-based access @filegroup_name = N'cdc';
GO

Hình dưới thể hiện sự thay đổi được ghi nhận lại qua tính năng Change Tracking

Để kích hoạt tính năng Change Tracking cho 1 table sử dụng câu lệnh

-- Enable Change Tracking at the database level
ALTER DATABASE YourDatabaseName
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO -- Enable Change Tracking on a specific table (replace 'SchemaName' and 'TableName' as appropriate)
ALTER TABLE SchemaName.TableName
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
GO

Ưu điểm

• Với tính năng có thể tích hợp với các công cụ bên thứ 3 như: Debezium, Fivetran,... để sao chép dữ liệu sang destination database.
• Ngoài ra bạn cũng có thể code 1 đoạn script để phát hiện sự thay đổi và liên tục replicate sang destination database.

Nhược điểm

• Khi sử dụng các công cụ bên thứ 3 chi phí thường khá cao
• Mức sử dụng tài nguyên ở source database sẽ cao hơn do phải theo dõi sự thay đổi và xử lý các query từ các công cụ bên ngoài.

Giải pháp dịch chuyển có downtime

Đối với giải pháp có gây downtime mình áp dụng phương thức backup & restore thức là backup dữ liệu của source database rồi ngay lập tức restore vào destination database.

Khoảng thời gian downtime sẽ là thời gian backup dữ liệu từ source database cộng với thời gian restore vào destination database.

Sử dụng các công cụ command line

bcp

Công cụ này cho phép dump dữ liệu của 1 bảng rất nhanh, mình viết script và test thì để backup và restore dữ liệu 1 database hơn 4GB tầm hơn 1 triệu record mất chỉ khoảng 2 phút

Câu lệnh backup dữ liệu

bcp [YourDatabaseName].[SchemaName].[TableName] out "C:\backup\table_data.csv" -S YourServerName -U YourUsername -P YourPassword -c

Câu lệnh restore dữ liệu

bcp [YourDatabaseName].[SchemaName].[TableName] in "C:\backup\table_data.csv" -S YourServerName -U YourUsername -P YourPassword -c

sqlcmd

Công cụ này cũng có thể được sử dụng để backup data của 1 table. Cá nhân mình gặp khá nhiều lỗi với công cụ này nên không thích lắm

Backup

sqlcmd -S YourServerName -U YourUsername -P YourPassword -d YourDatabaseName -Q "SET NOCOUNT ON; SELECT * FROM SchemaName.TableName" -o "C:\backup\table_data.txt" -s"," -W

Restore

sqlcmd -S YourServerName -U YourUsername -P YourPassword -d YourDatabaseName -Q "BULK INSERT SchemaName.TableName FROM 'C:\backup\table_data.txt' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')"

Sử dụng các công cụ UI

Ngoài ra SQL Server cũng có các công cụ có giao diện hỗ trợ backup và restore dữ liệu. Nổi tiếng nhất phải để đến SQL Server Management Studio (SSMS)

SSMS hỗ trợ backup database ra 2 loại file là bacpac (Extract Data-tier Application) và SQL file (Generate Scripts).

Bacpac là file nén có dữ liệu nhỏ độc quyền của SQL Server có đuôi là .bacpac. Cá nhân mình khi sử dụng file này để restore sang SQL Server khác môi trường gặp khá nhiều lỗi vặt nên không khuyến nghị sử dụng nếu 2 SQL Server của bạn nằm trên 2 môi trường khác nhau.

SQL file là file với các câu lệnh SQL để bạn có thể chạy trực tiếp trên destination server. Lưu ý cách này chỉ áp dụng với dữ liệu nhỏ, dữ liệu lớn khi import file SQL này có thể gây treo server đấy . Hoặc bạn phải chia nhỏ hay giới hạn số lượng command chạy đồng thời để tránh xảy ra sự cố.

Kết

Ngoài ra mình tin vẫn còn nhiều các cách khác để có thể dịch chuyển dữ liệu cho SQL Server. Bài viết trên là các cách mà mình biết hoặc đã thử để migrate dữ liệu cho SQL Server. Hy vọng bài viết đã đem lại giá trị cho bạn.

Nếu thấy bài viết hay hãy cho mình 1 UpVote với Follow mình để theo dõi các bài viết khác nữa nhé Have a nice day!