Data-Integrity Enhancements - SQL Server 2008

Microsoft has recently provided two interesting features within the SQL Server database engine:

  • A checksum-based data page verification scheme, in addition to the torn-page detection option from previous versions of SQL Server
  • The ability to put a database into an emergency, administrator-only access mode We detail both features in the sections that follow.

Verifying a Database’s Pages

The ALTER DATABASE statement includes syntax for page verification, with two options: one to enable torn-page detection and a newer checksum verification option. The checksum verification can detect most of the same types of failures as torn-page detection, as well as various hardware-related failures that torn-page detection cannot. However, it is more resource intensive than the older option, so as with any other change, you should test it carefully before rolling it out to production environments.

Enabling the checksum-based page verification scheme for a database called Sales could be done with the following T-SQL:

ALTER DATABASE Sales
SET PAGE_VERIFY CHECKSUM

To enable torn-page detection, use the TORN_PAGE_DETECTION option:

ALTER DATABASE Sales
SET PAGE_VERIFY TORN_PAGE_DETECTION

Note that only one of the two page-verification types can be enabled at any given time. To turn off page verification altogether, use the NONE option:

ALTER DATABASE Sales
SET PAGE_VERIFY NONE

To determine the current page-verification setting for a given database, use the page_verify_option column of sys.databases:

SELECT page_verify_option
FROM sys.databases
WHERE name = 'abc'

The column will have a value of 0 if the NONE option is set, 1 for the TORN_PAGE_DETECTION option, and 2 for the CHECKSUM option.

Putting a Database into an Emergency State

Unfortunately, even with data page verification and a very stable database management system like SQL Server, problems do sometimes occur. Should a problem arise, the DBA can set the database to the new emergency state.

This state makes the database read-only and restricts access to members of the sysadmin fixed server role. Although this sounds like a combination of the read-only and restricted user modes, there is a very important enhancement available with the emergency state option. This option can be set on databases marked suspect, thereby allowing the DBA to get in and fix errors or pull out vital data if errors cannot be fixed.

To set the database to the emergency state, use the EMERGENCY option of ALTER DATABASE. To set this mode for a database called Sales, the following T-SQL would be used:

ALTER DATABASE Sales
SET EMERGENCY

To turn off the emergency state, use the ONLINE option:

ALTER DATABASE Sales
SET ONLINE

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics