Makind DB highly available (failover clustering?)

[TheBugSlayer]

Hi all.

I have a web site that uses a SQl Server back-end DB. I would like the data to be available to the users at all time. How can i configure it so that when one server fails another one is immediately made available so that clients never notice something went wrong?

I have been reading on failover clustering but i am not sure if that is the solution or if there are more.

Any help is appreciated.

 

[SQLBill]

You want to create an active/passive cluster. That can cost a bit of money. You create a 2-node cluster with a shared storage space. The two nodes are basically two separate computers/servers that can both use one set of hard drives (usually external). One node is the active node and when it fails, the second node takes over. Since both nodes 'see' the shared drives, you install SQL Server on the shared drives.

NODE1 NODE2
\ /
\ /
SHARED DRIVES


But to do this you need clustering software. You also will need the SQL Server ENTERPRISE edition (2 licenses). So this can be an expensive option. Our cluster cost in the area of $300,000 (4 CPU, 4 GB of RAM, 1000 GB of data storage, 24 slot tape library, fiber channel).

-SQLBill

 

[TheBugSlayer]

Thanks SQLBill. The cost is scary. But I would like to know more because although I read a bit on it, I did not understand until your explanation. Clustering software comes with Windows 2000 Advanced Server or Data Center.

I have some questions:
1- Is it that clustering is not possible with Standard Edition?
2- Can the storage space be another PC's hardware, or does it have to be something like the SnapServer?
3- Can you briefly explain how the two servers become SQL Servers (virtual?) since the real installation is made on the shared drive?
4- How do the client applications connect to the database? Who does the DSN point to?

THANK YOU in advance.

 

[neo03]

I will try and answer some of the questions you have since I just recently went thur this and now in the process of putting together a DFO with 2 nodes on a W2k Advanced server.

Answers to questions:
1)Windows clustering is currently only supported on Windows 2000 advanced server and thwe Data Center. The DFO configuration piggybacks on the Windows 2000 Advanced server clustering so this is why you need an OS that supports it.

2) From what I have seen it needs to be a sharable storage array but I could be wrong. This will allow incase node 1 goes down and then node two comes back up it still has access to the controller and disk drives.

3)Actually the data files are are on the shared drives. The OS and database application files are on the node. The two nodes keep a "heartbeat" going between the two nodes. If the heartbeat is lost from the Active Database then with in about 5 to 15 seconds the pasive node (node 2) will become the active node. Resulting in seamless responces basicly.

4)The DFO configuration reqires one of two things. 1) A Domain controler or 2) Domainlets. I am using the 2nd approach only becuase of cost of a another server vs domainlets. What ever option you choice will be the point that DNS would be pointing to and the domainlets/Domain controler would determine the node1 to be routing requests too. This is about all I know about this part. I am sure someone else maybe able to better answer this quesiton but I hope I gave you an idea of what happens.


Like what was mentioned before this is not a cheat configuration. Did you check into the other cheaper solutions?

Thanks,

 

[SQLBill]

1. Yes, only the Enterprise Edition can be used in a cluster.

2. I don't think that will work (another PC) we tried that as a test and could never get it working.

3. The SQL Server software is on the C drives of each node. The databases are on the virtual server. When you install, you start on the Primary Node. Part of the installation will ask for a name and IP address for the virtual server. Then once the installation is done, you fail the cluster over and the installation takes place on the second node.

4. As in #3, when you do the installation and create the virtual SQL Server, you give it a name and IP address. That's what the client applications use to connect. So, in my cluster I have two nodes and one virtual server:

name IP address (example only)
SQL1 xxx.xxx.xxx.xx1
SQL2 xxx.xxx.xxx.xx2
SQLVS xxx.xxx.xxx.xx3

Then the client creates an ODBC connection (TCP/IP) to xxx.xxx.xxx.xx3.

-SQLBill

 

[TheBugSlayer]

Oh my, you guys are so great! This was the best explanation to any of my questions ever! You know sometimes your read up on something but you don't quite grasp until you get your hands on it. Neo3 your comments were just as good. Obviously SQLBill is more seasoned.

You mentioned the other ceaper solutions, what are they?
I mean, I could manually redirect the connection to another DB in a few minutes but...

BTW, I am starting an MCDBA program now. Doing the optional topic first, ASP .NET.

Thank you.

 

[Darph]

I know this is an older post, but I wanted to clarify one thing that was mentioned earlier.

In SQL 2000 Active/Passive clustering (now known as a single instance SQL server), you only need 1 SQL license, because you only have a single instance of SQL running at any given time.

 

[TheBugSlayer]

Never mind, but could you explain further?

 

[Darph]

Explain about the licensing - or something else?