Sample Header Ad - 728x90

SQL Server Master Data Services database history by snapshots

3 votes
2 answers
471 views
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. https://en.wikipedia.org/wiki/Slowly_changing_dimension) 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](http://technet.microsoft.com/en-us/library/ms175158.aspx) 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.
Asked by Veksi (269 rep)
Mar 23, 2014, 09:50 AM
Last activity: Nov 11, 2022, 09:05 AM