Pages

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

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

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