CODEWITHSUNDEEP
sql-server-2005installationsql-server-2005-express
zac
zac

Reputation: 4908

SQL Server Express 2005 set tcp listen port using command tool

I am making silent installation for SQL Server Express 2005 using the following command

SECURITYMODE=SQL DISABLENETWORKPROTOCOLS=0 SAPWD="****" ADDLOCAL=SQL_Engine,SQL_Data_Files,SQL_Replication,Client_Components,Connectivity,SDK

I need to know is there a parameter or a command line utility to configure the service to listen to port 7005 (see picture)

http://www.databasejournal.com/img/2007/01/mak_CLT_image002.jpg

Thanks

Upvotes: 0

Views: 4100

Answers (2)

vagelis
vagelis

Reputation: 334

I know this is more a workaround than a proper solution, but instead of specifying (and then connecting to from your application) a certain TCP port, you can instead enable the SQL Browser service during the silent installation of SQL Server. This will enable your app to connect to the instance you're interested based on the Instance Name, not the TCP port.

It may have a performance penalty on creating a new connection, since an additional network round-trip is needed for the client to acquire the port for the Instance, but (a) for client-server apps with long-living connections it won't be a problem, and (b) for app-server apps with connection pooling, a well-configured pool won't be affected much (I think), or it may need slightly more connections to achieve the same performance.

An additional advantage is that you can have more than one SQL Server instance on the same host and not care about ports. For example, in your (and mine) silent installation scenario, you would have to first check whether the port you want is used by another instance. Using the SQL Browser removes the need for this logic in your installation.

Upvotes: 0

Jeff Tucker
Jeff Tucker

Reputation: 3427

I'm currently trying to mess with this, here's what I've found:

  1. To make Sql server listen on TCP at all, you need to configure it to do so. You can use sac.exe to load a configuration from a file. Go to a working sql server configured with TCP/IP enabled (and whatever else) and run "sac.exe out settings.txt". Now, on a computer you wish to enable tcp on, run "sac.exe in settings.txt" and then restart the service. sac.exe is in the "%programfiles%\Microsoft SQL Server\90\Shared" directory

  2. To configure to a specific port, you'll have to edit the registry values. This will show you the keys to edit http://support.microsoft.com/kb/823938 however to script this, you'll need to use the "REG ADD" command in a .bat file and give the appropriate value to the appropriate key. FOr me, it's HKLM\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll\TcpPort and set it to the port number (default for sql is 1433) and then set HKLM\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll\TcpDynamicPorts to empty.

  3. Restart the SQL Server Agent (Net Stop "Sql Server (SQLEXPRESS)" Net start "Sql Server (SQLEXPRESS)" on my machine)

Hope this helps you or someone else searching for this information (like me)

Upvotes: 1

Related Questions