static port

How to Configure static ports for SQL Server

If a SQL Server instance is configured with a static port , SQL Server only listens on the specified static port. But it is important to delete all entries for dynamic ports otherwise SQL Server will listen on the static and dynamic ports.
 Use Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager) http://msdn.microsoft.com/en-us/library/ms177440.aspx
Follow these details to the letter. Failure to follow the details – can leave a situation where the SQL Server is listening to the static and dynamic ports.
Notes on configuring a static port for SQL Server
1)     If you want to use just a static port , clean up the entry for the dynamic port . If the dynamic port entry is not cleared , the setting  continues to be used.
You can easily verify  by use the sys.tcp_endpoints view.

view source
print?
1.SELECT       *
2.FROM            sys.tcp_endpoints
 You can also verify in the SQL Server error log , where an entry similar to this one appears:

view source
print?
1.Server is listening on [ 'any'  45426].

2)     Need to be careful to assign port not used by another program . Use netstat to view all ports currently open
view source
print?
1.netstat -a -o –n 

Comments

Post a Comment

Popular posts from this blog

current running queries in sql

SELECT r.session_id, s.TEXT, r.[status], r.blocking_session_id, r.cpu_time, r.total_elapsed_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s 1) When an application reports a memory pressure issue – use the DMV to identify the current load and choose a session that may be causing a problem. view source print? 1. SELECT * FROM sys.dm_exec_sessions 2) The memory_usage column returns the number of pages used by this session. ORDER BY memory_usage DESC and analyse the session. It may not be the session causing performance hit , but a methodical approach is important. view source print? 1. SELECT * FROM sys.dm_exec_sessions ORDER BY memory_usage DESC 3) Create a query to join with sys.dm_exec_requests. The sys.dm_exec_requests DMV returns detailed information about executed requests in SQL Server. This query includes a CROSS APPLY to sys.dm_exec_sql_text. view source print? 01. select es.session_id,es.memory_usage,sql_h...
Read more