Solved

SQL Server 2008: Changing Port for existing database

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

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

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

Accepted Solution

by:
Pawan Kumar Khowal 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

First some basics on Windows 7 Backup.  It has 2 components one is a file based backup which is stored in .zip files each zip is split at around 200 Megabytes and there is the Image Backup which is as the name implies a total image of the partition …
When you start your Windows 10 PC and got an "Operating system not found" error or just saw  "Auto repair for startup". After a while, you have entered a loop for Auto repair which does not fix anything and you will be in a  panic as all your work w…
This Micro Tutorial will go in depth within Systems and Security in Windows 7 and will go into detail regarding Action Center, Windows Firewall, System, etc. This will be demonstrated using Windows 7 operating system.
This Micro Tutorial will give you a introduction in two parts how to utilize Windows Live Movie Maker to its maximum capability. This will be demonstrated using Windows Live Movie Maker on Windows 7 operating system.

705 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

18 Experts available now in Live!

Get 1:1 Help Now