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:
An example of a view over two tables in two databases
CREATE VIEW [dbo].[maintable]
SELECT id,val FROM [dbo].[maintable_v2] --new table
SELECT id,val FROM [my_test_old].[dbo].[maintable_old]; --old table
Querying this newly created view on the new database
ORDER BY id;
1 oldvalue --old table
2 NewValue --new table
The best alternative would be David Browne - Microsoft's answer.
If that is not an option, here is a less optimal solution:
- Move the data in the table to a history table that is created on a read only filegroup in the same database.
- Create the new table
- Create the view on the history table and the new table
- 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';
- 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.