archive old data

[ersatz]

Hello,

I need your help.

I will try to explain you what is my problem

I 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?

[Chandra Vennapoosa]

Hello,

I would use partitions and add Year to the part of the key .. so you would have all the data available

some thing like this

table1_2008
table2_2009
table3_2010

then you would create a view with union all for the above with name as your original table name

you use that view to select, insert , update

That way all the data will be there , as you just have to add one table for each year and update the view with new table select

Let me know if that helps to get started

Thanks,
Chandra

[ersatz]

Hello Chandra,

Thank you very much for your answer Your solution seems to be a good idea.
I'll try it!
Yesterday I created a db with a second file group where I'll put column that contain files. What do you think about that? All information in the same table but with 2 files group.
Thanks again!

[Chandra Vennapoosa]

Hello,

Please give me some more details. You want to span one table across multiple file groups ..?. I don't see any issue which file groups you use

Thanks,
Chandra

[Chandra Vennapoosa]

Here is sample code

CREATE TABLE dbo.REPORT_DATA_FY01 (
EMPID INT NOT NULL ,
EMPNAME VARCHAR(20) NULL ,
FYEAR VARCHAR(4) not null CHECK (FYEAR = 'FY01')
CONSTRAINT PK_DATA_FY01 PRIMARY KEY CLUSTERED (EMPID, FYEAR)
)

CREATE TABLE dbo.REPORT_DATA_FY02 (
EMPID INT NOT NULL ,
EMPNAME VARCHAR(20) NULL ,
FYEAR VARCHAR(4) not null CHECK (FYEAR = 'FY02')
CONSTRAINT PK_DATA_FY02 PRIMARY KEY CLUSTERED (EMPID, FYEAR)
)



CREATE VIEW dbo.REPORT_DATA
AS
SELECT * FROM REPORT_DATA_FY01
UNION ALL
SELECT * FROM REPORT_DATA_FY02
GO


INSERT INTO DBO.REPORT_DATA(1,'NAME','FY01')
INSERT INTO DBO.REPORT_DATA(1,'NAME','FY02')

all of your insert, update , delete will be on the view only

view could be your actual table name you have now .. so there will not be any changes for your app , other then loading the current data to new table structure with year in it .

Cheers
Chandra

[ersatz]

hello. thanks again for your asnwer.
unfortunatly my boss didn’t agree this idea; he told me that in this way every year I must maintain this database (to create a new table).i forgot to tell you that this database will be used in the future, is not for the past
so no choice!
regards,
ersatz

[Chandra Vennapoosa]

It takes only few mins for each table create one table .. the advantage is having all the data at one place and single application can handle other wise you will end up with different apps one for live and the other for archive .. you can never have combined report unless you add the numbers ..

when you compare the work needed for partitions and the other is considerable

Thanks,
Chandra

[ersatz]

I agree with you. Even now I try to convince my boss. The project is not yet started!

[saloni]

It takes only few mins for each table create one table .. the advantage is having all the data at one place and single application can handle other wise you will end up with different apps one for live and the other for archive .. you can never have combined report unless you add the numbers .



____________
saloni