sunhux
asked on
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
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
1 page is 8k....
http://msdn.microsoft.com/en-us/library/ms190969.aspx
http://msdn.microsoft.com/en-us/library/ms190969.aspx
ASKER
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
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
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."
I don't understand his comment on "MS SQL table can't be mirrored."
ASKER
>"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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sites where selected SAN LUNs at 2 sites are 'mirrored' to each other.
Is this a more efficient solution that DB mirroring?