December 9, 2008 at 9:00 am
Hi everyone,
Here's a little background on what I am working on:
I am working on a data archiving project that is going to pull data out of one database and store it in an archived database, located on the same server (data older than a year in most cases that is not needed).
So the primary database is about 5GB right now, and I can expect it to grow twice in size each year for the next few years. So the archive database will be one year right behind it as I transfer data over.
I was wondering if I should use multiple filegroups for the archive database. An old co-worker of mine used to say to spreadout my tables and indexes over different filegroups, and I am not totally sure why.
So I was wondering if anyone could give the some insight on filegroups, the advantages of using multiple ones, and any disadvantages if there are any.
Many thanks!
December 9, 2008 at 9:07 am
Hi
Hope this link below helps
http://www.mssqltips.com/tip.asp?tip=1112
http://msdn.microsoft.com/en-us/library/ms179316.aspx
Cheers
🙂
December 9, 2008 at 11:03 am
In general, yes, you'll want to have multiple file groups. I'd recommend them for your OLTP system too. As to specifcally how many and where, that's hard to to say. As a general rule of thumb, seperate the log and data. You can go further an seperate the indexes from the data (heaps & clustered index on one filegroup and all other indexes on another). After that, it really depends on the data, the load it supports, the changes coming in... You can start to get into partitioning your data, actually that's something you can on your OLTP system to help aid the migration process (imagine just copying a file from one database to another instead of migrating data). But a lot more details would be needed to begin to make decisions along those lines.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply