Problems with 32bit and 64bit SQL Server 2008 instances on the same server

Hi all,

I have a Windows 2008 x64 server (MINERVA) set up with two SQL Server 2008 instances: a default instance called MSSQLSERVER which was installed as x64 and a second named instance, SQL32, installed as x86.

The default instance is the primary server.   The other 32bit instance is solely intended to connect to a 32bit ODBC legacy data source (our accounting system).

I installed the default instance first, then the SQL32 instance.   Both are patched to SP1, and report a version of 10.0.2531.  Both have TCPIP and Shared memory protocols enabled and named pipes and "VIA" disabled.

Now the problem is that strange things happen connecting to the SQL32 instance...

On the server (MINERVA), connecting to MINERVA (the default instance) connects me to the default instance...OK

On the server (MINERVA), connecting to MINERVA\SQL32 connects me to SQL32...OK

On the server (MINERVA), connecting to MINERVA\SQL32,1433 .... connects me to the DEFAULT INSTANCE, *NOT* SQL32.

On a workstation, connecting to MINERVA (the default instance) connects me to the default instance...OK

On a workstation, connecting to MINERVA\SQL32 times out.

On a workstation, connecting to MINERVA\SQL32,1433 .... connects me to the DEFAULT INSTANCE, *NOT* SQL32.

Any ideas how I can access SQL32 from a workstation?

Thanks,

Ben.
ben84Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ben84Author Commented:
Oops, I should have added that I am using the Standard edition of SQL Server 2008.
experts-exchange.jpg
0
nmcdermaidCommented:
Well , given that the default instance runs on port 1433 (this can be reconfigured but thats the default), then you are sending conflicting information.
SQL32, 1433
means connect to the instance called SQL32, and connect on port 1433. These are two different instances on two different ports. For whatever reason its picking the default.
Why do you need to specify an instance name AND a port?
If you send your configuration information we can verify that indeed the default instance is running on 1433 and the SQL32 instance is running a different port.
 
BTW You may not have to install a 32 bit instance of SQL Server to connect to a 32 bit datasource, unless you MUST use a linked server to connect.
You can, for example, run a SSIS package in 32 bit mode (from your 64 bit SQL Server Agent), which will read from a 32 bit ODBC datasource and write to your 64 bit SQL Server (over TCP/IP, which is 'bitless')
0
nmcdermaidCommented:
I see your other problem is a timeout when connecting from a workstation to a server. This is probably because you have a firewall on your server blocking the incoming port.
First configure your SQL32 instance to listen on a free static port.
From the web:
"follow the steps that are described in the How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager) topic in SQL Server 2005 Books Online or in SQL Server 2008 Books Online"
Once you have it listening on a static port, test from your server that you can connect on that port.
Then open your firewall on your server to accept incoming connections on that port
Then test on your workstation.
The easiest way I find to test basic network connectivity on a port is type this at a command prompt:
TELNET <hostname> <port>
So you would type
TELNET MINERVA <your static port number>
on both the server and workstation
and you should get a black screen, indicating that a connection can be opened.
 
This verifies that you can connect at the network level and you can continue your troubleshooting from there.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ben84Author Commented:
Hi nmcdermaid,

Thanks very much for your comments.

I mistakenly thought that all SQL Server instances were accessed (via TCPIP) through the same port, and that the SQL Browser service was responsible for directing incoming connections to the correct instance.

When I looked at my port settings, the default instance was specifically set to the default port (1433), but the SQL32 instance static port settings were blank.  I set SQL32 to listen on static 1434, cleared all the dynamic port settings and - YAY - it all works now.

Thanks very much.

Ben.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.