Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have SQL Server 2008 R2 cluster active/active/active (a, b, c) and three instances run on this environment.

We did a test on one of the instances, and failover test.

  1. Manual failover from node c to b. After 3 minutes, the application cannot connect to SQL Server.
  2. Back to node c we are OK and application is happy
  3. Manual failover from node c to a. After 3 minutes, the application cannot connect to SQL Server.
  4. Back to node c we are OK and application is happy

Please give me some helps. Thanks

share|improve this question

migrated from stackoverflow.com Mar 13 '13 at 15:30

This question came from our site for professional and enthusiast programmers.

3  
Can you connect to the instance after failover? If so, look in the SQL Server error log and see what is happening. Could be a few things taking a long time (i.e. database recovery). If you can't connect to the instance, take a look at the cluster log and look for errors/issues. –  Thomas Stringer Mar 13 '13 at 15:39
    
see my reply here social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/… and mind telling us what's error received by application as looks like you don't know how to check cluster log. –  SQL Learner Mar 13 '13 at 15:40
    
@ThomasStringer I forgot about database recovery. That's a great point. –  swasheck Mar 13 '13 at 15:41
    
Also check to see if the passive nodes are possible owners of the instance. What it boils down to is that there are a lot of things that could be causing this and either 1) you need to dig deeper into the problem and/or 2) provide us with more information and details. –  Thomas Stringer Mar 13 '13 at 17:01
    
Check your failover cluster manager critic events log for the nodes that aren´t running. What do they say? –  user18264 Mar 13 '13 at 21:13

1 Answer 1

There are a few things that I can think of off the top of my head. You're running a multi-instance failover cluster so in theory I'd expect to see each node to be sized such that at any given point in time it can handle the load of all three instances. Chances are that this is not the case, but maybe it is. Ideally, you'd also have a spare node that can handle failures but that doesn't sound like it's the case here.

There are some configurations that you can check to ensure that you've not set yourself up for failure and the first one I'd check would be to run

sp_configure 'max server memory (MB)'

If your run value is 2147483647 then you've got it set to allow SQL Server to take as much memory as it thinks it needs. This is set per instance so when you have multiple instances trying to consume all available RAM you will get memory pressure.

Having said that (read: actually, start here), you've not given us any other information about what you've done to discover why the application stops responding. Is it just the application that connects to the C node that chokes, or does the original application also not work? This could end up being something as simple as the application connection string is connecting to the IP/DNS name of the C node and not the VIP. If this is the case, then when C is no longer serving SQL Server then you're not going to be able to connect.

Step 1: Ensure the connection strings are actually connecting to the instance/VIP name and not the nodes.

Step 1.5: (Thanks to Thomas Stringer), make sure that you're giving the new instance enough time to actually recover the database. Connect to the instance via SSMS and see if your databases are in recovery.

Step 2: If Step 1 is correct, then get on the node that is running multiple instances and see what's going on. I'd recommend using PerfMon because "Task Manager is a dirty, filthy liar" and looking at metrics for the various subsystems starting with Memory, Network, CPU, and Disk IO. This answer contains much of what you'd need in order to check for resource pressure assuming you have connectivity to the instance and the databases are all fully recovered.

share|improve this answer
    
Thanks for all your replies. From the application site, I had the error message: "EODBC Error Text: [IBM][SQLServer JDBC Driver]The requested instance is either invalid or not running." According to the staff who manage the cluster environment, it seems to me that the C node is the only good one and all of three instances is running on this node. After the manual failover, we could connect to the SQL instance from SSMS. About 3 or 4 minutes later we could not see. Thanks. –  user79127 Mar 13 '13 at 16:10
    
Are you saying that you believe all 3 instances are running on the C node, or is that what the DBAs told you? –  swasheck Mar 13 '13 at 16:29
    
Yes. I believe so. –  user79127 Mar 13 '13 at 16:51
    
Have you been able to confirm this with your DBA team? Do you have access to the instance(s) with SQL Server Management Studio? –  swasheck Mar 13 '13 at 16:59
    
I did try and after 3-4 minutes i could not connect to the instance. –  user79127 Mar 13 '13 at 17:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.