February 22, 2010 at 10:37 pm
Comments posted to this topic are about the item Multiple Mirrors
February 23, 2010 at 2:14 am
Morning Steve,
Just wondering if you could provide the link to the white paper Paul Randal pointed you to?
Many Thanks,
Lee
February 23, 2010 at 4:50 am
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?
---------------------------------------------------------------------
February 23, 2010 at 6:30 am
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.
February 23, 2010 at 6:56 am
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
February 23, 2010 at 6:57 am
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.
February 23, 2010 at 6:59 am
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)
---------------------------------------------------------------------
February 23, 2010 at 7:02 am
..or you use the new replication model steve has just patented 🙂
---------------------------------------------------------------------
February 23, 2010 at 7:12 am
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.
February 23, 2010 at 7:19 am
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).
February 23, 2010 at 7:26 am
Two issues with snapshots - They require Enterprise Edition and you still have the connection issues. If I build a snapshot every 10 minutes and I have a report that runs for 15, either people have to know the name is changing constantly, or they would lose their connections.
Snapshots are a good try, but they're not there either.
February 23, 2010 at 12:39 pm
It sounds nice, but you have to take into account the other benefits that mirroring gives you - automatic failover primarily. How would you handle the conversations between multiple mirrors and the witness to ensure that each database is in the correct state? It's already somewhat complicated to determine what will happen to each database if connections drop, that complexity would grow exponentially given more servers.
If you are talking asynchronous mirroring then perhaps those issues go away - is there really that much benefit over replication or log shipping though?
February 23, 2010 at 7:25 pm
The ability to have multiple mirrors, or a waterfall mirror setup would be great. I think you stated it accurately that the setup becomes much more complicated to support this kind of setup. It would be a great technology though.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply