Thursday 23 February 2012

Performance increased with Multiport SQL Server

 

In this article I am trying to demonstrate you how the default instant of the SQL server listens on multiple TCP port.

By default when we are installing the default instance of the SQL server the TCP port 1433 is allocated. If the requested comes from multiple terminals the default port must be overloaded. So if we configure the default instance of SQL server as multi port TCP the load will be balanced. It will increase the performance of the SQL Server.

If the default port of SQL server is overloaded, then the client's connections are reset or forced to be reset to new port settings of the SQL Server.

How we configure the multiport TC in default instance of the SQL Server:

1.    In SQL Server network utilities select the TCP properties and added the new port separated by coma like this. 1433, 5000 etc.

2.  Stop and restart the SQL server and retrieve the error log

SQL
server listening on TCP, Shared Memory, Named Pipes.<BR/>

SQL server listening on 157.54.178.42:1433, 157.54.178.42:5000, 127.0.0.1:1433, 127.0.0.1:5000.

3.    In the SQL server client network utility modify your clients to spade load across TCP port.

 

For a general example, suppose we have two webs server named "webServ1" and "webServ2" the both use the same port 1433 of default SQL server instance, so the load is always high. Now it is distributed among port 1433 and port 5000 so the load is balanced and performance is increased.

  

Hope that the article is quite interesting and thanking you to provide valuable time in it.

 

 

 

Posted by: MR. JOYDEEP DAS

6 comments:

  1. Were is the performance improvement. How can listening on more than one port can increase performance. Port will never be a performance bottleneck in SQL server as long as I know. Correct me if I am wrong

    ReplyDelete
  2. Teshome, I haven't seen a bottleneck in IP communications either, but I suppose it can happen.
    I've never gone to "SQL Server network utilities" before, so I looked it up. It has a general page about it, a title "To open..." followed by a link that redirects you back to the article. IE useless.
    I went into SQL Configuration tool. Under "SQL Server Network Configuration" I picked "Protocols for MSSQLSERVER" I double clicked the "TCP/IP" Protocol name. Opens a Properties window with two tabs. On "IP Addresses" it has several IP labels (Mine has 1 through 9 followed by All) each (Except All) with several labels including Active, Enabled, Dynamic and regular ports and a unique IP Address. All of mine say they are Active and not enabled. Since this is my local device, and I'm not listening for anything, this is fine.

    ReplyDelete
  3. Thanks for given us ip related knowledge...

    ReplyDelete