Using Louis Vuitton and an Army bag to store your historical Data

This is a guest post by Christoph Hanser. Christoph is currently working as a freelancer in .NET, SQL Server, and BizTalk projects. He is originally from Hamburg but has studied also for a semester in Sweden at the Uppsala University. His blog is http://eai-technologies-and-their-pitfalls.blogspot.com/.

What does a fancy Louis Vuitton and an armee bag have in common? It’s good to carry stuff around. While the first one is a small, sexy lightweight, in which (presumably) women can easily put their daily stuff, the latter is a heavier, not so easy to repack but it can contain your entire wardrobe.

Short switch to your database: Talking about data bases filled with transactions, you might sometimes wish to have something similar: One Louis Vuitton database with the younger entries that is easily and fast accessible and an army bag that is filled with masses of old, forever unchanged entries that you seldom read, that shouldn’t affect the overall database performance, and that you don’t want to move into an extra archive.

SQL Server’s partitioning feature helps you realize this “fashion pattern”: Every, let’s say, month you create a new partition for new transactions. This partition is allocated on your “active filegroup”, which is rather small and resides on the fastest disk that you have. Your older partitions are allocated on a “historical filegroup” that is read-only, because you don’t change that old data, and could reside on a slower medium.

Louis Vitton and an Armee Bag

Do you travel light, or do you cary all your stuff along...

This now allows you to modify your backup mechanisms because you only have to backup the active filegroup permanently since the historical filegroup is read-only. This saves a lot of time, and furthermore the duration of your backup gets more or less constant because the active Filegroup keeps the same since (while the historical filegroup is growing and growing and growing).

Moreover, this also shortens your downtime in case of a disaster. In that case, you can do piecemeal restore and, thus, start the database earlier. If, for example, the active filegroup crashes, you can restore it fast (because it’s smaller). Or, if the historical filegroup crashes, you can anyway start the database because your application might only need the active filegroup. However, be aware that your application doesn’t query data from the historical filegroup.

Speaking in the parable, your system puts active transactions into your Louis Vuitton bag and after a month it moves its content into your armee bag. While the latter grows, the Louis Vuitton keeps small. Furthermore, you only “backup” the Louis Vuitton bag, since your army bag is already backuped somewhere and doesn’t change. And, if you loose your Louis Vuitton you find the backup fast; if you loose your army bag, you don’t worry, because still you can leave the house with your Louis Vuitton while your army bag is restored.

Additional to that, there are more advantages concerning indexes:

– Specific indexes: You can use different indexes for each partition. While your active filegroup is optimized to return rows to be inserted and changed, your historical filegroup is optimized to be read data.

– Faster index rebuild: Since the indexes on the historical filegroup are not changed, you only rebuild the indexes in the active filegroup.

(Thanks to Christian Br├╝ckner for the nice Louis Vuitton parable!)

 

1 comment to Using Louis Vuitton and an Army bag to store your historical Data

  • Feodor

    Great post!
    And the files in the filegroups are the compartments and the inner pockets of your bags. If you have only one pocket, then you take some time to dig out what you are looking for, regardless of whether you are looking in the bigger bag or the smaller one. On the other hand, if you have several compartments, you can find things easier.