Multiple Mirrors

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQLServerCentral.com » Editorials » Multiple Mirrors

13 posts, Page 1 of 2

1 2 »»

Multiple Mirrors

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Jones - SSC Editor

Posted Monday, February 22, 2010 10:37 PM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:20 AM
Points: 31,437, Visits: 13,752

Comments posted to this topic are about the item Multiple Mirrors

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #870914

lfallis

Posted Tuesday, February 23, 2010 2:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 11, 2011 8:19 AM
Points: 35, Visits: 178

Morning Steve,

Just wondering if you could provide the link to the white paper Paul Randal pointed you to?

Many Thanks,
Lee

Post #870987

george sibbald

Posted Tuesday, February 23, 2010 4:50 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:32 PM
Points: 5,270, Visits: 11,213

steve, I think you just reinvented transactional Replication!

I think we are in danger of over complicating things. People need to decide whether they want to use their databases copies for DR or for reporting, if you try and use them for both you have conflicting requirements.

Having said that providing a near real time reporting database is not as simple as you would think it might be. Perhaps we need a replication-lite?

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

Post #871046

bwillsie-842793

Posted Tuesday, February 23, 2010 6:30 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 12, 2013 7:43 AM
Points: 107, Visits: 287

Disclaimer: This is my experience as developer, not as a DBA.

I'm a fan of mirroring, but so far my experience with log shipping for any purpose other than backup/recovery leaves an awful lot to be desired.

About 6 months back our Corp IT dept implemented a "reporting server" that is updated every 1/2 hour via log shipping.

The original intent was that this server would be used for both standardized ERP system reporting and ad Hoc reporting done via query software (IEV) or MS Access.

Out of every hour, we lose 10 minutes of connect time (5+5) while the shipped logs are being posted to the databases. Existing connections are completely blown away, including tests I ran running an Express Server SQL Query to a DB on a linked server.

As the databases are apparently thrown into recovery mode during the log posting process, I now understand why this happens.

However, an undesireable consequence is I have to carefully schedule any reporting, to ensure it does not start or end during a blackout period.

Not sure how this will play in Podunk IF they ever try to push the regular users into using this server for reporting. I suspect the first issue will be the blown connections, rapidly followed by "I just did a transaction but it's not showing in my report."

At the very least the broken connection issue has to be fixed for users to accept running a reporting server this way.

Post #871081

Steve Jones - SSC Editor

Posted Tuesday, February 23, 2010 6:56 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:20 AM
Points: 31,437, Visits: 13,752

White paper link is in the article, and now here: http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/DBMandLogShipping.docx

It's a .DOC download. Apologies

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #871097

Steve Jones - SSC Editor

Posted Tuesday, February 23, 2010 6:57 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:20 AM
Points: 31,437, Visits: 13,752

george sibbald (2/23/2010)

steve, I think you just reinvented transactional Replication!

I think so! Replication is great, but one of the issues I see with it, and others, is in terms of moving an entire database across. It's a little harder to setup/manage than mirroring, and I'd like to see that option in mirroring or replication. I was just thinking of a two-phase commit to a "distribution mirror" (same box or new) that would push out those log transactions to the final mirror database.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #871100

george sibbald

Posted Tuesday, February 23, 2010 6:59 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:32 PM
Points: 5,270, Visits: 11,213

bwillsie, you just made my point. You are trying to use log shipping, which is primarily designed as an HA solution. for reporting purposes, which is just a handy byproduct of having a readable copy of the database. And you are finding drawbacks with it.

So what do you want to take priority, restoring the logs to give you more up to date information and Keep DR in synch, or do your reports have priority so log restores have to be rescheduled, or allowed to fail until such time as the restore gets lucky and succeeds (that can be done).

someone has to compromise.

Or you log ship to two different destinations with different purposes. which mirroring cannot do on its own (and why should it)

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

Post #871102

george sibbald

Posted Tuesday, February 23, 2010 7:02 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:32 PM
Points: 5,270, Visits: 11,213

..or you use the new replication model steve has just patented Smile

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

Post #871103

Steve Jones - SSC Editor

Posted Tuesday, February 23, 2010 7:12 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:20 AM
Points: 31,437, Visits: 13,752

bwillsie-842793 (2/23/2010)

You've hit on the main issue for log shipping with reporting. IMHO, it's not a reporting solution, other than a periodic one. You could schedule downtime, or you could schedule it to restore at night only and allow users to work with semi-stale data. It's not a realtime solution.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #871113

Simon Facer

Posted Tuesday, February 23, 2010 7:19 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:30 AM
Points: 1,566, Visits: 600

For reporting, mirroring with database Snapshots is an option you should consider. You will need to recreate your Snapshot(s) on a regular cycle (depending on your requirements). You may be able to use a cyclical snapshot schema - snp_DB1, then x minutes later snp_DB2, etc over a period, to mitigate lost connection issues, or you may have to drop the connections when you recreate the snapshot(s).

Post #871120

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions