Solved

SQL Server 2008: Changing Port for existing database

Posted on 2016-10-31
4
71 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
[X]
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
  • 3
4 Comments
 
LVL 29

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 29

Expert Comment

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

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
OfficeMate Freezes on login or does not load after login credentials are input.
This Micro Tutorial will teach you how to change your appearance and customize your Windows 7 interface to your unique preference. This will be demonstrated using Windows 7 operating system.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

626 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