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

PowerShell

PowerShell

2013 North American PowerShell Summit

Sep 25th, 2012
by Aaron Nelson.
Comments are off for this post

Summit-2013-Logo[1]The date has been set for the next North American PowerShell Summit.  It will be held Monday April 22nd – Wednesday April 24th at the Microsoft campus in Redmond, Washington.  I’m already secured my spot and if you want to be there you better act fast!  As of right now there’s less than 40 seats left.

The details for this event have come together quickly.  I know it’s hard to buy a ticket to an event when the speakers haven’t been chosen yet but I’m pretty sure this event will sell out before that happens.  I‘ve heard great things about the previous PowerShell Summits and I’m sure this one will be even better since it will be on the Microsoft campus.

Come help support the new PowerShell.org community as they put on their first community-driven conference!  Smile

TechEd Dirty Dozen Scripts – PowerShell for SQL Server 2012

Jun 12th, 2012
by Aaron Nelson.
Comments are off for this post

For those of you attending my presentation at TechEd North America today, here’s the scripts that we’ll be going through: http://SQLvariant.com/BlogSupport/PoShDirtyDozen/TechEdDozen.zip

PowerShell Scripts for Pragmatic Works Webcast

Apr 24th, 2012
by Aaron Nelson.
Comments are off for this post

I did a webcast for the folks over at Pragmatic Works for their Free Training series two weeks ago.  As promised, here are the scripts I covered in that webcast.

I want to thank everyone who joined the webcast and especially all the people that asked the great questions at the end!  I received some really awesome feedback from attendees about the webcast!!  Smile

When I get some time in a few weeks, I will go back and write a blog post showing code to answer the questions from the webcast.

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

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.

PowerShell Interfaces with Other Hammers

Feb 8th, 2011
by Aaron Nelson.
5 comments

This month’s installment of T-SQL Tuesday is hosted by Pat Wright (blog | twitter). Pat says: “So the topic I have chosen for this month is Automation! It can be Automation with T-SQL or with PowerShell or a mix of both. Give us your best tips/tricks and ideas for making our lives easier through Automation.”

I have to tell you, this doesn’t get old:

imageI love that somebody on the other side of the planet is telling someone else that I ‘probably have a PowerShell script to solve their problem’. The funny thing is that a lot of the time I don’t have a script for the problem they’re trying to solve *yet*. I love it when someone gets referred to me and I don’t have a script because it gives me a chance to go explore the language and figure out something I don’t know. PowerShell makes this quest pretty fast.

imageIronically Paul White ( blog | twitter ) didn’t know I had a script for that, he was just making a joke. Well, actually, I didn’t have a script for that. I did however know right where to find the person that did; and that is even better than if I had a script of my own. Why is it better that I had to point someone some where else? Could I not have figured this script out own my own? No that’s not it at all.

imagePowerShell is NOT a hammer it’s an Automation Language. Better yet PowerShell is a Toolset, and it happens to come with some pretty freaking awesome hammers built in. We all understand that SQL Server isn’t just a hammer, that it’s an entire toolset. Inside of that toolset is: T-SQL, SQL Agent, Profiler, SSIS, the SMO, SSRS, SSAS etc… just to name a few.

The cool thing about PowerShell is it’s actually built to work with other hammers. So the reason that you hear me talking about PowerShell all the time is not because I’m replacing my T-SQL Hammer, I’m just automating it! Like this, here’s a quick script to figure out how long your SQL 2008+ instances have been running:

<# Number of Days since the Instance has been restarted #>
$InstanceList = "WIN7NetBook", "WIN7NetBook\R2", "WIN7NetBook\SQLExpress"
foreach($Instance in $InstanceList)
{
Invoke-Sqlcmd -Query "SELECT @@SERVERNAME AS 'ServerName'
, DATEDIFF(D, Sqlserver_start_time, SYSDATETIME()) AS 'NumberOfDays'
  FROM sys.dm_os_sys_info" -ServerInstance $Instance -Database master
}

The automation doesn’t stop with SQL Server. You can find plenty of posts form people in our SQL Community on working with things like Red-Gate’s SQL Compare, Outlook, Active Directory, and Subversion just to name a few. The key here is that all of these people are automating something adjacent to their SQL world and they’re using PowerShell to make it happen.

Heck just last week I wanted to know how many sessions had been submitted to SQL Saturday #67 in Chicago. Do you think I went to the schedule page, copied the list of sessions into Excel and then checked to see what line number the last session was on to get my count?? Heck NO!! Last year I wrote a script for Allen Kinsel ( blog | twitter ) to count how many people at the PASS Summit had registered their twitter handle when the signed up. I took that script, changed the link, changed the term I was looking for and presto! I now know that 105 sessions have been submitted to SQL Saturday #67.

That has nothing to do with SQL Server right?!… Except for that whole bit about it being a SQL Saturday that I was interested in.

<#Start up a web client and download the web page into a text file#>
$url="http://www.sqlsaturday.com/67/schedule.aspx"
$file="c:\temp\DownloadSession$(((Get-Date).ToString("yyyyMMddHHmm"))).txt"
$webclient = New-Object system.net.webclient
$webclient.DownloadFile($url,$file)            

<#Sift through the file for lines that include "viewsession" #>
(Select-String -path $file -Pattern "viewsession").count

OK, so what’s your point?

My point is that I wouldn’t have even had the twitter script that I started with if Allen Kinsel hadn’t taken a chance and sent me an email at noon the Friday before the PASS Summit and asked me if I could use PowerShell to count the number of attendees with twitter handles.

Again, what’s your point?

My point is Use PowerShell to make one tool talk to another.

And…

And tell people faced with an annoying problem that you think I’ve got a script for that because whose knows, maybe I do!

← Earlier Posts