31 Posts of using Sql Server with Powershell: Modules

This isn’t a 31 days series, as I’ll undoubtedly miss a day here and there, so instead, this will just be a ’31 posts’ series instead.  I’m pragmatic.  And lazy.

I have noticed there is a bit of discomfort amongst most database administrators when it comes to dealing with powershell.  Do yourself a favor and learn it.  You’ll wonder how you ever got by without it before.

So here we go with post 1.

Importing Sql Server Cmdlets

In order to begin this series, you’ll need to import the sql server cmdlets that will enable you to work with the smo objects that you’ll use to work with sql server.  If you have sql server management studio 12 and above, you’re in luck.  All you need to do to import the sql server cmdlets is this:

Import-Module SqlPS -DisableNameChecking;

The –DisableNameChecking is optional, but if omitted you’ll get a warning that some of the verbs in the cmdlets are unapproved:

warning

A word of warning; this module loads extremely slow.  This has been fixed in Sql Server 2016, but as noted in the closing notes of this post, that requires that SSMS for Sql Server 2016 be installed.

If you’ve got Sql Server 2008 R2 and below, you’ll need to install a few things in order to get the sql server cmdlets working correctly.  More or less, the SqlPS module requires the use of Sql Server 2012 Shared Management Objects, so you’ll need to download 3 components from the Sql Server 2012 (or 2014) feature pack:

First, you’ll need download and install the SystemCLR Types for Sql Server 2012/14:

image

Next, you’ll need to download and install the Sql Server Shared Management objects:

image

And finally, download and install the Windows Powershell Extensions for Sql Server:

image

Once you have all three of these installed and restart your powershell environment, you should be able to run the Import-Module SqlPS command correctly.

Okay, post 1 complete.  Sort of.  As of Sql Server 2016, the SqlPS module is being replaced by a new SqlServer module, but that requires that the Sql Server 2016 SSMS must be installed to use it (at the time of this writing), so we’re just going to plod along using SqlPS for now.  Most of the functionality provided in the SqlServer module will still work.

Change Sql Server Configuration Manager IP Address

To change the IP address in sql servers’ configuration manager, you can use the following code.  Of note, when I changed the IP address in configuration manager under IP1 manually and tested connections, it really didn’t make a lick of difference.  I could connect whether or not the IP1 value reflected the correct IP address or not.  But, having a different IP listed in the configuration manager as opposed to what it really is just feels dishonest.

Import-Module SqlPS -DisableNameChecking
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
cls

try{

    $srvName = "servername"
    $Instance = "mssqlserver"
    $urn = "ManagedComputer[@Name='$srvName']/ServerInstance[@Name='$Instance']/ServerProtocol[@Name='Tcp']"

    $wmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $srvName
    $tcp = $wmi.GetSmoObject($urn);
    $tcp.IPAddresses["IP1"].IPAddressProperties["IpAddress"].Value = '10.10.1.1';
    $tcp.Alter();

}
catch{
    $_ | fl -Force
}