Server Failover

[kevin plummer]

Hi All,

I'm trying to build SQL failover redundancy into my web app.

I'm planning to mirror an SQL server (so the mirror will have a different server\instance name) and need to build the failover into my connection string.

I need a way to check if the server is available, if not use the failover connection string to access data from the backup server. Is there anything in NT that will do this?

it needs to be super quick as I would need to check in my processlink embed so would be called a lot.

Any suggestions for a better alternative?

Cheers,

Kev

[urayoan]

Kevin, i never build an SQL server with fail-over, but i think the connection string is the same no matter the node is running the services.

If the services fail in one node, the are moved to the second node, but i think the instance run in the Virtual Node (check the illustration) so in theory, the connection string is always the same.

Hope this helps

[attachment deleted by admin]

[kevin plummer]

Thanks for your input Urayoan.

That is true if you setup windows servers with failover clustering and SQL with failover clustering. This requires shared storage as shown in your diagram and I'm not ready to fork out more money for that yet. My cheaper option was to just do mirroring. I think if I turn off LogonScreen and then do a simple Open and check for an error then that might work.

In hindsight I should have set up VMWare with shared storage from the start and used the built in HA to handle all the failover!

[Poul]

Kevin,

You could just  decide when you want to Change the Global variable for the connectionstring before you open the table(s).
And make sure that you control how persistent that value is.  (where the initial values are pulled/stored)

i do something like this on specific pages to dynamically flip my connection to use different databases/servers simultaneously.
i don't use this for Failover but i don't see why it couldn't work.
(could be application specific things to consider if you cache or keep things open outside of nettalk pages like in Netwebserver)

The alternative could be to simply note the new preferred server and Trigger a restart of your webserver. So it will start using the Backup...
I think i like this better as everyone gets an error (you could even do it gracefully for active users if required) and it forces everyone to login,
where you can notify about whats up...  and it works regardless of file drivers- you didn't say but i assume MS SQL.
They lose current transactions, but that's likely anyway unless you get quite clever or its really simple.

the challenge is if you want it automatic is deciding when to failover.
By the book with M$, you have clusters, and a witness, with heartbeats to etc
in the end it is an arbitrary assumption you will make, probably based on some timeout.

When Anything on that connection fails is when you might suspect.
Perhaps I would check when files open before some specific critical activities - like login, but not every call, i think.
But i guess it depends on your requirements.

Regardless, once you detect failure(or suspect), you need more aggressive testing analysis.
You would want to  "fail-over to Backup" mode only when you are confident its correct to do so.
Like test to make sure the backup is available first!
do some additional test to make sure your primary is truly unresponsive
or that it wasn't just a momentary hiccup ... 

poul

[kevin plummer]

Thanks for your input Poul. Yes is it MSSQL 2008. It is becoming complex for the reasons you state and automatic failover may create more headaches than it solves initially. I think I might just start out with manual failover. I've never had one fail in 15 years but just want a bit more insurance.