SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple Mirrors


Multiple Mirrors

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61815 Visits: 19099
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
My Blog: www.voiceofthedba.com
lfallis
lfallis
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 178
Morning Steve,

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

Many Thanks,
Lee
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10272 Visits: 13687
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?

---------------------------------------------------------------------
bwillsie-842793
bwillsie-842793
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 290
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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61815 Visits: 19099
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
My Blog: www.voiceofthedba.com
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61815 Visits: 19099
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
My Blog: www.voiceofthedba.com
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10272 Visits: 13687
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)

---------------------------------------------------------------------
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10272 Visits: 13687
..or you use the new replication model steve has just patented :-)

---------------------------------------------------------------------
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61815 Visits: 19099
bwillsie-842793 (2/23/2010)
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.


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
My Blog: www.voiceofthedba.com
Simon Facer
Simon Facer
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1721 Visits: 724
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).



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search