Use PowerShell to Collect Server Data and Write to SQL

Use PowerShell to Collect Server Data and Write to SQL

  • Comments 25
  • Likes

 

Summary: Use Windows PowerShell to collect server data and automatically store that information in a Microsoft SQL Server.

 

Hey, Scripting Guy! QuestionHey, Scripting Guy! How is Windows PowerShell usage by database professionals different from the way that other IT Pros use Windows PowerShell?

 

-- MC

 

Hey, Scripting Guy! AnswerHello MC, Microsoft Scripting Guy Ed Wilson here. Next week, Nov 8-11, 2010, is the annual SQL PASS Summit in Seattle. In honor of this event, we will have guest bloggers from the SQL side of life. Today we will begin with Chad Miller.

 

Chad Miller(Blog|Twitter) is a SQL Server DBA and Senior Manager of Database Administration at Raymond James Financial. In his spare time, he is the Project Coordinator/Developer of the Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at SQL Saturdays and Code Camps.

Take it away Chad!

 

We are data people and we believe data should exist in databases. We spend a good part of our day writing Transact-SQL scripts to query and load data from other sources. Therefore, not surprisingly when we use Windows PowerShell we want to run a command, capture the output and then store the data in a database. After the data is in a database, we can use Transact-SQL to do additional reporting and analysis. We might even use SQL Server Reporting Services to provide Web-based access to the data.

Extract Data

SQL Server 2008 and 2008 R2 provide the Invoke-SqlCmd cmdlet, but only on computers where the sqlps (the SQL Server mini-shell) is installed. As an alternative solution, you can implement your own function which does not require loading external snap-ins. I have copied the invoke-sqlcmd2 function to the Scripting Guys Script Repository. Save the code as invoke-sqlcmd2.ps1 and then dot source our new function into your Windows PowerShell console by using the following command:

. ./invoke-sqlcmd2.ps1

The following example dot sources the invoke-sqlcmd2.ps1 script that contains the invoke-sqlcmd2 function, connects to the pubs database on a SQL server named SQL1 and runs a basic Transact-SQL query. The last command is a single command, but has wrapped to the third line in the output.

PS C:\> . C:\data\ScriptingGuys\2010\HSG_11_1_10\invoke-Sqlcmd2.ps1

PS C:\> Invoke-Sqlcmd2 -ServerInstance sql1 -Database pubs -Query "Select * from auth

ors"

 

 

au_id    : 172-32-1176

au_lname : White

au_fname : Johnson

phone    : 408 496-7223

address  : 10932 Bigge Rd.

city     : Menlo Park

state    : CA

zip      : 94025

contract : True

 

<...OUTPUT Truncated>

 

This example reads a file that contains T-SQL statements, runs the file, and writes the output to another file.

Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -InputFile "C:\MyFolder\tsqlscript.sql" | Out-File -filePath "C:\MyFolder\tsqlscript.rpt"

 

This example uses the Windows PowerShell -Verbose parameter to return the message output of the PRINT command.

Invoke-Sqlcmd2  -ServerInstance "MyComputer\MyInstance" -Query "PRINT 'hello world'" -Verbose

VERBOSE: hello world

 

Getting a Server List

When you have to run a Windows PowerShell command across multiple servers, you will frequently see examples in which the list of servers are stored in a text file and read by using the Get-Content cmdlet. However, most database professionals maintain a list of SQL Servers they manage in either a table they create or they may use Central Management Server (CMS) introduced in SQL Server 2008.  Instead of using a text file, for database professionals, it makes more sense to read a SQL table. Let's take a look at an example. For the purposes of this demonstration we'll create a table. However, you could just as easily substitute the msdb.dbo.sysmanagement_shared_registered_servers view from your CMS server.

From SQL Server Management Studio, create a SQL table.

CREATE TABLE server_instance

(server_name varchar(255) NOT NULL);

Next populate the table with a list of SQL Servers:

INSERT server_instance VALUES('Z001');

INSERT server_instance VALUES('Z002\SQ2K8');

INSERT server_instance VALUES('Z003\R2');

 

With our server_instance table populated, we can use our Invoke-SqlCmd2 function to retrieve a list of servers and then call Windows PowerShell command for each. The following example retrieves the version information.

Invoke-sqlcmd2 -ServerInstance "Z003\R2" -Database dbautility -Query "Select server_name FROM server_instance" | foreach-object {Invoke-SqlCmd2 -ServerInstance $_.server_name -Database master -Query "SELECT @@version"}

 

Extract and Load data

A common task for a database professional is collecting and loading data from multiple servers into central utility database. Using the invoke-sqlcmd2 function, we can extract data from a SQL Server data source, but to load data we have to introduce a new function called Write-DataTable. I have uploaded the Write-DataTable Windows PowerShell function to the Scripting Guys Script Repository. Save the Windows PowerShell function from the Script Repository as write-datatable.ps1 and then dot source our new function into the Windows PowerShell console by using the following command.

. ./write-datatable.ps1

The Write-DataTable function uses the .NET Data.SqlClient.SqlBulkCopy class to load an in-memory DataTable or DataRow array into a SQL Server table. This works out well because the cmdlet invoke-sqlcmd or our function invoke-sqlcmd2 returns a datatable object. Let us examine an example database named space usage collection for all databases in your environment to use in forecasting growth.

You have to create a SQL Server table using SQL Server Management Studio. To do this, run the following Transact-SQL script:

CREATE TABLE [dbo].[db_space](

      [server_name] [varchar](128) NOT NULL,

      [dbname] [varchar](128) NOT NULL,

      [physical_name] [varchar](260) NOT NULL,

      [dt] [datetime] NOT NULL,

      [file_group_name] [varchar](128) NOT NULL,

      [size_mb] [int] NULL,

      [free_mb] [int] NULL,

 CONSTRAINT [PK_db_space] PRIMARY KEY CLUSTERED

(

      [server_name] ASC,

      [dbname] ASC,

      [physical_name] ASC,

      [dt] ASC

)

)

 

The T script and management studio are seen in the following figure.

  

 

Next we'll use a Transact-SQL query to collect the server name, database name and file information. We'll save the query to a plain old .sql file that is named get-dbspace.sql. This is the same kind of SQL script that you would execute in SQL Server Management Studio. However, we will call the script from the Invoke-SqlCmd2 function:

$dt = invoke-sqlcmd2 -ServerInstance "Z003\R2" -Database "master" -InputFile ./get-dbspace.sql  -As 'DataTable'

 

Examining the type information about $dt variable we can see the type is of a DataTable as shown in the following figure.

 

 

To load the DataTable into our SQL Server table we'll call the Write-DataTable function:

Write-DataTable -ServerInstance "Z003\R2" -Database "dbutility" -TableName "db_space" -Data $dt

 

Using invoke-sqlcmd2 and piping the output to Out-GridView we can see the data has in fact been loaded.

invoke-sqlcmd2 -ServerInstance "Z003\R2" -Database "dbutility" -Query "SELECT * FROM db_space" |  Out-GridView

The results from this command are shown in the figure below.

 

Extract, Transform, and Load Data

As we've seen, by using a couple of simple functions - invoke-sqlcmd2 and write-datatable - we can easily load data from any SQL Server data source, but what about any Windows PowerShell command? As an example, we want to collect disk space utilization by using Get-WMIObject Win32_LogicalDisk across a group of SQL Servers into a central database for reporting trending. If we can convert the output of our WMI call into a DataTable then we can use our Write-DataTable function. Using a function called Out-DataTable adapted from a script by Marc van Orsouw (Blog|Twitter) we can do just that. I saved the modified script to the Scripting Guys Script Repository.

Save the following code as Out-DataTable.ps1 and source our new function

. ./ Out-DataTable.ps1

 

To load convert the output of a WMI call to a DataTable, we'll pipe to our newly created out-datatable function and assign the output to a $dt.

$dt = Get-WmiObject Win32_LogicalDisk -filter "DriveType=3" | Select @{n='UsageDT';e={get-date -Format "yyyy-MM-dd"}}, '

SystemName, DeviceID, VolumeName, '

@{n='Size';e={$([math]::round(($_.Size/1GB),2))}}, @{n='FreeSpace';e={$([math]::round(($_.FreeSpace/1GB),2))}} | out-datatable

 

Having assigned the output to a datatable you can call the write-datatable to load the collected data into a SQL Server table. This makes out-datatable very powerful in that the output of any Windows PowerShell command can be easily loaded into a SQL Server table. Using write-datatable requires a SQL Server destination table to exist, but instead of manually creating a SQL Server table, we'll use a new function called Add-SqlTable. The Add-SqlTable.ps1 script is also uploaded to the Scripting Guys Script Repository. Save the code as Add-SqlTable.ps1 and source the new function.

The following command will create a new empty SQL Server table named diskspace based on the structure of our DataTable variable $dt:

Add-SqlTable -ServerInstance "Z003\R2" -Database dbutility -TableName diskspace -DataTable $dt

 

Note: Using this technique to create a SQL Server table is not as precise as manually creating a table as certain elements including defining primary keys and data types are not handled. Add-SqlTable works best for a quick data dump of Windows PowerShell data to a SQL Server table.

Finally, with the destination SQL Server table created, we can call our write-datatable function to load the collected data:

Write-DataTable -ServerInstance "Z003\R2" -Database "dbutility" -TableName "diskspace" -Data $dt

 

Using invoke-sqlcmd2 we can see the data was loaded into the SQL Server table:

invoke-sqlcmd2 -ServerInstance "Z003\R2" -Database "dbutility" -Query "SELECT * FROM diskspace" | Out-GridView

 

 

Summary

This post demonstrated how to query and load the output of any Windows PowerShell command into a SQL Server table. The functions invoke-sqlcmd2, write-datatable, out-datatable and add-sqltable can be used as building blocks for many of your Windows PowerShell-based data loading needs.

MC, that is all there is to using Windows PowerShell and SQL Server. SQL week will continue tomorrow when SQL guest blogger Aaron Nelson will talk about how to work with SQL snap-ins.  Thank you Chad, for sharing your time and knowledge with us.

 

I invite you to follow me on Twitter or Facebook. If you have any questions, send email to me at scripter@microsoft.com or post them on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

 

Ed Wilson, Microsoft Scripting Guy

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • I tried to run the ./Invoke-SqlCmd2.ps1 in the PS command line window, and it "succeeded" without any error.

    However, when I try to run the following command

    Invoke-Sqlcmd2 -ServerInstance "MyServer" -Query "print getdate()"

    I will get error saying

    The term "Invoke-Sqlcmd2" is not recognized as the name of a cmdlet, function, script file or orperable program.

    However, if I load the Invoke-SqlCmd2.ps1 into PS IDE environment, and then run it, and after that if I run the same command, i.e. Invoke-SqlCmd2 -ServerInstance "MyServer" -Query "print getdate()", inside the PS IDE environment, I can get the result, i.e no error at all.

    Any explanation is greatly appreciated.

  • I know why now. It is caused by not doing the 'dot source' in powershell term.

  • Please read the open discussion gallery.technet.microsoft.com/.../Discussions

  • Good stuff!  This helped me a lot and worked perfectly!  Thanks!

  • emp0,

    Thanks for the feedback. I've updated the out-datatable function to include setting the datatype property.

  • PowerShizzle,

    You're welcome. For simple data loads PowerShell rocks!

  • I'm using get-content on a text file, and then using the select object on those results.  Finally I'm piping that to out-datatable, but get nothing but the length column.  Since I guess what is getting passed is a string array the actual string value is not grabbed from $object.PsObject.get_properties() in the out-datatable definition.  Is this by design? Does out-datatable not work with string arrays?

  • inanutshell

    See discussion under Out-Datatable for how to handle text files:

    gallery.technet.microsoft.com/.../4208a159-a52e-4b99-83d4-8048468d29dd

  • Say I run a query using invoke-sqlcmd2, then need to add a field to the datatable (preferably at the beginning of the row) prior to using write-datatable.  How do I do this?  I've been futzing with add-member without any success.  Any help greatly appreciated!

  • @mbourgon

    You can't a a column to the beginning of a table without dropping and recreating the table.

  • JV - cool!  Now I'm getting somewhere.  Is there an easy way to say "copy field + datatable1 into datatable2"?  

    I'm trying to take the results of an SP and add the servername to it, before writing to a table. All help appreciated!

  • @mbourgon

    insert into datatablea2 select 'myserver as ServreName, * from datatable1

  • JV - sorry, how do you do that in powershell?  (should have been more clear).  

    Say I did this:

    $dt = invoke-sqlcmd2 -serverinstance myservername -query "exec sp_who" -As 'Datatable'

    I now have a datatable.  I want to copy the details of $dt into a new DataTable (so that I can add the ServerName field), then write using write-datatable.  But since I'm multithreading it, I need to do this within the PS script; I can't use

  • (grr - stupid post button)

    JV - sorry, how do you do that in powershell?  (should have been more clear).  

    Say I did this:

    $dt = invoke-sqlcmd2 -serverinstance myservername -query "exec sp_who" -As 'Datatable'

    I now have a datatable.  I want to copy the details of $dt into a new DataTable (so that I can add the ServerName field), then write using write-datatable.  But since I'm multithreading it, I need to do this within the PS script; I can't use t-sql or interim tables for that part.

  • Answering my own question, sorta - asked on Stackoverflow and Chad Miller (the guy who wrote the above code) answered.  

    Two ways to do it, provided you ran something like this:

    $dt = invoke-sqlcmd2 -serverinstance $_.server -query "exec master.dbo.sp_who" -As 'Datatable'

    and you want to add the Servername (yes, sp_who returns the server name, but I wanted an SP everyone had)

    Either:

    $Col =  new-object Data.DataColumn

    $Col.ColumnName = "ServerName"

    $dt.Columns.Add($col)

    $dt | %{$_.ServerName = $server}

    Or (abbreviated version):

    $dt.Columns.Add("ServerName")

    $dt | %{$_.ServerName = $server}  #in my main foreach I say $server = $_.server