Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
joanne
Starting Member
46 Posts |
 Posted - 2010-10-31 : 20:09:46
|
| Hello, I need your help.I will try to explain you what is my problemI must create a sql server 2005 database that will be used by a .net application.This database will contain scanned bills too. The client have around 40 000 files every year and each file is 300-400 k.They need to keep data for 10 years.They want to keep only 3 years data available in the database, archive the rest and if needed they must be able to access old data (from the last 10 years).I don't know if it's a good idea to create a database file for every year of data or to archive the old information in another database. If I choose the last solution, later I can have problems if my database will change ( add/remove columns in tables, for example).Any suggestion will be really appreciated? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-10-31 : 20:29:37
|
| You need to look more carefully at the requirements. But an option might be to keep an image of the data and aggregates.Partition the tables by year then you can remove partitions or keep them on read only drives.You have per year 40000 * 400k = 16,000,000k = 16Gb if that calculation is correct then you should be able to holf 10 years data online if you wanted to.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
joanne
Starting Member
46 Posts |
Posted - 2010-10-31 : 20:49:28
|
| Hello nigelrivett, I realy apreciate your prompt answer.In the same time I want to say that I am one of your fans and I use many of your scripts.Regarding this solution, do you think that I'll not have later problem to select data from this table, with almost 160 gb . Do you think it's a good idea to create a view and apply a filter so that the view includes only the last 3 years of data ?Thanks again! |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-10-31 : 21:11:01
|
| The partotion should handle that for you. Might be worth going for a star schema type solution so transactions are kept separate from attributed. Create aggregate snapshots so that detail days isn't accessed frequently. 160Gb isn't a lot nowadays. Just finished a project importing 10GB a day.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-10-31 : 21:15:45
|
| Also as it's using a .net app make site all access is via SPs so you can keep control of what's happening and restricture of needed.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
joanne
Starting Member
46 Posts |
Posted - 2010-10-31 : 22:41:16
|
| Thank you very much. I will try the partitioned table and i'll let you know. |
|
|
|
|
|
|
archive old data