Sometimes the brilliance of a solution is lost in its simplicity. Full disclaimer, this was Joey D ’Antoni’s (
B|
T) idea, however, he has given me permission to blog about it.
Recently, Joey and I were helping a client to reconfigure their production SQL Server instances to have a new and improved TempDB configuration. We knew that this would require an outage to ensure the changes had correctly taken affect.
Of course, we ran into a snag. The current configuration had 30 plus tempdb data files and any attempts to delete the files were blocked because they were actively being used. Nothing we tried would give us the ability to delete the excess files.
We even tried to restart the instance into single user mode, however, every time that happened something else would take the connection before we could get into the instance. We eventually restarted the SQL Server instance to normal operation so that we could investigate why we could not get a connection when in single user mode.
Turns out that with the production nature of the instance, the clients large farm of application servers was connecting to it faster than we could. This was discovered by using sp_who2, however, you could use the DMV sys.dm_exec_connections to see what is connecting to the instance if you desired. So, we needed a way to block incoming connections while not being evasive like shutting down the application servers or a large network change.
This is where the brilliance comes in.
Disable TCP/IP as a network protocol to SQL Server. BOOM. Instant firewall.
How To
If you open SQL Server Configuration Manager, you will probably see something like this. If you have more facets installed or multiple named instances installed, your list on the right-hand side might look a bit larger but for the most part it should be similar.
Expand SQL Server Network Configuration in the left-hand menu and select Protocols for the instance you want wish to block connections to. In this case, I just have the default instance installed on my laptop.
On the right hand side, notice that the TCP/IP protocol is correctly enabled.
You can disable that by double clicking on TCP/IP and then changing the Enabled option to reflect No.
An alternative method is that you can also simply right click on the protocol and choose to enable or disable it.
With either method, you will acknowledge that the changes will not take effect until the service has been stopped and restarted.
Go ahead and restart the service. Once back online, the TCP/IP protocol will be disabled and any servers wishing to connect will be effectively denied. Keep in mind that we had local access to the server itself and retained connectivity to it even though SQL Server no longer allowed TCP/IP connections. Utilizing the shared memory protocol (it was the only thing enabled), we were able to restart into single user mode, get connected, and successfully remove the extraneous tempdb data files.
Once the tempdb work was completed, we re-enabled the TCPIP protocol, and then restarted the instance back into normal operations. Double checking sp_who2, we also verified that all the application servers had resumed connectivity to the SQL Server instance.
Summary
This is an example of a quick and effortless way to block all inbound connections to the SQL Server instance. This method required zero intervention from any engineers to stop application servers or implement firewall changes. A few simple clicks of the mouse and we were back in business.
Sometimes it is the simple things that are astonishing and brilliant.