Sql-server – SQL Server Master Data Services database history by snapshots

master-data-servicessql serversql-server-2012

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.

Best Answer

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.

Related Solutions

Sql-server – Cache statistic data in SQL Server

How about an indexed view?

CREATE VIEW dbo.LoginsByDate
WITH SCHEMABINDING
AS
  SELECT 
    UserID, 
    LoginDate = CONVERT(DATE, LoginDate), 
    LoginCount = COUNT_BIG(*)
  FROM dbo.LoginTable
  GROUP BY UserID, CONVERT(DATE, LoginDate);
GO
CREATE UNIQUE CLUSTERED INDEX IX_LoginsByDate
  ON dbo.LoginsByDate(UserID, LoginDate);
GO

What this does is pre-aggregate the login data for you (you pay the cost at write time), and flattens it into one row per UserID per LoginDate. Depending on how many times a user logs in in a given day, and how often you run these reports, this can be substantially more efficient than calculating those at query time (and much less hassle than running your own nightly report that aggregates the data and stores it somewhere).

Now, to get the total login events by day:

SELECT LoginDate, SUM(LoginCount)
  FROM dbo.LoginsByDate
  GROUP BY LoginDate;

To get the distinct logins by day:

SELECT LoginDate, COUNT(UserID)
  FROM dbo.LoginsByDate
  GROUP BY LoginDate;

Of course if you're looking for individual user activity, you can do that too, and you can easily aggregate the data to get whatever "total unique" really means to you. If UserID = 1 logs in today 4 times and tomorrow twice, is that 1 total unique (unique per entire time frame) or 2 total uniques (unique per day)?

If the former:

SELECT COUNT(DISTINCT UserID)
  FROM dbo.LoginsByDate
  WHERE LoginDate >= 'yyyymmdd'
    AND LoginDate < 'yyyymmdd';

If the latter:

SELECT SUM(c) FROM
(
  SELECT LoginDate, c = COUNT(UserID)
  FROM dbo.LoginsByDate
  WHERE LoginDate >= 'yyyymmdd' 
    AND LoginDate < 'yyyymmdd'
  GROUP BY LoginDate
) AS x;

Sql-server – SQL Server database snapshots for integration testing

Unfortunately, it's by design.

Taken from BOL page "Revert a Database to a Database Snapshot":

Limitations and Restrictions

Reverting is unsupported under the following conditions:

The database must currently have only one database snapshot, to which you plan to revert.

Any read-only or compressed filegroups exist in the database.

Any files are now offline but were online when the snapshot was created

As an alternative, you could drop the first snapshot (db)_Basis. I can understand that this seems very limiting but look at it this way: snapshots are sparse files based on the original data files, so reverting to a specific snapshot would invalidate all snapshots anyway (the base data files would be changed by the revert operation). The limitation can be annoying, but doesn't look unreasonable.

Best Answer

Related Solutions

Sql-server – Cache statistic data in SQL Server

Sql-server – SQL Server database snapshots for integration testing

Related Question