Hello, world! :) and off we go – with Power Shell for DBA

First, let me express I am really happy to post here. I was following BeyondRelational for quite some time and learned a lot (especially from T-SQL Challenges, I guess) and I have finally decided to contribute a bit. I hope I am able to deliver some quality, so any comments are welcome.

So, let’s start with something not really obvious. Everyone knows about Power Shell, but who uses it for database administration? Of course since SQL Server 2008 there is an option of running Power Shell from within SSMS, but there are situations when it comes really handy, for example, when running queries against multiple servers and/or databases.

The idea is taken from one of the blogs I’ve come across once. It states that Power Shell exposes useful methods for database developers, so let’s put it to good use for administrative work.
In this scenario you get a list of servers from somewhere (it may be a text file or Active Directory), connect to each of them using SMO and get the information you want. For testing purposes, let’s grab name, compatibility level, recovery model, size and some other options. For details or extensions, MSDN website is a good place to start.

Let’s break it to bits. First we get the servers we want to query, then we’ll work on getting the information.

The simplest way to get the list of servers to be queried is to place them in a file, then read it. You can fetch with one line:

$serverList = Get-Content 'filename'

The downside is that, if your server list changes, you have to remember to update your configuration file and since you are probably in a domain, this information is already out there somewhere just waiting for you to reach it. If you have a domain group containing all the servers, this gets really easy. Just remember to either run Active Directory Module for Power Shell, or include Import-Module ActiveDirectory before this:

$serverList = Get-ADComputer –Filter {MemberOf –eq "group_name"} | select Name

Normally, the Get-ADComputer returns more information than we really need, so first we get the data and then just take what’s necessary – in this case, Name. It’s, however, possible that the situation differs and you don’t have a group, you have a separate OU instead. In this case, you have to prepare an LDAP query to get to proper path and provide a dummy filter (Get-ADComputer requires Filter parameter):

$serverList = Get-ADComputer –Filter {Name –like "*"} –LDAPFilter “ou=Servers,ou=Computers,dc=contoso,dc=com” | select Name

Since we have our list of servers, we can start querying them. We have to load Microsoft.SqlServer.SMO (Server Management Objects) namespace first. Note that SMO does not support SQL Server version below 2000, but they should be pretty rare at the moment.

After we have the namespace ready, we will loop through all the servers. For each of the servers, we’ll create an instance of Microsoft.SqlServer.Management.Smo.Server class, and then we’ll look into Databases collection. To keep the output nicely formatted, we’ll store the results in temporary variable $out, which is displayed using Out-GridView at the end.

$out = @()
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
foreach ($server in $serverList)
	$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
	$dbs | select Name, CompatibilityLevel, RecoveryModel, AutoClose, AutoShrink, Owner, Size, LogFiles | Add-Member -Name "Server" -Value $server -MemberType NoteProperty -PassThru -Force -OutVariable +out | Out-Null
$out | ogv -Title "Database information"

There are more options starting from here, for instance you can list all permissions in all databases (again, refer to MSDN for detailed description):

$out =@()
foreach ($db in $dbs)
	$db.EnumObjectPermissions() | where {$_.ObjectSchema -ne "sys" -and $_.ObjectSchema -ne "INFORMATION_SCHEMA"} | select @{Name="dbName";Expression={$db.Name}}, * -OutVariable +out | out-null
$out | ogv -Title "Permissions on all objects in all databases"

I hope you find it interesting how you can make a bridge between development and administration – it’s a way to administrative stuff programatically. Anyway, I hope you enjoy my first post here and again – feel free to comment.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s