SQL Server Named instances

[kpsreenath]

Informatica jobs were erroring out with a sql server driver error
while connecting to a named instance in SQL Server.
The issue was because in sql server named instance, the port was allocated dynamically and Informatica 7.1.4 (provides 4.21 version of Datadirect odbc drivers for SQL server) which dont have support for named instances with dynamic port allocation. This feature is only present from version 5 of the Datadirect odbc drivers for SQL server
To fix this issue, I need to allocate the port for the named instance instead of getting it dynamically.
My questions
1) Whats the impact of doing a port allocation for the named instance on the existing environment.
2) Is there any issues which we can foresee in the applications
3) Things which i need to be aware of
Thanks in advance
sreenath

[rmiao]

1. You can assign static port number for named instance, no impact as long as the port is not been used by other processes.
2. You need to specify port number when connect to that instance if it's not default 1433.
3. If uses odbc, you can put ip address and port number in dsn instead of instance name.

[kpsreenath]

Thanks rmiao
1) I wanted to avoid the scenario where in there are changes from the application end. If i am allocating a static ip for the named instance (As the per the microsoft documentation pasted below), I feel that the application doesnt have to change as the SQL Server client library queries the server computer on UDP port 1434 to collect the information about the destination instance of SQL Server
2) Is it a good idea to create an alias using the client network utility

Documentation from the Microsoft Website

Dynamic port allocation
Only named instances of SQL Server can use the dynamic port allocation process. In the dynamic port allocation process, when you start the instance of SQL Server for the first time, the port is set to zero (0). Therefore, SQL Server requests a free port number from the operating system. As soon as a port number is allocated to SQL Server, SQL Server starts listening on the allocated port.

The allocated port number is written to the Windows registry. Every time that you start that named instance of SQL Server, it uses that allocated port number. However, if another program that is already running on the computer is using that allocated port number when you start SQL Server, SQL Server chooses another port.

When an instance of SQL Server uses dynamic port allocation, the connection string that is built at the SQL Server client does not specify the destination TCP/IP port unless the user or the programmer explicitly specifies the port. Therefore, the SQL Server client library queries the server computer on UDP port 1434 to collect the information about the destination instance of SQL Server. When SQL Server returns the information, the SQL Server client library sends the data to the appropriate instance of SQL Server.

If UDP port 1434 is disabled, the SQL Server client cannot dynamically determine the port of the named instance of SQL Server. Therefore, the SQL Server client may not be able to connect to the named instance of SQL Server. In this situation, the SQL Server client must specify the dynamically allocated port where the named instance of SQL Server 2000 is listening.

[MAK]

Good idea is to
1. make the port number static - Server network untility or registry update
2. pass FQDN / ip address and port number to connect to named instance of the box.