Solved

SQL Server 2008: Changing Port for existing database

Posted on 2016-10-31
4
33 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 23

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 23

Expert Comment

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

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

When you try to share a printer , you may receive one of the following error messages. Error message when you use the Add Printer Wizard to share a printer: Windows could not share your printer. Operation could not be completed (Error 0x000006…
While working, an annoying popup showing below will come and we cannot cancel or close it form the screen. The error message will come again and again.
This Micro Tutorial will give you a basic overview of Windows DVD Burner through its features and interface. This will be demonstrated using Windows 7 operating system.
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.

929 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now