MS SQL : DB mirroring vs log shipping across WAN & bandwidth sizing for mirroring

We're contemplating to propose MS SQL DB soltuion to a customer for either :

a) DB mirroring across 2 geographically sites (of about 25 km apart
    within the same country)    or
b) log ship  (I guess this mean batching groups of committed transactions
     & send it in batches over the WAN to the remote site for updating)
c) one-way replication

What's the pros & cons of each of the above?

If we go for mirroring, assuming on average the size of our current log
shipping file (yes, we currently do 'log shipping') that's created every
minute is 80MB during office hours (with max 180MB), what's the
bandwidth recommended for the WAN to be able to support DB mirroring
sufficiently ?  I heard if the WAN bandwidth is not enough, it would slow
down the IO updates/writes to the local SQL DB because in mirroring,
transactions have to be committed at the same time to the remote DB
as updating/committing the transactions to the local DB & if the WAN
is slow, it would take a while to write to the remote DB & thus would
be equally slow to write to the local DB.  Correct me if I'm wrong

Just for my education, what's the equivalent of MS SQL's log shipping
in Oracle?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aaron TomoskySD-WAN SimplifiedCommented:
It's more about latency than bandwidth. Here is a creative solution:
sunhuxAuthor Commented:
Instead of DB mirroring, I've also seen 'SAN replication' across remote
sites where selected SAN LUNs at 2 sites are 'mirrored' to each other.
Is this a more efficient solution that DB mirroring?
Aaron TomoskySD-WAN SimplifiedCommented:
At a high level, performance is based on if you wait for the changes to be applied to both places, everything slows down. Think about it from the "what if I pull the plug" perspective.
Doing read/write only to local with either log shipping or San replication will have the least performance hit. To use remote as a load balance and read from both is where you become limited by the slowest connection.

You also need to consider your data. Is it a bunch of reads with daily loads of writes?

San replication has the same issues as SQL replication, where you write to a middleman logic writes to two locations, or changes are synched continuously but only one is active at a time. There is no magic bullet for HA across a wan.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

johnsoneSenior Oracle DBACommented:
To address the Oracle part of your question.

Oracle does have log shipping.  What you are talking about would be a physical standby database in Oracle's terms.  You can read up on that in the documentation here:
sunhuxAuthor Commented:
"San replication has the same issues as SQL replication"

I just spoke to a SAN presales person & his advice:
when disaster strikes & the amount of data loss at DR site is to be 0,
I'll need synchronous SAN replication or MS SQL mirroring but he
is totally unfamiliar with MS SQL mirroring.

If I'm doing asynchronous SAN replication, might as well do
SQL log shipping as asynchronous SAN replication only replicate
the data across the WAN at every few interval & it comes at
quite a cost, so SQL Log shipping is more cost effective.

If the SAN does 'compressed' replication across the WAN, would
this be more efficient that SQL mirroring/replication?  Or does
SQL mirroring/replication also has 'compressed' IO writes concept?

Using SQL replication/mirroring, the Production SQL server has to
communicate the write IOs to the DR site SQL server & then the
DR SQL server writes to the DR SAN : is this the sequence it works?
For SAN replication, any write IOs performed on the Production
SAN will be 'replicated' directly by the DR SAN without going
through the DR site's SQL server.  Thus, isn't SAN replication more
efficient since it doesn't have to go thru the DR SQL server?

I suppose for "read"  IOs, be it SAN replication or SQL mirroring,
it would only read from the local data & not from DR SAN.

I ever support an OpenVMS with remote replication of HP EVA
SANs over 100Mbps WAN.  For regular office hours transactions
done by users, won't detect much of a difference but when a
system administrator is copying large chunks of data (of tens of
GBs) from one disk to another, I'll have to disable SAN replication
over the WAN & this would enable the disk to disk copying job
to complete in one fifth of the time.

Is there any way in MS SQL to turn on monitoring of write IOs
(in terms of MBytes/sec) to the database as I would like to see
what's the highest write IOs to the database during peak hours.
Appreciate if someone can provide step by step commands/
setup  to do this
If you are only replicating to a single destination, MS recommends mirroring:

However, the basis for the article is actually combining the two, so you could mirror to a primary recovery site and log ship to a secondary.

This comes down to how much performance are you willing to sacrifice for that last minute of data protection or that additional minute of uptime (I'm going to assume you are only considering the synchronous, high-availability mirroring and not the asynchronous method since it would be nearly identical to log shipping).

The advantage of a synchronous mirror is data redundancy and availability - if one site blows up you'll have all the committed data in another place.  You wouldn't have to worry about losing records that would normally have been on the next log shipment.  If it is that critical that you never loose a record, then this might be your only option.  Rolling maintenance is also an option.  Benefits are explained here:

However the cost of this data integrity and availability is performance (as you have already mentioned).  If you do not have performance issues now, and your mirror is the same or better in terms of hardware, than perhaps you'll be ok.  But just based on your log numbers, you need a pipe capable of providing an average of 24Mbps to your sql server.  Of course that's average.  If your transactions are large, you'll need much more than that not to make wan latency a big issue.

The additional cost that is also overlooked is in house expertise.  Log shipping is fairly simple in setup, concept, and maintenance.  HA mirroring requires an additional set of skills to setup, maintain, and test.  For the sake of disclosure I'll say that I've only used log shipping and two way merge replication.  I stumbled into the topic of mirroring in another post and decided to get into some of the details in case I ever needed it for my self:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I think this depends on your WAN link which may be compressed already.  In that case, further compression may be negligible.

This is a question for your SAN guy, but it concerns me that he is "Totally unfamiliar with SQL mirroring."  This san mirroring isn't an option in my eyes unless there is a sql server configuration that supports it.  Otherwise, he may be talking pure disaster recovery and not high availability.
Look at the perf counters SQL Server:Buffer Manager:Page reads/sec, SQL Server:Buffer Manager:Page writes/sec
sunhuxAuthor Commented:
Thanks very much B_Levitt.

For "SQL Server:Buffer Manager:Page writes/sec", can it be translated
to how many bytes/sec of writes?  Or one page translates to how many
sunhuxAuthor Commented:
One last confusion to clear:

EMC sales person told me EMC SAN replication (known as SRDF ) is
much more reliable than MS SQL DB mirroring & certain MS SQL
table can't be mirrored using MS SQL DB mirroring method.
Is this true or this EMC guy is trying to push his product?

On the other hand, someone in EE said he encountered certain
issues with SAN replication before & MS SQL DB mirroring works
(not DB replication) works much better for him though he did
not elaborate.

So I have 2 conflicting advice above: the one from EMC sales
person conflicts with someone in EE
Aaron TomoskySD-WAN SimplifiedCommented:
Before you get too deep in research, make sure the emc option is within your budget. 2 sans and a HA license costs way more than a second SQL server.
Very good point on the cost comparison.

I don't understand his comment on "MS SQL table can't be mirrored."
sunhuxAuthor Commented:
>"MS SQL table can't be mirrored."
To clarify, I seems to have read somewhere that one or two tables
(eg tempfile table?) can't be mirrored over using MS SQL mirroing
but this table may not be crucial?  Correct me if I'm wrong
I can see temporary tables only being an issue if you're using global temp tables (##table vs #table).  I would assume that there isn't much option to mirror tempdb where these tables are written to.  However, just because the tempdb data can be SAN replicated, that doesn't mean there is a sql server configuration that supports these replicated databases.  Again, if SAN replication is still an option to consider, I would make sure that it INCLUDES the setup of the sql servers to support it.  Otherwise it's simple data replication and automatic failover my not be part of that equation.  Personally, the idea of some other system other than sql server maintaining mdf and ldf files is not one that I'm comfortable with.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.