Change the port for sql server service broker

SQL Server Service Broker by default uses port TCP 4022. For security policy reasons I prefer to use a different port number. Before deciding on a port number check it won’t conflict with another application port usage. Use netstat to view all ports currently open . Read How to Configure static ports for SQL Server for more details Once you’ve decided on a port number – use these scripts to create the service broker endpoint and assign the listener port --create ENDPOINT USE master; GO CREATE ENDPOINT SqlBrokerEndpoint STATE = STARTED AS TCP ( LISTENER_PORT = 45000) FOR SERVICE_BROKER (... Read more →

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... Read more →

How to troubleshoot SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed

A guide to troubleshooting SSPI handshake failed with error code Read more →

SQL Server – DBCC SQLPERF(netstats) and network activity

A useful undocumented SQL Server command is DBCC sqlperf (netstats). It allows the DBA to monitor and report usage of network activity of the SQL Server Instance. Reporing on network usage is useful in different situations, such as analysing communication between SQL Server and the the network The command are : --issue the commnand to view the details DBCC SQLPERF('netstats') --clear existing statistics to start a new count DBCC SQLPERF ('netstats', CLEAR); The output is : Statistic | Value Network Reads 2.0427E+07 Network Writes 3.20437E+07 Network Bytes Read -1.322977E+09 Network Bytes Written -2.034788E+08 Command Queue Length 0 Max Command Queue... Read more →

SQL Server – network packet size

The SQL Server network pack size is by default set at 4096 bytes. Depending on the operation different values may give better performance – due to less reads and writes on the network. Verify by checking sp_configure under the name “network packet size (B)”. For certain circumstances , analysing the SQL Server network packet size is useful for troubleshooting. Before making any changes , consider the full range of traffic – and whether it will cause a performance improvement. I’ve seen DBAs who habitually change the network packet size , for example on bulk copy operations , but I rarely... Read more →