Wednesday, September 21, 2011

SQL Saturday #85

I'll be speaking at SQL Saturday #85 this weekend in Orlando.
Here's another example we'll review during my session "PowerShell by Example".
This example uses the SQLPSX 1.6 function get-sqlserver to return server properties.

This example was inspired by @PaulRandal's Ponderings on the instance-wide fillfactor setting in his SQLskills Insider Tips newsletter of 9/3/2011.  
To become a SQLskills insider, go here.

Looking forward to seeing everyone at SQL Saturday #85!

   1:  # In response to Paul Randal's Ponderings of Sep 3, 2011 regarding fill factor set at the instance level...
   2:  # His Call To Action is to verify it is set to 100. This script provides the basic means to check it on multiple instances.  
   3:  # The results are sent to a csv file
   5:  $FilePath = "C:\Output"
   6:  $OutFile = Join-Path -path $FilePath -childPath ("ServerWide_FillFactor" + (get-date).toString('yyyyMMdd_hhmmtt') + ".csv")
   8:  # Provide a list of servers one way or another...
   9:  $Servers = 'RED50\SQLEXPRESS'
  10:  #$Servers = get-content 'C:\Input\Servers.txt'
  12:  @(
  13:  foreach ($svr in $Servers)
  14:  {
  15:      $s = get-sqlserver $svr
  16:      $s.Configuration | select parent,{$_.Fillfactor.RunValue}
  18:  }
  19:  ) | export-csv -noType $OutFile

Friday, September 2, 2011

Execute Guardium SQL Server Permissions Script on Multiple Servers

# SQLSaturday#85 in Orlando is September 24, 2011
# I'll be presenting "PowerShell by Example"
# Here's a typical example we will review.
# Want more?  Come to my session at 11 AM.
# Need more info on SQL Saturday Orlando, go here.
# Set permissions on multiple servers for GUARDIUM Vulnerability Assessment 
# Pre-requisites:
# PowerShell V1
# SQLPSX 1.6.1
# add-pssnapin sqlserverprovidersnapin100
# add-pssnapin sqlservercmdletsnapin100

$serverName = "SQL999"
   2:  $databaseName = "InventoryDB"
   4:  $FilePath = "C:\GUARDIUM\DataSources\Output"
   5:  $OutFile = Join-Path -path $FilePath -childPath ("MSSQL_GDM_Output" + (get-date).toString('yyyyMMdd_hhmmtt') + ".txt")
   7:  # here string contains SQL query
   8:  $qry = @"
  10:          ISNULL(A.MACH_LPAR_NM, '') + CASE a.INST_NM
  11:                                         WHEN 'DEFAULT' THEN ''
  12:                                         ELSE '\'
  13:                                       END + CASE a.INST_NM
  14:                                               WHEN 'DEFAULT' THEN ''
  15:                                               ELSE ISNULL(a.INST_NM, '')
  16:                                             END AS InstanceName
  17:  FROM    InventoryDatabase
  23:   "@
  25:  $Servers = Get-SqlData $serverName $databaseName $qry
  27:  Start-Transcript $OutFile
  29:  $ScriptToExecute =  "C:\GUARDIUM\Scripts\gdmmonitor-mss.sql"
  31:  foreach ($svr in $Servers)
  32:  {
  33:      $serverName = $svr.InstanceName
  34:      Write-Host $serverName
  35:      trap {"Oops! It failed. $_"; continue } Invoke-Sqlcmd -InputFile $ScriptToExecute  -verbose -ServerInstance $serverName
  36:  }
  38:  Stop-Transcript

Wednesday, July 6, 2011

How cluttered is MSDB?

# My quick and dirty PowerShell script to check for the oldest entry in MSDB.
# I'm using a SQLPSX 1.6 function Get-SqlData to execute queries. 
# Runs against multiple servers

   1:  $start = get-date
   2:  write-host "Start: "  $start
   4:  $serverName = "SERVER\INSTANCE"
   5:  $databaseName = "dbINVENTORY"
   7:  $FilePath = "C:\Output"
   8:  $OutFile = Join-Path -path $FilePath -childPath ("MSDB_MIN_Date_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".csv")
  10:  # here string contains SQL query
  11:  $qry = @"
  13:          ISNULL(A.MACH_LPAR_NM, '') + CASE INST_NM
  14:                                         WHEN 'DEFAULT' THEN ''
  15:                                         ELSE '\'
  16:                                       END + CASE INST_NM
  17:                                               WHEN 'DEFAULT' THEN ''
  18:                                               ELSE ISNULL(INST_NM, '')
  19:                                             END AS InstanceName
  20:  FROM    dbInventory A
  24:  ORDER BY InstanceName
  25:  "@
  27:  $Servers = Get-SqlData $serverName $databaseName $qry
  29:  $qry = @"
  30:  SELECT  @@SERVERNAME AS ServerName, MIN(backup_finish_date) OldestMsdbBackupDate FROM  msdb.dbo.backupset 
  31:  "@
  32:  $databaseName = "msdb"
  34:  # Version inventory
  35:  @(
  36:  foreach ($svr in $Servers)
  37:  {
  38:      $serverName = $svr.InstanceName
  39:      trap {"Oops! Query failed. $_"; continue } Get-SqlData $serverName $databaseName $qry
  41:  }
  42:  )  | export-csv -noType $OutFile
  44:  $end = get-date    
  45:  write-host "End: "  $end

Sunday, February 27, 2011

When does a practice become BEST?

How many good references does an idea need before it becomes a best practice?
For starters, Ola Hallengren's script for Backup, Index Maintenance, and DBCC checks.

I've done the research and spoke to Ola in person at the 2010 PASS Summit.
Ola's good references come from no less than Microsoft, Red Gate Software, Quest Software, and Microsoft MVPs.

I think Ola Hallengren's maintenance solution is a BEST PRACTICE.  
I have a few questions for you.  
Do you agree?  
Do you use Ola's maintenance solution?  
Do you use Ola's solution as is or have you modified it?  
Please respond in the comments.

Wednesday, January 26, 2011

SQL Rally abstracts submitted

I've submitted two abstracts for the SQLRally.
If you're interested in learning PowerShell by example or want to hear how I automated our login provisioning and compliance reporting. Vote for my sessions.
It's a win-win situation in the PowerShell track for attendees no matter who you vote for.
Another great bunch of abstracts in the DBA track.
Get out and vote.

Friday, January 21, 2011

Opinion: SQL Rally Abstract Process

I'm really enjoying the process that the SQL Rally is using to select speakers.
I've submitted abstracts to several SQL Saturdays and the PASS Summit but this has been the most interesting process so far. What I really liked about the SQL Rally submission process:
  • Blind submission
  • Two abstract limit
  • Community votes for the final slate of speakers.
The blind submission process eliminates being concerned about what others will do. Submit what you have, if it's worthy, it will be selected. If not, move on.      
The two abstract limit levels the playing field for less experienced speakers who haven't compiled as much content but have gained enough speaking experience at the local level to move up to the regional level.  I think this limit also has produced better abstracts.  I think the candidates spent more time sharpening their abstracts because they only had two chances to get a slot.
Finally, letting the PASS community vote was a great idea but stretching the voting out over a month is brilliant from a marketing perspective.  The organizers have developed a very community focused method of creating maximum interest in this event.  Well done!

I'd like to see all or parts of this process used for future PASS events.

Tuesday, January 18, 2011


I had the honor of speaking at SQL Saturday #62 this past weekend in Tampa.
It's only the second time I've presented using my Windows 7 laptop and the first time I presented using the Presenter View in Powerpoint 2010.  We still run Windows XP at work so I don't use Windows 7 on a daily basis.  I arrived early to setup for my presentation, connected the projector and booted the laptop.  The laptop auto set the resolution to 800x600. I didn't like the setting so I increased the resolution to a higher level and started up the presentation and went into Presenter View.  Cue foreboding music...

By this time, the first attendees started rolling in.  I looked at the first few slides to check everything was OK and then went back to the first slide in the deck.  I started my presentation, got to the fourth slide on the deck and the picture was missing!  The next slide was blank!  WTF?  The slides looked good in the ribbon at the bottom of Presenter View but weren't rendering correctly on the screen or in the Presenter View's left pane. 

Needless to say, this really threw me off.  Thankfully, one of the attendees suggested I switch to the editor view and present from that.  So, I muddled through the rest of the presentation and my demos.  Despite my PowerPoint woes, I did receive decent reviews via Twitter but I was not happy. I had to figure out what went wrong. I kept thinking and talking about it with other people for the next couple of hours.  

Finally, I thought changing the resolution might have been the crux of the issue.  I went back in the room during the lunch break and plugged my laptop into the projector.  This time, I did not touch the resolution.  The deck displayed correctly.  Head slap!

So, the sole purpose of this post is to save some other speaker newbie from repeating this mistake.  

Thanks to @BrentO for his post on Presenter View and @mrdenny and @brianwmitchell for their input while I was trying to figure this out.

Tuesday, January 4, 2011

SQL PASS Summit Helium Talks

I've started listening to the PASS Summit DVD sessions during my commute.  By accident, I discovered that my Sansa Clip+ mp3 player has a playback speed option while listening to the Lightning Talks. 

Buck Woody, Brent Ozar, Adam Machanic are great speakers but the fun begins when you change the playback speed option to FAST. It really sounds like Helium has been introduced into the room. It's really entertaining, you can still understand the speakers and you get through the content quicker.

One final note, Michelle Ufford must have been talking REALLY fast.   

Monday, January 3, 2011

SQL Saturday #62

I'll be speaking at SQL Saturday #62 in Tampa, FL on January 15, 2011.

I'm presenting a session called "Why Learn PowerShell" because there was a request for a beginning PowerShell session.  Tons of speaker submissions for this SQL Saturday so the organizers gave most speakers one slot to give the maximum amount of people the chance to speak.  They are still trying to secure additional space to expand the schedule.
If you have any interest in learning PowerShell, I encourage you attend this session.  
I'll explain why it's worth learning and provide REAL WORLD examples that I use regularly.
To further convince you that SQL Saturday is worth your time consider the following:
  • The variety of topics is pretty amazing for a FREE event.
  • Local, regional, and national speakers will present.
  • Several 2010 PASS Summit presenters will speak.
  • The Microsoft Scripting Guy and The Scripting Wife will be attending
  • A local Microsoft Certified Master will be presenting on the Parallel Data Warehouse.
  • The best lunch of any SQL Saturday will be served.
I've also registered for the Day of Data session "Virtualization and Storage for the DBA" by Denny Cherry being held the day before at the Italian Club in Ybor City.
I encourage all IT Pros to attend this event.