Sql-server – SQL Server 2016 SP1 + strategy for long term version retention (snapshots, temporal tables, other?)

best practicessnapshotsql serversql-server-2016temporal-tables

We have a data mart fed from an upstream data warehouse that we generate reports out of for external parties. I have a business requirement to preserve the state of the database for each month end to ensure reproducibility of results in case we are asked to do more analysis on the same time period. Database is <100GB, about 40-50 tables, nothing too crazy.

I know there are some interesting new options in SQL 2016 SP1 to consider. My goals here are less about quick/easy access to prior months and more about integrity/immutability of the data, but any solution that covers the latter while coming out ahead in the former wins the round.

Which approach makes the most sense here?

  1. Traditional backups: Archive to external storage, restore as needed. But this is clearly the most hassle from the DBA side, and the least self-service option. Note: Since I need to keep around all the copies, after while this will be a lot of storage space.
  2. Database Snapshots: Create snapshots and keep indefinitely. But I'm not aware of any solution that lets me safeguard the snapshot itself, from what I've if there is any corruption on the server(s) then your snapshots are hosed and there is no way to reproduce them. But the self-service aspect looks hard to beat. Is there a 3rd party solution I'm missing?
  3. Temporal Tables: The cool new feature of 2016, is anyone actually using this? Refactoring our scheme to support this looks like a heavy lift, so maybe this is off the table.
  4. Based off of @Max Vernon's suggestion…Monthly Restores to Read-Only DBs, maybe using Stretch Database: This sounds pretty good as this will be a lot of cold data….buuut is anyone actually using Stretch Database? Is this something Microsoft will quietly deprecate in a couple of years?
  5. Something else I'm totally missing here?

Update: As of mid-2019, Stretch Database has a pretty high cost to get off the ground, starting at about $1.8K/mo just for compute in Azure (storage extra) so I would say this puts it out of reach for all but the larger use cases.

Best Answer

Since you're dealing with a single database (or a known set of databases), simply create a SQL Server Agent Job that restores the month end backup to a "month-end" database.

Schedule the job to occur early on the first day of each month. The job would simply restore "last nights" full backup. You are taking nightly backups, right?

In order to ensure the immutability of the month-end data, simply mark the database as read-only after the restore finishes. Something like this:

ALTER DATABASE [xyz] SET READ_ONLY WITH ROLLBACK IMMEDIATE;

Details for the above command are here.

Related Question