You administer a SQL Server 2000 computer that hosts your company's Accounts database. Because the server has experienced power interruptions in the past, you are concerned about protecting the physical integrity of the database files. You want to reduce the risk of physical corruption due to abrupt power loss, but you do not want to slow down normal server operations significantly. What should you do?

Introduction / Context:
Sudden power interruptions can cause partial writes or lost writes on disk subsystems, which may damage the physical integrity of SQL Server database files. At the same time, high-performance servers often use write caching disk controllers to improve throughput. The challenge is to protect data from power loss without sacrificing performance by disabling caching completely. This question asks which strategy best protects physical integrity while keeping the server responsive.

Given Data / Assumptions:

• The server runs SQL Server 2000 and hosts an important Accounts database.
• The environment has a history of power interruptions.
• You care specifically about the physical integrity of database files (that is, avoiding torn pages or lost writes).
• You want to avoid unnecessary performance degradation during normal operation.
• The server likely uses write caching disk controllers for better performance.

Concept / Approach:
Write caching controllers buffer write operations in memory before committing them to disk, improving performance but introducing risk if a power loss occurs before the cached writes are flushed. Battery-backed write cache mitigates this risk by preserving the contents of the cache through short power outages, allowing the controller to write the data to disk when power is restored. This approach offers both performance and protection. Completely disabling write caching reduces performance significantly. Maintenance plans and torn page detection help detect and repair problems after the fact, but they do not directly prevent physical corruption when power fails mid-write.

Step-by-Step Solution:
1. Confirm that your disk controllers support battery-backed write caching and that the batteries are correctly installed and maintained. 2. Ensure that write caching remains enabled so that SQL Server can benefit from improved write performance. 3. Verify that the controller's battery can hold cache contents for a reasonable period in the event of a power failure. 4. Combine the battery-backed controller with a UPS (uninterruptible power supply) if possible, for further protection and time for orderly shutdowns. 5. Continue to follow best practices such as regular backups and occasional integrity checks, but rely on the battery-backed cache as the primary protection against torn writes due to power loss.

Verification / Alternative check:
You can verify that write caching is enabled and that the battery status is good using vendor management tools. Perform performance tests with caching enabled and disabled to see the impact on throughput. In the case of a controlled power test (where safe), you can simulate a power loss to confirm that the controller preserves cached data and flushes it correctly when power is restored. Make sure the vendor documentation confirms that battery-backed cache is designed for this purpose.

Why Other Options Are Wrong:
Option A, running a nightly maintenance plan with DBCC CHECKDB and repairs, helps detect and fix logical errors but does not prevent physical corruption from torn writes at the moment of power failure. Option B, enabling torn page detection, can detect torn pages when they are read but does not prevent them from occurring; it may also slightly impact performance. Option C, disabling write caching on all controllers, does reduce the risk of losing buffered data but significantly slows disk performance and may not be necessary when battery-backed caching is available. Option D directly addresses the root cause by ensuring that cached writes survive short power interruptions without giving up performance.

Common Pitfalls:
Administrators sometimes focus excessively on after-the-fact detection (for example, DBCC CHECKDB) instead of preventing corruption in the first place. Another pitfall is disabling write caching entirely, which protects data but can severely hurt performance, especially on OLTP workloads. Failing to maintain or periodically test the controller batteries is also risky; a dead battery effectively turns a battery-backed cache into an unprotected cache. A balanced strategy uses properly maintained, battery-backed controllers combined with good backup and maintenance practices.

Final Answer:
You should ensure that write caching disk controllers have reliable battery backups so that cached writes survive power interruptions, protecting physical integrity without significantly slowing normal server operations.