[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

DR replica for a MS SQL 2005



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
akhalighi
Asked:
akhalighi
  • 5
  • 4
1 Solution
 
Froggy_chrisCommented:
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
 
akhalighiAuthor Commented:
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
 
akhalighiAuthor Commented:
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
Froggy_chrisCommented:
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
 
akhalighiAuthor Commented:
Thanks for your time , so above scenario is with mirroring ? or log shipping ?
0
 
Froggy_chrisCommented:
Only you can tell :)

Depends of your infrastructure, latency, RTO/RPO.
I can't judge that for your business.
0
 
akhalighiAuthor Commented:
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
 
akhalighiAuthor Commented:
closing .
0
 
Froggy_chrisCommented:
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now