Go Premium for a chance to win a PS4. Enter to Win


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

Posted on 2012-03-18
Medium Priority
Last Modified: 2012-04-08
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?
Question by:sunhux
  • 6
  • 5
  • 3
  • +1
LVL 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 696 total points
ID: 37734996
It's more about latency than bandwidth. Here is a creative solution:

Author Comment

ID: 37736625
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?
LVL 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 696 total points
ID: 37737622
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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 35

Assisted Solution

johnsone earned 332 total points
ID: 37737850
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:


Author Comment

ID: 37738195
"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
LVL 11

Accepted Solution

b_levitt earned 972 total points
ID: 37738223
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:
LVL 11

Expert Comment

ID: 37738318
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.
LVL 11

Assisted Solution

b_levitt earned 972 total points
ID: 37738337
Look at the perf counters SQL Server:Buffer Manager:Page reads/sec, SQL Server:Buffer Manager:Page writes/sec


Author Comment

ID: 37740477
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
LVL 11

Expert Comment

ID: 37741738

Author Comment

ID: 37743267
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
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 37743304
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.
LVL 11

Expert Comment

ID: 37743470
Very good point on the cost comparison.

I don't understand his comment on "MS SQL table can't be mirrored."

Author Comment

ID: 37799273
>"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
LVL 11

Assisted Solution

b_levitt earned 972 total points
ID: 37801224
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.

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
What we learned in Webroot's webinar on multi-vector protection.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question