Uninterrupted reporting against a log shipped DB

Question

Uninterrupted reporting against a log shipped DB

ignatov256

Say Hey Kid

Points: 677
More actions
October 4, 2010 at 11:45 am

#229974

I've been a fairly frequent visitor to this site for quite a while, but don't post a whole lot. The pros here beat me to it.
Anyway, here's a thought I am entertaining. How to query a log shipped database without disconnecting users for every log restore? I know there are other solutions native and third party, which would allow me to set something up, but I specifically want to leverage the existing log backup job to scale out some querying/reporting without putting additional strain on the prod server (by using replication or whatnot).
So the setup is pretty simple as far as having a read-only/warm-standby db on a second server with log restores hitting it, say, every 5 minutes. The problem is that, theoretically, I want to be able to have an open connection all day long and not rewrite any apps to have re-connect logic every time that log retore kills all of the conenctions.
Am I making sense here?

Viewing 15 posts - 1 through 14 (of 14 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

only way with log shipping is store the logs up and restore them out of hours. Easily done by adjusting the restore job schedule.

Users would have to accept data that was more out of date though.

---------------------------------------------------------------------

ignatov256 Say Hey Kid Points: 677 More actions · Answer 2

That's too easy! 🙂 I was looking for something with a 5 minute update interval. Thanks for the reply though.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

This is an area where SQL is lacking. Logshiping cannot support this requirement.

If you want it that up to date and 'off server' then replication is about it.

---------------------------------------------------------------------

ignatov256 Say Hey Kid Points: 677 More actions · Answer 4

I was thinking there could be a way to set this up with a third database/instance and have replication or something else set up between the log shipped database and a third database to which the users would be able to connect. I tried setting up replication this way, but it didn't really work. The roadblock is the read-only state of the log-shipped database.

What I did was I tried setting up replciation between second and third databases, and then settting up log-shipping from first to second (and switching to read-only at that point), but that didn't work out. Thought maybe there is a hacky way of doing it...

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

You could create a database snapshot for users to report from, and continue to apply logs to the database.

Users would only be disconnected when you recreate the snapshot. Users would see static data until the snapshot is recreated.

george sibbald SSC Guru Points: 104210 More actions · Answer 6

Michael Valentine Jones (10/5/2010)
You could create a database snapshot for users to report from, and continue to apply logs to the database.
Users would only be disconnected when you recreate the snapshot. Users would see static data until the snapshot is recreated.

trouble is michael a new snapshot would be required every 5 mins.........

---------------------------------------------------------------------

ignatov256 Say Hey Kid Points: 677 More actions · Answer 7

I found something that might be adapted to work in this situation... http://searchsqlserver.techtarget.com/tip/Tricking-SQL-Server-into-making-full-database-backups

I think if I can trick it to bring the db online and then run replication scripts against the second instance to replicate to the third (actually third and fourth) this might work. Although it is becoming a bit more complicated at this point... I'll try it sometime today.

george sibbald SSC Guru Points: 104210 More actions · Answer 8

where does the 5 minute update interval fit into that process? Have I misunderstood in thinking that you wanted you reporting databases near real-time?

---------------------------------------------------------------------

ignatov256 Say Hey Kid Points: 677 More actions · Answer 9

You understood correctly. Here is what my thought process was:

On subsequent passes replace original steps with "a" alternatives.

1. Set up log shipping from 1 to 2

1a. ship logs from 1

2. Create a shell db on 3

2a. retore logs on 2

3. Stop 2 & 3 and swap files.

4. Restart 2 & 3 (2 is still read-only, 3 is fully operational)

5. Synchronize 3 & 4 (On second thought I don't think anything will work effectively since the log is empty)

6. repeat every 5 min.

Users report against 4 and never have to disconnect.

george sibbald SSC Guru Points: 104210 More actions · Answer 10

Unless your files are tiny in which case you may as well do full restores from the primary I don't think you can fit this process in 5 mins.

but I wish you good luck.

---------------------------------------------------------------------

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 11

Log shipping is a DR technology. Not a reporting technology, which is where you're having issues.

What you can do is use SSIS instead. You might still need a log shipped database depending on the DR requirement, but you can build SSIS packages to move data. It's cumbersome, but you can essentially do a "load" of changed data as long as you can track this. In SS2K8 and later, CDC can help here.

Replication can work from the source database as well. You don't need a second log shipped one. Just implement replication from the source database to the reporting database.

ignatov256 Say Hey Kid Points: 677 More actions · Answer 12

Thanks for the suggestions. We are using SSIS to load a data warehouse specifically for reporting purposes. I think every 30 min or so. The solution I was looking for is something to create a production-like environment that could accomodate some of our heavier production apps (which require a max of 5 min latency and a constant connection) and not add ANY overhead to the prod box. I guess you can't have your cake and eat it too. I'll start looking at replication or smething third party. Thanks again for your input.

getoffmyfoot Hall of Fame Points: 3338 More actions · Answer 13

It might make sense to maybe set up an SSIS package that captures what has changed since last run (having a last_update_time column on the tables helps alot here), and propagate those changes over to another online database by loading the data to a staging table and merging the changes from staging table to regular. It isnt log shipping, it isnt replication, has more overhead, but might be good enough for your use case

Edit - just saw your last post. maybe change your DW ETL jobs to only push changed rows over to the dw?

ignatov256 Say Hey Kid Points: 677 More actions · Answer 14

We are kind of doing this already. There are tables with 130 million rows, 2.5 billion rows, etc. That would be impossible to blindly load without some change logic in place. We are on 2005 Ent.(don't have the guts to jump to 2008 although that's comming) Even then I'm writing 1.7 million rows to a single table daily + all the other tables that have changes in them, so it gets a bit complex when I start thinking about copying this stuff from prod. Every bit of performance counts.