1

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.

  • What is the issue with the size of the database? Your proposed solution doesn't seem to alter the fact that the DB is still queried and is going to remain its current size. – HandyD Apr 8 '19 at 7:44
  • Thanks for the comment, The issue is mainly the backup. I want to close it and make it read-only and this way we won't backup this DB anymore – Roni Vered Apr 8 '19 at 7:51
  • Are you taking FULL backups every day? Have you considered differentials? – George.Palacios Apr 8 '19 at 8:06
  • @RoniVered, I believe you are using compression for taking the backups? – Biju jose Apr 8 '19 at 8:44
  • We're using diff backups, we're using data compression and backup compression – Roni Vered Apr 8 '19 at 8:55
3

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.

  • Thanks, I think partitions with read-only FG and FG backups will be our solution. – Roni Vered Apr 10 '19 at 6:57
3

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';
  1. 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.
  • Thanks for your elaborate answer, you gave me things to think on :) – Roni Vered Apr 10 '19 at 6:56
  • @RoniVered good to hear & I am glad it helped! Good luck! – Randi Vertongen Apr 10 '19 at 6:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.