Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 80
  • Last Modified:

SQL Server 2008: Changing Port for existing database

I need to change the port for a named SQL Server 2008 database instance running on a Windows 7 server.

I went into the SQL Server Configuration Manager and manually changed the ports.  I got the warning about having to restart the server to activate the changes.  I right-clicked on the server and clicked 'Restart'.  It stopped OK, but failed to restart.  I tried starting it three times and it always failed.

Then I changed the ports back to the settings they were before the restart, and when I clicked on the server to start, it started right up.

Do I need to change the port settings somewhere else?  For example, do I need to update the entries in the ENDPOINT table?  I was thinking the worse thing that would happen is that the database would start right up, but I wouldn't be able to see it from some of the remote computers.  I was surprised that a port change would cause the database to stop entirely.

One more thing:  there are two databases on this server.  One is an unnamed default database, and the other (the one I'm trying to change the ports on) is a named instance.  The unnamed database has a fixed port number.  The named database has 'TCP Dynamic Ports' set to '0' for IP1, IP2 and IP3.  It has a 5-digit number for IPAll.  The 'TCP Port' value is blank for all four IP cases.  I was trying to set the named database to a different fixed port.  Is it possible that the named database is somehow tag-teaming off the unnamed database's port?

I'm super new to SQL Server 2008 so this is all confusing to me.

Any help would be much appreciated.
0
koughdur
Asked:
koughdur
  • 3
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Try..

Remove 0 from TCP Dynamic Ports Fields, make them blank.

TCP Port - Your Number

Restart SQL Server Services.
0
 
koughdurAuthor Commented:
I did remove the 0 when I specified the port.  But that caused the database to fail to restart.
0
 
Pawan KumarDatabase ExpertCommented:
What error you are getting ? Which SQL specific version you are using ?
0
 
Pawan KumarDatabase ExpertCommented:
Try..

CREATE ENDPOINT someConnection
STATE = STARTED
AS TCP
   (LISTENER_PORT = 1450, LISTENER_IP =ALL)
FOR TSQL() ;
GO

Also go thru-

https://msdn.microsoft.com/en-us/library/ms189310.aspx
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now