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

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.

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.

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.   

  

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.