Database snapshots used for reporting

Question

Database snapshots used for reporting

Animal Magic

SSChampion

Points: 13964
More actions
July 3, 2008 at 8:34 am

#122313

Hi All,
I have been looking into the benefits/downfalls of using database snapshots for reporting purposes. One of the main downfalls that i can see is that you cant "refresh" a database snapshot with a new snapshot, meaning you have to drop all connections, drop the snapshot and recreate. That might not necessarily be a bad way of doing it as you can script all that and it will run pretty much instantly, but is it the best way?
Are there any good articles out there on people who have implemented database snapshots as reporting solutions and what problems were faced.
The only way i can see it working at the moment is having the snapshot created overnight (using aforementioned script) and then you have a day old reporting dataset.
Thanks
John

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 1

Hi,

I am faced with this exact scenario. In doing some research I see that new snapshots need to be created and I was wondering the same thing...how to get users pointed to the new snapshots without disrupting their connections? We are currently using log shipping that gets restored each night so essentially they are reading '1 day behind' data and my thought was to use mirroring instead to have more current data for reporting purposes. But now I'm beginning to wonder if using mirroring and database snapshots is the way to go. I was thinking of doing hourly snapshots to 'refresh' the data but would that mean the users would get disconnected each the old snapshot is deleted and a new one is created with the same name?

Looking forward to hearing some responses to this... :hehe:

Thanks!

Thanks!
Bea Isabelle

Leo.Miller SSChampion Points: 13051 More actions · Answer 2

I would almost never use DB Snapshots for reporting, there are a lot of better options and a lot of restictrions with snapshots.

Far better to use replication. This allows you to only replicate the data you actually want to report on. Also, excep for the primary keys that are required, you can build a set of indexes on the replicated database that are designed for the reporting as opposed to for OLTP. You can slo clean up your OLTP database to remove unneeded reporting indexes whic improves performance there as well.

I've implemented this and in testing ran 12 months of reports sequentially. On the old database it took 19 hours to run all the report, on the replicated db, with proper indexing (same physical server), it took 40 odd minutes. If we had used snapshots, the best we would get was the same as the old performance.

We used Transactional replication which meant the report db was never more than a minute or two behind live.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

pankushmehta Ten Centuries Points: 1217 More actions · Answer 3

Hi,

We were earlier using LogShipping and then moved to mirroring and snapshots. We were running LogShipping on an hourly basis during the day, and disabling it during the night, so that the long running reports could be run during the night.

Now we have implemented mirroring and snapshots (for more recent data availablity for reporting). we check for user connections every 5 seconds and if the user connections are not there, we refresh the snapshots. In our reports / queries, we have a mechanism which checks if the snapshot esists and if not it waits for a few seconds and tries again, timeouting after 5 minutes.

however, we had to get good hardware to implement mirroring(atleast similar to OLTP server, as it repeats all the transactions for mirroring, and snapshot creation took a long time on slow hardware).

I think the snapshot refresh will always kill connections as we will drop the database and create it again, unless there is some other hidden way.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

This is a classic "it depends" question, with many options - each with their own pros and cons. Replication, log shipping, mirroring, snapshots each could be the right answer, or a workable answer, or completely off-kilter, depending on what you really need.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 5

Hi All,

Thanks for the great replies. Unfortunately we cannot use replication because the tables in our database do not have primary keys. This came out of the box from the vendor when we implemented our new erp system and it is too late to try and create new primary keys for over 3500 tables. we are using log shipping now and it works great excpet that we can only run the update in the early morning hours because we can't have users unable to run reports while it is updating. I will have to look more into the length it will take to create new snapshots and the affect it will have on the users and their reporting trying to create new snapshots every hour or so.

I agree that all are good options...just depends on what works for your situation.

Thanks again!! 😀

Thanks!
Bea Isabelle

winash SSChampion Points: 11446 More actions · Answer 6

One approach we used in of our projects was to use synonyms.

We mirrored our OLTP DB and created a snapshot of this mirror which was then used for reporting. We created synonyms on our reporting DB that point to the objects on the snapshot - everytime we needed a "data refresh" we created another snapshot, modified the synonyms to point to the objects on the new snapshot and then dropped the old snapshot. The effect of modifying the synonyms is minimal to the end users and normally doesn't affect anything.

Using config tables (to maintain the names permitted for the snapshot, the objects to create synonyms for and any other config paramters) it's pretty easy to write code that automatically creates a new snapshot on a schedule, modifies the synonyms to point to the new snapshot and drops the old snapshot.

This approach was documented in some article (which as usual I cannot locate now).

btw - the "it depends" holds here too. This worked out very well for us in one project - for another project (totally different everything) we ended up using transactional replication.

Animal Magic SSChampion Points: 13964 More actions · Answer 7

winash - if you can find that article id be interested in having a read. that sounds like a really nice reporting solution!

winash SSChampion Points: 11446 More actions · Answer 8

This was the article (I need to organize my favourites better ;-)):

http://blogs.msdn.com/b/sqlcat/archive/2008/08/05/microsoft-sql-server-database-snapshots-and-synonyms.aspx

Animal Magic SSChampion Points: 13964 More actions · Answer 9

Animal Magic

SSChampion

Points: 13964

October 22, 2010 at 3:36 am

#1240787

thanks 🙂

john.campbell-1020429 Ten Centuries Points: 1194 More actions · Answer 10

We use snapshots for one of our reporting databases. All processing is done by 7:00 AM when the old snapshot is dropped and the new snapshot is created. The job usually takes around 15 seconds. This was signed off on by the business users ahead of time and it is right before they start coming to work. They are ok with the data since this is a port of business data from another processing system.

John.