SQL Server filegroups for backup and recovery

When Microsoft publishes SQL Server2005, it extends the ability to take advantage of filegroups by introducing partitions. In addition, we can now use SQL Server2005 engine to do online database recovery. So with all of these available features, how do you optimize your filegroups for backup and recovery? Let's look at the filegroups and how to set up a backup and recovery strategy when they are in use.

How do files and filegroups work in SQL Server2005? Each database is made up of filegroups. Your database can consist of several filegroups that allow you to detach data. You can choose to separate the tables that are primarily read and the tables that do the primary writing, or to separate the tables from their nonclustered indexes. You can also use table partitioning to separate data.

The use of the filegroup is hundreds of-item. A filegroup is made up of one or more physical files on a disk. Why do you have multiple files in a file group? Although there are many reasons, such as a complete hard disk, to have multiple files, the key reason to understand is that the database consists of filegroups and filegroups are composed of files.

How you use the filegroup will depend to a large extent on the database we are discussing. Filegroups can be used strictly for restorative reasons or you can use them to improve database performance. But sometimes you end up with filegroups or multiple files because of poor capacity planning or unpredictable development. Regardless of what you are doing with filegroups, understand how they affect your ability to back up and restore databases.

Filegroup Backup in SQL Server

When backing up a database, one option is to back up a filegroup instead of the entire database. This is especially useful for large databases. A large database, depending on the hardware, about 500GB, the backup will take several hours. In fact, I've seen a system that spends 4-5 to six hours backing up a database of that size. Backups cost resources and may not be worth spending five hours a full backup every night.

There are several solutions to this problem. I've seen it. Set a full backup once a week, a series of transaction logs and a differential backup performed throughout the week. This works, but you will still need a separate window for a long time to make a full backup every week.

What if you split the database into seven filegroups of the same size? In that case, they are all around 72GB, and you will back up a filegroup every night. This shortens the original long full backup to a backup of seven short filegroups, and after one weeks you will complete the backup of the entire database. I have used some databases that contain massive amounts of data, and a large part of it is read-only.

According to compliance checks, like Sarbanes-oxley, a large financial database size may be 600GB or 700GB, but most often it is historical data dating back to seven years or longer. If you have such a database and only 20% of the data changes regularly, you may be able to improve efficiency by leveraging filegroups. Put the regularly changing tables into your primary file group, and place the historical or archived tables in an archive file group. Now you can back up the primary filegroup every day, perhaps one weeks or one months to back up the archive filegroup.

File group recovery in SQL Server

File group recovery provides some additional complexity, especially in SQL Server 2005. If a single file is broken, you can restore a file or file group to a database. Using a larger database allows for higher complexity. Suppose your file is on a different partition, a separate partition is broken, and you do not have to restore the entire database. This can save valuable time when you recover from failure.

A large database can take a long time to recover-just like a backup-but with multiple filegroups allows you to shorten the time it takes. In addition, SQL Server 2005 introduces online recovery. Note here that the database will be restored one filegroup at a time. In other words, you first restore the primary filegroup, and users can access the data in the filegroup as they continue to recover the other filegroups.

As each filegroup is restored, data for this filegroup is available to end users. This does require careful planning on the part of the administrator. You want to make sure that critical data is restored first, archived and infrequently accessed data. To do this properly, you must have a good understanding of your database and how it is used.

What I have to say may be a bit of a distance for some database administrators, but you have to spend some time with the people who are developing the applications to access the database. You need to understand what data is critical and what data can be delivered later online. Once you fully understand, you can develop a good online backup of the file Group Policy.

File group Planning

When you plan for a filegroup, it is as resilient as it is in performance. Be sure to consider the need to back up and restore your database. Don't go into a situation where filegroups are used for the wrong reasons and ensure that the file group structure prevents you from being able to back up and recover. Another common mistake is to take too many filegroups. If you split the data in the wrong way, it actually degrades performance. So keep in mind that when you do a recovery plan, you have to consider performance.

Avoid working in fantasies, which can be doubled when dealing with databases. Too many different processes and applications can access the database. Even other databases can depend on your database. A plan that optimizes backup and recovery and does not consider performance is not a good plan, and vice versa. Be aware of the overall situation before you enter the implementation phase, and use additional time to prevent major problems in advance. I'm not suggesting that you run to divide all your databases into multiple files or filegroups, but there are places and times you should use them.