Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Server 2008: Changing Port for existing database

Posted on 2016-10-31
4
50 Views
Last Modified: 2016-11-07
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
Comment
Question by:koughdur
  • 3
4 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41867776
Try..

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

TCP Port - Your Number

Restart SQL Server Services.
0
 

Author Comment

by:koughdur
ID: 41869061
I did remove the 0 when I specified the port.  But that caused the database to fail to restart.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41869460
What error you are getting ? Which SQL specific version you are using ?
0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41871613
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you get continual lockouts after changing your Active Directory password, there are several possible reasons.  Two of the most common are using other devices to access your email and stored passwords in the credential manager of windows.
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
Windows 8 comes with a dramatically different user interface known as Metro. Notably missing from the new interface is a Start button and Start Menu. Many users do not like it, much preferring the interface of earlier versions — Windows 7, Windows X…
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question