SQL Server – How to Reset a New Archiving Database

archivesql serversql server 2014synonymsview

I have an archiving question.

We have a History DB used for archiving old records from our main system and the application knows to query it

However, it becomes too big.

I would like to reinitialize the History DB.
My idea was – in a nutshell:

  • Close down this DB,

  • Make it READ-ONLY,

  • Rename it to *old

  • Create a similar history DB (same structure/tables).

  • With the help of views/synonyms, the application will be able to query the data for both DBs without a code change.

My question is about the last part

Will that work ?

Can I create view/synonym (the view will query both of table DBs with UNION ALL) that will be the default go-to object name instead of the actual original table name ?

Our main problem is the backups, as the Archiving is done daily, we need also to back up this DB, and as it's huge DB, it's a waist of time and resources.

Thanks,
Roni.

Best Answer

The built-in way to reduce backup size for VLDBs with read-only data is to parition your large tables, placing older partitions on read-only filegroups, and then take infrequent backups of the read-only filegroups (or whole database), combined with regular backups of the read-write filegroups. Then you can perform a Piecemeal Restore.

Will [using UNION ALL views over multiple databases] work ?

Yes, you can typically replace a table with a view without changing the application, unless the application does something unusual. The risk is in the performance, as the UNION ALL view across multiple databases doesn't really perform the same.

You might just try using columnstore compression on the large tables in the archive database.

Related Question