SQl Server How to maintain the data.

Question

Hi all,

I am working on a product (ASP.NET Web site) developed for educational institutions. There are around 20 educational inst. in my site. For each of them academic year start and end date varies. There are huge number of records in the database for attendance and results.

Now I need to show all previous years data (like attendance, results etc) whenever a student, teacher logs in. There are some reports which compares student performance in various academic years.

Now my problem is how to maintain that huge data ?

I wanted to go with 2 databases. 1 for current academic year, another for all previous yrs.

But my current year DB schema may change for enhancement. So whenever I move the current year data to archive database then it creates problem for me. Please suggest a good way to implement this.

Thanks,

seshu.

Answer 1

If you want to keep two databases in sync, schema-wise, there are plenty of tools available for that. Here is mine, here is Red Gate's and here is Apex's. There are many more available, including one which comes with Visual Studio Team System Database edition (if you have that already - if you don't then one of the ones I have previously mentioned will be a lot cheaper).

Answer 2

Have you thought about table partitioning? It allows you to rapidly move data through sliding windows - so that at the start of a new year, you slide last year's details into an archive partition. (You need to check the SQL Server edition you have to see whether it is enabled)

MSDN details: http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx