Newbie: HA over a WAN 2

[Stevehewitt]

Hi Guys,

I'm not much of a DBA, so please bare with me!

I have a number of DB's on a server in our main office running SQL 2005 SP2 Enterprise. We have a hot-site DR facility with a VPN between both sites - with the servers at the DR site on our domain (just a different AD site).

As a SME, we can't afford clustering HA, however with having a DR site with a number of servers I'm looking at ways so if we have a disaster, everyone gets shipped over to the DR site and after no more than 15 minutes of configuration we have our SQL databases running again.

From what I understand, there's three options. Replication, Database Mirroring and Log Shipping.

My worries are that we only have a 2Mb 50:1 DSL circuit between the office and the DR site. Also, if there is any data loss then the DR copy will be next to useless, so I need it to be atomic with assurance that the data committed to the DR site is the full record. Finally, I can't use the high-safety option in database mirroing (waiting for changes to be commited to the DR site before releasing the local database to further changes) e.g. synchronous operation. This is because the of speed of the circuit between the sites would create too much of a delay on the local clients.

Any advice really appreciated!

Cheers,



Steve.

"They have the internet on computers now!" - Homer Simpson

 

[ptheriault]

With all 3 options you will still have to move the same amount of data. I use Database Mirroring to my DR site. although I have a 10mb pipe. It works very well and offers automatic failover which the other two option don't. With some minor changes to code your application could fail over in seconds.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Stevehewitt]

Thanks Paul.

Do you mind me asking what mode you use it in? I'm worried about the impact of using high-safety will have performance wise on the clients in our main office. (Due to the link speed)

Cheers,



Steve.

"They have the internet on computers now!" - Homer Simpson

 

[ptheriault]

Use I high performance mode for one of my mirrors and the other I use high safety. The one I use hight safety on the boxes are right next to each other. for the high performance I have one server in VA and the other in RI. It works very well. I have never noticed a performance hit and the mirror is never more than a few MB behind the primary.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

Replication isn't really a good DR solution. It's very difficult to get the data back from the DR site to the main site after the event.

Mirroring and Log Shipping are basically the same thing. With Log shipping you move the logs on a scheule. With Mirroring you move the logs in real time (or near real time). Mirroring is the easier of the two to fail back. With a slow WAN link like you have you could easily flood the WAN which would then cause slow downs if you are using the auto failover via a witness.

Both methods are transactional which means that you won't get a partial record change. You will get the transactions in the order that they were commited on the primary server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Stevehewitt]

Thanks to both of you for your help.

I've opted for DB Mirroring in the end - which works fine! (Much better than I thought)

However I had a hell of a time setting it up. Between all of the different suggestions on forums and stuff I spent ages trying to get it to work. So to help anyone else out I have done a FAQ on it (pending approval) and if your in a similar situation to me regarding WAN link and wanting HA on the cheap I recommend DB mirroring.

Cheers,



Steve.

"They have the internet on computers now!" - Homer Simpson