Lab SQL 2014 P10 -Thực Hiện Bảo Trì Database

Những hư hỏng trong Database thường rất it xảy ra, nhưng việc phát hiện và khắc phục ngay các hư hỏng này là việc làm rất quan trọng của DBA. SQL server có thể làm việc trong thời gian dài mà không cần bảo dưỡng, tuy nhiên để hệ thông làm việc an toàn và ổn định cần có lịch bảo dưỡng định kỳ

SQL server Index cũng có thể làm việc liên tục mà không cần bảo dưỡng, nhưng hệ thống sẽ làm việc tốt hơn nếu định kỳ loại bỏ sự phân mảnh trong database.

SQL server sử dụng Maintenance Plan Wizard tạo ra SQL Server Agent jobs để thực hiện các tác vụ bảo trì database theo định kỳ.

Thực hiện xong bài lab này, bạn có thể biết cách sử dụng lệnh DBCC CHECKDB để kiểm tra lỗi database, loại bỏ phân mảnh trong database (Maintaining Indexes), và lập lịch bảo trì database định kỳ.

Thực Hiện Bảo Trì Database

A. Sử dụng lệnh DBCC CHECKDB:

A. Sử dụng lệnh DBCC CHECKDB:

Tại SSMS, mở file DBCCCheckDB.sql trong folder module 11, chọn các lệnh trong phần Run DBCC CHECKDB with default options, ấn F5 để kiểm tra tính toàn vẹn của AdventureWorks database và hiển thị thông tin chi tiết,

— Run DBCC CHECKDB with default options

DBCC CHECKDB(‘AdventureWorks’);

GO

Chọn các lệnh trong phần Run DBCC CHECKDB without informational messages, ấn F5 để kiểm tra tính toàn vẹn của AdventureWorks database và chỉ hiện thị thông tin khi có lỗi

— Run DBCC CHECKDB without informational messages

DBCC CHECKDB(‘AdventureWorks’) WITH NO_INFOMSGS;

GO

Chọn các lệnh trong phần Run DBCC CHECKDB against CorruptDB, ấn F5 để kiểm tra tính toàn vẹn của CorruptDB database và thấy có một số lỗi trong dbo.Orders table.

— Run DBCC CHECKDB against CorruptDB

DBCC CHECKDB(‘CorruptDB’) WITH NO_INFOMSGS;

GO

Chọn các lệnh trong phần Try to access the Orders table, ấn F5 để truy vấn vào dbo.Orders table trong CorruptDB database không thể truy vấn vì có lỗi về về tính nhất quán logic.

— Try to access the Orders table

SELECT * FROM CorruptDB.dbo.Orders;

GO

Chọn các lệnh trong phần Access a specific order, ấn F5 để truy vấn đến một record được chỉ định truy vấn thành công, điều này chứng tỏ chỉ có một số trang trong database bị lỗi về tính nhất quán logic

— Access a specific order

SELECT * FROM CorruptDB.dbo.Orders WHERE OrderID = 10400;

GO

Chọn các lệnh trong phần Repair the database, ấn F5 để repair lại database (lưu ý: chỉ thực hiện lệnh này như là một chọn lựa cuối cùng khi không có bản backup nào trước đó).

— Repair the database

ALTER DATABASE CorruptDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

DBCC CHECKDB(‘CorruptDB’, REPAIR_ALLOW_DATA_LOSS);

GO

ALTER DATABASE CorruptDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;

GO

Chọn các lệnh trong phần Access the Orders table, ấn F5 để truy vấn lại vào dbo.Orders table trong CorruptDB database. Truy vấn thành công vì các lỗi đã được sửa chữa.

— Access the Orders table

SELECT * FROM CorruptDB.dbo.Orders;

GO

Chọn các lệnh trong phần Check the internal database structure, ấn F5 để kiểm tra lại tính toàn vẹn của CorruptDB database. Lần này sẽ không có thông báo lỗi vì cấu trúc database đã được nhất quán

— Check the internal database structure

DBCC CHECKDB(‘CorruptDB’) WITH NO_INFOMSGS;

GO

Chọn các lệnh trong phần Check data loss, ấn F5 để kiểm tra dữ liệu bị mất. Lưu ý số record trong order details table không khớp với số record trong order table một số dữ liệu đã bị mất.

— Check data loss

SELECT DISTINCT OrderID

FROM CorruptDB.dbo.[order details] AS od

WHERE NOT EXISTS (SELECT 1

FROM CorruptDB.dbo.Orders AS o

WHERE o.orderid = od.orderid);

GO

B. Maintaining Indexes (bảo trì chỉ mục):

B. Maintaining Indexes (bảo trì chỉ mục): Tại SSMS, mở file MaintainingIndexes.sql trong folder module 11, chọn các lệnh trong phần Create a table with a primary key, ấn F5 để tạo ra table dbo.Phonelog với primary key, mặc định sẽ tạo ra clustered index primary key field.

— Create a table with a primary key

USE AdventureWorks;

CREATE TABLE dbo.PhoneLog

(PhoneLogID int IDENTITY(1,1) PRIMARY KEY,

LogRecorded datetime2 NOT NULL,

PhoneNumberCalled nvarchar(100) NOT NULL,

CallDurationMs int NOT NULL

);

GO

Chọn các lệnh trong phần Insert some data into the table, ấn F5 để insert 10.000 dòng vào table này.

— Insert some data into the table

SET NOCOUNT ON;

DECLARE @Counter int = 0;

WHILE @Counter < 10000 BEGIN

INSERT dbo.PhoneLog (LogRecorded, PhoneNumberCalled, CallDurationMs)

VALUES(SYSDATETIME(),’999-9999′,CAST(RAND() * 1000 AS int));

SET @Counter += 1;

END;

GO

Chọn các lệnh trong phần Check fragmentation, ấn F5 kiểm tra phân mảnh. ở cửa sổ kết quả ghi chú giá trị ở hai cột avg_fragmentation_in_percentavg_page_space_used_in_percent

— Check fragmentation

SELECT *

FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(‘dbo.PhoneLog’),

NULL,NULL,

‘DETAILED’);

GO

Chọn các lệnh trong phần Modify the data in the table, ấn F5 update dữ liệu cho table.

— Modify the data in the table

SET NOCOUNT ON;

DECLARE @Counter int = 0;

WHILE @Counter < 10000 BEGIN

UPDATE dbo.PhoneLog SET PhoneNumberCalled = REPLICATE(‘9’,CAST(RAND() * 100 AS int))

WHERE PhoneLogID = @Counter % 10000;

IF @Counter % 100 = 0 PRINT @Counter;

SET @Counter += 1;

END;

GO

Chọn các lệnh trong phần Re-check fragmentation, ấn F5 kiểm tra lại phân mảnh, ở cửa sổ kết quả ghi chú lại giá trị ở hai cột avg_fragmentation_in_percentavg_page_space_used_in_percent, lúc này đã bị thay đổi do data page đã bị phân mảnh.

— Re-check fragmentation

SELECT *

FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(‘dbo.PhoneLog’),

NULL, NULL, ‘DETAILED’);

GO

Chọn các lệnh trong phần Rebuild the table and its indexes, ấn F5 để thực hiện rebuild lại index trên table này.

— Rebuild the table and its indexes

ALTER INDEX ALL ON dbo.PhoneLog REBUILD;

GO

Chọn các lệnh trong phần Check the fragmentation again, ấn F5 thực hiện kiểm tra phân mảnh lại lần nữa, ở cửa sổ kết quả ghi chú lại giá trị ở hai cột avg_fragmentation_in_percentavg_page_space_used_in_percent lúc này đã giảm do không còn phân mảnh.

— Check the fragmentation again

SELECT *

FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(‘dbo.PhoneLog’),

NULL, NULL,’DETAILED’);

GO

C. Tạo Maintenance Plan:

C. Tạo Maintenance Plan: Tạo lịch bảo trì database định kỳ.

Tại SSMS chọn server SQL, phải chuột vào Maintenance Plans chọn Maintenance Plans Wizard. Tại màn hình Maintenance Plan Wizard ấn next. Tại cửa sổ Select Plan Properties, trong phần name: đạt tên là Daily Maintenance, ấn nút change

THỰC HIỆN BẢO TRÌ DỮ LIỆU

Tại cửa sổ New Job Schedule, trong phần Name: nhập vào Daily, trong phần Occurs chọn daily, Occurs once at: nhập vào 3:00:00AM (thực hiện hàng ngày vào lúc 3 giờ sáng) OK

THỰC HIỆN BẢO TRÌ DỮ LIỆU

Trở lại cửa sổ Select Plan Properties ấn next, tại cửa sổ Select Maintenance Tasks chọn các checkbox Check Database Integrity, Reorganize Index, Update Statistics và Back up Database (Full) next

THỰC HIỆN BẢO TRÌ DỮ LIỆU

Tại cửa sổ Select Maintenance Task Order ấn next, Tại cửa sổ Define Database Check Integrity Task chọn AdventureWorks database OK Next

THỰC HIỆN BẢO TRÌ DỮ LIỆU

Tại cửa sổ Define Reorganize Index Task, trong phần database chọn AdventureWorks database Ok, trong phần Object chọn Tables and views next

THỰC HIỆN BẢO TRÌ DỮ LIỆU

Tại cửa sổ Define Update Statistics Task, trong phần Database chọn AdventureWorks database Ok, trong phần Object chọn Tables and views next,

THỰC HIỆN BẢO TRÌ DỮ LIỆU

Trong phần Define Backup database (Full) Task, chọn AdventureWorks database Ok, qua tab Destination chọn option Create a backup file for every database, trong phần folder nhập vào F:\Module 11\backup next

THỰC HIỆN BẢO TRÌ DỮ LIỆU

Tại cửa sổ Select Report Options, chọn checkbox Write a report to a text file, trong phần folder location: nhập vào F:\Module 11 Next, tại màn hình Complete the Wizard ấn Finish, chờ cho tiến trình hoàn tất ấn close.Ở cửa sổ Object Explorer, chọn Maintenance Plans, Phải chuột vào Daily Maintenanceand chọn Execute, chờ tiến trình kết thúc ấn close. (tiến trình này có thể mất vài phút)

picture026

Phải chuột vào Daily Maintenanceand chọn View History, tại cửa sổ Log File Viewer xem cột Task Name sẽ thấy các các tác vụ đã thực hiện Close

picture033

Vào đĩa F:\Module 11\backup sẽ thấy file backup của AdventureWorksdatabase đã được tạo, trong thư mục F:\Module 11 có file Daily Maintenance_Subplan_1_xxxxx.txt

Lab SQL Server 2014 Phần 11

Mr CTL – CTL.VN

Leave a Reply