Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Multiple Mirrors Expand / Collapse
Author
Message
Posted Monday, February 22, 2010 10:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:49 PM
Points: 32,768, Visits: 14,929
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
Posted Tuesday, February 23, 2010 2:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Tuesday, February 23, 2010 4:50 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:53 PM
Points: 5,845, Visits: 12,576
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
Posted Tuesday, February 23, 2010 6:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 12:22 PM
Points: 107, 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.
Post #871081
Posted Tuesday, February 23, 2010 6:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:49 PM
Points: 32,768, Visits: 14,929
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
Posted Tuesday, February 23, 2010 6:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:49 PM
Points: 32,768, Visits: 14,929
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
Posted Tuesday, February 23, 2010 6:59 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:53 PM
Points: 5,845, Visits: 12,576
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
Posted Tuesday, February 23, 2010 7:02 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:53 PM
Points: 5,845, Visits: 12,576
..or you use the new replication model steve has just patented

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

Post #871103
Posted Tuesday, February 23, 2010 7:12 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:49 PM
Points: 32,768, Visits: 14,929
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
Post #871113
Posted Tuesday, February 23, 2010 7:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:30 AM
Points: 1,568, Visits: 652
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 »

Add to briefcase 12»»

Permissions Expand / Collapse