0

We have a SQL 2008 clustered named instance Server01\instance02. The port is set to static 1466. We'd like to be able to connect using the name through a F5 Big IP Virtual IP.

We've set up a VIP named Server1 <--please note there is no 0 in this name

Attempting to connect to Server1\instance02 does not work.

Server1,1466 does work.

We have ports 1433, 1434 and 1466 open.
Can we configure this to be able to use the name without any changes to the SQL client.

Thanks!!

note:

A vital tool in troubleshooting this was to start the sqlbrowser service on the server like this:

sqlbrowser.exe -c

This displayed all requests to the service and helped rule out sql being an issue.

Sam
  • 1,990
  • 1
  • 14
  • 21

2 Answers2

1

You do need to have port 1434 (UDP) open as well as the instance port if you want to use the instance name to connect to the server. 1434 is the port that the SQL Browser uses and that is the service that will translate instance name to instance port. If you only want to have one port open than you can specify SERVER1,1466 in the connection string and that will force a TCP connection on the instance port.

Additionally you will need to set the TCP Port (not TCP Dynamic Port) in the SQL Server Configuration Manager to port 1466 on each node in the cluster. This way when the IP of the virtual SQL Server moves from one node to the other it will have the same IP and the F5 rules will still work.

Jason Cumberland
  • 1,559
  • 10
  • 13
  • The server is configured how you describe. Still getting a message stating that the server\instance cannot be found - which is the same message I'd get if I make up a nonexistent server\instance pair. – Sam Apr 27 '10 at 20:02
  • I had the network admin run a trace on the VIP and he thinks the VIP is now not letting 1434 traffic through. I used 'sqlbrowser.exe -c' to confirm that nothing was getting through to SQL. – Sam Apr 28 '10 at 21:37
  • Awesome, I hope he is able to trace down the cause for the blockage. Thanks for the answer and post back here if you have any other questions. – Jason Cumberland May 03 '10 at 17:31
0

You dont need the 1433 and 1434 ports open... just the 1466. Also, make sure you opened the SQL Configuration control panel and specified that your network adapter interfaces (all of them) are bound to port 1466. Also, "sa" account is disabled by default if you are trying to use that. Also, the "SQL Authentication" mode is usually disabled by default also (if you didnt specifically change it during install).

Finally, sometimes Windows Server 2003 or 2008 can deny a user account access if the account you connect with hasn't been given rights. Troubleshooting that can be difficult. I would recommend changing the SQL service (as a temporary test) to be running as the same NT user account you are trying to connect with. That will probably do the trick.

I have experience with BigIp's and SQL server... lots of it, if you need additional help ever.

djangofan
  • 4,172
  • 10
  • 45
  • 59
  • The IPAll TCP Dynamic Ports setting is 1466. How is the name SERVER1\Instance1 translated to 1466 by the BigIP? I made a change to my question - forgot the little detail that this is a clustered named instance. – Sam Apr 26 '10 at 16:25
  • Security is not an issue. The account is mine and is an admin on the server and instance. – Sam Apr 26 '10 at 17:07
  • Well, each machine in the cluster should have been setup using the "Default instance" IMHO. that way the BigIP can load balance to the cluster and the instance name is not even involved. By the way, if its a cluster then why would you want to connect to a specific instance anyway? The BigIP cant translate the instance name (I dont believe) and so using "default instance" is required here. even if BigIP could translate it , I think reconfiguring the SQL server to use default instance might be easier to accomplish. – djangofan Apr 26 '10 at 18:11
  • There is already a default instance, so making it default would be impossible. Even though a clustered instance gets a new virtual server - the xxx in xxx\zzz - which can be named anything, it unfortunately still needs the instance name to coexist in the OS. There is no load balancing going on. The BigIP is doing layer 4 acceleration and setting a very long timeout to correct a problem with a vendor supplied app - that's what the network guys tell me. – Sam Apr 26 '10 at 23:56
  • sounds like there is more than one cluster represented on those machines and the other cluster is already using the default instance names? wow, thats really inconvenient. there may be an answer to this but I don't think I have the knowledge I guess. sorry about that. i know not how to get the BigIP to do a JDBC URL Rewrite. – djangofan Apr 27 '10 at 00:26
  • BigIP rule (if possible) would be : any request that contains the string "Instance02" should be forwarded to port 1466 at machine Server01. Doesnt the BigIP let you do something like that? – djangofan Apr 27 '10 at 00:31
  • Thanks for your attempts. Our administrator doesn't know of any functionality which would do that. Maybe I need to start digging through some docs for him. – Sam Apr 27 '10 at 17:37