Sql-server – Which PowerShell technique should I use to talk to SQL Server

monitoringpowershellsql server

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.

Best Answer

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.

Related Solutions

Sql-server – How to script out push subscription creation at the subscriber

I'm going to outline a T-SQL way that uses most of what you already have.

Create a table to hold your publication information (for the solution that I have written, it's just the name of the publication and an identity column)
Create a table to hold your subscriber information (in mine, I have name of subscriber, subscriber database, identity column, and a column that references back to the publication)
When you want to create a new subscription, you put a new row in the subscriber table with the relevant information
In your "add subscription" script, do a left join between your table and syssubscriptions to find out what subscriptions are in your table but don't yet exist. Run a cursor over that info and for each subscription that doesn't exist, create it.
After you're done adding all of the subscriptions, call sp_startpublication_snapshot for each publication that has uninitialized subscribers.
Fin (aka "you're done").

PowerShell – How to Get SQL Server Backup Status

Code gets backup history for all databases on a server and generates HTML report

Step 1:

   DECLARE @Body VARCHAR(MAX),
        @TableHead VARCHAR(MAX),
        @TableTail VARCHAR(MAX)
    SET NoCount ON ;


    SET @TableTail = '</body></html>' ;

    SET @TableHead = '<html><head>' + '<style>'
        + 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:10pt;} '
        + '</style>' + '</head>' + '<body>'


    SELECT  @Body = ''



    SELECT  @Body = @Body + '<table cellpadding=0 cellspacing=0 border=0>'
            + '<tr><td bgcolor=#E6E6FA>Database Name </td>'
            + '<td width="100px" bgcolor=#E6E6FA>Backup Size</td>'
            + '<td width="100px" bgcolor=#E6E6FA>Time Taken</td>'
            + '<td width="100px" bgcolor=#E6E6FA>Backup Start Date</td>'
            + '<td width="100px" bgcolor=#E6E6FA><b>First LSN</b></td>'
            + '<td width="100px" bgcolor=#E6E6FA><b>Last LSN</b></td>'
            + '<td width="100px" bgcolor=#E6E6FA><b>Backup Type</b></td>'
            + '<td width="100px" bgcolor=#E6E6FA><b>Server Name</b></td>'
            + '<td width="100px" bgcolor=#E6E6FA><b>Recovery Model</b></td>'
            + '<td width="300px" bgcolor=#E6E6FA>Physical Device Name</td>   </tr>'


    -- Backup History script of AAsim Adbullah                
    -- http://blog.sqlauthority.com/2010/11/10/sql-server-get-database-backup-history-for-a-single-database/ 
    SELECT  @Body = @Body
            + ( SELECT  td = s.database_name, '',
                        td = CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14))+ ' ' + 'MB', '',
                        td = CAST(DATEDIFF(second, s.backup_start_date,
                                           s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds', '',
                        td = s.backup_start_date,  '',
                        td = CAST(s.first_lsn AS VARCHAR(50)), '',
                        td = CAST(s.last_lsn AS VARCHAR(50)), '',
                        td = CASE s.[type]
                               WHEN 'D' THEN 'Full'
                               WHEN 'I' THEN 'Differential'
                               WHEN 'L' THEN 'Transaction Log'
                             END,'',
                        td = s.server_name, '',
                        td = s.recovery_model,'',
                        td = m.physical_device_name,''
                FROM    msdb.dbo.backupset s
                        INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
                WHERE   s.backup_start_date >= DATEADD(dd, 0, DATEDIFF(dd, 0,GETDATE() - 1)) -- --- Date check for one day backup history information
                ORDER BY backup_start_date DESC,
                        backup_finish_date
              FOR
                XML PATH('tr')
              ) + '</table>'



    SET @Body = REPLACE(@Body, '_x0020_', SPACE(1))
    SET @Body = REPLACE(@Body, '_x003D_', '=')
    SET @Body = REPLACE(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
    SET @Body = REPLACE(@Body, '<TRRow>0</TRRow>', '')


    SELECT  @Body = @TableHead + @Body + @TableTail


    IF ( @Body IS NULL ) 
        BEGIN 
                EXEC msdb.dbo.sp_send_dbmail @recipients='sqlcache@blogspot.com',  -- Add Valid Email Id
                @subject = 'Backup History Information',
                @profile_name = 'AA.SC', -- Change Profile Name
                @body = 'No Backup History Found for past 1 Day',
                @body_format = 'HTML' ;

        END
    ELSE 
    Begin
        EXEC msdb.dbo.sp_send_dbmail @recipients='sqlcache@blogspot.com', -- Add Valid Email Id
                @subject = 'Backup History Information',
                @profile_name = 'AA.SC', -- Change Profile Name
                @body = @Body,
                @body_format = 'HTML' ;
    END

Step 2: create a new job add this code in step and schedule it according to your requirement enter image description here

Best Answer

Related Solutions

Sql-server – How to script out push subscription creation at the subscriber

PowerShell – How to Get SQL Server Backup Status

Related Question