SQLvariations: SQL Server, a little PowerShell, maybe some Hyper-V Rotating Header Image

SQL University

PowerShell Week at SQL University – Post 7

Apr 25th, 2011
by Aaron Nelson.
Comments are off for this post

Deploying Code

Deploying SQL code with PowerShell can be very easy but why would you want to do that instead of just open a script in SQL Management Studio and run it?  The number 1 reason that I can think of is when you need to deploy to multiple destinations.  Let’s get started with something simple, we’ll deploy a table that I use in the post on collecting database sizes.

Invoke-Sqlcmd -ServerInstance Win7Netbook -Database CentralInfo -InputFile C:\temp\dbo.DatabaseFileSizes.SQL

That’s a pretty simple example and one that doesn’t offer too much any benefit over just opening up SSMS and running the script.   Let’s take it a little further with the loop construct and this time we’ll deploy Adam Machanic’s ( blog | twitter ) sp_WhoIsActive to a bunch of machines

$DeployFile = C:\temp\who_is_active_v11_00.sql
<# Loops through Registered SQL Severs and applys WhoIsActive #>
foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\Development\ | where {$_.Mode -ne "d"} )
{
"Deploying to "+ $RegisteredSQLs.ServerName;
Invoke-Sqlcmd -InputFile $DeployFile -ServerInstance $RegisteredSQLs.ServerName -database master}

So now we can see how we can deploy a SQL Script to multiple machines with just 4 lines of PowerShell.  The script obviously doesn’t have to be deploying code.  It could inserting/updating data or even verifying permissions.

PowerShell Week at SQL University – Post 6

Jan 20th, 2011
by Aaron Nelson.
1 comment

Yesterday we went over some loop constructs to get information out of SQL Server.  While getting the information out is great, as Data Professionals our very next concern is going to be: “ok, where do I put this so that I can refer to it later?”

Today we’re going to talk about formatting and storing our results.  Our storage mechanisms are going to be a simple flat file, CSV, and the obvious one, a table.  This post is a little long but it walks you through how to overcome some of the frustrations when I started working with PowerShell over a year ago.  Enough preamble, let’s get started!

Output on the screen:

Start from the beginning by running this little command:

Invoke-Sqlcmd -Query ‘SELECT * FROM sysfiles’ -Database MyBigFreakinDatabase -ServerInstance Win7NetBookNow depending on how wide your PowerShell window is open to right now PowerShell will format the data to spool out in the results pain the way it thinks is best.  It may have come out ‘one row at a time’:

Invoke-Sqlcmd -Query ‘SELECT * FROM sysfiles’ -Database MyBigFreakinDatabase -ServerInstance Win7NetBook | Format-ListOr it may have come out like this:

Invoke-Sqlcmd -Query ‘SELECT * FROM sysfiles’ -Database MyBigFreakinDatabase -ServerInstance Win7NetBook | Format-Table” so you’ll probably want to get into the habit of putting a “ | Format-Table”  after the end of you queries.  Or if you’re lazy like me:  | FT.

When we want to run this cmd (or whatever query you really want to run) against multiple databases we’re going to want to know what database that the data came out of.  We might write something like this

foreach ($db in invoke-sqlcmd -query " SELECT name FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook )
{
Invoke-Sqlcmd -Query ' SELECT * FROM sysfiles'  -ServerInstance  Win7NetBook -Database $db.name | Format-Table
}

But the problem with that is We don’t know for sure which database each file came out of.  I mean if we have logical naming we can guess but that’s all it is, a guess.  What we can do is add our little “$db.name” that we’re using to pass in the name of our database.

foreach ($db in invoke-sqlcmd -query " SELECT name FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook )
{$db.name;
Invoke-Sqlcmd -Query ' SELECT * FROM sysfiles'  -ServerInstance  Win7NetBook -Database $db.name | Format-Table
}

Object Sidebar: Probably the most important thing for SQL people to know about this language!

Believer it or not we’re using object oriented code right now.  In this example we’re pumping a list of databases as objects into a variable and then iterating over that list.  If we examine our object by piping it over to Get-Member “$db | Get-Member” we’ll see that it only has one property but if we had selected more columns it would have had more properties.  Typically objects have more than one property so that’s why we need to add the .name to our $db variable so that we only get the name property.

Outputting to a text file:

In the PowerShell language to output to a text file we can just use “>” and then give it a filename.  Since we’re inside of a loop we would just keep overwriting that file until we got down to the last database and all that would be in the file would be the info from that last database.  We can also use “>>” which allows us to append to a file like this:  >> C:\temp\MyDatabaseFiles_20110120.txt

Side Note: In addition to the “>>” we could have also used Out-File –FilePath C:\temp\MyDatabaseFiles_20110120.txt –Append with much the same results.  To make a long story short, use Out-File instead of >> whenever possible.

foreach ($db in invoke-sqlcmd -query " SELECT name FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook )
{$db.name;
Invoke-Sqlcmd -ServerInstance  Win7NetBook -Database $db.name -Query ' SELECT * FROM sysfiles' | Format-Table >> C:\temp\MyDatabaseFiles_20110120.txt
}

That’s not what we expected was it?

There are two main problems with the output when we look at  MyDatabaseFiles_20110120.txt.  First, those database names are missing.  They’re missing because they weren’t in the pipeline in the first place.  Each time that we looped we were executing two different statements not just one; and the database name was only present in the first.  We could fix that by making the first line inside of the loop look like this $db.name >> C:\temp\MyDatabaseFiles_20110120.txt; but we won’t, at least not this time.

The second problem is that unless we have the editor open on a really, really wide monitor the output was likely truncated with some “…” at the end.  Now if we changed the output mode Format-List we might avoid that truncation problem but as database professionals “might” rarely cuts it.

Using Export-CSV to save off our results:

Funny story about Export-CSV later…

We’ll fire up a new variable called $MyResults and push our results into it each time that we pass through the loop (sorta~like a temp table but don’t make too strong of a connection to that).  After we complete the loop we will take the results and pass them down the pipeline to the Export-CSV cmdlet.  When we open that file we’ll see nice clean data output that we can save.

foreach ($db in Invoke-Sqlcmd -query " SELECT name FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook )
{$db.name;
$MyResults += Invoke-Sqlcmd -Query " SELECT DB_NAME(db_id()) AS 'DatabaseName', * FROM sysfiles"  -ServerInstance  Win7NetBook -Database $db.name
}            

$MyResults | Export-CSV -Path C:\temp\MyDatabaseFiles_20110120.csv -NoTypeInformation

Output to a table:

I’ve talked about how to save rows to a table a couple of times before.  Do you remember that Wait Stats survey that Paul Randal ( blog | twitter ) did a little while ago?  Well, we have several hundred SQL instances where I work and I wanted to be able to send Paul results from a large number of those servers. The thing is I didn’t want to have to actually login to each instance, run the query, save off the results, then repeat over and over again.  So I came up with this post.  You can do the same to gather info multiple instances/databases in your environment.  It’s pretty straight-forward and very similar to the CSV example above but with a small pair of changes.  We need the two scripts that I mentioned in the other posts:  Chad Miller’s (Blog|Twitter)  invoke-sqlcmd2 and Write-DataTable functions.  Once we have those loaded (and hopefully their in our profile by now Winking smile) we’re off to the races being able to write information from multiple databases back into a single database.

foreach ($db in invoke-sqlcmd -query " SELECT name FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook)
{
$dt=invoke-sqlcmd2 -Query " SELECT DB_NAME(db_id()) AS 'DatabaseName', * FROM sysfiles" -ServerInstance  Win7NetBook -Database $db.name -As 'DataTable'
Write-DataTable -ServerInstance Win7NetBook -Database CentralInfo -TableName dbFileName -Data $dt
}

Here’s the SQL code to create that table so that you can test it out:

CREATE TABLE [dbo].[dbFileName](
[DatabaseName] [nvarchar](128) NULL,
[fileid] [smallint] NULL,
[groupid] [smallint] NULL,
[size] [int] NOT NULL,
[maxsize] [int] NOT NULL,
[growth] [int] NOT NULL,
[status] [int] NULL,
[perf] [int] NULL,
[name] [sysname] NOT NULL,
[filename] [nvarchar](260) NOT NULL
) ON [PRIMARY]

Output to Email:

Call me lazy but…

I put this next script together just to see if it could be done.  Going back to that Wait Stats Survey, if you read all the way to the end you’ll see I managed to get the collection process down to 3 lines.  The only thing is that you still had to do something to the result set to exclude your instance names for security, and then after that you still had to do the whole copy-paste thing.

To combat all that wasted time I came up with this script which will go ahead and enumerate the instances for you.  That way if for any reason Paul emailed you back and said ‘Holy Crap, how the hell did you manage to get that as your highest wait on Instnce #14?’ you’d still be able to go back and figure out which server he was talking about.  Also, If you would still have the results around in case you wanted to be proactive and take a look yourself (but let’s not get too carried away).

foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\ | where {$_.Mode -ne "d"} )
{
$dt=invoke-sqlcmd2 -ServerInstance $RegisteredSQLs.ServerName -database master -InputFile ./PaulAndGlensWaitQuery.sql -As 'DataTable'
Write-DataTable -ServerInstance "Win7NetBook" -Database CentralInfo -TableName TopWaitTypes -Data $dt
}            

$MultipleResults = Invoke-Sqlcmd2 -ServerInstance "Win7NetBook" -Database CentralInfo -Query "SELECT DENSE_RANK() OVER (ORDER BY [InstanceName]) AS 'SQLInstance' ,[WaitType] ,[Wait_S] ,[Resource_S] ,[Signal_S] ,[WaitCount] ,[Percentage] FROM [CentralInfo].[dbo].[TopWaitTypes] ORDER BY SQLInstance, Percentage" | ConvertTo-Html -Property SQLInstance, WaitType, Wait_S, Resource_S, Signal_S, WaitCount, Percentage | Out-String;
Send-MailMessage -To paul@SQLskills.com -Subject "Wait Stats Query Results" –From YourEmailAddress@GoesHere.com -SmtpServer smtp.SQLvariant.com -Body $MultipleResults -BodyAsHtml

Finally, you know we could put that SELECT statement into a .sql file.  That would cut it back down to ~6 lines.  We might even be able to wrap the email portion into something a little smaller but considering all that this script does I think this is good enough.

It’s Later

When I first tried to do something like this I was was shocked to find out that there is no –Append switch for Export-CSV.  I wasn’t very happy about that, I mean I couldn’t wrap my head around how you could leave something like that out.  This ranked right up there with finding out Utility Control Point only worked for managing other SQL 2008 R2 instances.  Luckily my story ended much better Tom’s.  First I reached out to ScriptingGuys ( blog | twitter ) to make sure that I wasn’t crazy.  Then I did all I could do, sulked and filed a Connect Item.  The following day I woke up to the sun was shining, the birds chirping, and a tweet from Dmitry freaking Sotnikov himself ( blog | twitter ) telling me that he had gone ahead and fixed that for me by building a proxy command.  (It’s a function and since functions out rank cmdlets in call order anyone can augment the PowerShell language by adding a proxy command.)

…  and THAT is why I love PowerShell.

Homework

Use the 3 techniques we walked through to run sp_configure against as many instances as possible.  Save it to a text file, CSV, and a table.  You might even want to add a date column or something.  You could also try email the results to yourself.

Do Not spam Paul Randal!  I cleared the email script with Paul before I included it.  If you want to send Paul the wait stats results from your servers I recommend that you test it first by emailing it to yourself.  If it looks good you go ahead and send it but make sure to include your email address in the –From parameter.

PowerShell Week at SQL University – Post 5

Jan 19th, 2011
by Aaron Nelson.
8 comments

In the previous posts we’ve just been poking around with PowerShell and trying to make the examples something that actually means something to a SQL person whenever we ca.  There are quite a few more language constructs that we need to cover but we have enough info to start recouping the time we’ve already invested.  Now it’s time to do one of those tasks that I just love to do with PowerShell.  We’re going to loop.  And we’re going to loop in a way that’s far easier than in any part of the SQL language.

We’re going to cover 3 different sources for our loop that are the most common for DBAs to use: table, text file, Registered Server/Central Management Server.  After that we’re going to do a double loop and then it’s time for you to find something to do with these.

Text File

In this chunk of code we’re just going to read from a simple text file from our local hard drive and then loop through the instances in that file one at a time.  You put one instance on each line of the text file.  Don’t put it in quotes unless you’re using a non-standard port number.  This is the easiest method because each “row” that comes out of the text file only has one property.  We’ll find out why that’s important in the next example.  In the meantime setup your text file and test it by running just this: Get-Content C:\PowerShell\AllInstances.txt.

foreach ($Instance in Get-Content C:\PowerShell\AllInstances.txt)
{
$Instance;
Invoke-Sqlcmd -ServerInstance $Instance -Database master -Query "SELECT  object_name ,
        counter_name ,
        instance_name ,
        cntr_value ,
        cntr_type
  FROM sys.dm_os_performance_counters"
}

 

Database Table/ Query

In this example we could be reading rows out of a table or running a more complex query to determine the list of databases that we want to run out query against.  This may actually seem easier to most database people and it is.  We’ve got a centrally located table and we can just look at it and know that we can change the first query to select a list of databases from somewhere else.  But there’s a really important thing to know if you swap out the query.   This approach is sending one usable property (column) to the foreach loop and it’s called “name”.  If you change the query and the column ends up being called database_name, you’re going to have to change $($db.name) to be called $($db.database_name).  Otherwise you’re going to loose a lot of hair and get really ticked after about 20 minutes like I did!

foreach ($db in invoke-sqlcmd -query "SELECT name  FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook )
{$db.name;
Invoke-sqlcmd -Query 'SELECT *
  FROM sys.dm_exec_procedure_stats' -ServerInstance  Win7NetBook -Database $($db.name)
}

 

Registered Servers/ Central Management Server

Before you get started looping through your Registered Servers you’ll need to run this: Import-Module Agent if you want to do this exact example.  What we’re looking for here is all the jobs that have failed in the last 3 days in our “QA” group of servers.  This example should be easy enough for everyone to tweak on their own.  If you get stuck just remember to do Get-Help -Full Set-AgentJobHistoryFilter
(Huge thanks to Chad Miller for helping me put together this demo so that it would be a fast one to run!)

$filter = Set-AgentJobHistoryFilter -startDate $(get-date).AddDays(-3) -endDate $(get-date) -outcome 'Failed'

foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\QA\ | where {$_.Mode -ne "d"} )
{
Get-AgentJobHistory $RegisteredSQLs.ServerName $filter | where-object {$_.StepID -ne 0}
}

 

Double Loop

This has to be one of my favorite PowerShell scripts of all time (so far).  I had to run a query against every database in a group of over 10 servers.  I’ve changed this one around a little but I’m sure you’ll find a use for it!  (Think permissions.)

foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\QA\ | where {$_.Mode -ne "d"} )
{
    foreach ($DBName in invoke-sqlcmd -query "SELECT name
  FROM sys.databases WHERE name in ('AdventureWorks',
            'AdventureWorks2008',
            'AdventureWorks2008R2',
            'AdventureWorksDW'
            ) " -database master -serverinstance $RegisteredSQLs.ServerName )
            {
                    invoke-sqlcmd -query 'SELECT *
        FROM sys.dm_db_index_usage_stats' -ServerInstance $RegisteredSQLs.ServerName -database $DBName.name
            } #EndOfTheFoundDatabasesLoop
} #EndOfTheRegisteredServerLoop

 

Homework

Try out each of these methods and think up something you could use this for.  If you hit on something that save you some clicking around in SQL Management Studio please mention it in the comments.

PowerShell Week at SQL University – Post 4

Jan 18th, 2011
by Aaron Nelson.
4 comments

Recap

So far we’ve walked though how to turn on PowerShell and add modules, adding SQL Server 2008 snapins and using PowerShell variables, what cmdlets and functions are, what providers are and how to work with methods & properties.

Profiles

PowerShellProfilesToday we focus on load things in our profile so that they are always available in PowerShell.  First, some basics:

What is a Profile?  A profile is nothing more than a PowerShell script that gets run as soon as you open up a given PowerShell window.

Why would we want to do this?  I use PowerShell almost exclusively for automating work with my SQL Servers.  When I’m ready to do something I don’t want to start by reloading the same toolset every time I open it. I want to click the icon and be ready to go.  The profile saves me from this repetitive loading.  I update my profile as I find cool new scripts that I want to add.

You are not limited to using a single profile in PowerShell.  Personally, I use one of three profiles depending on the task at hand.  Here is how that works. I use the PowerShell ISE or other third party products as my primary way of writing PowerShell scripts (.ps1) and rarely use the “console.”   I only use the console if I need to troubleshoot something that might have to do with STA/MTA. 

When I open up the PowerShell ISE it’s uses it’s own profile CurrentUserCurrentHost that is stored in C:\Users\USERNAME\Documents\WindowsPowerShell\Microsoft.PowerShellISE_profile.ps1 (picture-right).  Inside of that file on my machine it currently looks like this:

import-module ISEPack
cd C:\Users\Aaron\Documents\PoSh\Load
. ./invoke-sqlcmd2.ps1
. ./Write-DataTable.ps1
. ./Out-DataTable.ps1
. ./Add-SqlTable.ps1
. ./Export-CSV-Append.ps1
. ./invoke-sqlcommand.ps1
. C:\Users\Aaron\Documents\PoSh\Tasks\Get-DisksSpace.ps1
cd c:\temp\

Whatever is in this file only gets loaded into my session when I open the ISE.  Nothing above gets run when I open up the console.  These are items I use in ISE, but not in the console PowerShell window.

There are items I use in both ISE and the console.  These items live in a “super profile” of sorts since I want these items no matter how I access PowerShell CurrentUserAllHosts (which is located in C:\Users\USERNAME\Documents\WindowsPowerShell\profile.ps1). Profile.ps1 (picture-center) gets run every time I open up any PowerShell “host” including 3rd party tools.  This also includes the SQLPS host which may cause errors messages to appear when you open it or run a SQL Agent Job.  These error messages don’t impact a PowerShell step in a SQL Agent Job from running.

Here’s what I have inside of my Profile.ps1 file.

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Import-Module SQLServer

Whenever I open the ISE both the Microsoft.PowerShellISE_profile.ps1 and the Profile.ps1 scripts get run.

PowerShell.exe (the little blue “host”) has it’s very own CurrentUserCurrentHost profile that it loads.  That’s the exact same name as the profile that we talked about the ISE loading so does that mean that it’s loading that same file?  No.  Each one is configured to load it’s own CurrentUserCurrentHost from a different location.  So if there are items I want in console world only I have a place to store them thanks to C:\Users\USERNAME\Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1.  Since I rarely use the console my profile there sat empty until recently.  I only added items after I spotted a post by Oisin Grehan ( blog | twitter ) on Implementing a Matrix-Style Console Screen Saver and I HAD to try it.  This only works in the console and not in the ISE so it made my console profile the perfect place for implementing this.  Now my PowerShell_profile.ps1 file (picture-left) looks like this:

Import-Module screensaver
set-screensavertimeout (new-timespan -minutes 5)
enable-screensaver
Start-ScreenSaver

To edit one of the profiles that only load for the console or the ISE all you have to do is open them up and run notepad $profile.  It will figure out which file is the right one for that environment and open it for you.  To edit the profile that is run in every PowerShell environment just run notepad $profile.CurrentUserAllHosts.  If your WindowsPowerShell directory isn’t there it will not automatically create that for you and you will get an error message stating that it “can’t find the specified path“.  I’ve written a little script to check for that and create one for you if it’s not already there:

$myPowerShell = $HOME
$myPowerShell +="\Documents\WindowsPowerShell"
if ((Test-Path $myPowerShell) -eq "true") {"$myPowerShell is already there mate"}
else { "you need to make one"
mkdir $myPowerShell  }

Homework Assignment:

Setup one of your profiles to at least have one of the modules from the SQLPSX project and the SQL Server Snapins loaded up every time you start up the PowerShell ISE. Which profile to use is completely up to you.  Feel free to play around with your choices.  Optionally you can also include the Get-DisksSpace function like I do; but you don’t have to.

Let me know how this goes for you by dropping a note it the comments.  In tomorrow’s post we are going to work with multiple SQL Servers so you’ll need these things up and running for that.

If you’re confused by where your files are you can just run this script that I lifted off of the HeyScriptingGuys site to have it tell you where all of the profiles for your session have been loaded from.  Keep in mind that we’re not touching the ones in the Windows directory.

$profile |

Get-Member -MemberType note* |

Format-Table -Wrap -AutoSize -Property name,

@{ Label=“Path”; Expression={$_.definition -replace “System.String”,“”} }

PowerShellProfiles

In Case you would like more to play with…

There is a lot more that can be done with PowerShell Profiles but these are the basics you need to get up and running. If you want to dive deeper check out these posts over on the Hey Scripting Guy Blog.  Go here to download the SQLServer or ISEPack modules; they are both part of larger projects.  If you’d like some of the scripts that I reference in my profile you can grab the Get-DisksSpace one here and I’ll be blogging about the rest later this week.

Be Advised:

Everything that you put into you profile eats up RAM.  Thankfully most scripts are only a few kilobytes.  Keep in mind that the more items you add into your profile the longer that it is going to take to load.

imageIn case you’re wondering if that “Matrix Style Console Screen Saver” is worth having around, here is what it looks like after a few minutes:

That’s all that I’ve got for you today, see you back tomorrow!

PowerShell Week at SQL University – Post 3: Providers, PSDrive

May 20th, 2010
by Aaron Nelson.
5 comments

Providers

What are Providers? Providers in PowerShell are a hierarchical namespace way flattening out an infrastructure to make it accessible and traverse-able just like a directory structure hard drive. Why do you need them? Well you quite possibly may not ‘need’ them per-say but they sure do make things easier to work with from time to time. In case you don’t already know, you can traverse your SQL Server [2008] as if it were just another drive in either SSMS 2008 or in the PowerShell ISE if you add the Provider Snapin.

To make sure that you have this snapin installed on your machine see yesterday’s post but this time we are going to add the ProviderSnapin instead of the CmdletSnapin.

image

To do that just run

add-pssnapin SqlServerProviderSnapin100

and poof you can traverse SQL Server. Let’s take a look at what we might see. Run

get-psdrive

and you should see a list of drives including SQL Server. Now if you have more that one instance you will be able to access all of them through this one “SQLServer:\” PSDrive that you now have. In a lot cases what we find inside of these drives will be similar to what we see in the Object Explorer view in SSMS but almost always have something extra too. Let’s take a look at that here by changing directories down to our databases

cd SQLSERVER:\sql\YourComputerName\YourInstanceName\

cd SQLSERVER:\sql\WIN7\KILIMANJARO\

Now let’s go ahead and run the good old “Dir” command and we should get back something like this

dir

image

As I mentioned before, we got back more than we see if we expand our SQL Server node in the Object Explorer window of SSMS, now if we CD to Databases and do another dir we will see not just more than what we see in SSMS, we see what at first glace to a SQL person is just garbage (but it’s not).

cd Databases
dir

image

What you’re seeing here is the Methods and Properties of the databases in your instance. What are Methods and Properties? Well if you’re like I was 6 months ago you have no idea what Methods and Properties are. Methods are the Verbs of what you can do to your database (Create, Rename, Drop, Shrink) and Properties and the Adjectives that describe your database (Collation, CreateDate, Compatibility Level, Owner, AutoShrink Enabled). To get just the list of the names of your databases you’re going to have to run this:

dir | select-object name

image

Ok so now we can see a list of databases that our Provider made available to us. So what? Well I’m going to dive a little deeper tomorrow but for now let’s just go with one final example on scripting out tables (more on that here). Run this: cd AdventureWorks\Tables Then this:

foreach ($tbl in dir )

{
$k="C:\Temp\" + $($tbl.name) + "_table.SQL"
$tbl.Script() > $k
}

I really hope you have temp directory on your C:\ drive. If so go have a look what’s there now :-)

So is that it? Almost. While SQL Server 2008 only comes with one provider and it’s for the Relational Engine a few people have taken it upon themselves to create their own Provider for things like Analysis Services, Reporting Services, and event BizTalk up on the codeplex site. I can say that I have tried the Analysis Services project and it does work.

PowerShell Week at SQL University – Post 2

May 19th, 2010
by Aaron Nelson.
5 comments

Cmdlets, Functions and Snapins

So far we’ve learned how to open up PowerShell and add on to it with some modules; then we learned how to create a variable and populate it with anything from some numbers to an entire table. Today we’re going to quickly look at another way of expanding PowerShell with something called a snapin so that we can get to some cmdlets.

First, what are cmdlets? Cmdlets are similar to DOS commands but have some noteworthy differences. In SQL Server terms you might think of cmdlets as the System Stored Procedures of PowerShell and think of functions as the regular user defined Stored Procedures of PowerShell.

Cmdlets and functions accept parameters just like stored procedures do. Just like stored procedures you can even define default values for these parameters in case the user doesn’t have one to pass in. Unlike stored procedures though there is actually a drive that you can go to and see all of your functions. Just run this and you’ll see what I mean dir function:

Snapins are akin to finished product code. They were created for PowerShell 1.0 and were succeeded in PowerShell 2.0 by modules– which are more like the manifest list for deploying a bunch of SSIS packages. If you have SSMS 2008 installed you’ve got yourself two extra snapins already. And if you have Idera’s SQL Diagnostic Manager installed you may be in for a real surprise when you retrieve your list of snapins. Run this command to see all of the extra snapins that are available to you:

get-pssnapin –registered

At the very least you should see this:

image

If you don’t see those two you’ll need to install SSMS 2008. If you don’t have a copy of that handy you can get the free version of it here. The SqlServerCmdletSnapin100 snapin give us the 5 cmdlets that come with SQL Server 2008 and today we’re going to focus on Invoke-SQLcmd which basically just calls out to good old sqlcmd. To load this just run:

add-pssnapin SqlServerCmdletSnapin100

From there we can do a quick query like this:

invoke-sqlcmd -query “sp_databases” -database master -serverinstance YourServerName\YourInstanceName | format-table

invoke-sqlcmd -query "sp_databases" -database master -serverinstance WIN7\Kilimanjaro | format-table
image

Now the reason that I brought up functions is because they override cmdlets and that’s really important to know. If you run this command get-command invoke-sqlcmd you will only see one thing returned for invoke-sqlcmd. However if we create a new function like:

function invoke-sqlcmd { get-date }

You end up overriding the Invoke-SQLcmd with your new function. Give it a try, run just this invoke-sqlcmd and you should end up seeing something like this:

image

So how do you know when you’ve done something like that? Just run get-command invoke-sqlcmd again and this time you will see this:

image

I realize that we kind of rushed through that info but thankfully there is only one more piece to the basics before we can get on to code that you can use on a daily basis :-)

PowerShell Week at SQL University – Post 1

May 18th, 2010
by Aaron Nelson.
4 comments

Getting Started With PowerShell Variables

Yesterday we laid the ground work for PowerShell Week.  Today we will learn about Variables in PowerShell. You will notice that PowerShell variables work very differently from SQL Server variables.

The evolution of variables in SQL Server is pretty straight forward. In SQL Server 2000 you had to DECLARE a variable as a specific type in one statement and then SET the value of the variable in another statement, like this: DECLARE @SQLvariable VARCHAR(12) SET @SQLvariable = ‘OldFeature’. By the time SQL 2008 hit the streets we could DECLARE and SET a variable in one statement like this: DECLARE @SQLvariable VARCHAR(12) = ‘NewFeature’ . PowerShell evolves variables to the next logical step.  You no longer need to declare or type your variable. Instead PowerShell uses the value of the variable to infer it’s type. If a variable does not alreayd exist PowerShell simply creates it for you.  In PowerShell our statement looks like this: $SQLvariable = ‘NewFeature’. (Beginer tip: $ identifies variables in PowerShell just like @ identifies them in SQL Server.)

Since it doesn’t work in SQL let’s try it in PowerShell: $SQLvariable = ‘NewFeature’.  OK so we loaded data into a variable, ‘big deal’ right?  How do we know that we actually loaded it and more importantly what datatype is it?  Well in PowerShell you can simply call the variable to get its value. (Another beginer tip: you don’t need to use a keyword like SELECT for this, just the variable name: $SQLvariable.)  What if you just want to determine the datatype?  All you have to do is tack on the .GetType() Method: $SQLvariable.GetType().

$SQLvariable = 'NewFeature'
$SQLvariable
$SQLvariable.GetType()

When you run this here’s what your result will be:

image

As I alluded to earlier, another feature of PowerShell is that it remembers your variables for you, even between executions.  Basically as long as you don’t end your session the variable, it’s datatype, and its value will persist.  You will see later than you can also set a variable in one script and consume it in another.

Let’s try this again with a new variable and set it to a different value:

$PoShvariable = 123
$PoShvariable
$PoShvariable.GetType()

image

What happens if once the datatype is set we try to set it to a different, incompatible datatype?  It’s going to explode right?  Nope, you’ll have to throw something more difficult than that at it.  Give this a try:

$PoShvariable.GetType()
$PoShvariable = 'NewFeature'
$PoShvariable
$PoShvariable.GetType()

 

 image 

Since PowerShell relies on .NET a variable could be any datatype in .NET so if you’re sitting inside the AdventureWorks database you can see for yourself, just run this:

$MyTable = get-item TABLESProduction.TransactionHistory
$MyTable.GetType()            

$MyStoredProc = get-item StoredProceduresHumanResources.uspUpdateEmployeePersonalInfo
$MyStoredProc.GetType()
image

Now earlier I mentioned that PowerShell keeps track of and remembers the variables that you previously declared.  So how do you see what variables are already there?  Just run this command and you can see every variable you have running in the session, even the internal ones.

Variable

In the list of items that get returned you’ll see one called Error that holds the last error message that occurred in your session.  This handy feature can help you when you have to debug your own work.

Is that all there is to know about variables in PowerShell?  Not hardly, but I think that’s a good stopping point for this post.  More on variables later.

PowerShell Week at SQL University – Post 0

May 17th, 2010
by Aaron Nelson.
10 comments

Welcome to PowerShell Week at SQL University. For regulars at SQLvariant, SQL University is something Jorge Segarra organized to get industry experts together to create learning courses around SQL Server. These learning courses are in one week segments that focus on a specific area.

When I began blogging about PowerShell there were some complications based on your operating system. There were so many people blogging about these issues that I didn’t address it initially. Since this is an introductory course I decided to go ahead and spin up a brand new Windows 7 VM so that I can walk you through these small but vital details. If you don’t have Windows 7 this would be a great time to upgrade. If you can’t upgrade then download PowerShell for you operating system from this link. Now, onto getting started:

Disclaimer:
PowerShell opens in a protected state as a security measure. To enjoy the full power of PowerShell (and run scripts you downloaded off of the internet) you need to run without these safeguards. In today’s lesson we are simply launching PowerShell and priming it for the scripts and work we will interact with in the next several lessons.

To get started with PowerShell just click the Start button and type in ISE to locate the Integrated Script Editor in your program listing. Right click and launch PowerShell in Run as Adminsitrator mode:

image

Enter Get-ExecutionPolicy as a command. If you haven’t used PowerShell yet you will most likely see that the Execution Policy is set to “Restricted”. You’ll need to run the command Set-ExecutionPolicy RemoteSigned to be able to run the scripts and follow along with the lesson. You’ll also get a popup that you’ll have to say yes to.

image

Your output should look very non-informative like this:

image

Now that we have that set let’s go to SQLPSX.CodePlex.com and and download the SQLPSX project. It’s currently in version 2.2.1 and consists of 9 modules containing 133 advanced functions, only 2 cmdlets and 7 scripts. It’s for working with ADO.NET, SMO, SQL Agent, RMO, SSIS, SQL script files and using the PowerShell ISE as a SQL query tool:

NOTE: Update at bottom with workaround:

image

During the installation we will run it without modifying the profile (just yet).

image

Once you’ve installed it successfully you should now have a WindowsPowerShell directory under your My Documents if you didn’t have one already (which is really important):

image

After it’s installed we should be able to open up the ISE again and run this command :

Get-Module -ListAvailable

image

And finally we’re going to get to the step where we load something up. Run import-module SQLServer to load up the main SQL Server module. After you run that command it won’t give you any output telling you that it successfully loaded so you can run this command write-host -foregroundcolor DarkGreen “Modules Loaded: $(get-module)” to have it tell you what Modules it currently has loaded.

image

Alright that pretty much wraps it up for today. I know we didn’t do a lot with PowerShell and SQL Server yet but this gets some important ground work out of the way so that hopefully everyone is able to script along for the rest of the week. (If you’re dying to read ahead have a look at this post: Basic Querying From PowerShell)

If you run into any issues please leave a comment and I’ll be glad to lend any help that I can. See all you back here tomorrow for the next post in this series Post 1.

UPDATE:

There seems to be an issue with loading the SQLServer Module as I had described. I have found this to be a work-around: Go to \\~\My Documents\WindowsPowerShell\Modules\SQLIse\Modules\ and copy everything except for the SQLISE folder, then go to the \\~\My Documents\WindowsPowerShell\Modules\ folder and paste all of those items. At this point you should now be able to run the command and get it to load up properly.

I will post more when I found out other alternatives.