Computer >> Hướng Dẫn Máy Tính >  >> Lập Trình >> Cơ Sở Dữ Liệu

Hiệu Quả Chia Tách Database SQL Server Lớn: Di Chuyển MDF Sang NDF Trong Cùng File Group

Vấn đề: Công việc kiểm tra tính toàn vẹn database thất bại do vấn đề IOPS cho database TestDB lớn hơn 2 TB. Do kích thước tệp lớn, việc quản lý database trở nên khó khăn.

Cách tiếp cận: Để khắc phục, chúng ta quyết định chia dữ liệu giữa 2 tệp dữ liệu. Trạng thái hiện tại của dung lượng ổ đĩa và tệp dữ liệu như sau:

Tệp dữ liệu được lưu trữ ở ổ N:\ và chúng ta sẽ tạo tệp khác ở cùng vị trí. Cách tiếp cận là bắt đầu truyền dữ liệu bằng lệnh emptyfile và dừng thủ công query ở giữa để buộc dừng di chuyển dữ liệu. Lưu ý rằng dừng thủ công query ở giữa sẽ không ảnh hưởng đến database (tính toàn vẹn/nhất quán). Sau đó, chúng ta sẽ shrink tệp mdf để lấy lại không gian trống.

Giải pháp: Làm theo các bước dưới để chia dữ liệu giữa nhiều tệp dữ liệu SQL Server. Trước hết, cần thêm tệp dữ liệu thứ hai (secondary data file) để chèn dữ liệu. Nó sẽ được thêm như ndf (next data file). Chạy script dưới để thêm tệp dữ liệu thêm vào database TestDB:

USE [master]
GO
ALTER DATABASE [TestDB] ADD FILE (
  NAME = N'TestDB_1',
  FILENAME = N'N:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestDB_1.ndf',
  SIZE = 209715200KB,
  FILEGROWTH = 5242880KB
) TO FILEGROUP [PRIMARY]
GO

Khi thực thi script này, nó sẽ thêm tệp dữ liệu mới tên TestDB_1 ở ổ N:\ với kích thước 200 GB (chúng ta lấy con số này dựa trên ngữ cảnh database). Chúng ta đặt file growth 5 GB và datafile sẽ được thêm vào primary file group.

Bây giờ, sau khi thêm tệp dữ liệu, bắt đầu operation DBCC emptyfile trên database TestDB. Cú pháp:

use YOURDATABASE
go
dbcc shrinkfile('mdfFileName',emptyfile)

Với trường hợp của chúng ta:

USE [TestDB]
go
DBCC shrinkfile ('TestDB',emptyfile)

Ở đây TestDB là tên logic của tệp mà chúng ta muốn xóa dữ liệu, tức tệp mdf. Bây giờ khi bắt đầu operation này, chúng ta cần theo dõi bao nhiêu dữ liệu đã di chuyển từ mdf sang ndf. Bạn có thể dùng query dưới để theo dõi:

USE [TestDB]
GO
SELECT [TYPE] = A.TYPE_DESC,
  [FILE_Name] = A.name,
  [FILEGROUP_NAME] = fg.name,
  [File_Location] = A.PHYSICAL_NAME,
  [FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0),
  [USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)),
  [USEDSPACE_%] = CAST((CAST(FILEPROPERTY(A.name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(A.size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)),
  [FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0),
  [FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100),
  [AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -' WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted' ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A
LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id
order by A.TYPE desc, A.NAME;

Với cách tiếp cận của chúng ta, chúng ta muốn ndf đạt khoảng 500 GB, vì vậy khi ndf đạt kích thước này, chúng ta có thể dừng operation emptyfile. Khi emptyfile dừng, chúng ta cần thủ công lấy lại không gian trống trong mdf bằng query dưới:

DBCC Shrinkfile('TestDB', 1500000) --

Chúng ta cần thay đổi kích thước trong các chunk nhỏ hơn.

Bây giờ mdf của chúng ta là 2 TB, chúng ta di chuyển 500 GB sang ndf, do đó 500 GB có thể lấy lại từ mdf, mà chúng ta vừa lấy lại dùng query trên.

Chúng ta có thể lặp lại bước này nhiều lần để di chuyển dữ liệu giữa datafile, dừng thủ công operation giữa chừng theo lưu trữ và lấy lại không gian lại.

Một điều lưu ý khi dùng emptyfile trên mdf là bạn sẽ không thể làm rỗng hoàn toàn nội dung của primary data file với file ID 1. Để lấy số file ID, chạy script này.

select file_id, name,physical_name from sys.database_files

Ở ví dụ này, tên tệp là "mo" và file_id là 1. Khi bạn thử làm rỗng tệp mo có file_id 1, bạn sẽ gặp thông báo lỗi.

Điều này là vì có thông tin hệ thống trong tệp gốc, không thể làm rỗng. Nhưng, nếu bạn thử cùng lệnh trên tệp dữ liệu khác "mo2data", lệnh empty file sẽ thành công.

Kết Luận

Một khi hoạt động di chuyển dữ liệu hoàn tất, hãy chạy các job bảo trì database: – Index optimize job – Integrity check job – Full database backup job.

Sử dụng tab Feedback để bình luận hoặc đặt câu hỏi. Bạn cũng có thể bắt đầu cuộc trò chuyện với chúng tôi.

No