In a previous article, I showed you how to find all of the SQL servers and instances using a function that queries the registry. Moving on from this, I will now show how you can connect to a SQL server (and instance) using SQL Server Management Objects (SMO).

In case you are wondering why I am talking about SQL, it is because I enjoy working with SQL when I get the chance and while I may not be an expert at it, you can be sure that I will learn as much as I can and even more so how I can leverage PowerShell to work with it.

SMO can be used with PowerShell to make a connection to a SQL server by name (or name\instance) and allow you to utilize the objects to traverse the SQL server to look at the databases, jobs and just about anything else you need to do. Did I also mention that you can use T-SQL as well through this? If you are looking to only run T-SQL statements without anything else, then there is another method that might suit you without the need for SMO that I will try to discuss in a future article.

To use these, you need to have the SQL Server Management Studio installed (or you can find the assemblies on another installation, but I cannot promise that everything will work correctly for you OR you can download the SQL Feature Pack to get access to the assemblies). Once you have that installed, you can run the following command to load up the assemblies (SQL 2008 as an example).

#SQL 2008
add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, 
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, 
PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, 
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, 
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, 
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

Using Add-Type and specifying the exact version will  make sure that you are using the latest version of the assemblies if you happen to have multiple versions of SQL installed such as 2005 and 2008.

Once the assemblies have been loaded up, you can then attempt to make your first SQL server connection.

$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server('DC1')
#View the connection
$sqlServer

Ok, that is a ton of information to look at! But as you can see, there are a lot of things to look at and traverse through to view what is on the SQL Server. So what happens if I connect to a server that doesn’t have SQL installed? Will it just error out?

$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server('DoesNotExist')
$sqlServer

No error message, but it still has a somewhat empty shell of an object that is available. Keep this in mind when you are working with SMO and PowerShell. Just because the connection appears to have worked, it doesn’t mean it actually did work.

One last thing, if you want to check out all of the databases on this particular server, run this (make sure you re-connect to a known good server first):

$sqlServer.Databases

To make this more useful, I will only show a few properties so you can get the idea of what is available.

$sqlServer.Databases | 
Select Name,Owner,RecoveryModel,DataSpaceUsage

Hmm… my SUSDB is not using a Full RecoverModel. Better get that changed sometime soon, but as you can see, you can see the name, owner recoverymodel and dataspaceusage to name a few things which are available.

There is much more to explore with using SMO and PowerShell and I will do just that in some upcoming articles including showing how you can perform Transact-SQL (T-SQL) queries as well as checking the status of SQL Jobs and events.