Sql-server – Dividing data from one table to multiple storage

availability-groupssql-server-2012storage

I have the following tables:

  • [Table_A]
  • [Table_A_Changes]

The two tables are pretty large (more then 50GBs). The first one contains some data, and the second one records' changes.The database is in FULL recovery model (AlwaysOn).

WRITES:

  1. [Table_A] – constantly/many
  2. [Table_A_Changes] – constantly/many

READS:

  1. [Table_A] – constantly (in 99% of the cases only data for the past 6 months)
  2. [Table_A_Changes] – rearly (in 99% of the cases only data for the past month)

UPDATES:

  1. [Table_A] – constantly/many (in 99% of the cases only data for the past few months)
  2. [Table_A_Changes] – no updates

I am going to use a cloud storage where the fast storage is too expensive to hold all the data.

So, I want to store only the the new data in [Table_A] on fast storage, and the older data on slow/cheap storage.

Is there a way to achieve this?

Also, since the major part of the data is not (read/update) I want to perform only partial backups on the data stored into the fast storage only.Let's say a full backup each week and a partial backup each 12 hours.

I am looking both for storage and recovery strategies (any links are welcome, too).

Best Answer

Create a new table Table_A_Archive with exactly the same schema as Table_A_Changes. Partition both these tables by, say, one-month date ranges. As data becomes stale in Table_A_Changes swap that partition out and swap it into Table_A_Archive. Your data retention policy can be efficiently enforced this way, too.

A view which unions the two tables can make querying simpler.

Related Question