Resetting a new archiving database

Question

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.

Answer 1

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.

Answer 2

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 ?

Yes, you can create a view / synonym for cross database queries.

here are some side-effects that come to mind when implementing this:

• Your application might rely on expecting a table and being handed a view. (there should be a way to mask this but I cannot find it at this moment)
• Renaming your table(s) might break scripts / other views / procedures / ...
• Collation mismatch between databases (if different)
• Extra possible overhead due to two queries against two databases instead of one query against one.
• There are a lot of extra considerations when using Read Only databases, such as statistics not being updated, not being auto created, .... More on that here.

• Constraints / relationships between the two tables in the two databases will not work. This could result in duplicate primary keys, foreign key references not working, ....

• ...

An example of a view over two tables in two databases

CREATE VIEW [dbo].[maintable]
AS
SELECT id,val FROM [dbo].[maintable_v2] --new table
UNION ALL
SELECT id,val FROM [my_test_old].[dbo].[maintable_old]; --old table

Querying this newly created view on the new database

SELECT id,val
FROM [dbo].[maintable]
ORDER BY id;

Result

id  val
1   oldvalue --old table 
2   NewValue --new table

An alternative

The best alternative would be David Browne - Microsoft's answer.

If that is not an option, here is a less optimal solution:

1. Move the data in the table to a history table that is created on a read only filegroup in the same database.
2. Create the new table
3. Create the view on the history table and the new table
4. Backup the read only filegroup separately, backup the database without the read only filegroup on separate occasions.

To backup the read_write filegroups:

BACKUP DATABASE DatabaseName READ_WRITE_FILEGROUPS  
TO DISK = N'C:\SQLBackups\DatabaseBackup.bak';

5. If you are on enterprise you could do online filegroup (piecemeal) restores, on standard you cannot do this, meaning that you will have to wait on the restore of the read only filegroup.

This results in the only real advantage of splitting it into multiple databases when on standard edition would be restoring the non-history database first, making it accessible for queries while the history database is restoring.

• Another option, (Not for your version, starting from SQL Server 2016): [SQL Server Stretch database][3. If the cold history data is not queried that much, store it in azure. This can be done without any query or application changes.