3

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.
1

Since Master Data Services is available on the 64-bit editions of Business Intelligence and Enterprise only, you can leverage partitioning for data management.

You can partition the data by month or a quarter or year (depending on your requirements). This way you have a solid archival strategy and you can load new data into new partitions.

You can have different filegroups based on your archiving strategy as well.

Check this Partitioning & Archiving tables in SQL Server to get you started. Also refer to Best Practices + Table Partitioning: Merging Boundary Points and SQL Server Partitioned Tables with Multiple Filegroups for High Availability.

To fully automate the process of partitioning - switching in and out, you can use SQL Server Partition Management tool from CODEPLEX.

Note that if you absolutely dont need the data, you can even purge the partition and get rid of old data.

1

Snapshots do have some performance overhead. With a dozen snapshots you could have up to 12 additional pages written to disk for each page written to the main DB, depending on your write patterns.

Consider what you want to do with these snapshots. If they're simply for putative future auditing then do a backup and get them off your production server. You can restore them when they're needed. Ad hoc analysis? best not done on the production server (and engaging the live DB) either. Disaster recovery? DB snapshots do not give you that. To revert to in the event of a mistake? You'll be rolling back a lot of other work, too, which would have to be re-done. Then-versus-now analysis. Hmmm, maybe.

In short, a "proper" backup, restored as an independent database and only db_reader granted will be better in almost every way.

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.