Which PowerShell technique should I use to talk to SQL Server?

Question

I'd ultimately like to use PowerShell to replace the old KornShell scripts we use for SQL instance monitors. I'm having a hard time, though, getting my brain around all the different ways that PowerShell can actually talk to SQL server. Not sure if this is all of them, but here are 5 entirely different ways I can query the version of a SQL server:

1. SQLConnection .NET Class

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=MyServer;Database=Master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "Select @@version as SQLServerVersion"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

2. WMI Provider

$sqlProperties = Get-WmiObject 
    -computerName "MyServer"
    -namespace root\Microsoft\SqlServer\ComputerManagement10
    -class SqlServiceAdvancedProperty
    -filter "ServiceName = 'MSSQLSERVER'"
$sqlProperties.VERSION

3. SMO

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$smo-var = New-Object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\instancename'
$smo-var.VersionString

4. PSDrive

Set-Location SQLSERVER:\SQL\MyServerName\
$server = Get-Item Default
$server.get_VersionString()

5. Invoke-SQLCMD

Invoke-Sqlcmd -Query "SELECT @@version" -ServerInstance "MyServer"

How should I go about deciding which of these techniques to use for different scenarios? Are there pros/cons of each? Are some of these powershell 1.0 techniques that were superceeded in 2.0? Will some of them not work to communicate with SQL 2000 or 2005 servers?

On one level, I'm sure the answer is "use whatever works", but for someone new to Powershell, its very confusing to see so many examples written like #1 above, when that is the longest and (in my mind) least "powershell-like" example.

Slightly more info in case it is relevant: the SQL server that will actually be running the monitor scripts is SQL 2005, but it is used to connect to multiple instances from SQL 2000 up to 2008R2.

Answer 1

Obviously, a lot of this devolves to simple personal choice. Here are my own, personal, rationalizations.

I've been using Powershell with SQL SQL since PSH v 1.0, and before SQL Server started officially integrating it. (When I started with PSH, I was administering SQL Server 2000 and 2005 servers.) So, I learned with SMO (or it's slightly older incarnation, the name of which escapes me at the moment) and .Net and I'm used to them. I'd generally lean towards SMO, since it makes some things a lot easier, like scripting out objects. My own code uses SMO some times and .Net some times. I think it's handier to use .Net to get simple result sets, for instance.

I think that Invoke-SQLCMD makes more sense if you have lots of existing TSQL scripts. If you are creating strings and executing them through -Query, that's going to be messy. If you have a good grasp of how Powershell works with .Net and SMO, using Invoke-SQLCMD occasionally, when you have a script file to run, is easy.

I've always found the PSDrive thing clunky and felt that they implemented it because they got caught up in the "everything can look like a file system" idea. I know that the *nix guys love \proc and such, but I feel that this implmentation feels sort of forced. I think that PSDrive is OK, maybe even good if you hate the UI, for exploring things but I've never written a script that uses it.

I have never seen anyone use the WMI provider. So, that would be my last choice.

So, I'd lead with SMO and fall back to .Net when it's handier to.

Answer 2

4 for new work, 5 for reusing existing scripts or places T-SQL makes more sense than object-based/posh style code. I like those best because they are clear and simple.

Answer 3

I tend to lean toward using SQLPS if I can. It is simpler and if I use it in scripts is much easier to read and less typing than trying to use SMO. SMO does it have its place in that it has a good bit of power, but can be confusing at times to use if you are unfamiliar with it.

I think as SQL Server versions are released the SQLPS will be improved upon. Especially since with SQL Server 2012 SQLPS is not modular instead of being a snapin. This is going to allow Microsoft to push out fixes or improvements with SQLPS through service packs or hotfixes, maybe even CUs.

Then there are also the community offerings like SQLPSX, which has alot of that SMO code already prepared for you as cmdlets and functions. Which I am all about not re-inventing the wheel :)