If you're involved with SQL Server, you will have noticed a lot of commotion around Powershell, as people push for it in the context of SQLServer. A good friend and inspiration of mine, Allen White (MVP/sqlblog/@SQLRunr) who I met at SQLPass 2003, is currently one of the people teaching us- a group of SQLServer admins, operational DBA, and developers, how Powershell really can help to ease our lives.
At first I was kind of wary about the new kid in town called Powershell; with regards to Windows (7/2008) as well as towards SQLServer, which is more my turf. The reason I didn’t initially learn Powershell V1.0 is that I’d been bitten by the economy coming to a halt in 2008. The company I work for decided to go fully into survival mode and all upgrades were postponed.
However, nowadays we are installing SQL2008R2 as well as Windows2008R2, so the need to upgrade ourselves to be able to support these two domains is essential. Another reason why I couldn’t withhold my curiosity is that we are also making progress with other software monitoring systems, and these tools also have Powershell features incorporated, or they have APIs to be used by Powershell.
I’ve read some books, like the Free Powershell ebook (V1.0) and Master-PowerShell (V2.0), but as I got along, my fingers started itching and I started the (standard) Powershell IDE. I became annoyed fairly quickly, because by default your Powershell environment is really restricted, until you tear it open. If you start downloading little scripts of interest and try to execute them, you will hit the first boundary you’ll have to deal with. If you haven’t used Powershell yet, you will most likely see that the Execution Policy is set to “Restricted”.
# get help regarding execution policy
get-help executionpolicy
'--- current execution policy---'
Get-ExecutionPolicy
(by the way, the # is the comment indicator sign for powershell cfr ‘--‘ in T-SQL)
You’ll need to run the command Set-ExecutionPolicy to the level you need if you want to unlock restrictions.
For more in depth information on installing and configuring Powershell, you must read http://help.outlook.com/en-gb/140/cc952756.aspx
Another thing I've been impatient about is finding a development environment that offers me more than the standard IDE, one that caters for my need for easy debugging and a bit of intellisence (which will feature in V3). The first free one I encountered was Quest's PowerGUI (http://www.quest.com/powershell/powergui.aspx ) and that's the one I'm still using today. It has nice debug features, there is some IntelliSense and there are some nice add-ons that can help you ease your Posh life. (Posh being a common abbreviation for Powershell). Of course there are other IDEs for Powershell, and recently PowerSE (by Devfarm http://powerwf.com/products/powerse.aspx ) also became free.
For a nifty little comparison of some of the main players, see http://stackoverflow.com/questions/171514/best-ide-for-powershell. Test them and pick the one that suits you best.
So, what do you start with? Copy/Paste programming 101.
Copy/Paste programming 101.
Google/Bing for any functionality you would want to do and just add Powershell to the criteria, and off you go.
In my case, I started with copy file from x to y. Basic enough, right?
Copy-Item c:\scripts\test.txt c:\test(source: http://technet.microsoft.com/en-us/library/ee156818.aspx)
First mission completed.
I’m certainly not a programming whiz kid, but when I saw the IntelliSense, I started investigating and exploring the new kid on the block. Then I browsed Allen’s blog, as I wanted to see how easily I could get to my SQLServer instances. It didn’t take long (it’s only one copy/paste away) before I got my first SMO connection and scripted out some stuff. After all, scripting is the least dangerous thing you can do.
Then I stumbled on Invoke-SQLCMD2 script by Chad Miller from technet, which made life even easier.
SQLPSx was the next code function repository I downloaded and hooked up in my environment.
Fairly soon after, I also met Laerte Junior on Twitter, (@laertejuniorDBA) http://shellyourexperience.wordpress.com/ and he also helped me take my first steps, using his modules for 'Gathering perfmon data with Powershell' (based on Allen’s topic). I even assisted him on debugging an issue I’ve hit with it. (http://shellyourexperience.wordpress.com/2011/08/24/this-community-rocks-peformance-counters-module-updated/)
At one point I tweeted “Drip … drip …. drip … Woooosssh, Powershell entering my ram”.
This is the kind of experience I had, seeing the opportunities Powershell can deliver.
You’re holding the ball… What are you going to do?
This heading “You’re holding the ball” may confuse many of you, readers. Still, I prefer to use it because it draws a parallel with exploring new stuff. The phrase comes from ball sports and means if you have the ball you’re supposed to pass it on at a certain point in time. Here is my hand over!
Like many SQLServer admins, I have built my own statistics repository, which is being fed on a weekly basis, pulling sheer numbers from all my SQLServer instances. Sizes, number of rows, files, fragmentation, you know the drill. Before Powershell I just generated my SQLCMD commands batch to run my desired scripts and dump the results in a set of text files.
The next quest was to avoid having to generate all this – and copy/paste incomplete stuff – so I wanted to write a function that would run Invoke-SQLCMD2 for a given script against all my instances. And that’s what I wanted to share with you today.
This script pulls all my active SQLServer instances from my inventory database (DDBAStatistics) and pulls all VirtualLogFile information from these instances. Furthermore, it opens an Excel environment and dumps that information in a simple Excel sheet.
Conclusion
Do you remember the days when XML entered our SQLServer lives?
It is absolutely not mandatory for the sysadmin/dba, but if you have a basic knowledge of it, you’ll fairly easily figure out how it can help you in your day to day job, just by diving into it.
Now you see what I mean: Powershell eases your life too! Make some time for it; it is an investment with great ROI! All you have to do at this moment is download this script, walk it over and see how easily Powershell can help you deliver the inventory data of your choice.
You will notice that I've left the origins of parts of my script commented in the script itself. These can hopefully help out with new challenges and quests to go. You will also notice this script is primitive, and the error handling is primitive- but it goes mighty fast!
Once you figure out the strength of the pipe ( | ), and you will quickly learn that development will become way faster than before.
Start procedural, capturing sets of data in a variable (by the way, anything starting with $ is a variable) and process that variable’s data in the next step. Then replace it by pipes to do it all at once. You’ll notice it will become fun to play around with, so make it do your work for you in a way you feel comfortable with.
If you're still not convinced to try it, keep in mind you will be working with objects, not just text data telling you a name, date, or whatever you are looking for. Having the actual object at your disposal means it comes with all kinds of nice properties, attributes, and last but not least, methods that you can consume. That's a very strong point, as you are about to discover!
At the moment, Powershell marketing is evident all over the place, but Powershell is here to stay, and will be used by many applications to actually do the management. It can really make your life much more enjoyable, because you can now use the same scripting language to “connect” to the software domain of your choice and just use it to ease your life.
Embrace it!
Please don't shoot me for sloppy code. It's a test script to get the experience started.
By the time you've read this article, I'm sure I'll have written this script in another way, because of things I will have learned in the meantime.
I would like to thank Chad Miller, Buck Woody and Chris Federico for granting the usage of their code.
NOTE: Be careful NOT to run DBCC Loginfo using a snapshot database on SQLServer 2005 because you’ll hit a bug which will cause the need to restart your SQLinstance because of locking of the log file! AFAIK this bug has not been fixed for SQL2005.
<# ALZDBA dd 20110819 V1.0
Process a given SQL script against a list over servers provided by a DBA statistics server
#>
# MODIFY YOUR PARAMETERVALUES HERE !!!!!
#vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
#Stats server data
$StatsServer="YourInventory\Instance"
$StatsDb="DDBAstatistics"
#minimum SQLServer major version
$MinimumSQLversion=2005
$MaximumSQLversion=2010
#^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
# MODIFY YOUR PARAMETERVALUES HERE !!!!!
# MODIFY YOUR PARAMETERVALUES HERE !!!!!
function Invoke-MyServersSQLScript {
[CmdletBinding()]
param(
[Parameter(Position=0, Mandatory=$true ,ValueFromPipeline = $false)] [string]$iSQLCMD
)
<#
execution example:
Clear-Host
$mySQLFile="\\Uabe0db12\sqlserver$\Administratie\scripts\SQL2005\Overview VLF usage.sql"
$myLogFolder="C:\temp\logs"
try {
Invoke-MyServersSQLScript -iSQLCMD $SQLCMD -iLogFolder $myLogFolder
}
catch {
Write-Host "Something went wrong :$Error[0]"
}
#>
#Init worker vars
$SQLCMD2BeExecuted=$null
$LogFileFolder=$null
# ALZDBA make arguments mandatory
if ( $iSQLCMD.length -eq 0 -or $iSQLCMD -eq ' ' ) {
Write-Warning "You must provide Arguments";
#stop processing here
throw New-Object System.Management.Automation.PipelineStoppedException
}
Else {
# MODIFY YOUR PARAMETERVALUES HERE !!!!!
$SQLCMD2BeExecuted = $iSQLCMD
}
Clear-Host
#clear variables
$dsInstanceList = $null
$Servername_file = $null
$LogFileName = $null
$qry= $('SELECT S.ServerName
, CASE WHEN CHARINDEX(''\'', ServerName) > 0 THEN SUBSTRING(ServerName, CHARINDEX(''\'', ServerName) + 1, DATALENGTH(ServerName))
ELSE ''''
end as Instance
, CASE WHEN CHARINDEX(''\'', ServerName) > 0 THEN SUBSTRING(ServerName, 1, CHARINDEX(''\'', ServerName) - 1) + DNSSuffix + SUBSTRING(ServerName, CHARINDEX(''\'', ServerName), DATALENGTH(ServerName))
ELSE ServerName + DNSSuffix
end FQServerName
FROM V_Server S
where S.DienstStatus in ( ''A'' , ''I'' )
and S.ProductVersion between {0} and {1}
/* and S.ServerName = ''MyDevServer\DEV02'' */
order by S.ServerName ' -f $MinimumSQLversion, $MaximumSQLversion )
$dsInstanceList = Invoke-Sqlcmd2 -ServerInstance $StatsServer -Database $StatsDb -Query $qry -As 'DataTable'
$mytb = New-Object system.Data.DataTable "VLFs"
<# process list of servers #>
foreach($Instance in $dsInstanceList.rows)
{
try {
Write-host $("collecting for {0}" -f $Instance.FQServerName )
$tb = Invoke-Sqlcmd2 -ServerInstance $Instance.FQServerName -Database master -Query $SQLCMD2BeExecuted -As 'DataTable'
if ( $mytb.columns.count -eq 0 ) {
#Copy $tb structure and data
$mytb = $tb.clone()
}
else {
#only copy row content
# based on http://chrisfederico.wordpress.com/2008/06/25/creating-a-datatable-in-powershell/
$tb | % {$row = $mytb.NewRow(); $row.ItemArray = $_.ItemArray;$mytb.Rows.Add($row) }
}
}
catch [System.SystemException] {
write-host $("Something went wrong processing instance {0} - {1}" -f $Instance.FQServerName, $_ )
}
catch {
write-host $("Something went wrong processing instance {0}" -f $Instance.FQServerName )
}
}
Write-Output ( $mytb )
}
#######################
<#
Downloaded from: http://gallery.technet.microsoft.com/ScriptCenter/7985b7ef-ed89-4dfd-b02a-433cc4e30894/
.SYNOPSIS
Runs a T-SQL script.
.DESCRIPTION
Runs a T-SQL script. Invoke-Sqlcmd2 only returns message output, such as the output of PRINT statements when -verbose parameter is specified
.INPUTS
None
You cannot pipe objects to Invoke-Sqlcmd2
.OUTPUTS
System.Data.DataTable
.EXAMPLE
Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "SELECT login_time AS 'StartTime' FROM sysprocesses WHERE spid = 1"
This example connects to a named instance of the Database Engine on a computer and runs a basic T-SQL query.
StartTime
-----------
2010-08-12 21:21:03.593
.EXAMPLE
Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -InputFile "C:\MyFolder\tsqlscript.sql" | Out-File -filePath "C:\MyFolder\tsqlscript.rpt"
This example reads a file containing T-SQL statements, runs the file, and writes the output to another file.
.EXAMPLE
Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "PRINT 'hello world'" -Verbose
This example uses the PowerShell -Verbose parameter to return the message output of the PRINT command.
VERBOSE: hello world
.NOTES
Version History
v1.0 - Chad Miller - Initial release
v1.1 - Chad Miller - Fixed Issue with connection closing
v1.2 - Chad Miller - Added inputfile, SQL auth support, connectiontimeout and output message handling. Updated help documentation
v1.3 - Chad Miller - Added As parameter to control DataSet, DataTable or array of DataRow Output type
#>
function Invoke-Sqlcmd2
{
[CmdletBinding()]
param(
[Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
[Parameter(Position=1, Mandatory=$false)] [string]$Database,
[Parameter(Position=2, Mandatory=$false)] [string]$Query,
[Parameter(Position=3, Mandatory=$false)] [string]$Username,
[Parameter(Position=4, Mandatory=$false)] [string]$Password,
[Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600,
[Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15,
[Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile,
[Parameter(Position=8, Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$As="DataRow"
)
if ($InputFile)
{
$filePath = $(resolve-path $InputFile).ProviderPath
$Query = [System.IO.File]::ReadAllText("$filePath")
}
$conn=new-object System.Data.SqlClient.SQLConnection
if ($Username)
{ $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
else
{ $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }
$conn.ConnectionString=$ConnectionString
#Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
if ($PSBoundParameters.Verbose)
{
$conn.FireInfoMessageEventOnUserErrors=$true
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"}
$conn.add_InfoMessage($handler)
}
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
switch ($As)
{
'DataSet' { Write-Output ($ds) }
'DataTable' { Write-Output ($ds.Tables) }
'DataRow' { Write-Output ($ds.Tables[0]) }
}
} #Invoke-Sqlcmd2
Clear-Host
$MyDoc = [System.Environment]::GetFolderPath("MyDocuments")
$MyMgmtStudioFolder = "$MyDoc\SQL Server Management Studio\Projects"
$LogLocation = "c:\temp\logs"
#The beginning of my POSH script now looked like this:
$myWorkbook="$LogLocation\MyWorkbook.xls"
$myCSV="$LogLocation\MyWorkbook.CSV"
# Number of VLFs to Excel Chart
#Replace ‘servername’ with the name of your target server and the script should connect as long as
#you are running the script with credentials that can access the instance.
#The next part of the script defines the command text and populates a dataset:
$SQLCheckVLF="CREATE TABLE #VLF_temp
(FileID varchar(3), FileSize numeric(20,0),
StartOffset bigint, FSeqNo bigint, Status char(1),
Parity varchar(4), CreateLSN numeric(25,0))
CREATE TABLE #VLF_db_total_temp
(name sysname, vlf_count int)
DECLARE db_cursor CURSOR
READ_ONLY
FOR SELECT name FROM master.dbo.sysdatabases
DECLARE @name sysname, @stmt varchar(40)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
INSERT INTO #VLF_temp
EXEC ('DBCC LOGINFO ([' + @name + ']) WITH NO_INFOMSGS')
INSERT INTO #VLF_db_total_temp
SELECT @name, COUNT(*) FROM #VLF_temp
TRUNCATE TABLE #VLF_temp
END
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT TOP 10
@@servername as [ServerName], name as [DBName], vlf_count as [VLFCount]
FROM #VLF_db_total_temp
WHERE vlf_count > 50
ORDER BY vlf_count DESC
DROP TABLE [#VLF_temp]
DROP TABLE [#VLF_db_total_temp]
"
write-host "Connecting to server and collecting VLF data"
$tbVLFList = Invoke-MyServersSQLScript -iSQLcmd $SQLCheckVLF
write-host "Collect finished"
#$tbVLFList.Count
function Use-Culture {
param(
[System.Globalization.CultureInfo]
[Parameter(Mandatory=$true)] $culture,
[ScriptBlock] [Parameter(Mandatory=$true)] $code
)
trap {
[System.Threading.Thread]::CurrentThread.CurrentCulture = $currentCulture
}
$currentCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture
[System.Threading.Thread]::CurrentThread.CurrentCulture = $culture
Invoke-Command $code
[System.Threading.Thread]::CurrentThread.CurrentCulture = $currentCulture
}
#Export-Csv -InputObject $dsVLFList -path "C:\MyFile.Csv"
Use-Culture en-us {
$excel = new-object -comobject excel.application
$excel.visible = $true
$chartType = "microsoft.office.interop.excel.xlChartType" -as [type]
$workbook = $excel.workbooks.add()
$workbook.WorkSheets.item(1).Name = "Check_VLF"
$sheet = $workbook.WorkSheets.Item("Check_VLF")
#initialize headers
$x = 1
$sheet.cells.item($x,1) = "ServerName"
$sheet.cells.item($x,2) = "DBName"
$sheet.cells.item($x,3) = "VLFCount"
$x++
#fill data
#export the table rows to an excel sheet (in csv format)
For($i=0; $i -lt $tbVLFList.count;$i++ ) {
$sheet.cells.item($x,1) = $tbVLFList[$i].ServerName
$sheet.cells.item($x,2) = $tbVLFList[$i].DBName
$sheet.cells.item($x,3) = $tbVLFList[$i].VLFCount
$x++
}
$range = $sheet.usedRange
$range.EntireColumn.AutoFit()
}
Things I loaded to my Powershell environment at startup :
get-pssnapin –registered
Name : SqlServerCmdletSnapin100
PSVersion : 1.0
Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.
Name : SqlServerProviderSnapin100
PSVersion : 1.0
Description : SQL Server Provider
