Solved

Cannot connect to Active/Passive SQL2005 Failover Cluster via TCP/IP

Posted on 2008-10-04
14
1,015 Views
Last Modified: 2012-05-05
I've installed a new active/passive SQL2005 failover cluster, everything *appears* to be fine, but I cannot connect to the SQL virtual server via TCP/IP.  Named pipes works fine, but I need TCP/IP.  I have run the Surface Area Configuration tool and tried both TCP/IP only and TCP/IP + Named Pipes to no avail.  Cluster fails between the two nodes without error, runs fine when connecting via named pipes.

I've successfully implemented several SQL2000 failover clusters but this is my first SQL2005.  Any help would be appreciated.

O/S is Windows 2003 Enterprise R2 SP2 x64.  SQL2005 Standard x32.
0
Comment
Question by:CampusHosting
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
14 Comments
 
LVL 4

Expert Comment

by:randy_knight
ID: 22641698
A few basic questions:

1. Can you ping the Virutal Server name from the client and does it resolve to the Virtual IP?
2. If #1 is okay, can you telnet to port 1433 on the Virutal IP/Server?
3. What  port  is the instance listenting on?
0
 

Author Comment

by:CampusHosting
ID: 22651377
Yes, I can ping the Virtual IP, and I discovered that the Virtual Server is, in fact, listening for TCP/IP connections, albeit on port 2560.  So now I need to find out how to properly modify which port its listening on.  The Virtual IP does not appear in the interfaces list in SQL Configuration Manager, and even if I change the port for IP_All, after a restart of the Virtual Server, those values have reverted back to what they were.
0
 
LVL 4

Expert Comment

by:randy_knight
ID: 22651546
Your virtual IP doesn't show up in the IP Addresses tab in SQL Configuration Manager?  Also, are we talking about the right Virtual IP?  There will be 3 different virutal name / IP's associated with your cluster.

1. For the cluster itself.
2. For the MSDTC
3. For the SQL Serer Instance.

The third is the one you want.


0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:CampusHosting
ID: 22694764
Yes, the SQL Virtual Instance IP, 10.26.100.103, is the IP I am referring to.  Cluster IP is .102, VIP is .103, and it is not listed in the Conf Mgr.  I did discover that the Virtual Server is listening ont he dynamic port 2560, but am not certain how to change that.
0
 

Author Comment

by:CampusHosting
ID: 22694779
One additional comment, I do not have a unique IP assigned to the MSDTC clustered instance.  When configuring that resource, specifically configuring a local storage resource, there were no drives in the droplist, so I apparently have that incorrectly configured as well.  The server has two local drives, C: and D:, and two volumes mapped via iSCSI, Q: for the Quorum and S: for the Data volume.
0
 

Author Comment

by:CampusHosting
ID: 22906241
I've tried using the method here:

http://www.sqlserverfaq.net/2008/05/17/how-to-change-the-dynamic-port-of-the-sql-server-named-instance-to-the-static-port-in-a-sql-server-2005-clustered-instance/

To no avail.  When I bring the clustered sql instance back online it is still using dynamic port 2560.
0
 
LVL 37

Expert Comment

by:bbao
ID: 22916774
umm....

1. is there any firewall enabled on either SQL server?
2. can you PING the SQL server's physical and virtual IPs by names (NETBIOS *and* DNS)?

regards,
bbao
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22919217
I'm out of the office for a few days so I don't have all the normal SQL tools available and am working from memory.

In the Cluster Admin, you should have a Cluster Group which is your Q: drive and the MSDTC service and such.

Then you should have a SQL group that has a different virtual drive, the virtual IP and the SQL instance (SQL Server and SQL Agent) and such in it. You can only have one virtual instance per drive.

There is also a SQL Server Browser Service installed on each node as well. Make sure it is enabled on both.

Go into the SQL Configuration Manager and make the changes there on the active node.  There are 3 different places. Then flop to the inactive node and repeat.

If all else fails -- shut down all SQL services on both machines and manually edit it in the registry. Note that all warnings about regedit apply.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI10.0\tcp\Property1

Note that sometimes iSCSI is too slow to come up to handle SQL.
0
 

Author Comment

by:CampusHosting
ID: 22931056
Jimpen,

I do have the Cluster groups you mention, with the appropriate elements.  SQL Browser is enabled on both node's.

One odd thing, I do not see my Virtual IP in the SQL Config Manager.  I have the interface for my heartbeat connection, interface for the public connection, SQL cluster IP, system loopback, and then IPAll.

If I delete the TCP Dynamic Ports value for IPAll and enter 1433 for TCP Port, it returns back to port 2560 in the Dynamic field and <null> in the TCP Port field after I start the sql instance.

All of this is consistent across both nodes.

As I've been responding, I went back on the first node, made all the changes to 1433 (including in the registry location you specified), started the virtual instance, and <poof>, its back on 2560.

Must be something I'm still missing somewhere.

Thanks


0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 22940629
To use the technical terms: It sounds like your instance is hosed.

I hate to say it, but your best bet is to de-install and then re-install the instance. I ran into similar situation where I tried to use the "-c -m -T3608" to move the master database on an instance. Once the cluster/msdtc service had it registered, the only thing I could do was re-install.

http://support.microsoft.com/kb/224071


0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22989011
Sorry I couldn't have been of more assistance.  May all your days get brighter and brighter.
0
 

Author Comment

by:CampusHosting
ID: 22989031
Yes sorry, I ended up ripping everything out and reinstalling after which it is properly listening on port 1433 on the SQL Virtual IP.  Thanks for your help.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Force a website to use the second network card 3 41
SQL Syntax 6 41
SQL Syntax 6 32
T-SQL: How to extract records into a new table 7 21
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question