Chad's profileChad Miller's BlogBlogListsNetworkMore  |  Tools Help |
Chad Miller's BlogSQL Server, PowerShell and so on |
||||
|
Public folders
July 01 Powershell SQL Server Backup/RestoreI posted a script on Poshcode for doing backups and restores of SQL Server databases using SMO. The script is adapted from SQL Server Powershell Extensions functions of the same name. SMO 9.0 (2005) and 10.0 (2008) have slightly different methods and properties at times. For the most part there is very little difference, however for the backup class there is one big difference--the backup class was moved from the base SMO assembly to the SMOExtended assembly. In order to account for using either assembly, the script does a couple of things:
The other thing the script does is make use of the special error handling needed for SMO due to the use of innerExceptions.
Looking at the Powershell + SMO script you can't help but think, how much simplier backup and restores are in T-SQL. Here's the equivalent T-SQL command to backup a database:
BACKUP DATABASE AdventureWorks TO DISK = 'C:\backup\adventureworks.bak' WITH FORMAT;
In this case T-SQL would be much easier and less code to accomplish the same task of backing up or restoring a database. June 28 SQLPSX Release 1.6I completed Release 1.6 of SQLPSX which adds support for SQL Authentication and addresses several issues. SQLPSX consists of 106 functions, 2 cmdlets and 12 scripts for working with SMO, Agent, RMO, SSIS and SQL script files.
Here's an example of using SQL Authentication:
$server = Get-SqlServer 'Z002\SQL2K8' 'sa' 'mypassword'
Because Get-SqlServer, Get-AgentJobServer, and Get-ReplServer support either Windows or SQL Authentication you can pass a server object to the additional functions. Here's an example of getting databases from the server variable creating above:
Get-SqlDatabase $server | Select name
Many of the functions support shortcuts when using Windows authentication. Because Windows authentication is used we do not have to first get a reference to a server object. manually Here's the same example getting databases using Windows authentication:
Get-SqlDatabase 'Z002\SQL2K8'
Other than the addition of SQL authentication support, SQLPSX 1.6 is largely a maintenance release. I've replaced calls to WMIC with Get-WmiObject and addressed issues documented in the Issue Tracker. I've also incorporated the error handling technique Allen White provided into several functions in order to provide better error reporting. I had hoped to have a provider for SSIS completed, however I ran into a few issues with weak support of functionality in the SSIS API. If the SQL product team doesn't include an SSIS provider in SQL Server 2008 R2, I'll look at creating one then.
With Release 1.6 complete I'm planning on the next release, 2.0, to re-implement the V1 functions as advanced functions in Powershell V2.
June 24 Powershell + SMO Error Handling TipsAllen White posted a helpful post on SMO error handling with Powershell. Actually the same concept equally applies to SMO coded in any other .NET language. SMO uses an error object's InnerException which can be several layers deep and you must traverse the nested InnerExceptions to get to the detailed error message. Related to Allen's post, Michiel Worries gives us another useful technique to get back full error messages. If you find your Powershell scripts are throwing too generic of a error message, you may find some useful messages in InnerException.
Links:
June 19 Adventures in Powershell SSIS Administration ProgrammingIn January 2009, I released version 1.4 of SQL Server Powershell Extensions which included a Library of functions for working with SQL Server Integration Services (SSIS). This post describes the functionality included in the script library including enumerating msdb package stores, getting packages from msdb and file stores as well as copying packages and folders among other things. At the time I felt the script library was just a way to prototype what should later be implemented as a Powershell Provider. I even named the functions after what I would later implement in a provider (Get-ISItem, Copy-ISItem, New-ISItem, Remove-ISItem, Rename-ISItem, Test-ISPath) prefixing eaching with IS for Integration Services.
Over the past week I've been thinking about how to code an SSIS Powershell provider by re-reading Professional Windows Powershell Programming* chapter on Providers, re-familiarizing myself with the SSIS Powershell code and pouring over the Microsoft.SqlServer.Dts.Runtime Namespace (ManagedDts) documentation. I haven't writen a line C# code for my SSIS provider project, yet I've learned a few things...
I don't want to turn this post into a rant, so instead of listing every SSIS administration and scripting issue of which there are many, I will illustrate just the top one that is preventing me from creating an SSIS Powershell provider. First, let's go over the setup I have. I'm using SQL Server 2005 Standard Edition named instance called Z002\SQLEXPRESS. I know the name says SQLEXPRESS, but its not I just don't feel like changing demonstrate code and naming the instance other than SQLEXPRESS. Because I use a named instance I modified the MsDtsSrvr.ini.xml file located in C:\Program Files\Microsoft SQL Server\90\DTS\Binn directory according to the SQL Server documentation and added the following entry:
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder"> <Name>Z002_SQLEXPRESS</Name> <ServerName>Z002\SQLEXPRESS</ServerName> </Folder> I then created a SSIS package called "test" and deployed to the SQL Server store. I can the connect to Integration Services through SSMS 2005 as shown the in following screen shots:
  Now let's try to connect to Integration Services using Powershell and enumerate the folders and packages.
First load the ManagedDTS assembly (note this should be a single line). I'm loading the 2005 instead of 2008 assembly.
[reflection.assembly]::Load("Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") > $null
To load the 2008 assembly:
[reflection.assembly]::Load("Microsoft.SqlServer.ManagedDTS, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") > $null
An Application is the main class in the ManagedDTS, most of what an administrator would be interested in is accessible via its methods. So, next we'll create an SSIS Application object:
$app = new-object ("Microsoft.SqlServer.Dts.Runtime.Application")
And finally we will enumerate the folders and packages using the GetPackgeInfos method. $app.GetPackageInfos("\",'Z002\SQLExpress',$null,$null) The following information is returned:
Problem #1: The call to GetPackgeInfos requires the SQL instance name i.e. Z002\SQLExpress while the equivalent SSMS SSIS connection you specify just the server name i.e. Z002 Next let's load an SSIS package called test stored on the root of the package store. Note this does not execute the package this just loads the package into a variable, you can then execute using the execute method. $package = $app.LoadFromDtsServer("\Z002_SQLEXPRESS\test",'Z002',$null) Problem #2: The call to LoadFromDtsServer requires the top level folder as defined in MsDtsSrvr.ini.xml while the equivalent SSMS SSIS Object Explorer just presents it. Furthermore there are no methods in ManagedDts to return the top level folder. So, basically we have GUI, SSMS that includes functionality not available in the API one would use to automate SSIS administration Here's what I'd like to be able to do in an SSIS provider, but can't because the API does not expose the needed functionality: cd SSIS:\ SSIS:\> cd Z002 Get top level folder and the SSIS:\Z002\> get-childitem There are still some useful things to scripting SSIS administration activities. I recently worked with a DBA to move dozens of SSIS packages and the folder structure from one SQL Server to another. By using a Powershell script over a GUI approach this saved him a few hours of work. Here's an example of the script we used which is part of the LibrarySSIS functions in SQLPSX: copy-isitemsqltosql -path '\' -topLevelFolder 'Z002_SQL1' -serverName 'Z002\SQL1' -destination 'Z003_SQL2' -destinationServer 'Z003' -recurse $true I created a Connect item requesting functionality in Microsoft.SqlServer.Dts.Runtime to support discovering the top level folder and using the server name instead of the instance name. Both of these items are already in SSMS. Please vote on my connect item. Related Posts: *Note: Professional Windows Powershell Programming is a book geared for developers to create C# Powershell cmdlets and Providers. This is the best book on the subject. It's also the only book on creating snapins, cmdlets and providers. Because of the topic of the book I only recommend it to those who want to write C# cmdlets and providers. Most Powershell users will not have a need to do so and instead will simply use the built-in or 3rd party cmdlets and providers. June 16 Creating Red Gate SQL Compare Snapshots with PowershellCo-worker and fellow DBA,, John O'Shea developed a Powershell script to create Red Gate SQL Compare snapshot files. Check out John's blog post here. June 13 Give Me a Coconut and Six MonthsTim Benninghof tagged me with the question.
"So You’re On A Deserted Island With WiFi and you’re still on the clock at work. Okay, so not a very good situational exercise here, but let’s roll with it; we’ll call it a virtual deserted island. Perhaps what I should simply ask is if you had a month without any walk-up work, no projects due, no performance issues that require you to devote time from anything other than a wishlist of items you’ve been wanting to get accomplished at work but keep getting pulled away from I ask this question: what would be the top items that would get your attention?"
Although blog tagging reminds me of the chain emails I tell my mother to stop forwarding to everyone, the question gives me a chance to jot down a few items from my Powershell projects list.
My job is mixture of DBA and manager. As a DBA, I don't consider myself to be a developer, but I'll write few lines of Powershell or C# code to create something that will save time or money. As a manager, cost control, efficiency, doing more with less, is the mantra being delivered across every company. Today it is more important than ever to be productive, think about how you can get rid of low value work and measurably show expense reductions. Let's face it, a lot of us must fill out a performance appraisal at least once a year. Quantifying your contribution towards cost-savings is one way to keep you listed as a top performer. June 04 SQLServerCentral Article on Backup Monitoring and ReportingThe article, Backup Monitoring and Reporting, demonstrates a SQL Server backup reporting solution I use in my production environment. Some highlights of the solution:
See the full article for details. May 31 Gettting and Setting SQL DataMany of the Powershell scripts I write either retrieve data from SQL Server or execute a nonquery i.e. delete, insert, update against SQL Server. I find myself reusing a couple of simple functions often enough that posted Get-SqlData and Set-SqlData functions to poshcode as LibrarySqlData.ps1:
#######################
function Get-SqlData { param([string]$serverName=$(throw 'serverName is required.'), [string]$databaseName=$(throw 'databaseName is required.'), [string]$query=$(throw 'query is required.')) Write-Verbose "Get-SqlData serverName:$serverName databaseName:$databaseName query:$query" $connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;" $da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString) $dt = New-Object "System.Data.DataTable" [void]$da.fill($dt) $dt } #Get-SqlData ####################### function Set-SqlData { param([string]$serverName=$(throw 'serverName is required.'), [string]$databaseName=$(throw 'databaseName is required.'), [string]$query=$(throw 'query is required.')) $connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;" $conn = new-object System.Data.SqlClient.SqlConnection $connString $conn.Open() $cmd = new-object System.Data.SqlClient.SqlCommand("$query", $conn) [void]$cmd.ExecuteNonQuery() $conn.Close() } #Set-SqlData To use soure the . ./LibrarySqlData.ps1 file. And here are a couple examples:
Get-SqlData 'Z002\SQLEXPRESS' 'master' 'SELECT @@servername'
Set-SqlData 'Z002\SQLEXPRESS' 'pubs' "update authors set au_lname = 'White' WHERE au_lname = 'White'" May 23 Create Database Diagrams with Powershell + yUMLPowershell MVP, Doug Finke has an interesting post demonstrating how to create UML class digrams from Powershell using the website yUML. So, I thought it would be a fun exercise to create a UML class diagram of database tables. It might seem a little odd to represent database tables in UML, however it can done and a few commerical allow you to do so. Basically tables are represented as classes, columns as attributes, constraints and indexes are behaviors and foreign key relationships are associations. For a good overview of how to model databases as UML class diagrams see Database Modelling in UML by By Geoffrey Sparks.
The yUML website generates UML through a simple syntax, where parameters are appended to the URL string (See Doug's blog for an example). The hard part for generating class digrams from databases is getting the meta data about SQL Server tables in a usable format. In fact, the T-SQL query is much larger than the acommpanying Powershell code and also took me longer to figure out. The query sqlmeta.sql returns meta data about a SQL table and makes use of SQL 2005/2008 CTE's and XPath. A single XML document is returned with column, primary key, constraint, index, trigger, and relationship information. For example using the sample AdventureWorks database and the Sales.Store table as parameters the following XML is returned:
<root>
<class> <table>Sales.Store</table> <columns> <column>PK CustomerID: int</column> <column>Name: nvarchar</column> <column>SalesPersonID: int</column> <column>Demographics: xml</column> <column>rowguid: uniqueidentifier</column> <column>ModifiedDate: datetime</column> </columns> <relations> <relation>[Sales.Customer]</relation> <relation>[Sales.SalesPerson]</relation> </relations> <operations> <operation>FK: FK_Store_Customer_CustomerID</operation> <operation>FK: FK_Store_SalesPerson_SalesPersonID</operation> <operation>Index: AK_Store_rowguid</operation> <operation>Index: IX_Store_SalesPersonID</operation> <operation>Index: PXML_Store_Demographics</operation> <operation>PK: PK_Store_CustomerID</operation> <operation>Trigger: iStore</operation> </operations> </class> </root> The Powershell script, yuml.ps1 includes function to execute a query and return a DataTable called Get-SqlData:
function Get-SqlData
{ param([string]$serverName=$(throw 'serverName is required.'), [string]$databaseName=$(throw 'databaseName is required.'), [string]$query=$(throw 'query is required.')) Write-Verbose "Get-SqlData serverName:$serverName databaseName:$databaseName query:$query" $connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;" $da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString) $dt = New-Object "System.Data.DataTable" $da.fill($dt) > $null $dt } #Get-SqlData We'll call the Get-SqlData function from Get-SqlMeta function and pass the lengthy T-SQL query as a parameter. Having obtained the SQL Server meta data, we need to extract the information to generate a URL string using the ConvertTo-yUML function.
function ConvertTo-yUML {
param ([xml]$meta) $r = $meta.root.class | foreach {'[' + $_.table + '|'} $table = $meta.root.class | foreach {'[' + $_.table + ']'} $cols = $meta.root.class | foreach {$_.columns.column} $r += [string]::join(';',$cols) $ops = $meta.root.class | foreach {$_.operations.operation} if ($ops) { $r += '|' + [string]::join(';',$ops) } $r += ']' $rels = $meta.root.class | foreach {$_.relations.relation} if ($rels) { $r += ",$table->" + [string]::join(",$table->",$rels) } $r } #ConvertTo-yUML Using the XML document above we should see the following output:
[Sales.Store|PK CustomerID: int;Name: nvarchar;SalesPersonID: int;Demographics: xml;rowguid: uniqueidentifier;ModifiedDate: datetime|FK: FK_Store_Customer_CustomerID;FK: FK_Store_SalesPerson_SalesPersonID;Index: AK_Store_rowguid;Index: IX_Store_SalesPersonID;Index: PXML_Store_Demographics;PK: PK_Store_CustomerID;Trigger: iStore],[Sales.Store]->[Sales.Customer],[Sales.Store]->[Sales.SalesPerson]
Finally we pass the ouput generated from ConvertTo-yUML to Doug's original Get-yUMLDigram function:
Function Get-yUMLDiagram {
param( $yUML, $diagramFileName="c:\test.jpg", [switch]$show ) $base = "http://yuml.me/diagram/class/" $address = $base + $yUML if($show) { $wc = New-Object Net.WebClient $wc.DownloadFile($address, $diagramFileName) Invoke-Item $diagramFileName } else { $address } } #Get-yUMLDiagram To use the script source, the functions and run the commands passing in server, database, schema and table name parameter. Here's the completed example
. ./yuml.ps1
$serverName = 'Z002\SQLEXPRESS';$databaseName = 'AdventureWorks';$schema='Sales';$name='Store' $meta = Get-SqlMeta $serverName $databaseName $schema $name $yUML = (ConvertTo-yUML $meta[0]) Get-yUMLDiagram $yUML 'C:\Users\u00\bin\store.jpg' -show Running the code above produces the following output:
**UPDATE: yUML now supports pdf output. I've updated my script using Doug's revisions to output a pdf document. Here's an example of pdf output:
Get-yUMLDiagram $yUML 'C:\Users\u00\bin\store.pdf' -pdf
May 13 Sarasota SQL Server User Group PresentationI presented a one-hour session at the Sarasota SQL Server User Group entitled "Powershell vs T-SQL". The session Description:
"Automating database tasks traditionally has meant creating Transact-SQL code or an SSIS package. While the introduction of Powershell provides another option, DBAs who are already familiar with Transact-SQL and SSIS, question how Powershell fits into SQL Server Managementability. This session will provide a general overview of Powershell, the SQL Server 2008 Powershell host, and demonstrate use cases where Powershell provides an easier solution than T-SQL or SSIS. Likewise, use cases will be highlighted where T-SQL provides a superior solution over Powershell.."
The presentation and supporting material is available here
Please comment or contact me with any questions. May 12 Using Powershell to Import Excel file into SQL ServerThis is a quick script which demonstrates how easy it is to import an Excel file into a SQL Server table using Powershell. The script is posted on PoshCode also:
$filepath = 'G:\PowershellvTSQL\backupset.xlsx' #Comment/Uncomment connection string based on version #Connection String for Excel 2007: $connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";" #Connection String for Excel 2003: #$connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=`"$filepath`";Extended Properties=`"Excel 8.0;HDR=Yes;IMEX=1`";" $qry = 'select * from [backupset$]' $sqlserver = 'Z002\SQLEXPRESS' $dbname = 'SQLPSX' #Create a table in destination database with the with referenced columns and table name. $tblname = 'ExcelData_fill' ####################### function Get-ExcelData { param($connString, $qry='select * from [sheet1$]') $conn = new-object System.Data.OleDb.OleDbConnection($connString) $conn.open() $cmd = new-object System.Data.OleDb.OleDbCommand($qry,$conn) $da = new-object System.Data.OleDb.OleDbDataAdapter($cmd) $dt = new-object System.Data.dataTable [void]$da.fill($dt) $conn.close() $dt } #Get-ExcelData ####################### function Write-DataTableToDatabase { param($dt,$destServer,$destDb,$destTbl) $connectionString = "Data Source=$destServer;Integrated Security=true;Initial Catalog=$destdb;" $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString $bulkCopy.DestinationTableName = "$destTbl" $bulkCopy.WriteToServer($dt) }# Write-DataTableToDatabase ####################### $dt = Get-ExcelData $connString $qry Write-DataTableToDatabase $dt $sqlserver $dbname $tblname May 07 TechEd 2009 SQL + Powershell SessionsBill Ramos, Principal Program Manager on the SQL Server Management Studio team, will be delivering several sessions on two of my favorite topics, SQL Server and Powershell. Specifically Bill will be discussing Policy Based Management (PBM) and an interesting CodePlex project called Enterprise Policy Management Framework. The project has the stated goal of extending Policy-Based Management to all SQL Server instances in the enterprise and works against SQL 2008 as well as 2005 and 2000 instances. I've been meaning to kick the tires on PBM and Enterprise Policy Management Framework looks like something I should check out. Oh, and a little CodePlex project called SQL Server Powershell Extensions
 will be mentioned in Bill's Powershell presentation. I'm unable to attend TechEd this year, If you're fortunate enough to go and have an interest in SQL and Powershell, checkout Bill's sessions.April 26 Powershell V2 Release CandidateThe big news this week has been the announcement of the Windows 7 Release Candidate availability on April 30th. Upon seeing the tweet on Friday, April 24th, my first thoughts were of excitement and a bit of panic and not for the reasons you would typically think. The announcement is especially important to Powershell users because technically this is also the first release candidate of Powershell V2. For me it's not about the upgrade from Vista to Windows 7, it is the upgrade from Powershell V1 to V2 that I'm focused on. I'm excited about all the new features in Powershell V2, but at the same time a little concerned that I haven't started using it yet.
Why haven't I started using Powershell V2 in CTP release? Well, part of my job is providing production support to several thousand SQL Server databases. I have Powershell V1 installed on SQL Server 2000, 2005 and 2008 machines all running Windows 2003 Server and my team also uses Powershell V1 on their Windows XP and Vista workstations. Running CTP or beta versions of Powershell just isn't an option for production support. That's not to say I haven't participated in various accelerated adoption programs around SQL Server. I used SQL Server 2008 a full 6 months prior to the release; however Microsoft rightly so, does not provide production support until after a product releases and the accelerated adoption programs are restricted to one or two systems.
Aside from my day job, I develop a CodePlex project called SQL Server Powershell Extensions built entirely with Powershell. As a tool developer creating a packaged product, I should be more bleeding edge then a production support role and look at new technology changes prior to general availability. There are many features I'm very excited about in Powershell V2 that I can't wait to apply to SQL Server Powershell Extensions. This week's announcement means I've got to start now and that causes a little panic when I think about all I need to do.
How much time do I have to get ready for Powershell V2? The Release Candidate status moves Powershell V2 one step closer to production usage, but how much closer? At this point general availability release dates are mere speculation, however based on the Microsoft Watch blog post, it might be as early as June 30th. The Windows 7 release still means several months until Powershell V2 is available outside of Windows 7 or Windows 2008 R2. So, my guess, best case we are looking right around the ***Updated
April 19 The Value Proposition of Powershell to DBAsBrent Ozar posted the results of poll he conducted on Powershell adoption among database professionals in a post entitled, Powershell poll results. Of particular note, out of the 100 polled, which I assume are mostly database professionals only about 20% use Powershell. I wonder if the results of the poll are really that different when comparing DBAs with other system administration groups (Web, Exchange, Server, etc.). I mean Windows administrators really have never been heavy scripters. There is of course, a big opportunity for Powershell to change this. But, it wasn't so much the poll results which caught attention as it was the statements about Powershell benefits. You see I'm working on a Powershell presentation for SQL Server user groups and one thing I noticed is that we (DBAs who use Powershell) haven't done a great job of articulating the benefits of Powershell to our fellow SQL Server DBAs. I made a previous incomplete attempt to do so in my post, Is PowerShell necessary for a DBA?. DBAs are niche group of administrators and inevitably the Powershell discussion boils down to one question -- "Why should I learn Powershell when I have a pretty good set of tools in T-SQL, SSMS and SSIS". Notice the question isn't about VBScript vs. Powershell because DBAs like most Windows administration groups, have not used VBScript extensively. They do however, make heavy use of T-SQL. The use of a T-SQL as scripting language with near 100% adoption is what sets DBAs apart from their web, server or Exchange administrator counterparts. So the argument is really one of Powershell vs. T-SQL (and maybe SSIS), with that mind here is my attempt to better define the benefits of Powershell to DBAs:
Getting started with Powershell SQL tasks can be a bit of a learning curve, if you’re looking at using SQL + Powershell you may want to check out my Codeplex project SQL Server Powershell Extensions which provides over 100 functions for common SQL administration tasks. Can you think of any additional items or am I way off on my initial assumption (Powershell vs. T-SQL)?
April 11 Send Powershell output to an Excel, PDF, or Image fileWhile reading through Wrox book, Professional SQL Server 2008 Reporting Services (SSRS) I noticed a chapter on "Integrating Reports Into Custom Applicaiton" where the authors make use of the ReportViewer control to embed an SSRS report in a WinForm application without needing an SSRS server. Since SSRS reports can use DataTables as a data sources I thought this would make a nice way to export the output of any Powershell command/script to a number of different formats natively supported by SSRS including Excel, PDF, and various image formats: BMP,EMF,GIF,JPEG,PNG or TIFF.
In Searching for examples of ReportViewer WinForm implementations I found a very nice set of classes called ReportExporters by Andriy Protskiv which greatly simplify working with ReportViewer. Using ReportExporters I created an Out-Report script available on Poshcode. To use you'll need to install the Microsoft Report Viewer Redistributable, either the 2005 or 2008 version will work. I think Visual Studio and/or SSRS server includes the ReportViewer. I have both installed on my machine and did not need to install the Redistributable. You will also need to download or compile the ReportExporters dlls. You can grab the compiled version from demo code in the article.
Here are few examples of what you can do with the script:
get-alias | ./out-report.ps1 "c:\users\u00\documents\aliases.xls" xls
get-alias | ./out-report.ps1 "c:\users\u00\documents\aliases.pdf" pdf get-alias | ./out-report.ps1 "c:\users\u00\documents\aliases.jpeg" -filetype image -imagetype JPEG -height 22 -width 11 The script makes use of a DataTable routine by Marc van Orsouw (/\/\o\/\/) . I often use variations of the DataTable code in many of my scripts, especially for importing data into SQL Server tables.
Andriy points out, not only can you generate Excel, PDF and images from the ReportViewer control, but you can also extended SSRS to support DOC, RTF, WordprocessingML, and OOXML. A little hacky, but interesting.
April 05 Executing Powershell in SQL ServerLet me put out a disclaimer the script and technique described in this post is a bit of a hack and I find loading SQL Server data from Powershell easier rather than executing Powershell within SQL Server to load data. The former is cleaner and uses standard .NET assemblies, XML or SQL Server utilities. See my previous post and the referenced article entitled SQLServerCentral Article on Importing Powershell Output into SQL Server for details. But if you really want to run Powershell in SQL Server and get back a table result set here's one way to do so:
Copy the New-Xml script below which is simply a minor adaptation (I turned the original function into a script) of a function called New-Xml posted on the Powershell Team blog blog entry Using PowerShell to Generate XML Documents. Save the code as New-Xml.ps1 and place in directory accessible by SQL Server service account on the SQL Server machine.
param($RootTag="ROOT",$ItemTag="ITEM", $ChildItems="*", $Attributes=$Null)
Begin { $xml = "<$RootTag>`n" } Process { $xml += " <$ItemTag" if ($Attributes) { foreach ($attr in $_ | Get-Member -type *Property $attributes) { $name = $attr.Name $xml += " $Name=`"$($_.$Name)`"" } } $xml += ">`n" foreach ($child in $_ | Get-Member -Type *Property $childItems) { $Name = $child.Name $xml += " <$Name>$($_.$Name)</$Name>`n" } $xml += " </$ItemTag>`n" } End { $xml += "</$RootTag>`n" $xml } Now, run the SQL script below from SQL Server Management Studio:
CREATE TABLE #output
(line varchar(255)) INSERT #output EXEC xp_cmdshell 'powershell.exe -c "get-service | c:\users\u00\bin\new-xml.ps1 -childItems Name,Status"' DELETE #output WHERE line IS NULL --The result set contains one row per line 255 characters we need to have a single variable with all lines --Code below sets @doc to the entire contents of the #output table DECLARE @doc varchar(max) SET @doc = '' DECLARE @line varchar(255) DECLARE xml_cursor CURSOR FOR SELECT line FROM #output OPEN xml_cursor FETCH NEXT FROM xml_cursor INTO @line WHILE @@FETCH_STATUS = 0 BEGIN SET @doc = @doc + @line FETCH NEXT FROM xml_cursor INTO @line END CLOSE xml_cursor DEALLOCATE xml_cursor DROP TABLE #output --Get the name and status out of the xml doc SELECT item.ref.value('(Name/text())[1]', 'nvarchar(128)') AS name, item.ref.value('(Status/text())[1]', 'nvarchar(128)') AS status FROM (SELECT CAST(@doc AS xml) AS feedXml) feeds(feedXml) CROSS APPLY feedXml.nodes('/ROOT/ITEM') AS item(ref) The script uses xp_cmdshell to execute powershell.exe and passes in a Powershell command or script file for execution (in this case Get-Service). Because xp_cmdshell returns the output of a command as a multiple rows of text up to 255 characters in length the output is first converted to XML using the New-Xml.ps1 script. The XML is then transformed into a relational result set using the XQuery extensions available in T-SQL. The solution is not without issues the output of Powershell commands may contain reserved words or special characters which the New-Xml script does not handle, but it's good enough for what we want to do. April 03 Making Disk, Database and Table Graphs with PowershellSometimes seeing data visually rather than in text format makes things easier to understand. For instance, I prefer to use WinDirStat to see a Treemap of disk space usage by file and type rather than a sorted text list of large files. Seeing the files visually I can immediately determine which files are taking up the most space. I also would rather view database file space usage in the TaskPad view of SQL Server 2000 Enterprise Manager instead of running a query. Outside of pre-built tools I'll often use Excel pivot charts to quickly create graphs. In Powershell creating your own graphs is fairly simple thanks to Joel Bennet's (Jaykul), Powerboots and Visifire SiliverLight and WPF charts.
Getting Started
*Note: I'm using Powershell V1 and I've placed Powerboots, SQLPSX, and Visifire in my ...Documents\WindowsPowerShell\Libraries directory. In addition since Powerboots and Visifire use WPF, the .NET 3.5 Framework is required.
Once you've completed the install you can use this script to create a test chart:
$libraryDir = Convert-Path (Resolve-Path "$ProfileDir\Libraries")
[Void][Reflection.Assembly]::LoadFrom( (Convert-Path (Resolve-Path "$libraryDir\WPFVisifire.Charts.dll")) ) if (!(Get-PSSnapin | ?{$_.name -eq 'PoshWpf'})) { Add-PsSnapin PoshWpf } New-BootsWindow -Async { $chart = New-Object Visifire.Charts.Chart $chart.Height = 300 $chart.Width = 600 $chart.watermark = $false $ds = New-Object Visifire.Charts.DataSeries $ds.RenderAs = [Visifire.Charts.RenderAs]"Bar" $chart.Series.Add($ds) for($i=0; $i -lt 5; $i++) { $dp = new-object Visifire.Charts.DataPoint $dp.YValue = (get-random -min 1 -max 20) $ds.DataPoints.Add($dp) } $chart } -Title "Test" Here are a few examples I've posted on Poshcode for disk, database and table space graphs.
WPFDiskSpace -- Uses Powerboots and Visifire to display a WPF graph of disk space including percent used and free
Get-WmiObject Win32_LogicalDisk -filter "DriveType=3" | ./WPFDiskSpace.ps1 WPFDbSpace -- Uses Powerboots, Visifire and SQLPSX to Display a WPF graph of SQL Server database data and log file space:
Get-SqlDatabase 'Z002\Sql2k8' | where {$_.name -like "pubs*"} | ./WPFDbSpace.ps1
 WPFTableSpace -- Uses Powerboots, Visifire and SQLPSX to display a WPF graph of SQL Server table data and index space usage:
./WPFTableSpace.ps1 'Z002\SqlExpress' AdventureWorks
The graphs I've created so far have been simple/static graphs, but you can do a lot more with Powerboots. For example Joel has a really cool script showing a real-time graphical ping monitor. And not only can you make charts, you can use WPF to create some very slick looking screens. In short, Powerboots is an impressive toolkit which I will definitely find additional uses for. March 28 SQLPSX 1.5 ReleaseI completed Release 1.5 of SQLPSX which adds 31 new functions for working with database maintenance (CHECKDB, Index rebuilds, backup and restore) as well as login, user, role and permission management. With this release there are now 104 total functions, 2 cmdlets and 12 scripts around SMO, Agent, RMO, and SSIS.
Here's a few examples working with database maintenance functions:
#Get a database object The complete list of new functions added in the 1.5 Release: SQLPSX 1.5 marks what I consider the first feature complete release. I believe the most common database administration tasks can be accomplished using the 107 functions/cmdlets provided in SQLPSX. If you feel something is missing please post to the discussion forum.
With Release 1.5 complete, I'm starting work on the 1.6 Release which will include two enhancements. First, re-implement LibraryShowmbrs as a compiled V1 cmdlet primarily to remove a dependency on WMIC. Second, replace LibrarySSIS with a proper PSProvider so that SSIS packages using a SQL Server store can be navigated and modified using a drive analogy. My goal is to the 1.6 release be the last Powershell V1 release. I’m considering using a mix of compiled and script based functions for Release 2.0 and will re-implement the remaining script-based functions as advanced functions in Powershell V2. I know the lack of help files has become an issue as the function libraries have grown and become more complex. The main benefit moving script-based functions to V2 will provide is the ability to have help files just like compiled cmdlets. March 14 Build Your Own SQL Server 2008 Object Dependency ViewerI saw a demonstration by Doug Finke during the Windows PowerShell Virtual User Group Meeting #9 for displaying network graphs and thought this would be a great technique for visualizing SQL Server object dependencies. The Powershell code is available on Doug's blog post entitled PowerShell, Visualize the Peanut Butter Recall Data. The scripts he provides use the NodeXL .NET class libraries for creating network graphs.
With the first piece of our do-it-yourself project in place, we need to get the dependency information in a simple source/target pair format to pass the data to the Show-NetMap function. Unfortunately obtaining reliable object dependencies in SQL Server is somewhat difficult due to deferred name resolution and other dependency tracking issues. Aaron Bertrand has a very detailed post describing the problems with dependency tracking in SQL Server 6.5 through SQL Server 2008 entitled Keeping sysdepends up to date in SQL Server 2008. As a result of the SQL dependency tracking issues most SQL Server professionals have learned not to trust the sysdepends tables. The only truly reliable method of determining dependencies remains to be parsing SQL code or purchasing 3rd party dependency viewer tools. I had originally planned on writing my own dependency parsing cmdlet, leveraging Visual Studio Database Edition ScriptDom class libraries, but quickly discovered the properties and methods which would expose this information is private. Fortunately SQL Server 2008 has added a new system catalog view called sys.sql_expression_dependencies which solves some, but not all of the dependency tracking issues (see Aaron's post for details). The new system catalog view may still have some issues, but for the most part its good enough for getting SQL object dependencies without parsing SQL code, so this is what we will use.
Before we get started we'll need to download Doug's functions and SQL Server Powershell Extensions. The following code below uses the SQL Server Powershell Extensions function, Get-SqlData to get the output of a query against sys.sql_expression_dependencies and sys.objects. In order to show a simplier graph I'm filtering out check constraint dependency information. The data is then piped to Doug's Show-NetMap Powershell function:
. .\Show-NetMap
$qry = @"
SELECT DISTINCT OBJECT_NAME(referencing_id) AS [Source], COALESCE(referenced_server_name + '.','') + COALESCE(referenced_database_name + '.','') + COALESCE(referenced_schema_name + '.','') + referenced_entity_name AS [Target], o.type_desc AS SourceType FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id "@ get-sqldata 'Z002\SQL2K8' AdventureWorksLT $qry | ? {$_.SourceType -ne 'CHECK_CONSTRAINT'} | Select Source, Target | Show-NetMap F Network graph, showing a diagram of SQL Server object dependencies in the AdventureWorksLT sample database:
 Alternative query that provides more detailed column level dependency information:
SELECT OBJECT_NAME(referencing_id) + COALESCE('.' + COL_NAME(referencing_id, referencing_minor_id),'') AS [Source], COALESCE(referenced_server_name + '.','') + COALESCE(referenced_database_name + '.','') + COALESCE(referenced_schema_name + '.','') + referenced_entity_name + COALESCE('.' + COL_NAME(referenced_id, referenced_minor_id), '') AS [Target], o.type_desc AS SourceType FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id UNION ALL SELECT OBJECT_NAME(referencing_id) AS [Source], OBJECT_NAME(referencing_id) + '.' + COL_NAME(referencing_id, referencing_minor_id) AS [Target], o.type_desc AS SourceType FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id WHERE referencing_minor_id <> 0 "@ March 05 Test-SqlScript and Out-SqlScript cmdletsI 've been using Visual StudioTeam System 2008 Database Edition (VSDB) and noticed an interesting post on Gert Drapers' blog, entitled Getting to the Crown Jewels. In the posting Gert demonstrates a basic C# WinForm application which parsers and formats T-SQL using the assemblies Microsoft.Data.Schema.ScriptDom and Microsoft.Data.Schema.ScriptDom.Sql included in VSDB. A subsequent posting states if you own an official copy of Visual Studio Team System 2008 Database Edition GDR, which I do, you are allowed to redistribute these assemblies. Looking at the WinForm code, I thought this would make a couple of interesting cmdlets. So, I created two cmdlets called Test-SqlScript and Out-Sqlscript. Test-SqlScript parses a T-SQL script and tests whether the script is valid. Out-SqlScript in addition to validating the script, re-formats the script output with 25 different formatting options. I created help files with the cmdlets, you can see the formating options by using get-help Out-SqlScript.
You might ask, how can I use these cmdlets? Well I can immediately think of two use cases:
To setup the formatter within SQL Server Management Studio:
It would have been nice to take the T-SQL parsing routine a step further and build an object dependency list from a SQL script, but unfortunately the method/properties for getting to the referenced objects appear to be private. The cmdlets will be included in the next release of SQL Server Powershell Extensions. In the meantime I've included compiled cmdlets here until I can get a release packaged up. The source code is available on the CodePlex project site Source Code section. March 04 Perl, sed and WMIC ScriptsLast week I received a notification from my internet service provider they were migrating their member web pages to a new location. I had some old static web pages on my site that I hadn't updated in years. The notification requested I manually move the pages I wanted to keep to their new site. Since I hadn't hadn't updated the pages in some time and the information wasn't something I use, I figured its not worth the effort to move, but instead zipped them up and placed them here.
The zip file contains a few Perl, sed and WMIC scripts I used before adopting Powershell as my primary administrative scripting language in September 2007. The only scripts I still ocassionaly use are the WMIC ones. The rest have either been replaced by Powershell scripts or I haven't had a need to do the tasks in my current job function. WMIC has some built-in format files, but you can create your own, so one of the interesting exercises I went through with WMIC was creating an XSL transformation of the XML output. I literally spent a few days figuring out XSL and applying it to the raw XML WMIC generates. Looking at the XSL now, I'm struck with how easy it is to accomplish the same thing in Powershell with no need to fiddle with XSL formatting.
The other thing I noticed is that with some of my Perl scripts and Windows bat files I would implement some basic help. For Perl it would look something like this:
if (!defined $opts{S} or !defined $opts{m} or !defined $opts{r}) sub printUsage {
print <<__Usage__; usage: cmd>perl $0 -S <Server> -m <Month in Number format> -r <Report C or P> __Usage__ exit; And for Windows bat files I would do something like this: @echo off
For Powershell I'll throw an error, like this:
param([string]$sqlserver=$(throw 'Get-SqlServer:`$sqlserver is required.') Powershel V2 allows you to implement a more advanced help feature equivalent to the help that comes with most cmdlets. However, with V1 in looking at my old Perl and bat scripts I wonder if a usage based throw like below should be used? param([string]$sqlserver=$(throw 'Usage:Get-SqlServer -SqlServer Z002') Which throw do you prefer?
February 28 Disk Alignment Partitioning: The Good, the Bad, the OK and the Not So UglyLately I've been reading about the disk alignment partitioning and performance degragaton that occurs in Windows 2003 when using the wrong partition NTFS partition offset. There is an I/O performance impact of 30 - 40% when disk partitions are not properly aligned. So, this starting me thinking, what is good offset anways and how can I write a Powershell script to verify disk are configurated correctly?
It turns out Windows 2003 uses a 64KB or 128 sectors (64KB = 128 sectors) so you want to choose 64 or a multiple of 64. However different storage arrays use varying offsets (4, 64, 256, 512) and not only do you have to consider the Windows offset you should also take into account the storage array offsets to ensure they are aligned. Because of the need to consider the storage array offsets, Microsoft KB artcle 929491 recommends a starting offset of 2,048 sectors (1 megabyte). The 2,048 offset cover most storage arrays including EMC Symmetrix and CLARIION which may use a setting other than 64KB depending on the configuration. An offset of 63 which is the detault offset for MBR, is a bad setting. For the boot partition 63 is OK, but for a SQL Server data and log drive 63 is bad always.
With the question of a good offset answered; 64 might be OK, but 2,048 will cover almost any SAN configuration, I created a Powershell script to verify disk settings. I want to report the system name, logical disk name, partition name, blocksize starting offset, and finally the start sector. The start sector is calucated by dividing the StartingOffset by the value of BlockSize as described in the "More Information" section of KB article 929491. The WMI classes Win32_LogicalDisk and Win32_DiskPartition provide the needed information, however we'll need to use the association class Win32_LogicalDiskToPartition to link logical disks with partitions. .
Save the script below as a ps1 file, partalign.ps1, and run by specifying the computer name as a parameter ./partalign.ps1 Z002
param ($computer)
Using the WMI assocation classes is something you won't have do very often, but its not too ugly. You could construct the Powershell WMI query as one statement, but I found it easier to understand to break up the statments in two parts (Partition, and LogicalDisk). The Win32_LogicalDiskToPartition class returns a Win32_LogicalDisk WMI object which I then add the partition information to using add-member. I have a few servers with mount points and use the Win32_Volume class for returning space usage information. I haven't figured out a way to associate mount points to partitions and I don't think the WMI association classes exist to do so. If anyone knows how to associate mount points with partitions using WMI please post a comment February 25 Automating MOM 2000/2005 Report GenerationEvery month I go through a capacity planning exercise looking at things like processor, memory, storage and backup success rates. As part of the review I'll run two simple Microsoft Operations Manager 2005 SQL Server Reporting Services reports for the past 30 day period:
The reports aren't perfect as the data summarized into hourly data points over the 30 day period you loose some details, but they work remarkably well at spotting performance trends and developing a performance baseline for a particular server over time. This is especially true with database servers. If the processor usage on the chart increases by a double digit percentage, usually this is a direct result of a sudden event, meaning one day it suddenly jumps. In the database area by using additional tools we can often tie the performance change directly to configuration changes, new code deployments, increased usage, or business processing (month-end, quarter-end, etc.). When I notice sudden performance changes I will research why the change occurred, if a cause can be identified and usually it can, I will work to remediate the SQL code, change a configuration, order a larger server or simply continue to observe the performance.
To generate the MOM reports I created a Powershell script .Over the past several years the Powershell script is my third version of a MOM report/IE automation script, the first two version were Perl scripts using Win32::SAM or Win32::IEAutomation. There are better ways at automating MOM reports, for instance it would be much easier to create your own customized version of the report based on the out of the box report and then create subscriptions in SQL Server Reporting Services to deliver the reports on a monthly basis. There are also some nice frameworks like WASP and WAITN which should make IE automation in Powershell easier. Again using SQL Server Reporting Services direclty is the best approach, but hey that wouldn't give me an excuse to write a Powershell script to automate IE to execute a report :). Whenever I have the time I will create the customized report I need, but for now this script works for me so I continue to use it.
A few of notes about the Powershell script:
#get-content servers.txt | % {$server = $_.split(",")[0]; $serverType = $_.split(",")[1]; ./mom.ps1 $server $serverType '5' '2008'} param ($server=$(throw '`$server is required.'),$serverGroup,$month=$(throw '`$month is required.'),$Year=$(throw '`$year is required.')) function runReport { param ($server,$group,$beginDT,$endDT,$url) $ie = new-object -com "InternetExplorer.Application" $ie.navigate($url) $ie.visible = $true Write-Host "$server" while ($ie.Busy) { [System.Threading.Thread]::Sleep(500) } $doc = $ie.Document $begin = $doc.getElementByID("ctl143_ctl00_ctl03_txtValue") $begin.value = $beginDT $end = $doc.getElementByID("ctl143_ctl00_ctl05_txtValue") $end.value = $endDT $groupList = $doc.getElementByID("ctl143_ctl00_ctl07_ddValue") $groupValue = $groupList | where {$_.text -eq $group } $groupList.value = $groupValue.value $groupList.FireEvent('onchange') while ($ie.Busy) { [System.Threading.Thread]::Sleep(500) } #Sleep for 10 seconds to allow postback to fully refresh [System.Threading.Thread]::Sleep(10000) $computerList = $doc.getElementByID("ctl143_ctl00_ctl09_ddValue") $computerValue = $computerList | where {$_.text -eq "MyDomain\$server" } $computerList.value = $computerValue.value if ($computerList.value -ne 0) { $viewReport = $doc.getElementByID("ctl143_ctl00_ctl00") $viewReport.Click() #Sleep for 5 minutes [System.Threading.Thread]::Sleep(300000) } } switch ($serverGroup) { '0' { $group = 'Microsoft SQL Server 2000' } '5' { $group = 'Microsoft SQL Server 2005' } default { throw '`$serverGroup must be 0 or 5.' } } $curl = 'http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fMicrosoft+Operations+Manager+Reporting%2fMicrosoft+Windows+Base+Operating+System%2fPerformance+History%2fPerformance+History+Processor-Percent+Processor+Time' $purl = 'http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fMicrosoft+Operations+Manager+Reporting%2fMicrosoft+Windows+Base+Operating+System%2fPerformance+History%2fPerformance+History+Paging+File-Percent+Usage' $beginDT = $([DateTime]"$month/$year").ToString("G") $endDT = $([DateTime]$beginDT).AddMonths(1).ToString("G") #Run CPU Report runReport $server $group $beginDT $endDT $curl #Run Page Report runReport $server $group $beginDT $endDT $purl February 18 Windows PowerShell Virtual User Group Meeting #9Presentation and code examples from Windows PowerShell Virtual User Group Meeting #9 presentation on the CodePlex project SQL Server Powershell Extensions from Feb 18th, 2009 are available here.
My thanks to Marco Shaw for inviting me to present at the user group meeting. February 17 Check for Windows Server 2003 Scalable Networking PackI've previously blogged about the issues with Windows Server 2003 Scalable Networking Pack included in Windows 2003 Service Pack 2. I keep finding servers in my environment with the feature enabled, so I created a short Powershell script to check for SNP features by querying the registry of the remote server. The registry keys involved are documented in the Microsoft KB article 948496.
To use, save the following Powershell code as a script file and pass the computer name as a parameter to the script:
param ($computer) $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine',$computer) $regKey = $reg.OpenSubKey("SYSTEM\\CurrentControlSet\\Services\\Tcpip\\Parameters") new-object psobject | add-member -pass NoteProperty computer $computer | add-member -pass NoteProperty EnableTCPA $regKey.GetValue('EnableTCPA') | add-member -pass NoteProperty EnableRSS $regKey.GetValue('EnableRSS') | add-member -pass NoteProperty EnableTCPChimney $regKey.GetValue('EnableTCPChimney') |
|
|||
Send a private message
Subscribe to RSS feed
Tell a friend
Add to My MSN
Add to your network
Sign up for alerts
|
|
