Data disk archive archive#

For example, this will prevent you from accidentally inserting a year of data into the history table twice. I would recommend having at least the primary key and unique indexes so that you can get the data integrity benefits they provide. So I wouldn't make this decision lightly if it's a heavily used database. However, this is a big change that can come with a lot of performance implications, both positive (e.g., partition elimination, more efficient data loading) and negative (e.g., slower singleton seeks, greater potential for thread skew in parallel queries).

Data disk archive archive#

If you need to query the archive data regularly, I might consider partitioning the table by date. The disadvantage you listed for this approach is not accurate you will be able to query across databases on the same server easily and the query optimizer generally handles cross-database queries very well.

This may be fine for you, but it's something to be aware of.

The primary disadvantage I see for a history database (as opposed to a schema) is that you can no longer use foreign keys for your archive table. I generally prefer the history database, and I think Guy describes good reasons for this in his response. Perhaps your queries are performing table scans on the entire fact table even when only a small portion (e.g., the last year) of the date range is needed? If there is a particular query that is most important to optimize, considering posting your schema, query, and an actual execution plan in another question to see if it can be optimized.ĭo you prefer one of the solutions over the other? What performance problems are you having? It seems unusual that a database would have performance problems just from growing to 250GB in size. I think the answer to many of your questions is that it depends. Or should they just have the columns but without keys / constraints? Would the newly created archive table also need primary / foreign keys?

Are there existing tools with which this task is easily possible?.

Do you prefere one of the solutions over the other?.

Advantage: Easy joining, if old data would be needed in the future.

Insert all old data in these new tables in the new schema. with the same tables as in the production database.

Disadvantage: Since linked servers are not allowed in our environment, it would be difficult to join the old data if neededĬreate a new schema f.e.

I've thought about two possibilities: New DatabaseĬreate Database similar to the one on the production server and insert all the old data in the new database.

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.

The database needs 250 GB on the hard disk. There are about 310'000'000 records in the database in total. 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 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. We're currently running into some performance problems since our database is getting too big.