Partial Database Availability in SQL Server

Some capabilities of SQL Server are lesser known than others. The concept of Partial Database Availability has been with SQL Server for close to a decade now and was introduced with SQL Server 2005, however I find that amongst my customers, most are unaware of this capability. Recently, I’ve been approached by clients stating that something went wrong with their HDD and suddenly the database was unavailable. The first question I ask them is if they are using Enterprise Edition. Since most of the queries are for mission critical servers, it is common for them to be running on Enterprise Edition. Once I’ve confirmed this, I try to show how powerful this version can be in the use of partial database availability.

Here’s an example that demonstrates what I’m talking about.

The first step in our demonstration is to create a database with multiple filegroups:

CREATEDATABASE [PartialAvailability]

 CONTAINMENT =NONE

 ON  PRIMARY

(NAME=N'PartialAvailability',FILENAME=N'C:\Data\PartialAvailability.mdf', SIZE = 10240KB , FILEGROWTH = 65536KB ),

 FILEGROUP [FG1]

(NAME=N'PA_FG1',FILENAME=N'C:\Data\PA_FG1.ndf', SIZE = 10240KB , FILEGROWTH = 65536KB ),

 FILEGROUP [FG2]

(NAME=N'PA_FG2',FILENAME=N'C:\Data\PA_FG2.ndf', SIZE = 10240KB , FILEGROWTH = 65536KB ),

 FILEGROUP [FG3]

(NAME=N'PA_FG3',FILENAME=N'C:\Data\PA_FG3.ndf', SIZE = 10240KB , FILEGROWTH = 65536KB )

 LOGON

(NAME=N'PartialAvailability_log',FILENAME=N'C:\Data\PartialAvailability_log.ldf', SIZE = 20480KB , FILEGROWTH = 65536KB )

GO

USE [PartialAvailability]

GO

IFNOTEXISTS(SELECTnameFROMsys.filegroupsWHERE is_default=1 ANDname=N'PRIMARY')

      ALTERDATABASE [PartialAvailability] MODIFYFILEGROUP [PRIMARY] DEFAULT

GO

BACKUPDATABASE [PartialAvailability]

TO  DISK='NUL'WITHNOFORMAT,NOINIT, 

NAME=N'PartialAvailability-Full Database Backup',

SKIP,NOREWIND,NOUNLOAD,  STATS= 10

GO

I have taken a NUL backup here for demonstration purposes only. Once this is done, I’ll create three tables to be mapped to each Filegroup. This can be done using the construct with the ON clause with CREATE TABLE syntax.

CREATETABLE t1_FG1(t1 INT, Details VARCHAR (50))ON FG1

GO

CREATETABLE t1_FG2(t1 INT, Details VARCHAR (50))ON FG2

GO

CREATETABLE t1_FG3(t1 INT, Details VARCHAR (50))ON FG3

GO

INSERTINTO t1_FG1 VALUES (1,'On FG1');

INSERTINTO t1_FG2 VALUES (1,'On FG2');

INSERTINTO t1_FG3 VALUES (1,'On FG3');

GO

Our next step is to corrupt our database. I took a simple step as shown below:

At this point if we check the database node, it would look like this:

At this point, if we were to look at how the Error Logs look, we can find the exact problem and then solve it.

Error: 5120, Severity: 16, State: 6.

Unable to open the physical file “C:\Data\PA_FG2.ndf”. Operating system error 2: “2(The system cannot find the file specified.)”.

Error: 17207, Severity: 16, State: 1.

FileMgr::StartSecondaryDataFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘C:\Data\PA_FG2.ndf’. Diagnose and correct the operating system error, and retry the operation.

Getting Database Into Partial Availability

Now is the exact time where we will make our database into Partial Availability. Since we know the Filegroup that is causing the error, we will take the filegroup Offline.

USEmaster

GO

ALTERDATABASE [PartialAvailability]

      MODIFYFILE (NAME=N'PA_FG2',OFFLINE)

GO

Once the Filegroup is brought offline, we can now bring the database online using the following command. Here you can see the filegroup’s state using the DMV, too.

ALTERDATABASE [PartialAvailability]

      SETONLINE

GO

USE [PartialAvailability]

GO

SELECTfile_id,name, physical_name, state_desc

FROMsys.database_files

GO

The output for this looks like this:

As a result of these steps, we will be able to query any data that resides on FG1 and FG3 while any query for objects / data residing on FG2 will fail. This can be tested using:

— Table resides on FG1. Will work without problem

SELECT*FROM t1_FG1

GO

-- Table resides on FG2 and will error

SELECT*FROM t1_FG2

Msg 8653, Level 16, State 1, Line 1

The query processor is unable to produce a plan for the table or view ‘t1_FG2’ because the table resides in a filegroup that is not online.

Now that your database is online and queries can run without issue, we can look into restoring the database to get it fully operational. When we have part of the database offline, this is what we call ‘Partial Database Availability.’

Have you ever used this SQL Server feature. I would love to hear your thoughts.

Datavail Script: Terms & Conditions

By using this software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.

2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.

3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.

4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.