I’ve previously mentioned how I’m building out a whole bunch of clustered servers. In an effort to standardize all the installation and configuration components I’ve used multiple PowerShell scripts. One of the most challenging to get working was one that enabled the TCP/IP for SQL and set it to listen on Port 1433.
This script is based on the great post by Allen White (blog|twitter) on finding your enabled SQL Server protocols using PowerShell.
Is TCP/IP enabled?
For the script to work we have to load the SMO objects with the context of the SQL Server we want to change (if a clustered instance we would use the virtual name)
Then we build the path to the TCP/IP protocol in the instance and query the IsEnabled property.
Now to enable TCP/IP if it’s disabled.
Set the listening port
Now we drill down a little further and set the listening port.
Now for the fun stuff
What we have there is strictly functional. There’s no error checking or funky components.
First off, we need to run this as admin, so we can check to see if this is an administrator running this in an elevated shell:
We can then check the value of $RunningAsAdmin. If true then continue on our way, otherwise error out and warn the user.
Next we’ll prompt the user for the SQL instance that we’ll be configuring, after all that’s pretty important. The entered value will be placed in the $SQLInstance variable and used in the script.
We’ll need to split out the server or virtual name from the instance name (if there is one) in order to use the values within the SMO portion (we’ll also make everything upper case).
Given that we are making a significant change we should provide the option to cancel the action. We do this with a quick confirmation.
We can then check whether yes or no button was pressed and handle the action.
Quick and easy
The script only takes a few seconds to run and does exactly what is says on the box. You can copy the script below or download here. Happy playing.