PowerShell Week at SQL University – Post 5

In the previous posts we’ve just been poking around with PowerShell and trying to make the examples something that actually means something to a SQL person whenever we ca.  There are quite a few more language constructs that we need to cover but we have enough info to start recouping the time we’ve already invested.  Now it’s time to do one of those tasks that I just love to do with PowerShell.  We’re going to loop.  And we’re going to loop in a way that’s far easier than in any part of the SQL language.

We’re going to cover 3 different sources for our loop that are the most common for DBAs to use: table, text file, Registered Server/Central Management Server.  After that we’re going to do a double loop and then it’s time for you to find something to do with these.

Text File

In this chunk of code we’re just going to read from a simple text file from our local hard drive and then loop through the instances in that file one at a time.  You put one instance on each line of the text file.  Don’t put it in quotes unless you’re using a non-standard port number.  This is the easiest method because each “row” that comes out of the text file only has one property.  We’ll find out why that’s important in the next example.  In the meantime setup your text file and test it by running just this: Get-Content C:\PowerShell\AllInstances.txt.

foreach ($Instance in Get-Content C:\PowerShell\AllInstances.txt)
{
$Instance;
Invoke-Sqlcmd -ServerInstance $Instance -Database master -Query "SELECT  object_name ,
        counter_name ,
        instance_name ,
        cntr_value ,
        cntr_type
  FROM sys.dm_os_performance_counters"
}

Database Table/ Query

In this example we could be reading rows out of a table or running a more complex query to determine the list of databases that we want to run out query against.  This may actually seem easier to most database people and it is.  We’ve got a centrally located table and we can just look at it and know that we can change the first query to select a list of databases from somewhere else.  But there’s a really important thing to know if you swap out the query.   This approach is sending one usable property (column) to the foreach loop and it’s called “name”.  If you change the query and the column ends up being called database_name, you’re going to have to change $($db.name) to be called $($db.database_name).  Otherwise you’re going to loose a lot of hair and get really ticked after about 20 minutes like I did!

foreach ($db in invoke-sqlcmd -query "SELECT name  FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook )
{$db.name;
Invoke-sqlcmd -Query 'SELECT *
  FROM sys.dm_exec_procedure_stats' -ServerInstance  Win7NetBook -Database $($db.name)
}

Registered Servers/ Central Management Server

Before you get started looping through your Registered Servers you’ll need to run this: Import-Module Agent if you want to do this exact example.  What we’re looking for here is all the jobs that have failed in the last 3 days in our “QA” group of servers.  This example should be easy enough for everyone to tweak on their own.  If you get stuck just remember to do Get-Help -Full Set-AgentJobHistoryFilter
(Huge thanks to Chad Miller for helping me put together this demo so that it would be a fast one to run!)

$filter = Set-AgentJobHistoryFilter -startDate $(get-date).AddDays(-3) -endDate $(get-date) -outcome 'Failed'
foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\QA\ | where {$_.Mode -ne "d"} )
{
Get-AgentJobHistory $RegisteredSQLs.ServerName $filter | where-object {$_.StepID -ne 0}
}

Double Loop

This has to be one of my favorite PowerShell scripts of all time (so far).  I had to run a query against every database in a group of over 10 servers.  I’ve changed this one around a little but I’m sure you’ll find a use for it!  (Think permissions.)

foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\QA\ | where {$_.Mode -ne "d"} )
{
    foreach ($DBName in invoke-sqlcmd -query "SELECT name
  FROM sys.databases WHERE name in ('AdventureWorks',
            'AdventureWorks2008',
            'AdventureWorks2008R2',
            'AdventureWorksDW'
            ) " -database master -serverinstance $RegisteredSQLs.ServerName )
            {
                    invoke-sqlcmd -query 'SELECT *
        FROM sys.dm_db_index_usage_stats' -ServerInstance $RegisteredSQLs.ServerName -database $DBName.name
            } #EndOfTheFoundDatabasesLoop
} #EndOfTheRegisteredServerLoop

Homework

Try out each of these methods and think up something you could use this for.  If you hit on something that save you some clicking around in SQL Management Studio please mention it in the comments.