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

x
?
Solved

DR replica for a MS SQL 2005

Posted on 2010-09-14
9
Medium Priority
?
366 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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

On July 14th 2015, Windows Server 2003 will become End of Support, leaving hundreds of thousands of servers around the world that still run this 12 year old operating system vulnerable and potentially out of compliance in many organisations around t…
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

877 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