Solved

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

Posted on 2012-03-18
15
2,087 Views
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

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

Q2:
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

Q3:
Just for my education, what's the equivalent of MS SQL's log shipping
in Oracle?
0
Comment
Question by:sunhux
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 38

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 174 total points
Comment Utility
It's more about latency than bandwidth. Here is a creative solution:
http://mysqlguy.net/blog/2010/08/03/mysql-master-ha-yahoo
0
 

Author Comment

by:sunhux
Comment Utility
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?
0
 
LVL 38

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 174 total points
Comment Utility
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.
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 83 total points
Comment Utility
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:

http://docs.oracle.com/cd/E11882_01/server.112/e25608/toc.htm
0
 

Author Comment

by:sunhux
Comment Utility
"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.

Questions:
Q4:
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?

Q5:
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.


Q6:
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
0
 
LVL 11

Accepted Solution

by:
b_levitt earned 243 total points
Comment Utility
If you are only replicating to a single destination, MS recommends mirroring:
http://msdn.microsoft.com/en-us/library/ms187016.aspx

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:
http://msdn.microsoft.com/en-us/library/ms189852.aspx

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:
http://www.experts-exchange.com/Programming/Languages/.NET/Q_26958261.html
0
 
LVL 11

Expert Comment

by:b_levitt
Comment Utility
Q4:
I think this depends on your WAN link which may be compressed already.  In that case, further compression may be negligible.

Q5:
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.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 11

Assisted Solution

by:b_levitt
b_levitt earned 243 total points
Comment Utility
Q6:
Look at the perf counters SQL Server:Buffer Manager:Page reads/sec, SQL Server:Buffer Manager:Page writes/sec

http://msdn.microsoft.com/en-us/library/ms175903.aspx
0
 

Author Comment

by:sunhux
Comment Utility
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
bytes?
0
 
LVL 11

Expert Comment

by:b_levitt
Comment Utility
0
 

Author Comment

by:sunhux
Comment Utility
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
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
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.
0
 
LVL 11

Expert Comment

by:b_levitt
Comment Utility
Very good point on the cost comparison.

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

Author Comment

by:sunhux
Comment Utility
>"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
0
 
LVL 11

Assisted Solution

by:b_levitt
b_levitt earned 243 total points
Comment Utility
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.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now