Archiving of old data

Question

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.

---

Info

• 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:

New Database

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

History Schema

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?
  • Why?
• Are there any better possibilities?
• Are there existing tools with which this task is easily possible?
• Any other thoughts?

Thanks in advance

Edit

Additional Question:

Would the newly created archive table also need primary / foreign keys?

Or should they just have the columns but without keys / constraints?

Answer 1

I think the answer to many of your questions is that it depends. What performance problems are you having? It seems unusual that a database would have performance problems just from growing to 250GB in size.

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.

Do you prefer one of the solutions over the other?

I generally prefer the history database, and I think Guy describes good reasons for this in his response.

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. This may be fine for you, but it's something to be aware of.

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.

Are there any better possibilities?

If you need to query the archive data regularly, I might consider partitioning the table by date. 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). So I wouldn't make this decision lightly if it's a heavily used database.

Would the newly created archive table also need primary / foreign keys? Or should they just have the columns but without keys / constraints?

I would recommend having at least the primary key and unique indexes so that you can get the data integrity benefits they provide. For example, this will prevent you from accidentally inserting a year of data into the history table twice. And as a side benefit it may improve performance if you do need to query the history table.

Any other thoughts?

Since you are using Enterprise edition and planning to upgrade to SQL 2008+, you might consider data compression for this table. Compression will certainly reduce disk space, but depending on your server's disk and CPU resources it may also improve query performance for reads by reducing disk I/O and improving memory utilization (more data fits in cache at once).

Answer 2

I would prefer having a history schema or a second historical database over a linked server any day. It saves license costs is easier to manage and query. You can then also use simpler schema and drop some of the indexes making the database smaller

But since you have the enterprise edition you have the third option which is to partition your tables which, when put in place makes it easier to archive the data and querying the old data is transparent to your users and you will not need to make application changes.

Answer 3

In my experience a second database would be the preferred choice for two reasons.

1. You can restore the data from an historic backup then drop the tables and indexes you don't need.
2. You can move this to a different server for reporting purposes, this has the benefits of not using the resources of the primary server

You would still need to delete all the historic data from the primary database but this could be scheduled in.

Answer 4

Ignoring license for now as that's not where I spend my time.

IMHO, archive database is simplest to implement and maintain. They're distinct, loosely coupled entities. Data movement and load/resource controls have clear boundaries. Can easily move to a different instance or server for better performance management and cost is not a major issue. Note that simplest != cheapest or least effort. It actually has a quite a bit more tasks but they're all simple tasks with two important exceptions:

1. constraints enforcement - no such thing as cross database constraints in SQL Server so you need to decide if that's a deal breaker.
2. cross database queries use distributed queries that is still dependent on OLEDB which is deprecated. That means you might encounter issues with new data types plus if you encounter performance issues, it's unlikely they'll ever get fixed

Archive schema or just archive table is a bit more complex to implement but much easier to use. All objects in the same database means you don't have to replicate and maintain access controls. No cross database queries making for easier performance tuning, monitoring, troubleshooting, etc...

Table partitioning is a great solution and afford many of the benefits of a archive table/schema but provides transparency to users/queries. That said, it is the most complex to implement and requires on-going care that isn't easy for a beginner.

Some important considerations:

• Do queries return historical/cold data regularly or is cold data infrequently accessed?
• Is the historical data immutable or does it get updated/deleted regularly?
• 310m rows is "moderate" (assuming all in 1 table) depending on row size. Do you have row size data? How many GB is that 310m row?
• What is the growth rate of that table?
• Are you able to modify application code and its SQL queries?

These are important considerations as they can have significant impact on the solution you choose or may not even allow certain solutions. For example, if your historical data is modified/updated regularly (more than once a week), using a separate database means you have to either use DTC for those queries or manually manage transaction safety (non trivial to assure always correct). Cost is significantly higher than immutable historical data.

Also, if you're thinking of upgrading, do consider 2016 and the new Stretch Database feature: https://msdn.microsoft.com/en-us/library/dn935011.aspx

Answer 5

I would prefer splitting the database into a separate logical database for the following reasons:

1. Resource Requirements

By splitting this out into a separate database, it can be stored on a different drive and monitored at a different rate to the main production data.

2. Performance

By splitting out the data to a separate database, the main Production database is reduced in size, helping the overall performance.

3. Simpler Backups

Backing up archived data may not be deemed as essential as the ‘live/current’ records in the main SQL database. This may mean that Archived data could be backed up less often. Also due to the sequential nature of how Archived data is logged, it may be possible to backup sections of the Archived database once and then never again. E.g. once archive data is written in the Change archive database for 2014 there will never be any change to that data again.

Note: I think the answer to many of your questions is all depends on your circumstances, nature of data and the performance problems that you were having.