Configure SQL Server TCP Protocol using SMO

 

I’ve started working on a new version of xSQLServer DSC Resource which in my opinion lacks few very important feature to be totally used in production. The first thing i was looking for in this bunch of resources was a meaning to configure TCP connexions. We don’t use dynamic ports in production in most of our customers, so we have a bunch of scripts to configure the registry key, and it’s working pretty good. But i knew from long time that i can also do this using PowerShell and SMO assemblies. So let’s have a look about how it works.

As SMO are assemblies, you need to have SQL Server (maybe only Management Studio?) installed on the computer that’ll execute scripts. Microsoft says that the prefered way of loading assemblies, is to import the SQLPS Module. Well, ok, but we may want to use only SMO assemblies needed for our task 😉

Load SMO Assemblies

So let’s use only the three assemblies that i need to access TCP protocol configuration

Connect to SQL Server WMI

We have all what we need loaded in the GAC, let’s do some PowerShell now! And the first thing to do is to build an object to access WMI informations about SQL Server.

SQL Server WMI object

We have few informations in this object, the services associated to SQL Server, the client protocols, the instance(s) available and the Name of the server. Seems enough for me right now 🙂

Read Protocols Configuration

Let’s have a closer look at ClientProtocols property.

ClientProptocols in details

 

We have much more informations and one that you’ll use to access the property of a protocol this is the URN 🙂 Why ? because everything we saw until there is just readable, and we need to write configuration.

Change Protocols Configuration

You can see that if you use my favourite CMDLet

WMIObject Get-Member

If you look at all the properties, they have all a get ability. So we’ll use the GetSMoObject method and use the URN property to access target data.

TCPProtocol

We have now access to everything we need to change TCP Configuration for our SQL Server accessibility. For example, if you want to list every IP addresses available for the configuration you just have to do the following

Target a specific IPAddress

This will return many lines. Let’s have a closer look.

IPAddress

There is a property called IPaddressProperties that seems pretty handy for what we need 😀

listed properties

Exactly what we need! And everything is writable.

So, in order to modify them, it’s easy to do the following

And to make sure that changes are applied

Target only one IP

Change for all IPAddress

This is pretty the same. But we’ll use a full URN to access the IPAll IPAddress that’ll have exactly the same name everytime, so pretty easy to target it like that.

Changes are visible in your SQL Server Configuration Manager

IPAll Changes

OK this is everything for this post. If you have any comments or needs any precision, feel free to post bellow.