Click here to monitor SSC
  • Av rating:
  • Total votes: 59
  • Total comments: 4
Feodor Georgiev

Designing Databases for Rapid Resilience

15 October 2012

As the volume of data increases, DBAs need to plan more actively for rapid restores in the event of failure.  For this, the intelligent  use of filegroups is important, particularly when the Enterprise Edition of SQL Server offers the hope of online restores. How, though, should you arrange your data on the different filegroups? What happenens if the primary filegroup gets corrupted? Why backup and restore indexes?

Because the volume of data is increasing relentlessly, we are forced to change the way we store our data to guarantee that it stays available. In this article, I will focus mainly on why a good physical design of a database is vital for its availability, and, in turn, to the well-being of the entire organization.

First, let’s create an imaginary context for the subject matter of this article involving a company that resembles no particular existing company.

  • A company called XPEF works within the financial sector
  • XPEF has a client base of 1.5 million private users, and some 100 businesses, all of which rely on XPEF’s data
  • Aside from the busy OLTP system that serves the clients and the business, the XPEF relies heavily on a ReportMart database. Data is offloaded nightly to it from the transactional system; the data in the ReportMart is used for various purposes, and it is the backbone of the organization’s backoffice functions (customer services claims, reporting, fraud analysis, trending, client data analysis for offloading to other vendors etc.)
  • Finally, let’s suppose that the ReportMart database grows at the rate of at least 5 million rows per day, and the demand is to keep at least 3 years of data at all times, of which the past 365 days should be accessible for making data changes, and the rest of the data should be Read-only.

After a heated discussion, the following guidelines are established and given to the database team to implement:

  • The data from the past night’s load is not critical, because only 1% of the daily work is related to it: In the case of data loss, the batch job which does the daily import can bring the data from the main system in less than 10 minutes
  • The data from between 2 days ago to 1 month ago is vital to the daily operations: In case of data loss it must be restored within 1 hour, otherwise there will be heavy penalties from other vendors and from the state. About 80% of the workload revolves around this data.
  • The data from between 1 month ago to 1 year ago is important for trend and fraud analysis, but since it represents only about 15% of the workload, it needs to be brought online within 4 hours in case of the data loss.
  • The rest of the data is important but, since it consumes only about 4% of the daily workload, it can be restored within 8 hours in the event of data loss.

Having said this, here is how the database team sees the requirements and the challenges:

  • Regardless of which segment of the data fails, make sure that it is restored as fast as possible and without affecting the availability of the rest of the data
  • The priority is to have the data from 2 to 30 days back available first, then the data for the past year, then the ReadOnly data and lastly, the data from the past 24 hours.
  • The database team needs to identify, and address, a potential data loss before the business administration detects it, and they need to maintain data availability proactively.

The database team knows that, in order to meet all requirements, they need to take advantage of the piecemeal restore.

Only the Enterprise edition of SQL Server provides Online restore functionality, i.e. the rest of the database may remain Online and operational while one of the filegroups is restored from a backup. The recovery time is very dependent on the speed of both the backup media storage and the destination system storage. The most important aspect, however, is this: How much data is to be restored at once, and how can the data be ‘split’ in a smart way in order to restore without a tremendous time penalty?

The answer lies, to a great extent, in the smart use of filegroups!

From here on this article will cover the different options the XPEF DBAs have in regards to ‘splitting’ the data and backing up / restoring it.

We will cover several scenarios, i.e.:

  • Scenario 1: All user data is in the PRIMARY filegroup
  • Scenario 2: All user data is in a single filegroup, different from the PRIMARY
  • Scenario 3: the user data is split in different filegroups by importance and by recoverability priority

After covering these scenarios, we will cover some important points about backups in general, proactive monitoring and caveats of smart database modeling.

Scenario 1: All user data is in the PRIMARY filegroup

Right off the bat, we can say that keeping all user data in the PRIMARY filegroup is unnecessary, and even comes with potential business risks. The reasons are simple:

  • With today’s constantly growing volume of data, no one can afford to continue to keep inexorably expanding data in one filegroup.
  • The more data we have in a single filegroup, the longer time it will take before it is available to the end users.
  • The PRIMARY filegroup contains metadata and vital system data, critical to the proper operation of the database (think about it – the PRIMARY filegroup contains up-to-date references to all the objects in the database; whereas your backup may not contain the latest changes to the system objects).
  • And finally, the big question: what happens to the database if there is data corruption on page(s) in the PRIMARY filegroup and there is no other choice but to restore the PRIMARY filegroup? What happens to the rest of the filegroups and the objects within them?

We will come to the rather alarming answer to this question in due course.

The basic steps of a piecemeal restore:

The basic idea of a piecemeal restore is to partially perform maintenance on a database by recovering one or more of its filegroups from a full or differential backup.The sequence in which the filegroup restore is done depends on the database recovery model (FULL/BULK, SIMPLE or changed from FULL to SIMPLE) and on the SQL Server edition (Enterprise or other). For this article I will discuss the FULL recovery mode with Enterprise edition. (This setup gives the most colorful experience, since it uses the logs and the ONLINE availability.)Here is an outline of the restore sequence of a database in FULL recovery in Enterprise edition:

  1. Mark the damaged filegroup OFFLINE:
    USE master
    MODIFY FILE (name=N'FìleGroup2File', offline)
  2. Restore the filegroup only from a backup file (full, then differential)
  3. Backup the tail of the log of the damaged database by running:
  4. Then restore all log backups WITH NORECOVERY since the last FULL or DIFEERENTIAL backup
  5. Restore the tail of the log WITH RECOVERY, which brings the recovered filegroup ONLINE

So, let’s get back to the big question: ‘what happens to the database if there is data corruption on page(s) in the PRIMARY filegroup or if the disk drive fails and there is no other choice but to restore the PRIMARY filegroup? What happens to the rest of the filegroups and the objects within them? ‘As you may have guessed: a disaster happens. When the primary filegroup is damaged, the entire database becomes unreadable since the metadata is damaged.

When this happens, you may see similar messages in the SQL Server logs when trying to access any objects within the database:

Error: 823, Severity: 24, State: 2.The operating system returned error 1006(The volume for a file has been externally altered so that the opened file is no longer valid.) to SQL Server during a read at offset 0x00000000148000 in file 'P:\XPEF1.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

That DBCC CheckDB is going to take a long time. To make matters even worse, you’ll find that, the entire database has to go OFFLINE in order to restore the PRIMARY filegroup from a backup. There is no way to restore the PRIMARY filegroup and have all other data ONLINE.

Scenario 2: All user data is in a single filegroup, different from the PRIMARY

So, as we have already seen in Scenario 1, it is completely inappropriate to keep all data in PRIMARY filegroup, but is it enough to create another filegroup, let’s call it UserFileGroup1, and keep all data there instead?

Well, it is definitely a step forward, but not too big a step.

As mentioned earlier, the XPEF company of our example has clear definitions of the data ‘weight’ according to the age of the data itself.

So, if we just create the UserFileGroup1 and place all data there, we avoid the problem of data corruption in the PRIMARY filegroup, but we still have not solved the problem of how long it will take to restore the UserFileGroup1 in case of a disaster.

Hence, let’s look at a third scenario and try to solve the backup and recovery problem.

Scenario 3: the user data is split in different filegroups by importance and by recoverability priority

As mentioned earlier in the business requirements, the data has different value to the business, according to the age of the data.

The data from 2 days ago to 1 month old is the most critical to the business (80% of the workload depends on it!), hence it has to be restored first. This means that in order to be restored fast, the data has to be stored in its own filegroup. Let’s call it UserFileGroup1.

In second place, the data from 1 month to 1 year old has to be restored (only 15% of the workload depends on it), so it has to be in its own filegroup. UserFileGroup2.

In third place, the data older than 1 year has to be restored (only 4% of the workload depends on it). It has to be in its own filegroup. Let’s call it UserFileGroup_Historical.

And in the end, there is the data from the past 24 hours, which is of the least importance to the business. This data can be in the UserFileGroup1 filegroup.

By ‘splitting’ the data in such manner, we guarantee that the data will be restored in accordance to the SLAs and the company will save money and time.

Easier said than done:

Of course, I specifically did not promise in the beginning of this article that it was easy or cheap to segregate data by importance and have a resilient database system. Here are a few points which can be used as food for thought, especially since the value of data is different for each company / organization.

  • On creation of tables and indexes we can specify which filegroup they should reside on
  • The Standard edition of SQL Server supports filegroups, but only in Enterprise edition can a table be ‘spread’ over several filegroups; in Standard edition each table and index belong to only one filegroup
  • Each company values the data differently, and it is not necessarily by the age of the data, as it is in the example above; some other company may want to ‘split’ the data according to a region, or by a specific customer / vendor, etc. It all depends on the definition of the data’s importance to the specific company.
  • Non-clustered indexes save us a lot of IO when they exist, but they generate a lot of IO when they are restored from a backup. Consider keeping backups without the non-clustered indexes and after restore, just re-create them from scripts
  • In Standard edition there is no ONLINE restore (i.e. while restoring one filegroup the rest of the database cannot be ONLINE at the same time), however it is still faster to restore only a portion of the database which is most needed and most critical than to have to wait to restore the entire database
  • In keeping all data in a single filegroup (whether it is the PRIMARY or not) there is a single point of failure; in case of data corruption in the backup file (yes, it does happen!), the ‘healthy’ filegroups can be restored from backup and the problematic one can be worked on at a later time
  • Each filegroup can have one or several files which means that the different filegroups can be ‘assigned’ to different performance speeds; this means that you don’t have to buy the fastest disks for your entire data, but only for the most important part. In the example above, only the UserFileGroup1 in Scenario 3 will reside on a SSD, and the UserFileGroup_Historical may reside even on a much slower cheaper disks (hopefully not on an external USB harddrive )

Lessons learned: Important points about backups in general, proactive monitoring and caveats of smart database modeling

And if this article so far is not enough of a reason to start separating the User Data from the PRIMARY filegroup, here is another reason: imagine having to restore 3 years of historical data all at once while your phone rings every 5 minutes with the question: “When is ANY data going to be available ONLINE?”

The bottom line is this: when designing your databases try to create separate filegroups according to purpose, volume and SLA requirements.

Think hard before designing tables and indexes: how much data, how important, what restore time is required and so on.

Also, make sure to set up SQL Server Agent alerts for high severity errors, and make sure you act immediately when you get the alert. The Severity 24 error, for example, is the only way to find out before the users that there is a problem with your database – data corruption or potential hardware failure. Keep in mind that if the data is in the cache the users may not find out about the hardware failure for a while, but the error log will have indications of the problem.

Feodor Georgiev

Author profile:

Feodor has been working with SQL Server since 2002, starting on the 2000 version and mixing it up as newer versions - 2005, 2008 and 2012 - were released. He specializes in database performance tuning, documentation and scalability management. He also works as project leader and mentor on SQL Server and Business Intelligence projects on Microsoft-based solutions. HIs specialties include: Database Architecture, Microsoft SQL Server Data Platform, Data Model Design, Database Design, Integration Solutions, Business Intelligence, Reporting, Performance Optimization, Big Data. When he is not busy with his DBA work, keeping up with the latest SQL Server tricks or sharing tips on forums, he writes articles on

Search for other articles by Feodor Georgiev

Rate this article:   Avg rating: from a total of 59 votes.





Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.

Subject: One of the fantastic read
Posted by: Pinal Dave (not signed in)
Posted on: Thursday, October 18, 2012 at 3:29 AM
Message: Very very informative article.

Here is the key line of the blog - The bottom line is this: when designing your databases try to create separate filegroups according to purpose, volume and SLA requirements.

Subject: Adds complexity
Posted by: GrumpyOldDBA (view profile)
Posted on: Monday, November 12, 2012 at 3:39 AM
Message: It's good and it's valid but the danger is introducing complexity. I've often seen this approach taken with a database without considering why, for example putting secondary indexes on another filegroup or adding files. In certain circumstances this can actually degrade performance. The whole restore and backup process becomes much more complex and as such may also introduce more potential points of failure.
The worry I always have is that readers will just adopt this practice without really thinking it through.

Subject: good one
Posted by: yazalpizar (view profile)
Posted on: Tuesday, November 13, 2012 at 2:00 AM
Message: Very good example on how to design and think ahead about filegroups and how to manage them in order to succesfully get back online as quick as possible.

Just one comment. You pointed out on the 3rd scenario: "And in the end, there is the data from the past 24 hours, which is of the least importance to the business. This data can be in the UserFileGroup1 filegroup."

Shouldn't be a different group? Why to mix the most critical data with the least important data? Wouln't that slow down the restore of the first filegroup?

Subject: re: good one
Posted by: sibir1us (view profile)
Posted on: Monday, November 26, 2012 at 11:42 PM
Message: Yazalpizar, this is great note, and I think that this can be built depending on the specific requirements of each system.
What I had in mind was that since the data for the past 24 hours takes only 10 min to re-import (by re-running the regular ETL process, and not by restoring backup) I thought it would be much easier to add the data to the UserFileGroup1 filegroup.
Again, your comment is valid, and in a real system it most likely be setup as you suggest.


Simple-Talk Database Delivery

Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
How to Build and Deploy a Database from Object-Level Source in a VCS

It is easy for someone who is developing a database to shrug and say 'if only my budget would extend to buying fancy... Read more...

 View the blog

Top Rated

Predictive Analysis Basics
 Statistics holds out the promise of teasing out significant relationships and to determine cause and... Read more...

The Enterprise DBA Mindset as a Practical Problem-solving Approach
 In order to keep the demands of the job under control, any DBA needs to automate as many as possible of... Read more...

In-Memory OLTP - Row Structure and Indexes
 There are several decisions to be made when designing indexes for Memory-optimized tables in In-Memory... Read more...

Automatically Creating UML Database Diagrams for SQL Server
 SQL Server database developers seem reluctant to use diagrams when documenting their databases. It is... Read more...

SQL Server Security Audit Basics
 SQL Server Server Audit has grown in functionality over the years but it can be tricky to maintain and... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.