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

   4:   

   5:  $FilePath = "C:\Output"

   6:  $OutFile = Join-Path -path $FilePath -childPath ("ServerWide_FillFactor" + (get-date).toString('yyyyMMdd_hhmmtt') + ".csv")

   7:   

   8:  # Provide a list of servers one way or another...

   9:  $Servers = 'RED50\SQLEXPRESS'

  10:  #$Servers = get-content 'C:\Input\Servers.txt'

  11:   

  12:  @(

  13:  foreach ($svr in $Servers)

  14:  {

  15:      $s = get-sqlserver $svr

  16:      $s.Configuration | select parent,{$_.Fillfactor.RunValue}

  17:      

  18:  }

  19:  ) | export-csv -noType $OutFile

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


1:  $serverName = "SQL999"

   2:  $databaseName = "InventoryDB"

   3:   

   4:  $FilePath = "C:\GUARDIUM\DataSources\Output"

   5:  $OutFile = Join-Path -path $FilePath -childPath ("MSSQL_GDM_Output" + (get-date).toString('yyyyMMdd_hhmmtt') + ".txt")

   6:   

   7:  # here string contains SQL query

   8:  $qry = @"

   9:  SELECT  DISTINCT

  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:   "@

  24:   

  25:  $Servers = Get-SqlData $serverName $databaseName $qry

  26:   

  27:  Start-Transcript $OutFile

  28:   

  29:  $ScriptToExecute =  "C:\GUARDIUM\Scripts\gdmmonitor-mss.sql"

  30:   

  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:  }

  37:   

  38:  Stop-Transcript

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

   3:   

   4:  $serverName = "SERVER\INSTANCE"

   5:  $databaseName = "dbINVENTORY"

   6:   

   7:  $FilePath = "C:\Output"

   8:  $OutFile = Join-Path -path $FilePath -childPath ("MSDB_MIN_Date_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".csv")

   9:   

  10:  # here string contains SQL query

  11:  $qry = @"

  12:  SELECT DISTINCT

  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:  "@

  26:   

  27:  $Servers = Get-SqlData $serverName $databaseName $qry

  28:   

  29:  $qry = @"

  30:  SELECT  @@SERVERNAME AS ServerName, MIN(backup_finish_date) OldestMsdbBackupDate FROM  msdb.dbo.backupset 

  31:  "@

  32:  $databaseName = "msdb"

  33:   

  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

  40:      

  41:  }

  42:  )  | export-csv -noType $OutFile

  43:   

  44:  $end = get-date    

  45:  write-host "End: "  $end

Meme Monday

Thomas LaRock (blog|twitter) has started Meme Monday and challenged folks to write a blog post in 11 words or less. Here’s mine…


The Scripting Games have begun! I will be participating this year!

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.

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.

SQLskills Free Immersion

Why do I want to attend a SQLskills class taught by Paul Randal and Kimberly Tripp?

I revere competence.

To be a master, you must train with the masters.

This Zen proverb sums up my reasons for wanting to attend.

To follow the path:

look to the master,
follow the master,
walk with the master,
see through the master,
become the master.

True masters are humble and approachable. SQLskills is.

Why I'd make the best use of the knowledge I'd get from being in the class.
I consider myself well-informed regarding SQL Server but I’d never say I’m an expert. I’ll let my peers decide that. Officially, I'm on the Level 3 Support Team at my company.  It implies that I am my company’s SQL Server Subject Matter Expert.  We only get the difficult questions.  
This class would make me a more competent SME and be the foundation for my MCM effort.

I would love to attend the SQLskills Master Immersion Event on Internals and Performance in Dallas, TX  February 21-25, 2010.  

Class info here:  http://goo.gl/mZp46

Contest info here:  http://goo.gl/Or6Qv Entries must be submitted by 1/25/2011 23:59.

Finally, another chance to ogle Paul's sweet ORANGE Dell laptop would be a wonderful thing.