use log shipping to populate a 'data mart'?

randyv · October 12, 2009 2:52PM

Hello -

I'm wondering about whether anyone uses SQL Backup Log Shipping to populate a 'data mart' and whether you find this advisable.

In reading about log shipping and putting the 'standby database' into read only mode, it occured to me that this would be an ideal way to have a 'data mart' copy of my production databases offloaded to another server for reporting purposes; as well as ensuring when we purge a year off the production system we have a backup of it.

Have you seen this done elsewhere, if not, what do you think of the idea?

Eddie D · October 13, 2009 3:28PM

Thank you for your post into the forum.

One possible use of the standby or destination database in a Log Shipping setup, is to have the database used for reporting purposes configured using the WITH STANDBY recovery model.

You will need to be aware, depending on how frequent you wish to restore additional log files to the destination database. If you have a user connected to the database, the restore process will fail due to the well known Exclusive Access could not be obtained, SQL Error 3101.

The restore process requires exclusive access to the database, so to restore additional files to the database you must wait for all users connected have finished using the database.

Alternatively you can use the 'Kill any existing connections to the database' option when performing a restore. However, you may annoy connected users who are running long queries to obtain the data they are seeking for their report.

Are there any Red Gate SQL Backup users that have 'real world' examples or experiences to share using a Log Shipped Read-Only database?

Many Thanks
Eddie

randyv · October 15, 2009 2:05PM

'doh!

use log shipping to populate a 'data mart'?

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk