We are currently installing/configuring a new (2) two node server cluster with a SQL Server 2005 failover cluster. We have not used FactorySQL long, but we would like to take advantage of our cluster and install FactorySQL to use the cluster. I’m wondering if anyone has a step-by-step (detailed) to do this?
Current setup
*FactorySQL is running on a desktop PC.
*FactorySQL project connects to our current SQL server cluster that we are upgrading (no stand-alone DB install on the PC – MySQL, etc.)
Desired setup
*Install FactorySQL on both nodes of new Microsoft Cluster
*Have the FactorySQL project run on the node that has control of the cluster.
*Have the second node of the cluster running the FactorySQL project in a “warm” state and then take over in the event of a failure on node 1.
I would like to know the following:
We have already purchased a license. Do we need to purchase a second one for the second node, or are we OK with the one since node2 is only running if node 1 fails?
I would like a detailed step-by-step procedure for installing and configuring FactorySQL to run in the desired configuration
Any help would be greatly appreciated. New SQL cluster is set to go live early next week.
First off, you would definitely need another FactorySQL license. Any time you’re using redundancy with one node running in a “warm” state, both need to be activated. Some people have a cold stand by and use a hardware activation key to switch over, but that’s a much different scenario than what you’re describing.
I’ll have to think a bit about how you could accomplish the requirement of having the db master machine also be the FactorySQL master. FactorySQL redundancy works by having each node update a row in the shared database- the “best” node (determined based on several factors) is the master. The only real variable in this system is to use “node ranking” to force a specific node to be master when it’s up- not what you want.
Maybe I should ask you this: how can you tell which machine is the cluster controller? Can this be queried? If so, there might be a few options. Obviously we could try to work some sort of option into FactorySQL to give you more control over what it does (not a horrible idea, since others have tried to do slightly different things as well). Short of that, and perhaps quicker to implement, would be to use an updatable view in sql server to “fake out” FactorySQL. That is, FactorySQL just thinks it’s querying a table. That table can be a view instead, which can return whatever data is appropriate. In this way, it could always return that cluster controller as the master.
Jim,
With Datacenter edition and 4 node clusters (maybe 3) you get more magical clusters that behave more similarly to what intuition would dictate (load balancing, looks like a single logical DB). With a 2 node cluster you need to determine if you want an active/active or active/passive setup - the latter makes more sense. In that case you have a single database that can get “picked up” by a second warm cluster node (downside being that the warm node is just sitting there during normal operation). This should work well running FactorySQL as a warm backup.
I would read this redundancy on the cheap tutorial. It’s focused on using MySQL mirroring, but it should be insightful as many of the techniques and considerations are the same. Specifically, setting up failover datasources, considering “realtime” or SQLTags versus historical data and caching.