To learn PowerShell, Laerte suggests that you just start using it. To encourage you to start, he provides a series of tips on using PowerShell with SQL Server to solve various everyday problems. With a little patience, a good IDE, and a bit of help and advice, "the lion is dead".
I'm the kind of guy who can't resist praising PowerShell, whether it’s applied to SQL Server or some other administrative task. Unfortunately, PowerShell presents the user with an initial hurdle to jump: the initial learning effort that's required. Thankfully, there is a very easy way to overcome that hurdle, as pointed out to me by Shay Levy ( Twitter| blog) when I asked him for advice on the best way to learn PowerShell:
"Just use it"
And although it sounds facetious, it’s the honest truth; you will only begin to understand PowerShell if you start using it. More than that, you have to spend hours reading, testing the code, looking to improve it and not be ashamed to ask for pointers. This might sound like a real trial, but it’s actually deeply satisfying, and I’ve found the community which has sprung up to be warm and welcoming.
My little knowledge of PowerShell is applied purely to SQL Server, and for this purpose it is not difficult to learn. However, that doesn’t change the fact that I'm always looking for ways to improve the code I’ve written, and I‘ve seen some examples that truly make my brain hurt! Yet with a little patience, a good IDE and, of course, good contacts with the community, no challenge is too great (or as we say in Brazil, "the lion is dead").
To show just how easy PowerShell can be if you have a bit of determination, I got some of my unpublished blog posts together and decided to use them as demonstrations in this article. We'll see some solutions using just PowerShell, PowerShell with SQL Server and also SQLPSX. I hope you find it useful!
SSIS and the T-SQL Hammer
I firmly believe that you have to read Chad Miller’s Blog Post entitled “The T-SQL Hammer”. It is a fantastic example of productivity in PowerShell, and I took the liberty of using the same title here because, after reading his words...
“The over-reliance on a familiar tool is best described with the quote, 'if all you have is hammer, everything looks like nail', and for database professionals this means using or sometimes misusing T-SQL. Whenever database administrators are presented with a scripting problem they instinctively reach for good-old-familiar T-SQL. And why not? In many cases T-SQL provides an ideal solution to SQL Server administration scripting problems, however there are certain scenarios where another tool, PowerShell, provides a more elegant solution.”
...I believe we can demonstrate another great argument in favor of using PowerShell. As you may have guessed, PowerShell might be my own ‘hammer’, but nevertheless, I feel that it offers remarkably elegant solutions to many of the challenges I face, and can do the same for you.
I’d like to draw your attention to a great article by Rodney Landrum, showing a very interesting solution for consolidating SQL Server Error Logs. He shows how to use SSIS and some T-SQL to neatly handle this operation, and I decided to tinker with his solution. Before I carry on, you should either go and read it, or just accept what I’m saying on faith! Rodney’s solution is excellent, make no mistake; the only thing I feel I’m adding to it is ease-of-use and perhaps a little more elegance with respect to my own environment (prompted, perhaps, by Chad’s blog post). You’ll have to be the judge when it comes to which works better for you.
To start with, one of the things I’ve changed in my version is that I don’t use the MERGE statement, for a reason that become clear in a moment. I have one Server and Database repository, one table repository to save the logs to, and I created another control table called ServersInfo with only two columns, Server Name (ServerName) and last collection Date (DateLastLogErrorImported)
In his example, Rodney uses the Merge statement and SSIS to record the data that is brought across in the log for each server. In my case, rather than using an SSIS package to control which logs I store, I record the date of the most recent logs in this ServersInfo table, and only insert data collected after this date. This last table is handy; it means that, when I access the error log, I can always filter my information by date.
But let’s see the code:
First, we need some T-SQL to create the repository table and Server/date control table. Obviously, you will create these tables on your server and database repository.
Create Table SQLLogInfo ( ServerName varchar(50),
LogDate datetime,
ProcessInfo varchar(100),
Text varchar(max)
)
Create Table ServersInfo ( ServerName varchar(100),
DateLastLogErrorImported datetime
)
Now let's populate the Servers table (Serversinfo) with the names of all the servers from which you want to have the error logs collected.
insert into ServersInfo(ServerName) values ('Colombo-pc\SQLExpress')
insert into ServersInfo(ServerName) values ('Colombo-pc')
After that’s sorted, let’s look at the PowerShell code (remember, I am using SQLPSX)
#Define Server and Database Repository
$ServerRepository = $env:COMPUTERNAME
$DatabaseRepository = "tempdb"
#Return the servers and the last collection date from error logs
get-SqlData -sqlserver $ServerRepository -dbname $DatabaseRepository -qry "Select ServerName,DateLastLogErrorImported from ServersInfo" | foreach {
$ServerName = $_.Servername
$DateLastLogErrorImported = $_.DateLastLogErrorImported
get-sqlserver $ServerName | foreach {
#If this is the first collection, takes the date 2010/01/01
if ($DateLastLogErrorImported.value -eq $null -or $DateLastLogErrorImported.value -eq "")
{ $DateLastLogErrorImported = '2010/01/01' }
#Retrieve the error log from the current server in foreach. Apply a #filter to only LogDate above and equal to Last Collection date
#and insert into Repository
$Error.Clear()
Get-SqlErrorLog -sqlserver $ServerName -lognumber 0 | where-object { $_.LogDate -ge $DateLastLogErrorImported} | foreach {
$Text = $($_.text) -replace "'"
Set-SqlData -sqlserver $ServerRepository -dbname $DatabaseRepository -qry "Insert into SQLLogInfo (Servername,LogDate,ProcessInfo,text) values ('$($ServerName)','$($_.Logdate)','$($_.ProcessInfo)','$Text)')"
}
#Update Information Table with the server and the last collection date.
Set-SqlData -sqlserver $ServerRepository -dbname $DatabaseRepository -qry "Update ServersInfo set DateLastLogErrorImported = getdate() where Servername = '$($Servername)'"
}
}
I'm not handling errors at this stage, but that would be very simple to implement. With just a Try-Catch before the SQLPSX functions, we cover all error possibilities, logging them into a file for later inspection. To make your life even easier, you can find this code in my Simple-Talk articles.
With that , the data is neatly collected and saved into a SQL Server table. We can schedule this script to run once a day and, as I said earlier, we only insert data from the date of the last collection, so avoiding having to work with huge volumes of data. Naturally, with this collection system in place, you can apply summations, aggregates, filters and whatever else is necessary to set up your monitoring. To quote Chad, PowerShell provides a more elegant solution.
Searching and Logging with PowerShell
Not too long ago, I had the pleasure of helping a friend (and great Microsoft community influencer) Jorge Segarra (Twitter| blog), in another PowerShell challenge. Jorge needed the following:
A folder with several txt files (i.e. flat files) should be filtered for files that had the most recent change date (LastWriteTime) in a determined period, searched for whether they contain a particular string, and the result this search should be stored in a newly-created file. The results in this file should show the path and file name, as well as the line number corresponding to the string search result, and text of this line.
It sounds fairly complicated, but it really is quite simple in PowerShell. I’ve created two txt files to illustrate:
File1.txt contains this content:
111111111111111111111111111
222222222222222222222222222
33333Error33333333333333333
444444444444444444444444444
Error5555555555555555555555
666666666666666666666666666
777777777777777777777777777
8888888888888888888888Error
And File2.txt contains:
111111111111111111111111111
222222222222222222222222222
33333Error33333333333333333
444444444444444444444444444
Error5555555555555555555555
666666666666666666666666666
777777777777777777777777777
8888888888888888888888Error
999999999999999999999999999
100000000000000000000000000
AAAAAAAAAAAAAAAAAAAAErrorAA
BBBBBBBBBBBBErrorBBBBBBBBBB
CCCCCCCCErrorCCCCCCCCCCCCCC
You can see that in red & bold is the word “Error”, which is the string that I want to look for.
Let's go to the code:
Function Invoke-AdmErrors
{
<#
.SYNOPSIS
Search for errors in files
.DESCRIPTION
Search for errors in files
.INPUTS
Pipe Path to files
.OUTPUTS
Flat file with files found
#>
param (
[Parameter(Position=0, Mandatory = $true, ValueFromPipeline = $true)] [String] $Path,
[Parameter(position=1,Mandatory = $true )]
[system.DateTime] $InitialDate ,
[Parameter(position=2,Mandatory = $true )]
[system.DateTime] $FinalDate ,
[Parameter(position=3,Mandatory = $false )]
[string] $ResultFile = "c:\users\jsegarra\desktop\example.txt"
)
Process
{
Get-ChildItem $path | Where-Object {$_.lastwritetime -ge $InitialDate -and $_.lastwritetime -le $FinalDate } | foreach {
Select-String -Pattern "Error" -Path $_ -AllMatches | Add-Content -Path $ResultFile -Force
}
}
}
If you look closely at the PowerShell above, you'll see that the process of finding and filtering results, and the saving of the final file is all done in one line. The rest are input parameters, help and function configuration.
Let’s say I want to search for the string “Error” in all files under the path C:\temp, with a filter focusing on files changed between the dates 01/01/2010 and 01/05/2010 (dd/mm/yyyy), and the save the results into the file C:\temp\result.txt.
Invoke-AdmErrors -Path c:\temp -Initialdate '01/01/2010' -FinalDate '01/05/2010' -SearchFor "Error" -ResultFile c:\temp\result.txt
...And the file result will look like this, showing the file name (with path), line number of the result, and string searched:
Figure 1. Search and Filter results
A Simpler Way to Get Values from SQL Server
This is a very quick demonstration of something which surprises me. In many code snippets and articles on the web, I see this method (or something similar) of returning values from a query (either a simple query, stored procedure etc. ..) from SQL Server to PowerShell:
$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=myserver;Integrated Security=SSPI;Initial Catalog=Master")
$cn.open()
$sql = "Select name,number from master..spt_values"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($sql, $cn)
$rt = $cmd.ExecuteReader()
while ($dr.Read()) {
$rt.GetValue(0)
$rt.GetValue(1)
}
It’s correct, for sure, but why not simplify the code? We could rewrite it to just:
Invoke-Sqlcmd -ServerInstance MyServer -Database Master -Query "Select name,number from master..spt_values" | Select-Object name,number
We can even put a condition on the return; let’s say we only want to display rows where the value of the column number is 2...
Invoke-Sqlcmd -ServerInstance MyServer -Database Master -Query "Select name,number from master..spt_values" | where-object { $_.number -eq 2} | Select-Object name,number
...Or even run a stored procedure.
Invoke-Sqlcmd -ServerInstance MyServer -Database MyDatabase -Query "exec usp_myproc" | Select-Object column1, column2
PowerShell provides many ways to do this, and you can choose the best method for yourself.
Scripting SQL Server Objects with SQLPSX
I’ve recently started using the SQLPSX library, and I am constantly finding gems buried inside it. One of my personal favorites, and one which I use a lot, is a very easy method for generating scripts of objects . For example, if we want to generate a script from a table, use:
Get-SqlDatabase -sqlserver YourServer -dbname YourDatabase | Get-SqlTable -name YourTable | Get-SqlScripter
And to output to a text file:
Get-SqlDatabase -sqlserver YourServer -dbname YourDatabase | Get-SqlTable -name YourTable | Get-SqlScripter |Out-File c:\temp\Script.txt
What if I want all the tables in my database, and not just a specific one? Simple; just delete the –name parameter in Get-SQLtable:
Get-SqlDatabase -sqlserver YourServer -dbname YourDatabase | Get-SqlTable | Get-SqlScripter |Out-File c:\temp\Script.txt
The same (i.e. how to generalize your scripts) can be applied to all other objects, such as stored procedures:
Get-SqlDatabase -sqlserver YourServer -dbname YourDatabase | Get-SqlStoredProcedure | Get-SqlScripter
To see a complete list of the functions of SQLPSX, and read more about how to use them, go to SQL Server PowerShell Extensions Help.
How PowerShell Custom Tables Saved the day
This article’s final showcase of PowerShell Proclivity came about because a friend contacted me on MSN, and said:
"Laerte, I saw the hint you gave via twitter to Jorge Segarra, and want to see if you can help me. I need to output a result of a proc to an XML document every day (as is described in the Jorge´s blog post), but the result comes from a third-party procedure, and I need to insert a description of a flag. The problem is that the Stored Procedure only returns the code, and I cannot change the procedure (because it’s third–party). Can you help me?"
Well, enumerating the problems before us:
- We need to add one more column to the Stored procedure output,
- This column is a description of another column: a flag,
- We cannot change the Stored Procedure to add this column,
If I told you that we could solve this whole problem with only one line of PowerShell, you believe me? No? Well... Let's see.
First, let's create the scenario. I created a table and a stored procedure with the code below, and also included some names in the table. This table has a column called InsanityState and the content is a code and describe as:
Column InsanityState Char(1) - can be I – Insane, O - Out of Control, W - Without Hope, D - He has been a DBA
The T-SQL Code:
--Create The Table
Create table Arkham_Asylum ( Id int ,-- Patient ID,
name varchar(100), -- Patient Name,
InsanityState char(1) –-
State of madness - can be I – Insane, O - Out of Control, W - Without Hope, D - He has been a DBA
)
go
-- Insert some Insanity
insert into Arkham_Asylum values (1,'Poison Ivy','I')
insert into Arkham_Asylum values (2,'Scarecrow','O')
insert into Arkham_Asylum values (3,'Two Face','W')
insert into Arkham_Asylum values (4,'Joker','I')
insert into Arkham_Asylum values (3,'Laerte Junior','D')
--Create Stored Procedure
go
alter procedure usp_getInfoInsanes
as
select Id,
name,
InsanityState
from Arkham_Asylum
go
If we execute the proc, you will see the Flag that it needs to display as a description:
Figure 2. The results of the Stored Procedure.
As I said, we cannot change the Stored Procedure to return the description of InsanityState, so we need to use PowerShell Custom Tables. First, let’s see just the code to return all these crazy people with their Insanity State descriptions, but not the code to output to XML.
To repair this situation, I use a custom table, calling a function to convert the ID of the InsanityState flag, and display the description.
function get-InfoInsanes
{
Begin
{
function ConvertTo-FlagCaption($Value)
{
switch($value)
{
"I" {"Insane";break}
"O" {"Out of Control";break}
"W" {"Without Hope";break}
"D" {"He has been a DBA";break}
default {"Unknown";BREAK}
}
}
}
Process
{
invoke-sqlcmd -server $env:computername -database teste -Query "exec usp_getInfoInsanes" |
Select-Object Id,
Name,
@{Expression={ConvertTo-FlagCaption -value $_.insanityState};Label = "Insanity State"}
}
}
...And look at the output:
Figure 3. InsanityStates, clarified.
Now, for the output to an XML file, just use the ConvertTo-XML cmdlet and the save property:
(invoke-sqlcmd -server $env:computername -database teste -Query "exec sp_getInfoInsanes" | Select-Object Id,
Name,
@{Expression={ConvertTo-FlagCaption -value $_.insanityState};Label = "Insanity State"} | ConvertTo-Xml -NoTypeInformation).save("c:\temp\ResultXml.Xml")
...And the XML will look like:
Figure 4 – The Insane XML Output
Now you just need to schedule this to run, and be happy!
Summary
Well my friends, I have described a few of the uses that I’ve put PowerShell to whilst working as a DBA. I realize I’ve presented a mixed bag here, but that reflects the wide spectrum of problems that a working DBA comes across that PowerShell can help to solve. I chose them because they can be used in a lot of different ways by people who are taking the first steps with the language. I hope that you, like me, look at the productivity gain that can be found in working with this fantastic tool.
A big hug to everyone, and here are some links for further reading:
