Solved

DR replica for a MS SQL 2005

Posted on 2010-09-14
9
357 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I've always wanted to allow a user to have a printer no matter where they login. The steps below will show you how to achieve just that. In this Article I'll show how to deploy printers automatically with group policy and then using security fil…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

743 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

15 Experts available now in Live!

Get 1:1 Help Now