July 2, 2013 at 7:58 pm
I've been using timestamps and status dates on tables to do historical reporting (e.g. today last year, quarter over quarter, etc). But I've been thinking that using snapshots might simplify life a little. Are snapshots generally the best practice for historical reporting? I really on have 15 or so tables (out of the hundreds) that I query a bunch for history, but I read snapshots run against the entire instance. So I'm just looking for ideas and general advice. As always, thanks a bunch for any help.
July 2, 2013 at 8:01 pm
...I should probably add that I'm needing something that will capture the data every week. We have a Monday run that shows progress in varies areas since the previous Monday.
July 3, 2013 at 5:21 am
Database Snapshots are per database. I would not rely on them for historical reporting mainly because of the recurring need to create and retain additional snapshots. For every snapshot in place, additional overhead is incurred whenever the source database has data pages modified for the first time since the snapshot was created.
For what you're after I would recommend considering standing up a new "history database". A scheduled SQL Agent job could run when needed to copy the data you want to report off into history tables with timestamps showing when the data was retrieved, and those tables will act as your "snapshot" of the data. Whatever you do though, try to avoid creating new tables each month with names like SomeData_201307 and SomeData_201308. Rather, create a table named SomeDataHistory that looks exactly like SomeData plus a column or two that tells you the time it was captured and maybe the source database if you have multiple database feeding a single history table.
Note the history database could reside on a different instance too, in which case SSIS would be your friend. You would have the beginnings of an ODS after a few months of capturing the history.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 3, 2013 at 9:08 am
Not a good idea.
Snapshots impact insert/update/delete performance, they take up space, they can't be backed up and if for any reason you need to restore the source database you'll have to delete all snapshots first.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2013 at 9:17 am
We did something similar in a financial system by putting in data and fields that marked as "as of" a certain date. This allowed us to join tables together based on these dates and report on different values at different points in time.
It's not simple, and it takes some work to get up, but it can work if you can modify the schema and code to support this.
July 3, 2013 at 2:59 pm
DataAnalyst011 (7/2/2013)
I've been using timestamps and status dates on tables to do historical reporting (e.g. today last year, quarter over quarter, etc). But I've been thinking that using snapshots might simplify life a little. Are snapshots generally the best practice for historical reporting? I really on have 15 or so tables (out of the hundreds) that I query a bunch for history, but I read snapshots run against the entire instance. So I'm just looking for ideas and general advice. As always, thanks a bunch for any help.
If allowed, I would suggest it might be time to plan for a data warehouse.
To achieve the kind of reporting described be sure DIM tables are designed as Type 2 SCD; snapshot FACT tables would complete the picture.
If done that way, reporting can go back to any point in time defined on the designed grain, showing a true picture of what was going on at the selected time.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply