We're currently running into some performance problems since our database is getting too big. There are data stored from the last 10 years and I don't see a reason why the data older than 2 years have to be stored in the same tables as the new data.
Now since I don't have very profound experience in administrating databases, I'm looking for the best ways to archive old data.
There are about 310'000'000 records in the database in total.
The database needs 250 GB on the hard disk.
- The Server version is SQL Server 2008 with compatibility level SQL Server 2005 (90), but we're planning on upgrading to SQL Server 2012 soon
I've thought about two possibilities:
Create Database similar to the one on the production server and insert all the old data in the new database.
- Disadvantage: Since linked servers are not allowed in our environment, it would be difficult to join the old data if needed
Create a new schema f.e. [hist] with the same tables as in the production database. Insert all old data in these new tables in the new schema.
- Advantage: Easy joining, if old data would be needed in the future
- Do you prefere one of the solutions over the other?
- Are there any better possibilities?
- Are there existing tools with which this task is easily possible?
- Any other thoughts?
Thanks in advance
Would the newly created archive table also need primary / foreign keys?
Or should they just have the columns but without keys / constraints?