Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
I ran across a question on network protocols recently, which is something I rarely deal with. Often the default setup for SQL Server is fine, but there are certainly times you should add or remove network connectivity according to your environment.
Here’s a short post on turning off (or on) a network protocol for SQL Sever.
The easiest way to verify what’s enabled is to use the SQL Server Configuration Manager. You’ll need administrative permissions on the host to run this, but it’s easy to find.
Once you open it, typically you’ll have a list of the items that can be configured.
We want the SQL Server Network Configuration, which is the server level configuration for this host. The Client configurations are for the host being used a client to connect to a SQL Server.
As you can see here, I have Shared Memory and TCP/IP enabled for this instance, but Named Pipes disabled.
Disabling a Protocol
As you might expect, this is easy. I right click on a protocol, and I can change the status. In this case, I’ll disable Shared Memory
Once I do that, the protocol is disabled. However not on the instance. I’ll get this message.
I need to restart the server. Once that’s done, no one will be able to use Shared Memory on the host.
I can fix this
Of course, I need to restart my instance again.
Checking the Log
When SQL Server starts, quite a bit of configuration information is written into the log. This is useful for troubleshooting in many cases. One of the things you’ll find is the network listeners, as shown here.
This is usually after the database startup information, so if you look, you can see I have some local pipes and some TCP/IP settings here.
After reading a question, this was less than 10 minutes to write, with making screenshots. However I’ve done this before. If this was your first time, then it might take you longer to research and write, but I bet most DBAs could do this in 30-45 minutes.