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

SQL Server

I’m speaking at TechEd!

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

imageI’ve been meaning to post this for months but I’ve been a bit busy.  Better the day before than never right?  I’m Speaking at TechEd!!!!  Smile

In addition to speaking Tuesday morning, I will be answering questions at the SQL Server booths (there’s 12 of them):

Monday from 12-2 and again from 5:30 – 7:30
Tuesday from 3 – 5
Wednesday 10:30 – 1
Thursday 10:30 – 1

If you’re at the conference and you have questions about SQL Server (especially if they involve PowerShell) I hope you’ll stop by!

P.S.  The easiest way to find where the SQL Server booths are is to head over to the Porsche the Visual Studio team is giving away.

T-SQL Tuesday #31 – Logging

Jun 5th, 2012
by Aaron Nelson.
26 comments

I’m excited to be hosting this month’s T-SQL Tuesday.  This month we’re talking about logging.  Logging comes in many form and fashions.  If you think about it, when you go to the grocery store with at big list, Do you put the items in the cart and then check them off the list?  If so, isn’t that a kind of write-ahead log?

I point that out because I don’t want anyone to constrain themselves to talking about logging within just SQL Server.  Please bring your ideas for file transfers, report generating, performance gathering, uptime monitoring and the like.  But don’t stop there!  This is an open invite to anyone that does anything in the SQL Server community.

If Karla Landrum ( blog | twitter ) wants to explain to us how on Earth she keeps track of all these SQL Saturdays around the world, that’s logging!  If Tim Radney ( blog | twitter ) wants to tell us how he makes sure he keeps in touch with all of the chapters he’s responsible for as a PASS Regional Mentor, that’s logging!

How you keep track of blog ideas, white papers you read, or however it is you life-hack *your* SQL world: please share it with us!

Rules
•Your post must be published between 00:00 GMT Tuesday June 12th, 2012, and 00:00 GMT Wednesday June 13th, 2012
•Your post must contain the T-SQL Tuesday logo from above and the image should link back to this blog post.
•Trackbacks should work, but if you don’t see one please link to your post in the comments section below so everyone can see your work

Optional, but highly encouraged…
•Include a reference to T-SQL Tuesday in the title of your post
•Tweet about your post using the hash tag #TSQL2sDay
•Consider hosting T-SQL Tuesday yourself. Adam Machanic keeps the list.

 

P.S.  Since I’m the host, any PowerShell topic on logging is cool too!

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

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!

Why I Need a Week of SQLskills Immersion

Jan 26th, 2011
by Aaron Nelson.
3 comments

SQLskills is giving back to the community with a free seat to their training class in Dallas next month (Feb. 21st – 25th)

To answer Paul Randal’s question of why I should get to win a week of training from SQLskills this is what I have to say:

Plain and Simple.  I’m a Second Generation DBA with over a decade of experience with SQL Server.  I want some deep level ideas for some new PowerShell scripts to simply tasks for DBAs everywhere.  I’ll also spend countless hours telling all my fellow DBAs here in Atlanta what all I learned until they can take it no more and demand this level of top-notch training for themselves!  Open-mouthed smile

I’ll get started with the PowerShell scripts right now even.  This little script will search twitter for a giver hashtag, in this case: #SQLskills.  It will then save everything it finds off to an XML file and then query it:

$SearchHashtag = "sqlskills"            
            
$wc = new-object net.webclient            
$url = "http://search.twitter.com/search.atom?q=%23$SearchHashtag"            
$OutFile = "c:\temp\Download_$($SearchHashtag)_$([DateTime]::Now.ToString('yyyyMMddHHmmss')).xml"            
$wc.DownloadString($url) | add-content $OutFile;            
            
[xml]$tweetxml = get-content $OutFile            
            
$tweetxml.feed.entry | Format-List            
            
($tweetxml.feed.entry).count

Is the Connect Feed for SQL Server in Your Reader?

Jan 6th, 2011
by Aaron Nelson.
3 comments

Last week Nicolas Cain ( blog | twitter ) Rob Farley ( blog | twitter ) Aaron Bertrand ( blog | twitter ) and I were all working together on a small skunk-works project to read in all of of the items on the SQL-Connect website (more in that project in a later post).  A few hours into this process I realized “hey, I don’t even have this RSS feed in my Reader!”  I’m not sure why I didn’t; maybe I was lulled into safety by Aaron Bertrand’s “Connect Digests” but unfortunately he doesn’t do those any more. Crying face

So I just wanted to encourage everyone who makes their living off of SQL Server to take a minute to add these two RSS feeds to your favorite feed-reader. 

https://connect.microsoft.com/rss/68/RecentFeedbackForConnection.xml

https://connect.microsoft.com/rss/68/RecentlyModifiedFeedbackForConnection.xml

If you haven’t been on Connect for a while and wonder what’s out there, well I’ve got a PowerShell script for you Open-mouthed smile 

Invoke-Sqlcmd -ServerInstance denali.db.5026258.hostedresource.com `
-Database denali -Username denaliconnect -Password Wide0pen -Query "
SELECT ID
        , Title
        , ItemStatus
        , UpVoteCount
        , DownVoteCount
        , ItemDescription
        , Author
        , Modified
        , ValidationCount
        , WorkAroundCount
        , URL
        , OpenedDate
  FROM denali.dbo.ConnectItems" | out-gridview

OK, you can totally take that login info and use your SQL Management Studio to connect to this database too but I wanted to show you a REALLY cool use for the Out-GridView command.  When run the PowerShell script and the window pops up Just Click on + Add Criteria > ItemDescription > Add.

Like this:

image

Then just type in a search term like “SSIS” and see what you get back…

image

Rob has also put together a way to search the data that is much better and where we hope to ultimately go with it.  Again, that’s all for another post.

Guest Posts for Hey Scripting Guy Blog on TechNet

Jan 4th, 2011
by Aaron Nelson.
Comments are off for this post

imageLate last year I contributed three guest blogger posts to a full week of posts about PowerShell and SQL Server. The posts were published a week before the PASS Summit. In them I tried to not only cover how useful PowerShell is for automating your every day SQL tasks but also the different approaches you have for accomplishing them: PowerShell cmdlet, WMI, SMO, SQL Provider, and .Net.

It’s a huge honor to get the chance to be a guest blogger on Hey Scripting Guy! and I hope these posts were able to make some people’s (o.k. A Lot of People’s) lives easier. Given the number of people checking out PowerShell for the first time based on the comments to my MCM Videos download script I figured I’d call these out today:

Use PowerShell to Obtain SQL Server Database Sizes

Use PowerShell to Change SQL Server Service Accounts

Use PowerShell to Script SQL Database Objects

image

I know that the Hey Scripting Guy! blog is the #1 blog on TechNet but I’m not allowed to know any specifics beyond that. I did however catch this in yesterday’s post: “In general, the articles we published on SQL Server and on SharePoint have been really popular.” So hopefully that means we’ll get to hear some more SQL voices there in the near future. Maybe even in time for SQL Rally, who knows? Winking smile

← Earlier Posts