Published: May 29, 2007

Writer: Danny Tambs

Technical Reviewers: Bill Emmert, Tom Davidson, Kevin Farlee, Sanjay Mishra, Alejandro Hernandez Saenz, Steve Schmidt, Eric Jacobsen, Kevin Cox, Lubor Kollar

This white paper outlines the fundamental recovery and design patterns involving the use of filegroups in implementing partial database availability in SQL Server 2005. As databases become larger and larger, the infrastructure assets and technology that provide availability become more and more important.

The database filegroups feature introduced in previous versions of SQL Server enables the use of multiple database files in order to host very large databases (VLDB) and minimize backup time. With data spanning multiple filegroups, it is possible to construct a database layout whereby failure of certain data resources do not render the entire solution unavailable. This increases the availability of solutions that use SQL Server and further reduces the surface area of failure that would render the database totally unavailable.

Note   These features should not be considered as a substitute for a robust backup regime.

SQL Server 2005 introduces two new features that relate to partial database availability: OFFLINE database files and ONLINE PIECEMEAL RESTORE¹. The OFFLINE directive is a new feature of the ALTER DATABASE command. This allows databases that employ multiple filegroups to be online serving queries, while some of the database data may unavailable, in one or more filegroup(s) marked as offline.

Online PIECEMEAL RESTORE² in SQL Server 2005 Enterprise Edition, allows administrators of databases that employ multiple filegroups to restore missing filegroups in stages while the database is online. This is significantly more versatile than the SQL Server 2000 PARTIAL³ restore functionality. Piecemeal restore works with simple, bulk-logged, and full recovery models. Piecemeal restore, combined with the ability to set an unavailable file to offline status gives administrators the flexibility to minimize downtime when a given filegroup fails. It also provides the ability to postpone a restore process until a noncritical time for "business as usual" operations.

In previous versions of SQL Server, when a given filegroup was lost, perhaps due to a disk or other hardware failure, the entire database was rendered unavailable. This was problematic as it incurred immediate and potentially lengthy downtime periods to perform the restore. Other potentially unaffected areas of the same database were also shutdown, causing a total solution outage.

In SQL Server 2005, it is possible to mark a lost or corrupt filegroup as offline, and then bring the rest of the unaffected database online so that the solution can continue to function. Some of the data is not accessible as it is located on the offline filegroup; transactions and queries referencing the unavailable data will fail. However, transactions and queries that do not involve the unavailable data continue to operate normally with no errors or warnings, thereby potentially avoiding a total solution failure.

This is new functionality is provided by the ALTER DATABASE⁴command. A nominated database file can be set to OFFLINE and the database brought online. In previous versions of SQL Server, only the entire database could be set to offline or online. In SQL Server 2005 specific files can be set to offline or online.

When the damaged filegroup is restored, the transaction log ensures that consistency is maintained between all the filegroups, thereby ensuring that the database as a whole remains consistent.

This provides numerous business benefits:

• The application or solution can continue to be available and broker data reqests. Some data will not be available; however, an appropriately designed system can continue to operation albeit with reduced functionality.
• The business can choose when to take the application offline to restore teh missing filegroup. If the data loss is on a filegroup that is typically not in use by the application, the system can continue to operate during the busy period of the day and the problem corrected during a scheduled maintenance window.
• The performance of queries that depend on the unaffected data remain at the same levels as before the loss of a filegroup. Queries requiring data in the offline filegroups will fail.
• Since data is segmented in filegroups, which are logical backup and restore entities, backup and restore processes can complete faster since the amount of data for each filegroup is significantly less than that for the entire database. If those filegroups are marked as read only, they only have to be backed up once, thereby reducing the ongoing overall backup window timeframe.
• In previous versions, if a multi-terabyte database needed to be restored to be brought back online, users had to wait until the database was restored in its entirety before they could access the database. The partial database availability and piecemeal restore features enable a database to be initially restored with only the minimal set of data required to bring critical systems online very quickly. The rest of large database can then be restored at a more opportune time, or in the background while database is online.

Included in this paper:

• The Problem Space
• Introduction
• The ALTER DATABASE Command
• SQL Server Editions and Database Recovery Models
• Considerations for Partial Database Availability
• Designing for an Offline Filegroup
• Recovery Example Scenarios
• Designing for Partial Database Availability
• Conclusion
• More Information

Link to download this whitepaper: Partial Database Availability

Other resources: 

Performing Piecemeal Restores