Art of the DBA Rotating Header Image

A Month of SQLPS: Collecting

It’s one thing for us to talk about what your options are for a piece of tech, but that rarely shows what you can do. Whenever I talk about Powershell, I want to show people what can actually be done with the language so people can make practical use of it. The provider gives us more than just a nice way to browse SQL Server.

Treating SQL Server like a file system lets us easily gather up objects as collections for processing. The strength of this should be obvious to database folks, who like to work with sets of data. For example, we can use them to quickly gather information across our servers.

Ever wonder what your SQL Server versions are? Wouldn’t be nice if you could just query the build number of all our instances? Well, using the provider we can do that. Here’s my pattern for using your Central Management Server to getting a list of all our servers:

$servers = @(‘PICARD’)
$servers += (dir "SQLSERVER:\SQLRegistration\Central Management Server Group\$servers").Name

Now, if we combine this with Get-Item and Select-Object and we can have a quick list of our version information across all our servers:

$servers = @(‘PICARD’)
$servers += (dir "SQLSERVER:\SQLRegistration\Central Management Server Group\$servers").Name
foreach($server in $servers){
    Get-Item "SQLSERVER:\SQL\$server\DEFAULT" | Select-Object Name,VersionString
}

SQLPS-8-1

We can extend this to retrieving other properties and drilling down into other aspects of our environment. We can use this to gather inventory information quickly. But wait, there’s more! We’re not just limited to displaying properties, we can also change them.

Have you ever connected to an instance to create some database objects and accidentally created it in master because that’s set as your default? Not only is it annoying, but it can be problematic and difficult to clean up. This is why I like to set user logins to tempdb. The provider makes it extremely easy to do this:

$logins = dir SQLSERVER:\SQL\localhost\DEFAULT\logins
foreach($login in $logins){
    $login.DefaultDatabase = ‘TempDB’
    $login.Alter()
}

This is a pretty simple approach that can be extended easily to suit our purposes. If we wanted to update only SQL Logins, we could add an additional filter to build our collection:

$logins = dir SQLSERVER:\SQL\localhost\DEFAULT\logins | Where-Object {$_.LoginType -eq ‘SqlLogin’}

I can hear you saying “So what, I can do that in T-SQL”. And you can by writing a T-SQL cursor and some dynamic SQL. The immediate advantage here is we avoid that dynamic SQL. But there’s another advantage: the ability to easily combine multiple collections. If we go back to the CMS pattern and combine it with this loop, we can now update all our default databases on all our servers:

$servers = @(‘PICARD’)
$servers += (dir "SQLSERVER:\SQLRegistration\Central Management Server Group\$servers").Name

foreach($server in $servers){
    $logins = dir "SQLSERVER:\SQL\$server\DEFAULT\logins"
    foreach($login in $logins){
        $login.DefaultDatabase = ‘TempDB’
        $login.Alter()
    }
}

This means we are using collections to drive our automation. Also, because our collections are based on the current state of the data, our scripts become dynamic, adapting as the state of the data changes. This is awesome, because we’re used to letting data determine how our work gets done in our database scripts and now we get to do it easily in Powershell.

We’ve spent a lot of time on the provider because it’s the biggest piece of functionality in the module, but there’s more there. Contained within the module are several cmdlets for handling specific actions in and around SQL Server. Tomorrow we’ll start looking at what’s provided and how we can use them.

 

Leave a Reply

Your email address will not be published. Required fields are marked *