I have historical/log tables which are:
- very large
- holds data that is not rarely queried
- only inserts are performed (constantly, very often)
I want to either compressed them or move them to cheaper(slow) storage.
Because of the tables structure (there are
nvarchar/varchar max columns) I was not able to apply or achieved good results using row, page, column store or column store archive compressions.
So, I have decided to move them to cheaper storage. I guess I have to options here, but let me know if I am wrong about something:
The tables are stored on slow storage. The database is under
FULLrecovery model and transaction log backup is made each 15 minutes. The issue is I am not able to perform partiatl backup (restoring only
importantdata from the fast storage) and apply transaction log chain as well. Right?
To create mirror tables on the slow storage and continue to store the history table on the slow storage. Each day a job is migrating data from the primary history tables to the mirror tables. So can I now restore a partial backup and apply the transaction log chain as well, as the tables on the slow drive are not same?