![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
Hey, Scripting Guy! How is Windows PowerShell usage by database professionals different from the way that other IT Pros use Windows PowerShell?<\/p>\n<\/p>\n
Summary:<\/b> Use Windows PowerShell to collect server data and automatically store that information in a Microsoft SQL Server.<\/p>\n
<\/p>\n
Hey, Scripting Guy! How is Windows PowerShell usage by database professionals different from the way that other IT Pros use Windows PowerShell?<\/p>\n
<\/p>\n
— MC<\/p>\n
<\/p>\n
<\/p>\n Chad Miller(Blog<\/a>|Twitter<\/a>) 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<\/a>). Chad leads the Tampa Powershell User Group<\/a> and is a frequent speaker at SQL Saturdays and Code Camps. <\/p>\n Take it away Chad!<\/p>\n <\/p>\n 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.<\/p>\n Extract Data<\/b><\/p>\n SQL Server 2008 and 2008 R2 provide the Invoke-SqlCmd<\/b> 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<\/a> to the Scripting Guys Script Repository. Save the code as invoke-sqlcmd2.ps1<\/b> and then dot source our new function into your Windows PowerShell console by using the following command:<\/p>\n . .\/invoke-sqlcmd2.ps1<\/span><\/p>\n The following example dot sources the invoke-sqlcmd2.ps1<\/b> script that contains the invoke-sqlcmd2<\/b> 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.<\/p>\n PS C:\\> . C:\\data\\ScriptingGuys\\2010\\HSG_11_1_10\\invoke-Sqlcmd2.ps1<\/span><\/p>\n PS C:\\> Invoke-Sqlcmd2 -ServerInstance sql1 -Database pubs -Query “Select * from auth<\/span><\/p>\n <\/span>ors”<\/span><\/p>\n <\/span><\/p>\n <\/span><\/p>\n au_id : 172-32-1176<\/span><\/p>\n au_lname : White<\/span><\/p>\n au_fname : Johnson<\/span><\/p>\n phone : 408 496-7223<\/span><\/p>\n address : 10932 Bigge Rd.<\/span><\/p>\n city : Menlo Park<\/span><\/p>\n state : CA<\/span><\/p>\n zip : 94025<\/span><\/p>\n contract : True<\/span><\/p>\n <\/span><\/p>\n <…OUTPUT Truncated><\/span><\/p>\n <\/p>\n This example reads a file that contains T-SQL statements, runs the file, and writes the output to another file.<\/p>\n Invoke-Sqlcmd2 -ServerInstance “MyComputer\\MyInstance” -InputFile “C:\\MyFolder\\tsqlscript.sql” | Out-File -filePath “C:\\MyFolder\\tsqlscript.rpt”<\/span><\/p>\n <\/p>\n This example uses the Windows PowerShell -Verbose<\/b> parameter to return the message output of the PRINT<\/b> command.<\/p>\n Invoke-Sqlcmd2 -ServerInstance “MyComputer\\MyInstance” -Query “PRINT ‘hello world'” -Verbose<\/span><\/p>\n VERBOSE: hello world<\/span><\/p>\n <\/b><\/p>\n Getting a Server List<\/b><\/p>\n 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<\/b> 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<\/b> view from your CMS server.<\/p>\n From SQL Server Management Studio, create a SQL table.<\/p>\n CREATE TABLE server_instance<\/span><\/p>\n (server_name varchar(255) NOT NULL);<\/span><\/p>\n Next populate the table with a list of SQL Servers:<\/span><\/p>\n INSERT server_instance VALUES(‘Z001’);<\/span><\/p>\n INSERT server_instance VALUES(‘Z002\\SQ2K8’);<\/span><\/p>\n INSERT server_instance VALUES(‘Z003\\R2’);<\/span><\/p>\n <\/p>\n With our server_instance<\/b> table populated, we can use our Invoke-SqlCmd2<\/b> function to retrieve a list of servers and then call Windows PowerShell command for each. The following example retrieves the version information.<\/p>\n 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”}<\/span><\/p>\n <\/b><\/p>\n Extract and Load data<\/b><\/p>\n 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<\/a> to the Scripting Guys Script Repository. Save the Windows PowerShell function from the Script Repository as write-datatable.ps1<\/b> and then dot source our new function into the Windows PowerShell console by using the following command.<\/p>\n . .\/write-datatable.ps1<\/span><\/p>\n The Write-DataTable<\/b> function uses the .NET Data.SqlClient.SqlBulkCopy class<\/a> to load an in-memory DataTable<\/b> or DataRow<\/b> array into a SQL Server table. This works out well because the cmdlet invoke-sqlcmd<\/b> or our function invoke-sqlcmd2<\/b> returns a datatable<\/b> object. Let us examine an example database named space usage collection for all databases in your environment to use in forecasting growth.<\/p>\n You have to create a SQL Server table using SQL Server Management Studio. To do this, run the following Transact-SQL script:<\/p>\n CREATE TABLE [dbo].[db_space](<\/span><\/p>\n [server_name] [varchar](128) NOT NULL,<\/span><\/p>\n [dbname] [varchar](128) NOT NULL,<\/span><\/p>\n [physical_name] [varchar](260) NOT NULL,<\/span><\/p>\n [dt] [datetime] NOT NULL,<\/span><\/p>\n [file_group_name] [varchar](128) NOT NULL,<\/span><\/p>\n [size_mb] [int] NULL,<\/span><\/p>\n [free_mb] [int] NULL,<\/span><\/p>\n CONSTRAINT [PK_db_space] PRIMARY KEY CLUSTERED <\/span><\/p>\n (<\/span><\/p>\n [server_name] ASC,<\/span><\/p>\n [dbname] ASC,<\/span><\/p>\n [physical_name] ASC,<\/span><\/p>\n [dt] ASC<\/span><\/p>\n )<\/span><\/p>\n )<\/span><\/p>\n <\/p>\n The T script and management studio are seen in the following figure.<\/p>\n <\/p>\n 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<\/a>. 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<\/b> function:<\/p>\n $dt = invoke-sqlcmd2 -ServerInstance “Z003\\R2” -Database “master” -InputFile .\/get-dbspace.sql -As ‘DataTable’<\/span><\/p>\n <\/p>\n Examining the type information about $dt variable we can see the type is of a DataTable as shown in the following figure.<\/p>\n <\/p>\n To load the DataTable<\/b> into our SQL Server table we’ll call the Write-DataTable<\/b> function:<\/p>\n Write-DataTable -ServerInstance “Z003\\R2” -Database “dbutility” -TableName “db_space” -Data $dt<\/p>\n <\/p>\n Using invoke-sqlcmd2<\/b> and piping the output to Out-GridView<\/b> we can see the data has in fact been loaded.<\/p>\n invoke-sqlcmd2 -ServerInstance “Z003\\R2” -Database “dbutility” -Query “SELECT * FROM db_space” | Out-GridView<\/span><\/p>\n The results from this command are shown in the figure below.<\/p>\n <\/p>\n Extract, Transform, and Load Data<\/b><\/p>\n As we’ve seen, by using a couple of simple functions – invoke-sqlcmd2<\/b> and write-datatable<\/b> – 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<\/b> 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<\/b> then we can use our Write-DataTable<\/b> function. Using a function called Out-DataTable<\/b> adapted from a script<\/a> by Marc van Orsouw (Blog<\/a>|Twitter<\/a>) we can do just that. I saved the modified script<\/a> to the Scripting Guys Script Repository. <\/p>\n Save the following code as Out-DataTable.ps1<\/b> and source our new function<\/p>\n . .\/ Out-DataTable.ps1<\/span><\/p>\n <\/p>\n To load convert the output of a WMI call to a DataTable<\/b>, we’ll pipe to our newly created out-datatable<\/b> function and assign the output to a $dt<\/i>.<\/p>\n $dt = Get-WmiObject Win32_LogicalDisk -filter “DriveType=3” | Select @{n=’UsageDT’;e={get-date -Format “yyyy-MM-dd”}}, ‘<\/span><\/p>\n SystemName, DeviceID, VolumeName, ‘<\/span><\/p>\n @{n=’Size’;e={$([math]::round(($_.Size\/1GB),2))}}, @{n=’FreeSpace’;e={$([math]::round(($_.FreeSpace\/1GB),2))}} | out-datatable<\/span><\/p>\n <\/p>\n Having assigned the output to a datatable you can call the write-datatable<\/b> to load the collected data into a SQL Server table. This makes out-datatable<\/b> very powerful in that the output of any Windows PowerShell command can be easily loaded into a SQL Server table. Using write-datatable<\/b> 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<\/b>. The Add-SqlTable.ps1<\/b> script is also uploaded to the Scripting Guys Script Repository.<\/a> Save the code as Add-SqlTable.ps1<\/b> and source the new function.<\/p>\n The following command will create a new empty SQL Server table named diskspace<\/b> based on the structure of our DataTable<\/b> variable $dt<\/i>:<\/p>\n Add-SqlTable -ServerInstance “Z003\\R2” -Database dbutility -TableName diskspace -DataTable $dt<\/span><\/p>\n <\/b><\/p>\n Note: <\/b>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<\/b> works best for a quick data dump of Windows PowerShell data to a SQL Server table.<\/p>\n Finally, with the destination SQL Server table created, we can call our write-datatable<\/b> function to load the collected data:<\/p>\n Write-DataTable -ServerInstance “Z003\\R2” -Database “dbutility” -TableName “diskspace” -Data $dt<\/span><\/p>\n <\/p>\n Using invoke-sqlcmd2<\/b> we can see the data was loaded into the SQL Server table:<\/p>\n invoke-sqlcmd2 -ServerInstance “Z003\\R2” -Database “dbutility” -Query “SELECT * FROM diskspace” | Out-GridView<\/span><\/p>\n <\/p>\n <\/b><\/p>\n <\/b><\/p>\n Summary<\/b><\/p>\n This post demonstrated how to query and load the output of any Windows PowerShell command into a SQL Server table. The functions invoke-sqlcmd2<\/b>, write-datatable<\/b>, out-datatable<\/b> and add-sqltable<\/b> can be used as building blocks for many of your Windows PowerShell-based data loading needs.<\/p>\n 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. <\/p>\n <\/p>\n I invite you to follow me on Twitter<\/a> or Facebook<\/a>. If you have any questions, send email to me at scripter@microsoft.com<\/a> or post them on the Official Scripting Guys Forum<\/a>. See you tomorrow. Until then, peace.<\/p>\n <\/p>\n Ed Wilson, Microsoft Scripting Guy<\/b><\/p>\n","protected":false},"excerpt":{"rendered":" Summary: Use Windows PowerShell to collect server data and automatically store that information in a Microsoft SQL Server. Hey, Scripting Guy! How is Windows PowerShell usage by database professionals different from the way that other IT Pros use Windows PowerShell? — MC Hello MC, Microsoft Scripting Guy Ed Wilson here. Next […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[195,146,56,3,176,45],"class_list":["post-16641","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-chad-miller","tag-databases","tag-guest-blogger","tag-scripting-guy","tag-sql-server","tag-windows-powershell"],"acf":[],"blog_post_summary":" Summary: Use Windows PowerShell to collect server data and automatically store that information in a Microsoft SQL Server. Hey, Scripting Guy! How is Windows PowerShell usage by database professionals different from the way that other IT Pros use Windows PowerShell? — MC Hello MC, Microsoft Scripting Guy Ed Wilson here. Next […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/16641","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=16641"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/16641\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=16641"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=16641"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=16641"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
Hello MC, Microsoft Scripting Guy Ed Wilson here. Next week, Nov 8-11, 2010, is the annual SQL PASS Summit<\/a> 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.<\/p>\n![](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/2234.HSG-11-1-10-01.jpg\")
<\/p>\n![](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/0830.HSG-11-1-10-02.jpg\")
<\/p>\n![](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/1031.HSG-11-1-10-03.jpg\")
<\/p>\n![](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/4863.HSG-11-1-10-04.jpg\")
<\/b><\/p>\n