Sep
15
2011

Redirecting SQL Client connection to different instance of SQL who is listening to 1433 (default port) without altering client ConnectionString

Here is a very useful article by Vipul T. Shah via his blog

http://blogs.technet.com/b/vipulshah/archive/2010/06/07/redirecting-sql-client-connection-to-different-instance-of-sql-who-is-listening-to-1433-default-port-without-altering-client-connectionstring.aspx

Excerpt from his article,

Scenario:

Current Setup:

  • SQL Server 2005 Default instance – PRODServer listening at default port 1433

Client Connection String using SQL Native Client – Provider=SQLNCLI ; Server=PRODServer; Database=PRODDB; Trusted_Connection=Yes; with TCP/IP protocol with default port 1433

Now – Upgrade Happens… a side-by-side upgrade on a same server.

New Setup:

  • SQL Server 2008 Named Instance – PRODServer\NewPRO

Now if you change the listening port of original default SQL Server to something other than 1433 (for its all IP addresses on TCP/IP protocol from Server side Network property).. and then change new instance’s listening port to 1433…

..confused?

1. Go to old SQL server network TCP/IP settings, modify the port to some other available port number, lets say 1435
2. Now go to new SQL server network parameter, modify TCP/IP settings and configure port as 1433
3. Now shutdown the old SQL server and test the client connections, connections should work for new SQL server without any modification required i.e. client will be able to connect to upgraded version of new instance of SQL Server without altering anything on client side connecting string.

This is very useful when you have many client applications and you don’t want to make changes to any client code.

Download PDF

Related Posts

About the Author: Nitin Garg

Indian born, trekker, biker, photographer, lover of monsoons... I love to blog the topics I research and find useful for self or online community to save time and energy :) Everything you read on my blog is my own personal opinion!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 174 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

May 2019
M T W T F S S
« Nov    
 12345
6789101112
13141516171819
20212223242526
2728293031  

View Post by Categories

%d bloggers like this: