September 2, 2020

SQL Server: 2 lý do bạn cần sao lưu Transaction Log

SQL ServerTrong công tác quản trị SQL Server, có một hiểu lầm phổ biến là Transaction Log sẽ tự động được cắt bỏ (truncate) khi tiến hành sao lưu Full database. Nhưng thực tế không phải như vậy. Dù bạn sao lưu Full database hay một Checkpoint xảy ra, Transaction Log vẫn không hề được truncate. Transaction Log vẫn tiếp tục tăng lên khi các Log record mới được lưu vào; cho đến khi quá trình Log truncation diễn ra, hoặc khi…ổ đĩa bị đầy. Hẳn nhiên, nếu ổ địa bị đầy, Database sẽ không thể hoạt động (Lỗi 9002, The log file for database ‘%.*ls’ is full).

Cách duy nhất giúp quá trình Log truncation diễn ra để ngăn chặn tình trạng này là sao lưu Transaction Log thường xuyên. Bên cạnh đó, sao lưu Transaction Log còn mang giúp bạn tăng khả năng phục hồi khi lỗi xảy ra với Database. Vì chỉ có bản sao lưu Transaction Log mới giúp bạn phục hồi Point-in-Time. Và dữ liệu Transaction Log có dung lượng rất nhỏ nên bạn mới có thể sao lưu thường xuyên (điều khó thực hiện với sao lưu Full hay Differential).

Lưu ý: Bài viết này chỉ áp dụng khi Recovery model của Database là FULL hoặc BULK-LOGGED. Nếu Recovery model của Database là SIMPLE, bạn không cần quan tâm vấn đề này. Bởi đơn giản, với Recovery model là SIMPLE, bạn không thể sao lưu Transaction Log. Với Recovery model này, Transaction Log cũng sẽ tự động được truncate khi một Checkpoint xảy ra.

1. Giúp khôi phục Database khi sự cố xảy ra

Sau khi khôi phục từ bản sao lưu Full (và Differential), bạn có thể sử dụng các bản sao lưu Transaction Log kế tiếp để khôi phục Database trở về thời điểm mong muốn. Vì Transaction Log lưu trữ tất cả thao tác cập nhật dữ liệu vào Database nên bạn có thể khôi phục Database trở về đúng thời điểm mong muốn (Point-in-Time Recovery).

Tham khảo 2 ví dụ bên dưới để thấy được vai trò quan trọng của bản sao lưu Transaction Log khi cần khôi phục Database.

Ví dụ 1: Database được sao lưu Full hàng ngày vào 21:00. Sự cố mất dữ liệu xảy ra vào 16:20. Nếu chỉ sao lưu Full, bạn chỉ có thể khôi phục Database trở về thời điểm 21:00 của hôm qua. Nghĩa là bị mất lượng dữ liệu phát sinh từ 21:00 hôm qua đến 16:20 hôm nay (tương ứng 19 giờ 20 phút). Ngược lại, nếu bạn sao lưu Transaction Log cứ 30 phút/lần (vào các thời điểm 21:30, 22:00,… 15:00, 15:30, 16:00), bạn có thể khôi phục Database trở về thời điểm gần nhất là 16:00. Nhờ đó, bạn chỉ mất lượng dữ liệu chỉ tương ứng 20 phút làm việc (phát sinh từ 16:00 đến 16:20). Rõ ràng nhỏ hơn rất nhiều so với lượng dữ liệu phát sinh trong 17 giờ 20 phút ở tình huống đầu.

Ví dụ 2: Database đang hoạt động bình thường, một Developer thực thiện thao tác UPDATE sai với hàng loạt dữ liệu lúc 10:42. Lỗi này không được phát hiện cho đến ngày hôm sau. Trong tình huống này, bạn cần khôi phục Database về thời điểm ngay trước khi thao tác UPDATE đó xảy ra. Nếu chỉ có bản sao lưu Full, bạn không thể thực hiện điều này (trường hợp may mắn nhất là bản sao lưu Full được tiến hành ngay trước khi thao tác UPDATE; nhưng đây chỉ là….may mắn). Ngược lại, nếu có bản sao lưu Transaction Log, bạn dễ dàng khôi phục Database trở về thời điểm ngay trước 10:42 bằng lệnh RESTORE LOG với tùy chọn STOPAT.

Vì Transaction Log có kích thước rất nhỏ (so với Database) nên bạn dễ dàng sao lưu thường xuyên mà không gây ảnh hưởng đến hiệu năng SQL Server cũng như dung lượng lưu trữ và đường truyền. Nếu sao lưu Full database (hoặc thậm chí Differential database), chắc chắn bạn khó có thể sao lưu với mật độ 30 phút/lần hoặc 1 tiếng/lần.

Free eBook: Download ebook 8 lưu ý quan trọng khi sao lưu & phục hồi SQL Server. Những kinh nghiệm hữu ích chia sẻ trong ebook giúp bạn sao lưu an toàn và đảm bảo khả năng phục hồi khi sự cố mất dữ liệu xảy ra với database SQL Server.

2. Giúp kích thước Log file không tăng lên

Với Database có Recovery model là FULL hoặc BULK-LOGGED, các Inactive log sẽ không được đánh dấu có thể tái sử dụng (reusable) cho dù Checkpoint xảy ra và dữ liệu được ghi từ vùng cache trên bộ nhớ xuống đĩa cứng. Quá trình này chỉ xảy ra khi bạn tiến hành sao lưu Transaction Log. Lúc đó, các Inactive log được đánh dấu là có thể tái sử dụng. Do đó, các Log record mới phát sinh có thể được ghi đè vào vùng lưu trữ này mà Log file không phải tăng thêm kích thước.

Lưu ý: Bạn cần lưu ý, Log truncation không có nghĩa là kích thước vật lý của Log file giảm xuống. Thực tế, kích thước Log file vẫn giữ nguyên như vậy. Bạn có thể hình dung quá trình này như hoạt động của một cái kệ sách. Log truncation tức là bạn lấy ra những quyển sách cũ, chừa chỗ cho những quyển sách mới. Và kích thước cái kệ sách không giảm xuống. Để giảm kích thước vật lý của Log file, bạn cần tiến hành thao tác Shrink bằng lệnh DBCC SHRINKFILE (tham khảo thêm tại https://msdn.microsoft.com/en-us/library/ms178037.aspx.