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

Phát Hiện & Khôi Phục Nâng Cao Cho Hư Hỏng Dữ Liệu Trong Microsoft SQL Server

Giới thiệu

Hiện nay, SQL Server là một trong những hệ thống quản lý cơ sở dữ liệu quan hệ (RDBMS) phổ biến và được sử dụng rộng rãi nhất nhờ cấu trúc nội bộ tiên tiến và độ tin cậy cao. Nhiều tổ chức đã chọn cơ sở dữ liệu SQL Server để lưu trữ và duy trì dữ liệu kinh doanh quan trọng.

Các doanh nghiệp mong đợi quản trị viên cơ sở dữ liệu (DBA) liên tục cải thiện hiệu suất, bảo trì và bảo mật cơ sở dữ liệu. Khi cơ sở dữ liệu bị hỏng và dữ liệu không thể truy cập, bạn có thể nghi ngờ nhiều nguyên nhân như hư hỏng phần cứng, sự cố đĩa, tấn công virus hoặc lỗi hệ điều hành (OS). Việc sửa chữa cơ sở dữ liệu bị hỏng không phải là nhiệm vụ dễ dàng cho đến khi bạn biết kỹ thuật tốt nhất.

Bài viết này thảo luận về một số nguyên nhân gây hư hỏng cơ sở dữ liệu, giúp bạn xác định hư hỏng trang, khám phá lệnh DBCC CHECKDB của Database Consistency Checker, và trình diễn các kỹ thuật khôi phục và sửa chữa nâng cao.

Hư hỏng cơ sở dữ liệu

SQL Server lưu trữ dữ liệu người dùng dưới dạng các trang. Những trang này nằm trong file dữ liệu .MDF (Primary). Hư hỏng file .MDF có thể dẫn đến hư hỏng toàn bộ cơ sở dữ liệu. Trang dữ liệu file tốt khi được ghi ra khỏi bộ nhớ của SQL Server (tức là lên đĩa) nhưng xấu khi được đọc lại vào bộ nhớ, như minh họa trong hình sau:

Phát Hiện & Khôi Phục Nâng Cao Cho Hư Hỏng Dữ Liệu Trong Microsoft SQL Server

Các nguyên nhân gây hư hỏng cơ sở dữ liệu:

Các loại hư hỏng cơ sở dữ liệu bao gồm:

  • Hệ thống con I/O (Đây là một trong những lý do phổ biến nhất gây hư hỏng cơ sở dữ liệu.)
  • Hệ điều hành Windows
  • Trình điều khiển hệ thống tệp như mã hóa hoặc phần mềm diệt virus
  • Bộ điều khiển SAN hoặc RAID
  • Đĩa cứng
  • Bộ nhớ
  • Tiêu đề file
  • Lỗi SQL Server
  • Lỗi con người

Thông báo lỗi:

Khi truy cập cơ sở dữ liệu bị hỏng, bạn có thể thấy các thông báo lỗi sau:

  • Msg 823 trong SQL Server
  • Msg 824 trong SQL Server
  • Msg 825 (đọc lại thử) trong SQL Server
  • Lỗi 9004 SQL Server
  • Lỗi Hư Hỏng Metadata
  • Lỗi Hư Hỏng Cấp Trang

Theo dõi hư hỏng trang

SQL Server có cơ chế tích hợp để tự động xác định và cảnh báo khi có hư hỏng xảy ra trong các hoạt động I/O như đọc và ghi trang lên đĩa.

Các tùy chọn xác minh cấp trang khác nhau sau đây có sẵn trên SQL Server và giúp bảo vệ các trang trên đĩa:

  • TORN_PAGE_DETECTION
  • Checksum

Khi TORN_PAGE_DETECTION được chỉ định, một bit sẽ được đảo ngược cho 16 x 512-byte disk sector cho một trang file dữ liệu 8 KB bất cứ khi nào trang được ghi lên đĩa. Sau khi trang sau đó được đọc vào bộ nhớ, các giá trị này được so sánh. Nếu các bit được tìm thấy ở trạng thái sai, trang có thể đã được ghi không đúng. Trong trường hợp này, hệ thống tạo ra thông báo lỗi 824 (chỉ ra lỗi trang rách).

Khi Checksum được chỉ định, hệ thống tính giá trị checksum trên nội dung trang và lưu giá trị đó trong tiêu đề trang khi ghi trang lên đĩa. Sau khi trang được tải từ đĩa sau đó, checksum được tính toán lại và so sánh với giá trị được lưu ở tiêu đề trang. Nếu giá trị không khớp, hệ thống tạo ra thông báo lỗi 824 (chỉ ra lỗi checksum).

Cả hai lỗi, 823 hard I/O và 824 soft I/O, đều là lỗi mức độ 24 và được ghi nhật ký trong msdb.dbo. suspect_pagestable.

Bảng msdb.dbo. suspect_pagestable được dùng cho thao tác khôi phục trang đơn lẻ và có thể được ghi nhật ký vào nhật ký lỗi SQL Server và nhật ký sự kiện Windows®.

DBCC CHECKDB

DBCC CHECKDB kiểm tra tính toàn vẹn vật lý và logic của tất cả các đối tượng trong cơ sở dữ liệu.

Đây là một hoạt động tốn nhiều tài nguyên và sử dụng xử lý song song, nhưng bạn có thể chạy nó trong một luồng đơn bằng cách dùng cờ truy vết 2528.

Kiểm tra bảng hệ thống quan trọng cơ bản

Các kiểm tra cơ bản được thiết kế để thực hiện kiểm tra trên các bảng hệ thống quan trọng chứa metadata lưu trữ và trên các đường dẫn phân bổ nơi dữ liệu được lưu trữ trong file MDF.

Các kiểm tra cơ bản được hiển thị trong danh sách sau:

  • DBCC CHECKALLOC: Kiểm tra tính nhất quán cấu trúc phân bổ trong cơ sở dữ liệu. Nó kiểm tra xem cấu trúc phân bổ có hợp lệ không và không có trang file dữ liệu đơn lẻ nào được phân bổ cho hai bảng.

  • DBCC CHECKTABLE: Kiểm tra tính nhất quán của bảng và chỉ mục. Nó xác thực cấu trúc của bảng và các chỉ mục liên quan. Nó xác định xem dữ liệu chỉ mục có hàng khớp trong bảng không và kiểm tra các khóa thứ tự chỉ mục. Nếu có FILESTREAM được sử dụng trong bảng, nó xác thực sự tồn tại của liên kết.

  • DBCC CHECKCATALOG: Kiểm tra tính nhất quán giữa các catalog hệ thống.

  • DBCC CHECKFILEGROUP: Tương tự như DBCC CHECKDB, kiểm tra này thực hiện kiểm tra tính nhất quán cho một nhóm file và kiểm tra phân bổ cho cơ sở dữ liệu.

Mã mẫu

DBCC CHECKDB [ ( database_name | database_id | 0 [ , NOINDEX | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ] ) ] [ WITH { [ ALL_ERRORMSGS ] [ , EXTENDED_LOGICAL_CHECKS ] [ , NO_INFOMSGS ] [ , TABLOCK ] [ , ESTIMATEONLY ] [ , { PHYSICAL_ONLY | DATA_PURITY } ] [ , MAXDOP = number_of_processors ] } ] ]

Snapshot cơ sở dữ liệu nội bộ

Sử dụng snapshot cơ sở dữ liệu nội bộ với CHECKDB để thực hiện các kiểm tra và duy trì tính nhất quán giao dịch giúp ngăn chặn các vấn đề chặn và đồng thời. Nếu bạn không thể tạo snapshot cơ sở dữ liệu, hãy đảm bảo bạn có khóa độc quyền cho cơ sở dữ liệu và chia sẻ khóa bảng vì điều này là bắt buộc để thực hiện kiểm tra cấp bảng. CHECKDB sẽ thất bại trên master nếu không có snapshot được tạo.

Danh sách sau chứa các kiểm tra nội bộ được thực hiện bởi DBCC CHECKDB:

  • Kiểm tra bảng hệ thống quan trọng.
  • Kiểm tra logic bảng hệ thống quan trọng.
  • Kiểm tra logic các bảng khác.
  • Kiểm tra phân bổ.
  • Kiểm tra metadata.
  • Kiểm tra xác thực Service broker.
  • Kiểm tra indexed view, spatial index.

Lỗi CHECKDB

Bảng sau hiển thị một số lỗi DBCC CHECKDB:

Phát Hiện & Khôi Phục Nâng Cao Cho Hư Hỏng Dữ Liệu Trong Microsoft SQL Server

Thực hành tốt nhất

Nếu cơ sở dữ liệu VLDB gặp vấn đề thời gian chạy cho CHECKDB, bạn nên sử dụng tùy chọn PHYSICAL_ONLY thường xuyên để giảm thời gian chạy cho cơ sở dữ liệu VLDB production. Tuy nhiên, bạn thường nên chạy DBCC CHECKDB mà không chỉ định tùy chọn nào. Lên lịch chạy CHECKDB tùy thuộc vào môi trường production riêng biệt của bạn.

Các tùy chọn khôi phục nâng cao

Mặc dù hầu hết mọi người sử dụng kỹ thuật khôi phục cơ sở dữ liệu đơn giản cho các vấn đề hư hỏng, các kỹ thuật khôi phục nâng cao sau đây có sẵn:

Khôi phục trang

Bạn có thể khôi phục một hoặc nhiều trang bằng kỹ thuật này. Khôi phục cấp trang là hoạt động online cho bản enterprise edition của cơ sở dữ liệu, và bạn có thể sử dụng hoạt động offline cho các phiên bản khác, ngụ ý rằng cơ sở dữ liệu có thể offline trong quá trình khôi phục.

T-SQL script

RESTORE DATABASE <database_name> PAGE = '<file: page> [ ,... n ] ' [ ,... n ] FROM <backup_device> [ ,... n ] WITH NORECOVERY

Để lấy Page Id, hãy sử dụng các nguồn khác nhau như nhật ký lỗi, Eventtraces, DBCC, và bản ghi bảng msdb..suspectpages liệt kê các trang bị hỏng và ID của chúng.

Lưu ý: Bạn không thể khôi phục trang boot, trang tiêu đề file, một số trang trong bảng hệ thống quan trọng, và bitmap phân bổ dưới dạng khôi phục trang.

Khôi phục từng phần và khôi phục một phần

Tương tự như khôi phục trang, bạn có thể thực hiện khôi phục từng phần và khôi phục một phần online cho enterprise edition và offline cho các phiên bản khác chứa nhiều file hoặc filegroup.

Mỗi khôi phục từng phần bắt đầu với một chuỗi khôi phục một phần RESTORE DATABASE khôi phục bản sao lưu đầy đủ với tùy chọn PARTIAL. Khi khôi phục này hoàn tất, cơ sở dữ liệu nên online một phần, ngụ ý rằng các file còn lại đang ở chế độ chờ khôi phục vì khôi phục đã bị trì hoãn.

Khôi phục từng phần phụ thuộc vào mô hình khôi phục của cơ sở dữ liệu và trình tự khôi phục của nó.

Trình tự khôi phục

Để thực hiện khôi phục một phần của filegroup X và Z và filegroup primary, hãy chạy mã sau:

RESTORE DATABASE DB_XYZ FILEGROUP='X',FILEGROUP='Z' FROM partial_backup WITH PARTIAL, RECOVERY;

Tại điểm X trước đó, những điều sau đây là đúng:

  • Filegroup Z và filegroup primary đang online.
  • Các file trong Filegroup Y đang chờ khôi phục.
  • Filegroup đang offline.

Chạy RESTORE DATABASE DB_XYZ FILEGROUP='Y' FROM backup WITH RECOVERY;

Bây giờ, tất cả filegroup đều online.

Các kỹ thuật sửa chữa nâng cao khác

Sửa chữa có đảm bảo khôi phục dữ liệu không? Câu trả lời là "KHÔNG."

Có thể có nhiều tổ hợp khả năng làm hỏng dữ liệu, và không thể kiểm tra tất cả các tổ hợp. Ví dụ, một bảng hệ thống bị hỏng, và sửa chữa sẽ không hoạt động trên các trang như Boot và PFS.

Dưới đây là một số tùy chọn sửa chữa:

REPAIR_REBUILD

Tùy chọn này thực hiện sửa chữa, nhưng có thể gây mất dữ liệu khi xây dựng lại chỉ mục NC bị hỏng.

REPAIR_ALLOW_DATA_LOSS

Tùy chọn này thực hiện sửa chữa, nhưng có thể gây mất dữ liệu.

Xây dựng lại chỉ mục bảng hệ thống

Bạn không thể sửa chữa chỉ mục cụm của bảng hệ thống, nhưng bạn có thể sửa chữa chỉ mục không cụm trong một số tình huống bằng cách kiểm tra tùy chọn DBCC CHECKTABLE.

Lưu ý: Luôn thực hiện bất kỳ loại kỹ thuật sửa chữa nâng cao nào trên bản sao của cơ sở dữ liệu chứ không phải trên cơ sở dữ liệu gốc để tránh các tình huống khẩn cấp.

Xây dựng lại dữ liệu từ chỉ mục không cụm:

Nếu chỉ mục cụm hoặc heap bị hỏng, sửa chữa là tùy chọn duy nhất để khôi phục dữ liệu từ chỉ mục không cụm (NC). Tuy nhiên, trong một số trường hợp, sửa chữa sẽ không hoạt động nếu metadata bị hỏng.

Bạn có thể sử dụng câu lệnh select để ép buộc chọn chỉ mục NC không bị hỏng, nhưng điều đó có thể phụ thuộc vào độ bao phủ cấp cột của chỉ mục NC.

DBCC Page

Sử dụng DBCC PAGE … WITH TABLERESULTS có thể xác định phạm vi khóa. Bạn có thể xây dựng nó từ chỉ mục không cụm và kiểm tra các trang bị hỏng để thử lấy dữ liệu từ các trang.

Kết luận

Sau khi đọc bài viết này, bạn nên có hiểu biết tốt hơn về hư hỏng cơ sở dữ liệu và cách khôi phục cơ sở dữ liệu của mình bằng cả kỹ thuật đơn giản và nâng cao. Ngoài ra, các kỹ thuật này giúp đưa cơ sở dữ liệu online sau khi hư hỏng. Bạn nên luôn có một kế hoạch sao lưu mạnh mẽ để tránh bất kỳ sự cố ngừng hoạt động nào do hư hỏng.

Sử dụng tab Feedback để đưa ra bất kỳ nhận xét hoặc đặt câu hỏi nào.

Tối ưu hóa môi trường của bạn với quản trị, quản lý và cấu hình chuyên gia

Các chuyên gia Dịch vụ Ứng dụng của Rackspace (RAS) cung cấp các dịch vụ chuyên nghiệp và được quản lý sau đây trên danh mục ứng dụng rộng:

  • Nền tảng Thương mại điện tử và Trải nghiệm Kỹ thuật số
  • Lập kế hoạch Nguồn lực Doanh nghiệp (ERP)
  • Business Intelligence
  • Salesforce Quản trị Quan hệ Khách hàng (CRM)
  • Cơ sở dữ liệu
  • Lưu trữ Email và Năng suất

Chúng tôi mang lại:

  • Chuyên môn kiến nghị: Chúng tôi đơn giản hóa và hướng dẫn hành trình hiện đại hóa của bạn, tập trung vào các khả năng mang lại giá trị tức thì.
  • Fanatical Experience™: Chúng tôi kết hợp phương pháp Quy trình trước. Công nghệ sau.® với hỗ trợ kỹ thuật chuyên dụng để cung cấp giải pháp toàn diện.
  • Danh mục không đối thủ: Chúng tôi áp dụng kinh nghiệm đám mây rộng lớn để giúp bạn chọn và triển khai công nghệ phù hợp trên đám mây phù hợp.
  • Giao hàng nhanh chóng: Chúng tôi gặp bạn ở đâu bạn đang ở trong hành trình và điều chỉnh thành công của chúng tôi cho phù hợp với của bạn.

Trò chuyện ngay để bắt đầu.