I'm involved in a project where there is SQL Server 2012 Master Data Services used. There's a requirement to save a version of the database contents in certain point of time, say, every month or a quarter or so and possibly replacing previous versions on a yearly rhythm. That is, if a monthly history of database versions is used, there will be at most twelve different snapshots at any given time.
I'm curious and hence two questions:
- Is this a preferred way of doing this?
- Would there be a better way?
For what I've seen, from a developer perspective, snapshots are utilised for example for reporting purposes, but dropped soon after they are done. It seems they impact performance and perhaps make backups and restores more difficult.
<edit:
As an answer to the database size question: some hundred megabytes. But that's a very rough guess. Certainly at least on hundred.
I've now learned the snapshots are in fact a plan to store MDS history and possibly retained indefinitely -- or until someone explicitly deletes the oldest ones. I don't know the frequency they are taken. At least four times a year, possibly more often and seemingly retained until someone decides to delete the oldest ones. The rationale is to store MDS history as "untampered" read-only snapshots, of which at least a few previous quarterly snapshots are actively queried (but not altered). There is a plan to publish the MDS periodically -- every ten minutes or so -- the MDS to a special purpose "publish database", which may be the place the snapshots are taken (I'm not sure, but certainly there ought to be awfully lot of snapshots then to cover all the published changes).
This doesn't feel the right way to work with MDS. This looks like the slowly changing dimension problem and it looks like the (great) majority of data does not change. What I know, or believe is that there are nightly bulk loads, but mostly the data loaded should be the same as it has been, and changes are mostly new items plus some small modifications and some additional attributes maintained purely by the data stewards. Then looking Technet regarding Database snapshots it looks me it's difficult to run the system efficiently (e.g. I/O) and reliably (backups and restores) in presence of multiple snapshots, which do carry an operational and contractual purpose in this case.
Maybe I shouldn't fret or this. This doesn't even look like I have any real questions here anymore than
- Would it be normal to version database data in snapshots? Be it an operational database or a MDS one.
