High availability on a subset of the data

On my site, I have a set of big databases (+6TB total) that is running on a SQL Server 2012 instance. One of these is the main DB and the other ones are archived data that are queried sometimes (unions on the main db stored procedures) but do not have to be 24/7 online. Only the main db is mirroed to another datacenter because of storage constraints.

To mantain all this there is a job that keep moving old data from the main to the archive dbs, but this is slow and ineficient. I plan to improve all that with a sliding window paritioning solution but I couldn't find a way to switch an archived partition or table to another database without data movement on the datafiles.

I can put everything on a single database but I couldn't find an HA solution that I could choose the objects that actually need the high availability.

I'm currently doing some tests with replication but it will be tricky to pull it off and it is not a true HA solution.

Any ideas on how I can parition my data and only provide HA to a subset of the whole data?

Thanks in advance.

Switch the partition to a separate table in the main database, then use bulk copy to move those rows into the secondary database. ALTER TABLE ... SWITCH can be used to make meta-data only moves between tables, not just partitions within a table.

StackExchange Database Administrators Q#130372, answer score: 2