?
Solved

DR replica for a MS SQL 2005

Posted on 2010-09-14
9
Medium Priority
?
369 Views
Last Modified: 2012-05-10


We have a production SQL database and considering a DR instance for it. after a lot of research
I selected log shipping to keep secondary instance Suncronized with Primary one.

Today , this scenario crossed my mind ;


- let's say log shipping happens every 30 min. if something happens and we failover to DR instance; then we lose some changes that happened
during this period (0 to 30 min) , in addition ; users will make change to DR instance during the fail-over ; how all these changes will get merged
after Primary is backup ?

- Is there any utility or software to monitor this situation ? any suggestions ?
0
Comment
Question by:akhalighi
  • 5
  • 4
9 Comments
 
LVL 6

Expert Comment

by:Froggy_chris
ID: 33672399
Hi there,

all depends on how long you can have SQL down & how much data you can afford to loose.

- let's say log shipping happens every 30 min. if something happens and we failover to DR instance; then we lose some changes that happened
during this period (0 to 30 min_
+> correct

 , in addition ; users will make change to DR instance during the fail-over ; how all these changes will get merged after Primary is backup ?
-> you will have to set your logging shipping in the other direction (Secondary to primary), plan downtime and fail-over from secondary to primary.

- Is there any utility or software to monitor this situation ? any suggestions ?
SQL 2005 Enterprise as all built-in.

Note: If both servers are in the same site (meaning on fast network), you can investigate the (a)Synchronous mirroring, this might be useful to you.
0
 
LVL 10

Author Comment

by:akhalighi
ID: 33672511
It's a DR site , so .. I don't know ... whenever an outage happens .. could be 1 hour or more. does this mean that we loos data ??  both data on primary that isn't transferred to replica yet and the data that users updated during he outage....?

Is mirroring supported on SQL2005 ? I thought it needs specific hardware...
0
 
LVL 10

Author Comment

by:akhalighi
ID: 33672533
another question .. if we use mirroring .. It just syncs the changes or the whole database ?
and also .. after the outage .. what happens to the data that got modified on secondary during he outage ?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 6

Accepted Solution

by:
Froggy_chris earned 1500 total points
ID: 33672592
Well, I have using mirroring with SQL2005 for a while on Dell poweredge :)

It's a DR site , so .. I don't know ... whenever an outage happens .. could be 1 hour or more. does this mean that we loos data ??  
=> Yes the data between two backup point.

both data on primary that isn't transferred to replica yet and the data that users updated during he outage....?
No no.

Normal situation
Users work on Primary (P), data replicate to Secondary (S)

Outage
User fail over to S, P is down until repair

Outage recoverd
Users still work on S. From that point, you will initiate a replication from S -> P (user work on S)

When P is ready, you'll fail over (in a control manneer) back to P

the only loss of data happens at Outage time.
0
 
LVL 10

Author Comment

by:akhalighi
ID: 33672847
Thanks for your time , so above scenario is with mirroring ? or log shipping ?
0
 
LVL 6

Expert Comment

by:Froggy_chris
ID: 33672962
Only you can tell :)

Depends of your infrastructure, latency, RTO/RPO.
I can't judge that for your business.
0
 
LVL 10

Author Comment

by:akhalighi
ID: 33674013
Our database will grow about 2 GB each year. we have a 10MB VPN link between PR and DR site but our users traffic goes through the same link too. That's why we were considering log shipping for DR. |

That's why I asked if mirroring just sends the changes or replicates the whole database.

Good thing is that our business hours ends at 7:00 PM and then we can bring down both servers and copy the most updated DB instance back to Primary site. So the only issue is that we may loos some data at the time of the outage. Are you aware of any solution that can minimize it ?
0
 
LVL 10

Author Closing Comment

by:akhalighi
ID: 33681698
closing .
0
 
LVL 6

Expert Comment

by:Froggy_chris
ID: 33681970
Hi There,

Sorry for my lack of reaction so far:)

Our database will grow about 2 GB each year. we have a 10MB VPN link between PR and DR site but our users traffic goes through the same link too. That's why we were considering log shipping for DR. |
=> You;'ll have to measure here. the disadvantage here  of logshipping is that it will send every hours a possible massive amount of data. No load flatening.
This can be adressed partially by scheduling your log shipping to run on 10min base for example.

That's why I asked if mirroring just sends the changes or replicates the whole database.
=> Mirroring sends only the writes to the replica (well, not at starting point point of course(

Good thing is that our business hours ends at 7:00 PM and then we can bring down both servers and copy the most updated DB instance back to Primary site. So the only issue is that we may loos some data at the time of the outage. Are you aware of any solution that can minimize it ?
=>  Mirroring or log shipping ? :)
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Learn about cloud computing and its benefits for small business owners.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

589 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