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

PowerShell

PowerShell Presentation at SQLskills open-mic night

Dec 9th, 2011
by Aaron Nelson.
1 comment

Kimberly Tripp ( blog | twitter ) and Paul Randal ( blog | twitter ) SQLsklls are here in Atlanta this week.  Last night they held an ‘open mic’ night and allowed anyone who wanted to present a topic 15 minutes to show something to the rest of the class.  Surprise, surprise I decided to talk about PowerShell.

I received multiple requests to post the scripts I used before I even had a chance to sit down so here they are.

If you were in the class and would like to use these and don’t even know where to start with PowerShell have a look at my “Resources for my SQL Server PowerShell Extensions webcast” post.

If you ever get a chance to attend one of their classes and present in front of Paul and Kim TAKE IT! :-)   Even if it’s just about your SSMS tips or how to work around a problem you encountered.  The feedback you get is *more* than worth it!

Quick Blog: Grabbing basic machine info with PowerShell

Nov 29th, 2011
by Aaron Nelson.
2 comments

The other day I needed to track down how much RAM a couple of our servers had installed.  A few days later I needed to verify that a couple of them were in fact 64-bit and not 32-bit.  I decided I wanted to be able to get at this basic info any time that I wanted without having to remember all the syntax so I built it into a PowerShell function.

Building a PowerShell function is almost as easy easier than building a stored procedure around a select statement in SQL.  The reason PowerShell is easier than SQL is that when you have a parameter that you are passing in, you can give it a data type, but you don’t have to.

For the function below I gave it a default value of the local machine but you can pass in a machine name that you are trying to get to.

Just copy the code below into an ISE window and hit F5.

function Get-MachineInfo($ServerName="localhost")
{
get-wmiobject win32_computersystem -ComputerName $ServerName |
select DNSHostName, Manufacturer, Model, SystemType ,
        @{Name="TotalPhysicalMemoryInMB";Expression={"{0:n2}" -f($_.TotalPhysicalMemory/1mb)}},
        NumberOfLogicalProcessors, NumberOfProcessors, CurrentTimeZone, DaylightInEffect
}# End Get-MachineInfo

After you’ve done that, to call the stored proc function simply type in the name ( Get-MachineInfo ) to the prompt at the bottom and hit enter

image

My First MSSQLTip!

Nov 2nd, 2011
by Aaron Nelson.
2 comments

imageToday my first ever MSSQLTip was published.  Before you ask: Yes, it talked about PowerShell.

It came about because some of the SQL MVPs were trying to figure out the best way to download a VM that had been split up into 36 different equal-sized files.  I’m sure there’s a better way than this but at the same time it took me about 4 minutes to write.  Write is the wrong term, more like copy/paste/change a few things.

Give it a whirl and let me know what you think!  Smile

Whats in my PowerShell Profile

Oct 11th, 2011
by Aaron Nelson.
2 comments

Here are two zip files.  The first file is my profile and the second zip file is the scripts that my profiles references.  These are just some of the tools I find useful for enhancing PowerShell.

Resources for my SQL Server PowerShell Extensions webcast

Oct 5th, 2011
by Aaron Nelson.
Comments are off for this post

imageI did another webcast for the Secrets of SQL Server webcast series today.  In the today’s webcast we built on some things I had demoed in a prior webcast and showed how PowerShell in conjunction with SQLPSX can really help you solve some common problems DBAs face.

I showed how you could pull size and free space information from all of your data files, then used the same technique to pull size information from all of your tables across every database on an instance.  Later we created a database and some tables, and then showed how to do some basic login comparisons, all with SQLPSX.

In addition, we stored the results of the frees pace and table size information in a database using the Out-DataTable and Write-DataTable functions.  Finally we wrapped up with a quick demo on how the new SQL Server cmdlets inside of the SQLPS Module in Denali combined with some new functionality in PowerShell v3 CTP1 can really make backing up a database with PowerShell a lot easier.

Here’s what you’ll need to recreate today’s demos;

I hope I see you next week at the PASS Summit or next month during another webcast, this time on customizing your own functions to work with the SMO.

New PowerShell Cmdlets in SQL Denali CTP3

Jul 12th, 2011
by Aaron Nelson.
2 comments

CTP3 of SQL Denali is finally publicly available!

I woke up at 5 am this morning to kick off my download.  I’ve finished the install and I’m happy that I can finally report that we have new cmdlets for SQL Server. 

First off, after running Get-Module -ListAvailable I saw:

Manifest   SQLASCMDLETS
Manifest   SQLPS

This means that SQLPS inside of SQL Agent should finally allow you to import a module during your job step. 

There is now a Function called SQLServer that, from what I can tell, is the SQLServer Provider.  When under PS SQLSERVER:\> if you do a dir | Select pschildname you will find the following list:

PSChildName
———–
SQL
SQLPolicy
SQLRegistration
DataCollection
XEvent
Utility
DAC
IntegrationServices
SQLAS

From my notes it looks like XEvent, IntegrationServices, and SQLAS are the directories that have been added here.  Can’t wait to try out the IntegrationServices portion!  [Update]: Apparently I may need a defaul instance install for this to work.

I also noticed that Tab-Completion appears to be fixed!!  :-)   Also, SQL Registrations > ‘Database Engine Server Group’ are split up into instances like CMS was.  Not sure what that’s all about just yet.  In fact when I try to use it, it doesn’t seem to work.  Maybe I should read the help files.  But I think they may have just gotten CMS & RS reversed.  CMS isn’t working for me either yet.

When you drill down under XEvents and go to your Denali instaince you will see: Packages, & Sessions.  I guess this means that I finally need to read Jonathat’s blog series on X-Events.

When I imported the SQLPS module I received a warning about unapproved verbs.  Oh well, hopefully they’ll work that out soon.  Next I ran Get-Command -Module “SQLPS” and found the following list of cmdlets.

Name Module
Add-SqlAvailabilityDatabase SQLPS
Add-SqlAvailabilityGroupListenerStaticIp SQLPS
Backup-SqlDatabase SQLPS
Convert-UrnToPath SQLPS
Decode-SqlName SQLPS
Disable-SqlHADRService SQLPS
Enable-SqlHADRService SQLPS
Encode-SqlName SQLPS
Invoke-PolicyEvaluation SQLPS
Invoke-Sqlcmd SQLPS
Join-SqlAvailabilityGroup SQLPS
New-SqlAvailabilityGroup SQLPS
New-SqlAvailabilityGroupListener SQLPS
New-SqlAvailabilityReplica SQLPS
New-SqlHADREndpoint SQLPS
Remove-SqlAvailabilityDatabase SQLPS
Remove-SqlAvailabilityGroup SQLPS
Remove-SqlAvailabilityReplica SQLPS
Restore-SqlDatabase SQLPS
Resume-SqlAvailabilityDatabase SQLPS
Set-SqlAvailabilityGroup SQLPS
Set-SqlAvailabilityGroupListener SQLPS
Set-SqlAvailabilityReplica SQLPS
Set-SqlHADREndpoint SQLPS
SQLSERVER SQLPS
Suspend-SqlAvailabilityDatabase SQLPS
Switch-SqlAvailabilityGroup SQLPS
Test-SqlAvailabilityGroup SQLPS
Test-SqlAvailabilityReplica SQLPS
Test-SqlDatabaseReplicaState SQLPS

Analysis Services got some love too in the form of 11 cmdlets!  After inporting the SQLASCMDLETS module I ran this Get-Command -Module “SQLASCMDLETS” and found the following.

Name Module
Add-RoleMember SQLASCMDLETS
Backup-ASDatabase SQLASCMDLETS
Invoke-ASCmd SQLASCMDLETS
Invoke-ProcessCube SQLASCMDLETS
Invoke-ProcessDimension SQLASCMDLETS
Invoke-ProcessPartition SQLASCMDLETS
Merge-Partition SQLASCMDLETS
New-RestoreFolder SQLASCMDLETS
New-RestoreLocation SQLASCMDLETS
Remove-RoleMember SQLASCMDLETS
Restore-ASDatabase SQLASCMDLETS

More news to follow soon!

PowerShell takes the Pole at SQLRally!

May 10th, 2011
by Aaron Nelson.
Comments are off for this post

PowerShell takes the Pole at SQLRally!

PowerShell has taken the Pole for the DBA division at SQLRally!  Come see why the organizers have chosen this session to lead the pack to the Green Flag.

We will be covering new ground, not rehashing last year’s PASS Summit presentation.  I have developed several new examples for leveraging PowerShell in your everyday DBA role since then.  I am promising 6 new scripts for everyone but if I get on a role that number might be closer to 10 when they drop the green flag on this year’s event.  :-)

If you’ve never used PowerShell before don’t worry. We will spend a few minutes on a ‘get up to speed lap’ so everyone is going at the same pace when we step on the gas with all this new material.  All scripts will be made available for you to download during the session and access later on from pit-row.

After my session here’s what I’ll be checking out:

 

What sessions I'll be checking out during SQLRally

What sessions I'll be checking out during SQLRally

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.

← Earlier Posts