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
2. WMI Provider
$sqlProperties = Get-WmiObject -computerName "MyServer" -namespace root\Microsoft\SqlServer\ComputerManagement10 -class SqlServiceAdvancedProperty -filter "ServiceName = 'MSSQLSERVER'" $sqlProperties.VERSION
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null $smo-var = New-Object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\instancename' $smo-var.VersionString
Set-Location SQLSERVER:\SQL\MyServerName\ $server = Get-Item Default $server.get_VersionString()
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.