SQL 2005 Multiple Instances

[RichardParry]

Hi All,

In short, I am having a whole load of problems running SQL 2005 with multiple named instances.

Is it possible, to have the following configuration with SQL 2005?

1) Named Instance "A", with unique IP address, listening on port 1433

2) Named Instance "B", with unique IP address, listening on port 1433

These named instanced do work, but I must specify port 1433 in connection strings. If I don't, then the client will attempt to connect to the server using port 1433, but will return a server not found error after a lengthy timeout period.

It's a very weird one this! Your help is much appreciated!


Many Thanx, Richard

 

[chigley]

You should be able to have named instances running on the same ip address with different port numbers. Then specify the port number in the connection string

PROVIDER=SQLOLEDB;SERVER=server1, 65535;UID=sa;PWD=password;Trusted_Connection=True;DATABASE=YourDB;"

Or are you saying you have tried this?
I think the named instances will listen on the same ip address as the default instance and it is not possible to stipulate the ip address per instance, only the port number.


Charlie Benger-Stevenson
Hart Hill IT Ltd

http://www.harthill-it.co.uk

 

[RichardParry]

Hi Charlie,

Thank you for the reply. That's not what I want to do though, I dont want to specify the port number in the connection string, as this will cause problems (won't go into that right now!)

What I have done is setup each instance on it's own IP address, but specified the same port number, 1433. This works perfectly if I specify this port number in a connection string, for each instance, but as soon as I don't specify the port then it won't connect - but looking at TCPView on the server, the connection is still trying to come in through 1433 , but for some weird reason is refused.

Any ideas?



Thanx! Richard

 

[chigley]

Can you paste both connection strings? Thanks

Charlie Benger-Stevenson
Hart Hill IT Ltd

http://www.harthill-it.co.uk

 

[RichardParry]

Hi Charlie,

We use one of two connection strings in ASP functions, either using the MSDASQL provider or the MSDataShape provider (for SHAPE SQL queries).

An example of a common SQL connection string is;

data provider=MSDASQL;Driver={SQL Server}; Server=SERVERNAME;Database=DBNAME;User Id=DBUSERNAME;password=DBPASS;

We've been using these connection strings with a default install of SQL for years no problems, on many sites, but now have a new 2005 SQL server and want to divide some customers up by SQL Instance.



Thanx! Richard

 

[chigley]

Your connection string is using ODBC. Not that that is overly bad, its just worth a shot using oledb.

Try

PROVIDER=SQLOLEDB;SERVER=SERVERNAME;UID=DBUSERNAME;PWD=DBPASS;DATABASE=
YourDB;"

Its just worth a shot ....



Charlie Benger-Stevenson
Hart Hill IT Ltd

http://www.harthill-it.co.uk

 

[RichardParry]

Hi Charlie,

Just gave that a try and still no joy, should have also noted that I can't connect to the instances with SQL Management Studio/Enterprise Manager or MS Access, unless I specify the port number. Same for ODBC connections, they won't work until I specify the port number. Unfortunately this isn't any good, as some software I use that manages the SQL Databases cannot specify the port number.



Thanx! Richard

 

[chigley]

Are you blocking port 1434? This would need to be open for the client to talk to the SQL browser service. As you are connecting to a named instance it needs to query which port to use by making a request to the SQL Browser service on port 1434 on that server.

For that matter, is SQL Browser running?

If the port is closed then you could try however, using the Client Network Utility to create a client-side alias for the named instance that includes the IP address and port that the instance uses.

Further reading about SQL Browser service here

http://msdn2.microsoft.com/en-us/library/ms165724.aspx

A walkthrough on creating client aliases can be found here

http://www.tritac.nl/default2.asp?CID=50

I'd be tempted to try the client alias first.

Charlie Benger-Stevenson
Hart Hill IT Ltd

http://www.harthill-it.co.uk

 

[chigley]

Did this fix the problem?

Charlie Benger-Stevenson
Hart Hill IT Ltd

http://www.harthill-it.co.uk

 

[RichardParry]

Hi Charlie,

I am afraid that still doesn't help. I am having a good fiddle at the moment. It has something to do with dynamic ports I am sure. I will keep fiddling and let you know how I get on.

Should be noted if I install a default instance of SQL 2005 it works perfectly, but I need multiple instances to be honest.



Thanx, Richard