July 31, 2014 at 6:56 am
Hi All,
I'm trying to configure a named instances of SQL Server 2012 SP2 to have specific IP addresses and listen to that IP on port 1433. Alongside this, I have the default instance running on a different IP and listening to that on port 1433.
I've managed to get them configured so that they are reachable using the IP, and looking at the log for each they are listening correctly on that IP on port 1433; in addition, the default instance connects fine using the SERVERNAME.
However, when I try and connect to the named instance using the SERVERNAME/INSTANCENAME syntax, I receive the following:-
"Cannot connect to SERVER\INSTANCE.
Instance failure. (System.Data)"
Configuration for the TCP/IP protocol for that instance is as follows:-
Protocol Tab
Enabled= Yes
Keep Alive = 30000
Listen All = No
IP addresses Tab
IP1
Active = Yes
Enabled = Yes
IP Address = 10.1.1.1
TCP Dynamic Ports = 0
TCP Port = 1433
IP2
Active = Yes
Enabled = No
IP address = 10.1.1.2 (this is the ip of the default instance)
TCP Dynamic Ports = 0
TCP Port = 1433
IP3
Active = Yes
Enabled = No
IP Address = 127.0.0.1
TCP Dynamic Ports = 0
TCP Port = [blank]
IP4
Active = Yes
Enabled = No
IP Address = fe80::5efe:10.1.1.1%12
TCP Dynamic Ports = 0
TCP Port = [blank]
IPALL
TCP Dynamic Ports = [blank]
TCP Port = [blank]
The SQL Browser service is running and prior to switching off Listen To All, I could connect to the instance remotely using the instance name.
Any thoughts on where I need to look next?
Cheers
Matthew
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
July 31, 2014 at 7:37 am
Matthew Darwin (7/31/2014)
Hi All,I'm trying to configure a named instances of SQL Server 2012 SP2 to have specific IP addresses and listen to that IP on port 1433. Alongside this, I have the default instance running on a different IP and listening to that on port 1433.
I've managed to get them configured so that they are reachable using the IP, and looking at the log for each they are listening correctly on that IP on port 1433; in addition, the default instance connects fine using the SERVERNAME.
However, when I try and connect to the named instance using the SERVERNAME/INSTANCENAME syntax, I receive the following:-
"Cannot connect to SERVER\INSTANCE.
Instance failure. (System.Data)"
Configuration for the TCP/IP protocol for that instance is as follows:-
Protocol Tab
Enabled= Yes
Keep Alive = 30000
Listen All = No
IP addresses Tab
IP1
Active = Yes
Enabled = Yes
IP Address = 10.1.1.1
TCP Dynamic Ports = 0
TCP Port = 1433
IP2
Active = Yes
Enabled = No
IP address = 10.1.1.2 (this is the ip of the default instance)
TCP Dynamic Ports = 0
TCP Port = 1433
IP3
Active = Yes
Enabled = No
IP Address = 127.0.0.1
TCP Dynamic Ports = 0
TCP Port = [blank]
IP4
Active = Yes
Enabled = No
IP Address = fe80::5efe:10.1.1.1%12
TCP Dynamic Ports = 0
TCP Port = [blank]
IPALL
TCP Dynamic Ports = [blank]
TCP Port = [blank]
The SQL Browser service is running and prior to switching off Listen To All, I could connect to the instance remotely using the instance name.
Any thoughts on where I need to look next?
Cheers
Matthew
Your named instance needs its own port. The default instance is using port 1433. Normally named instances use dynamic ports assigned when the instance starts. The SQL Browser service is what allows you to connect to the named instances. If I remember correctly you still need the Browser service to connect to named instances when you static the port used unless you specify the port in the connection string used to connect to the named instance.
July 31, 2014 at 7:50 am
Is that true even though they're using different IPs?
Basically what I'm trying to achieve is that both instances will appear as the default instance on seperate DNS entries for those IPs in order to mimic the behaviour of a live environment; but to do that I need them both listening on the default 1433 port.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
July 31, 2014 at 8:04 am
Matthew Darwin (7/31/2014)
Is that true even though they're using different IPs?Basically what I'm trying to achieve is that both instances will appear as the default instance on seperate DNS entries for those IPs in order to mimic the behaviour of a live environment; but to do that I need them both listening on the default 1433 port.
Best way to find out is test it.
July 31, 2014 at 8:08 am
Indeed, usually the way to get stuff done!
Ok, so after a quick test changing the port for the named instance, this is now clearly only listening on that ip and port, and can connect to it using the SQL Browser with SERVERNAME/INSTANCENAME.
However, because it's now on a port that's not the default, when I use the additional DNS entry for that IP only, it doesn't connect; which is really the aim of the game.
I'm wondering if this might work better if both instances were named instances rather than one named and the other the default instance?
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
July 31, 2014 at 9:07 am
Yep, that was the problem. I've uninstalled the default instance and replaced with a named instance; but otherwise using the same configuration (the new named instance is the same as above but with the ips reversed). These now connect using both the SQL Browser with SERVERNAME/INSTANCENAME and via the ip and the additional dns record without a port being specified.
Happy days!
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply