Like most application software, SQL Server listens on a specific port for client connections.
What is the default port for SQL Server?
The default port is 1433. It is strongly recommended not to run SQL Server on the default port, as it is well-known. The general guidance is to choose a port number that is much higher, just make sure that it is unused!
How do I find which port my SQL Server is listening on?
There are several methods for determining which port SQL Server is listening on, though ideally this should have been documented at install time. Additionally, if the SQL Server Browser service is running – then you don’t generally need to know, unless you need to open up firewalls to allow database access.
SQL Server Error Log
When SQL Server starts up, it will make an entry in the error log about which ports are being listened to:
Server is listening on [ ‘any’ <ipv4> 50000].
SQL Server Configuration Manager
SQL Server Configuration Manager is the tool for – yes, you’ve guessed it – configuring SQL Server: services including service account passwords, protocols, and especially the port number(s) for TCP.
Can a SQL Server listen to more than just one port?
Yes, in SQL Server Configuration Manager you can enter the ports that you want SQL Server to listen on separated by commas.
Why might you want to do this? You might want to segregate traffic perhaps.
On a SQL Server 2005 pre-production box, I was giving a developer SQL Server Agent permissions to allow them to execute jobs – the new roles in 2005 such as SQLAgentOperatorRole, SQLAgentUserRole, and SQLAgentReaderRole. I used to have to deploy a custom role in SQL Server 2000, but these new roles work just fine. Except the roles didn’t work today on this server.
I reproduced the issue by creating a new SQL login, and giving it the appropriate permissions…. there was nothing in the SQL log. But I did come across a Microsoft KB which talked about permission problems after moving MSDB. Had someone previously moved MSDB, or renamed the server?
I’d seen this before when an old dinosaur SQL Server was cloned for P2V, and then moved to a new domain. The Windows server was renamed, but no one told SQL Server that it’s name had changed. It caused all sorts of problems with job scheduling.
Today I had to re-familiarise myself with 32 bit memory settings on a legacy server that just hasn’t been migrated to 64 bit as yet – realistically, I haven’t had to think about 32 bit limitations for nearly five years! So, I dug out my old battered copy of Wrox’s SQL Server 2005 Performance Tuning. I read technical books that I don’t own on Safari Online, but my preference is physical!
So, the 32bit architecture limits the set of addresses the processor can access to 4GB. Each process has a total of 4GB of virtual address space (VAS), and this VAS is split evenly in the middle so the kernel has 2GB and user-mode has 2GB. This split can be moved to give the user mode 3GB by using switches like /3GB or /USERVA on Windows 2003. So, on a SQL Server system with 4GB and no switches, SQL Server will typically use 1.7GB for Total/Target Server, and a few hundred MB for other memory operations like linked servers, extended stored procedures etc.
To get past the 4GB limit, Intel physically extended the address bus to 36bit, which was called Physical Address Extension (PAE). This allows a 32bit operating system to address up to 64GB of RAM, but Windows Server 2003 must be Enterprise Edition, and it must be Service Pack 1 or later. It’s not all plain sailing – SQL Server can only use this extended memory for the buffer cache, so each SQL Server still needs some memory in that first 2 or 3GB for those linked servers etc.
You can use /3GB and PAE together, and Microsoft support this to 16GB… but it’s more complicated. I like simplicity if possible! And using them both together can throttle the system twice as /3GB reduces the non-paged pool whilst PAE doubles the size of each allocation.
For SQL Server, some configuration may be required. To allow SQL Server to access more than the 2GB memory limit (or 3GB if /3GB or /USERVA is configured), a setting called Address Windowing Extensions (AWE) must be enabled. Hand in hand with AWE is a OS privilege called Lock Pages In Memory – this enables SQL Server to hold on to memory if Windows essentially wants memory back. The potential downside is that other applications on a shared box may start paging instead.
– migrate to a 64 bit architecture as soon as possible!
– on 32bit, if you have 4GB of RAM use /3GB if memory is the bottleneck for SQL Server
– on 32bit, if you have more than 4GB of RAM, use /PAE together with AWE.
From time to time, DBAs will be called in to investigate connection issues – “my application couldn’t connect to the database at 13:30…. was the database ok?”
Management Studio is great for validating connectivity, but it’s not easily portable. Port Query UI (portqry) is a convenient graphical tool that allows ports to be tested to see if SQL Server is listening – and can be connected to from a test workstation or server. It comes as a ZIP download, it doesn’t install anything, and you just need to run the EXE. The added bonus is that it’s not part of the SQL Server suite, so application teams can often be persuaded that it’s legitimate proof that SQL Server really is available and listening.
Once it’s running, you just need to enter the server-name or IP address, and then either provide the TCP/UDP port – or select the SQL Server drop down, which will try the default port of 1433 together with the SQL Server Browser port of 1434. Then just click on the Query button, and the results will be displayed at the bottom.
Whilst at a client recently, I was called over as the Windows support team have received an alert that a SQL Server was running at 100% CPU. Strangely, it was on a server-name that wasn’t supposed to host SQL Server, according to the naming standards.
Whilst trying to drive from the back seat over someone’s shoulder, we couldn’t find the SQL Server binaries in the Program Files directory, or much other evidence of SQL Server – no data files or transaction log files; no directory structures; no client or configuration tools. The only “evidence” was TaskManager showing to all and sundry that it was a “SQL Server problem”. Again!
I looked at some tools that remotely list what programs and applications are installed on a server – nope, no SQL Server there. I pointed Microsoft’s MAP tool against the server as well – no joy! Truly frustrating! It turns out is was a version of Sybase, yet again showing the shared ancestry between SQL Server and Sybase!