Failover clustering 1

[robsuttonjr]

I have a scenario which I want to throw out for suggestions. We plan to have a central datawarehouse with 2 SQL Servers (mirrored). So we will purchase 1 sql server license and mirror the 2 servers. Now we have 25 store locations which will have say sql server express and will be setup to push daily store data to the central mirrored sql server.
1.) If the active sql server goes down how does the store sql express connect to the passive server??
2.) More important what is the best way to send data from remote stores to the central server and have rollover automatically to passive server vice versa if one or the other server is not available.



Regards,

Rob

 

[mrdenny]

First of all if you are using mirroring to backup from one server to another, you still need to license the second server. The only way that you get away without paying for the second node is if you are using MCSC in an Active/Passive cluster.

All that said, let's work with the issue at hand.

Since your fail over will be manual anyway (you'll need to tell the mirrored server that it is the active server) you can use DNS to handle the switch over.

The basic setup would be something like this.
The physical machines are called PRODSQL1A and PRODSQL1B. You setup a DNS record called PRODSQL1 and have it point to PRODSQL1A. Then if PRODSQL1A fails, you simply change the DNS record to point to PRODSQL1B. The downside to this, is that DNS replication takes time to propigate accross the entire network.

If you are using SQL 2005 database mirroring, and the app that's handelign the communication between the stores and the mail SQL server is a .NET 2.0 app you can tell it both machines and it will handle the failover automatically.

Based on the information provided I would recommend an Active/Passive cluster with a SAN backend using Microsoft Cluster Service MCSC. This will provide you with a five nines enviroment that your stores can connect to with automatic fail over in the event of a hardware failure of the active node. In addition when it comes time to patch the OS of the active node you have only a few seconds of down time while you fail over to the passive node so that you can reboot the just patched node.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com