SQLSaturday #21 is Coming to Orlando...

...On October 17th.

For anyone who's not familiar with SQLSaturday, it's a free (a $10 fee for lunch is being charged this year due to funding challenges) all day event for SQL Pro's & those wanting to expand their SQL skills. There's a wide variety of sessions throughout the day organized in "tracks". For example there's a track for Administration which focuses more on, well, the administrative aspects of working with SQL & a track on SSIS which focuses on ETL. The sessions cover a broad range of depth & there are sessions suitable for beginners all the way through advanced users.

I had the privilege of attending the very first SQLSaturday & several more since then & I can't recommend it highly enough. Top notch speakers (for example Andy Warren, Brian Knight, Buck Woody & Kendal Van Dyke to name a few), lots of info, lots of fun & you certainly can't beat the cost. In today's economic climate, with training budgets being slashed or eliminated this is a great way to pick up high quality training for the cost of lunch.

In addition to the free event, EndToEnd Training is also presenting 5 full day seminars the week leading up to SQLSaturday. Each seminar is $149 with a 20% discount to those that attend 2 or more sessions.

<shameless plug>
This year I'll be presenting a 15 minute session on Encrypting SSIS Connection Strings.
</shameless plug>

So, get signed up & plan on being in Orlando on the 17th. Trust me, you'll thank yourself.

Checking SQL & SSIS Versions with Powershell

I'm currently in the process of applying SP3 + CU5 to all our SQL2005 Instances. I wanted to be able to quickly check whether the install was successful (we're experimenting with using SCCM to push to our standalone Instances).

Checking SQL was easy enough, just using @@version in a multi-query was enough. However SSIS is a little tougher, you can't check it via T-SQL and I don't have the patience to login to every machine and check it that way.

The solution I came up with uses SERVERPROPERTY('ProductVersion') to check the SQL build & [system.diagnostics.fileversioninfo]::GetVersionInfo() to check the Product version on the SSIS executable.

It returns a table with the Instance, Version, SQL & SSIS Builds and Notes (for example whether SSIS is installed & if installed do the SQL & SSIS Builds match). The code is here.

Finding Event ID's

A while back, I had the need to look for a particular EventID across all our SQL servers. The script I came up to do the work is here.

This was one of my earliest efforts so its a bit primitive, but works just fine. The main working portion of the script is a straighforward query of the Win32_NTLogEvent wmi class.

The fun part was converting the wmi time into something more usable/readable. I did this in a function that breaks the wmi time into discrete chunks & then strings them all back together in my preferred format. Additionally it modify's the new date/time to account for daylight savings.

Function Convert-WMITime
{
    param([string]$WMITime)

    #Break Date & Time into discreet elements
    $ds = [string]$WMITime.substring(21,4)
    $yr = [string]$WMITime.Substring(0,4)
    $mo = [string]$WMITime.substring(4,2)
    $dy = [string]$WMITime.substring(6,2)
    $tm = [string]$WMITime.substring(8,6)
    $hr = [string]$tm.Substring(0,2)
    $min = [string]$tm.substring(2,2)
    $sec = [string]$tm.substring(4,2)
    
    #Create string in desired format
    $s = "$mo/$dy/$yr " + $hr + ":" + $min + ":" + $sec
    
    #cast result as DateTime type
    $result = [DateTime]$s
    
    #Account for DST
    if ($cp = $ds.Contains("-240"))
    {
        $result = $result.AddHours(-1)
    }
    
    return $result
}

Get Files, Sizes & Percent Free by DB

This week I've been working on our space needs for the upcoming budget year. As part of that effort I spent a few minutes writing a script to return infrmation on database files; including file group, logical name, physical name, allocated size, used size & percent free space for each file. The code is here.

In the next few weeks I'm planning to do a write up on the entire process I used to generate my space requests, it involves several pieces, inluding a process to populate database size (this is not mine).

Generating log file shrinks

We had a situation where we needed to shrink all log files for all databases on a couple of drives preparatory to moving all the files to a new LUN which would then be mounted as the same drive letter. This was being done to correct improperly aligned partitions on the drives causing performance issues.

When disk partitions are not properly aligned there is a significant performance degradation (30-40%) on the disk, see Chad Miller's blog post on this for more detail. Additionally, correcting this will reduce throughput to the SAN array which can benefit overall performance of the array.

I don't normally recommend (and would typically strongly oppose) shrinking files, but in this case we had a limited time frame to make this move & shrinking these files meant 150GBs less data to move which was a make or break for the timeline we were allowed to complete the work in.

Since these were all log files I choose to shrink them all to 1MB for maximum benefit. For the sake of paranoia (I like to double/triple/quadruple check scripts prior to running in Production) I had it generate the statements to run & then ran them manually. To customize for your drives you would just change the T-SQL defined in the @sql variable. The script is here.

Simple-Talk article

The folks at Simple-Talk were kind enough to give me an opportunity to write an article for them about Powershell, Reg-Gate SQLCompare & Snapshots.

The result is here, please let me know what you think by voting. Thanks!

Granting use of Graphical Execution Plan in SQL2005

I recently had the need to allow non-sysadmin, non-db_owner users to use the graphcal execution plan in SQL2005 on a query using tempdb (temp table). Assuming this worked just like "SET SHOWPLAN_* ON" I granted the user Showplan on the DB in question.

To test, I created a user with access to only the DB in question & tested "SET SHOWPLAN_TEXT ON", "SET SHOWPLAN_XML ON" & "SET SHOWPLAN_ALL ON" with no issues. However, when I tried to use the graphical execution plan ("Include Actual Execution Plan" option on the query) it failed with the below error...
Msg 262, Level 14, State 4, Line 29
SHOWPLAN permission denied in database 'tempdb'.

So, for a "normal" user who uses the graphical execution plan it seems my only option is to explicitly add them to tempdb & grant Showplan permissions (this would entail adding them to model as well so their perms persist on startup when tempdb is recreated). The other option would be to grant Showplan perms to guest on model/tempdb which given the official & observed (for example) vulnerabilities associated with SHOWPLAN seems a little crazy.

I don't normally knock MS around too much, but I really dislike both these options. Basically I'm being forced to choose between a gaping security hole (the guest scenario), a smaller security hole + a (to me) bad practice of granting a user access to a system DB that they really shouldn't need OR denying the perms to a user who's actually trying to do right.