Our current setup is a principal database used in production with a manual failover mirrored database for HA.

I need to setup a reporting database as well. What would be the best approach? Shall I setup a SQL job to drop and create the snapshot of the mirrored Database and refresh this every night?

Does anyone have any other suggestions?



Yes, that is a good and popular approach to offloading reporting, especially since you already have database mirroring in place.

Please see this BOL reference for more specifics: http://msdn.microsoft.com/en-us/library/ms175511.aspx

As for when you should create the snapshots, that is completely up to you and your business requirements, depending on how up-to-date your reporting infrastructure has to be at any given time.

  • Will it be okay to set the snapshot up as a SQL job so the previous snap shot is removed and a new one added with the same name every night? Are there any examples out there showing best practice when using snapshots?
    – davey
    Oct 10 '12 at 13:34

There's a few questions you need to ask first.

Are you using SQL Server Enterprise Edition? Mirroring can be done with either Standard or Enterprise, but snapshots can only be done with Enterprise.

Are you already paying for licensing on the mirror? There's a lot of fine print, but you basically get the mirror for free - only as long as you're not querying it. However, if you're going to add snapshots on the mirror and offload the reporting work there, then you'll have to start paying for the mirror.

Are you doing synchronous mirroring? If so, the mirror hardware needs to be as fast as possible in order to keep up with writes on the primary. Adding query loads to the mirror may slow down your primary's writes, and that may be a no-go for the business.

Is the business okay with data being as-of-yesterday? No one can be running a query while you drop and recreate the snapshot, which generally means you can only refresh it after-hours.

Do you have enough space for a growing snapshot? If you do high-volume activities like index rebuilds or reloading ETL tables, your snapshot can grow - by a lot. Ideally, if your database is 1TB (and nearly full), you'll need another 1TB free for the snapshot - not right away, but it can happen if you do things like rebuild all indexes. You'll want to know your change rate, design free space for that, and implement monitoring on both free space and database growth.

  • Thankyou for your reply. Please see my comments for each of your points: We are using Enterprise Edition , I will have to look into the licensing , We are currently doing synchronous mirroring , Yes i think the business will be happy with this, I will do the snapshot overnight , I think space is not an issue, do you have to rebuild indexes on snapshots? , Many Thanks D
    – davey
    Oct 10 '12 at 13:33
  • If you're doing synchronous mirroring, I'd think hard about adding any load to the secondary, and make sure space is not an issue. You don't want to find out about this the hard way, because when you run out of space, the snapshot will break. You don't have to rebuild indexes on snapshots though.
    – Brent Ozar
    Oct 10 '12 at 17:16

http://colleenmorrow.com/2011/03/14/database-snapshots-part-2-offload-reporting/ may prove to be helpful for using synonyms to report on snapshots without disruption when generating new snapshots.


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.