Solved

DR replica for a MS SQL 2005

Posted on 2010-09-14
9
362 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 6

Accepted Solution

by:
Froggy_chris earned 500 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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Learn about cloud computing and its benefits for small business owners.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

752 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