Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a 90 GB SQL Server database that I want to make more manageable. It stores stock data from 50+ different stocks from 2009 and 2010, and each stock is a separate table. Some tables have hundreds of millions of rows, and other have just a few million.

What I want to do is somehow split the database, so that I don't have a single database file that is 90 GB.

What I want is to be able to somehow magically split all the tables so that I can backup the 2009 data once and not have to keep on including it in the backup every time I backup the entire database, however, I would like the 2009 data to be included whenever I do a query.

Is partitioning the database the way to go? Will it do the above for me, or will I need some other solution? I research partitioning, but I wasn't sure if that would solve all my problems. I wasn't able to find anything that would tell me whether or not it would migrate prexisting data, or whether it only worked for newly inserted data.

Any help or pointers would be much appreciated.

Thanks in advance,

Ken

share|improve this question

migrated from stackoverflow.com Jun 14 '10 at 1:24

This question came from our site for professional and enthusiast programmers.

1  
Belongs on Serverfault and I have no more close votes. –  John Saunders Jun 13 '10 at 20:27
2  
What version of SQL Server? Because only Enterprise provides table partitioning: blog.sqlauthority.com/2008/01/25/… –  OMG Ponies Jun 13 '10 at 20:30
add comment

2 Answers

Kimberly Tripp put out a great webcast series that answers questions just like these. Instead of making a general recommendation fro your specific case, I think it would be better to check out this webcast. I'm sure it will give you the guidance on partitioning and aging to make the right decision for your situation.

share|improve this answer
add comment

Partitioning should solve your problem of splitting the table while still having access to all the data in one query, but if you don't have Enterprise, you can still use partitioned views

To solve your backup issue once the table is split into multiple tables, then move your tables to separate filegroups and then you can backup the filegroups which aren't changing separately from the filegroups which are getting new data.

Is there any reason you have each stock in its own table? (Because now you have 50 tables to manage the partitioning on) If the stocks were in a single table, you could partition that one table by month, say.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.