Beer, Cheese and SQL Saturday 118 Madison

This past weekend I (w|t) headed up to Madison, WI to attend and present at SQL Saturday 118. The MADPASS team did a fantastic job on their first SQL Saturday.

Attendance was excellent with 227 total consisting of locals and groups from Minnesota, Iowa, Missouri, Indiana, Illinois, Michigan, Nebraska and other places far flung.

I drove up early Friday and worked remotely for the day to make sure that I didn’t miss the speaker dinner.

The fun started early as I met Jes Borland (b|t), Erin Stellato (b|t) and Joey D’Antoni (b|t) for lunch. Jes and Erin arrived “fresh” from a 15 mile run with hearty appetites. Cheeseburgers, grilled cheese and a beef melt were the fare. No beer at lunch, I had more work to do.

After work and settling into my accommodations, I headed over to the venue to give a hand with bag stuffing. Wow, the MATC was bigger than it looked. By the time I got to the room where bag stuffing was taking place, all the work was done. I developed quite a thirst trying to find that room. Fortunately we headed over to the Ale Asylum for a pre-speaker dinner beverage. I had a Big Slick Stout along with some good conversation. That stout was awesome. I would’ve liked to try some of Ale Asylum’s other beers, but we had to move on.

The speaker dinner was at a local Italian place. More good conversation and another excellent stout. I chose the Lilja’s Sasquatch Stout from Sand Creek Brewing Company, a big and powerful beer. It went very well with the food (cheese) and the fantastic chocolate that Bill Fellows (b|t) shared with us. The highlight of the the dinner was the impromptu paper airplane fight.

After the speaker dinner, a group of us took over the hotel bar. Geek rule. More good conversation and Capitol Brewery’s Maibock.

Saturday morning came quickly and since I was on at 9:30, I headed over to MATC early to grab a bite and park in the room where my session was scheduled. Mike Donnelly (b|t) was there giving his first SQL Saturday presentation ever, SSIS: Figuring Out Configuring. I listened as I was configuring the VM for my presentation. He did a great job.

My presentation, SSIS 2012 New Features, was next. Attendance was very good. Thank you to all who attended. Audience participation was also good with lots of questions as well as valuable additions from fellow speakers Bill Fellows, Mark Vaillancourt (b|t) and Aaron Lowe (b|t).

With my speaker duties fulfilled, I headed over to Stacia Misner’s (b|t) presentation, (Way To Much) Fun with Reporting Services. Stacia’s daughter Erika Bakse (b|t) was also assisting with the presentation. The banter between these two made for a very entertaining session. They covered the implementation of Words with Friends in RSS. Let’s see Connect 4 implemented with a play the computer mode using set based algorithms.

After lunch I attend Eddie Wuerch’s (b|t) session, TempDB Performance Troubleshooting and Optomizing. I’ve talked with Eddie at the previous Chicago and Indianapolis SQL Saturdays, but I’ve never attend one of his sessions. This was an excellent session with lots of myth busting and fairly deep insights into tempdb behaviour. He did a great job explaining this all in a simple manner.

I then attended fellow Chicagoan Luke Jian’s (b|t) session, Optimizing SQL Server I/O with Solid State Drives. Lots of good information on SSD hardware. I was surprised by the light attendance in this session. Are SSDs not a hot topic? You missed a good one here. You can catch Luke and this session at SQL Saturday 119 in Chicago

The last session I attended was Zach Mattson’s (b|t), A PowerShell Driven Life. Zach mixed up PowerShell script walk throughs with reviews of a pair of beers, one bottled (good) and one canned (probably bad). I picked up a few new PowerShell tricks and a few recommendations on beers to avoid. Good info and good fun!

A great day and I am definitely looking forward to the next SQL Saturday in Madison.

Congratulations to MADPASS for running a stellar event.

SQL Saturday 98, Iowa City

Calling all SQL peeps in Chicago, Milwaukee, St. Louis, Minneapolis, Omaha,…!

It’s time for another SQL Saturday at the beautiful University of Iowa campus.

The speaker lineup looks great.

I’ll be there presenting on the SSIS Script Task.

There will be more SSIS goodness presented by Ted Krueger and Ross McNeely.

There’s still time to register and take a nice fall SQL road trip this Saturday.

See you there.

PowerShell, the New SQL Hammer

This is my PowerShell presentation that I have given to both Chicago SQL Server User Groups. The name of the presentation is having some fun with the old quote, “If you have a hammer, everything is a nail”; which allows me use a real hammer in the presentation to good effect. I have received positive feedback from the people that attended and I even had someone stop me in the hall at SQL Saturday Chicago to say that it helped them use PowerShell to be the hero. It is a powerful hammer indeed.

I attended various PowerShell presentations and I just was not “getting it”. The presentation chronicles my journey trying to understand how all the bits and pieces of PowerShell work. It shows how to dig into things a bit deeper to help use PowerShell and the SQL Server integration provided with SQL Server 2008 more effectively.

I will be giving the presentation at other user groups in the Midwest and I have submitted it to nearby SQL Saturdays.

The slides and scripts are available here for download.

Thanks to Aaron Nelson (Blog|Twitter) and Nic Cain (Blog|Twitter) for their help with script reviews, feedback and inspiration.

SQL Saturday 67 Chicago Pre-con Double Down

Yes, this looks familiar. Didn’t I write this last week? Well it’s new and the same. We have another pre-con available on the Friday, 3/25, before SQL Saturday 67.

Here is the link to register, http://sqlsat67troubleshootingprecon-estw.eventbrite.com/

The presenter is Kevin Kline, MVP, SQL Server expert, author, keynote presenter, Sql cruiser, former PASS President, etc.

This presentation will be held at the Quest office in Warrenville, IL.

Here is the detailed information on the pre-con session:

Cost: $99 (til March 11th), $119

Location Quest, 4320 Winfield Rd # 500 Warrenville, IL 60555-4036

Troubleshooting & Performance Tuning Microsoft SQL Server 2008 R2

 

Presenter : Author, expert  and Microsoft MVP Kevin Kline                         (http://KevinEKline.com)

 

This full day seminar will teach you introductory, intermediate and advanced techniques for troubleshooting and performance tuning using SQL Server’s built-in Dynamic Management Views (DMVs), Performance Monitor, Profiler, Wait Stats, and Extended Events.  In troubleshooting, you’ll learn how to detect, diagnose, and resolve problems on SQL Server using the native tools, as well as how to determine where SQL Server is experiencing bottlenecks that prevent it from achieving optimal performance. Bring your laptop with SQL Server 2008 R2 and AdventureWorks R2 to work through the labs as Kevin demonstrates various techniques.  (SQL Server 2005 is also allowable, but you will not be able to demo those that are specific to SQL Server 2008 R2).
Topics covered include:
–          The “Rocks, Gravel, Sand” Troubleshooting Methodology
o   Understanding Wait Stats
o   Using beginner, intermediate, and advanced DMVs
o   Correlating Performance Monitor and Profiler
o   Extended Events
–          Determining meaningful key performance indicators thru benchmarking
–          Catch problems before your users do thru monitoring
–          Preventative Maintenance Best Practices
Seminar includes lunch and refreshments.

Attendees will receive troubleshooting scripts, posters detailing important DMVs and PerfMon counters, session video recordings on DVD, and a free signed copy of Kevin’s book Database Benchmarking.

SQL Saturday #67, filled with many free sessions and events, will be held the next day.  For more info and to register for SQL Saturday, please visit the event website:http://www.sqlsaturday.com/67/eventhome.aspx

SQL Saturday 67 Chicago Pre-con

We have big news for SQL Saturday #67 Chicago. There is going be a pre-con on Friday 3/25 at the Hampton Inn right around the corner from the SQL Saturday venue.

Here is the link to register, SQL Saturday #67 Pre-Conference: DBA Skills Upgrade Toolkit.

Questioning why you should attend the pre-con? Check out Noel McKinney’s take on it.

Before I give details about the event, let me introduce you the presenter. The pre-con will be presented by Eddie Wuerch (Blog|Twitter) from Indianapolis. I had the pleasure of meeting Eddie at SQL Saturday 45 in Louisville a few weeks ago. We had some interesting discussions about the extreme loads that hit his environment. With this daily experience, I’m sure Eddie will have some great things to share in the pre-con session. Eddie is also a regular presenter at SQL Saturdays and Indy PASS. Eddie tweeted this earlier. Need I say more: “@dawndevirgilio: A million rows of data isn’t cool, you know what’s cool? A billion…! #ETHub” <-we’re approaching a trillion, actually…

Here is the detailed information on the pre-con session:

The DBA Skills Upgrade Toolkit

Cost: $99 (before March 7)

Presenter: Eddie Wuerch

This full-day seminar is for DBAs and developers with DBA responsibilities looking to beef up their skillsets and simplify their lives with additional insight into their SQL Servers.  Eddie Wuerch takes his extensive experience as a speaker, trainer, mentor, and DBA in one of the largest and busiest SQL Server environments in the world and distills it into a collection of topics for DBAs managing databases of all sizes.

Topics included in the seminar include

  • Current and historical monitoring of server processes and resources
  • SQL I/O for the DBA:
    • RAID, disk options, and SANs
    • File and Filegroup planning, placement, and monitoring
    • Transaction log internals
    • TempDB considerations
  • Encryption for the DBA
  • Locking, Latching, and Deadlocks
  • Capacity planning (CPU, memory, and files)
  • Performance and operational troubleshooting techniques
  • Backup and recovery planning
  • Automation of standard tasks
  • Includes a large collection of T-SQL scripts for performing many of the tasks covered in the seminar

Seminar fee includes lunch, breaktime refreshments, and seminar materials.

SQL Saturday #67, filled with many free sessions and events, will be held the next day.  For more info and to register for SQL Saturday, please visit the event website:http://www.sqlsaturday.com/67/eventhome.aspx

Getting Drive Info, Part 5, the SSIS WMI Data Reader Task

In the final installment of the Getting Drive Info series (Part 1, Part 2, Part 3, Part 4), SSIS will again be used to collect and save the drive information. This WMI Data Reader Task will be used to collect the drive information.

WMI, Windows Management Instrumentation, is the Microsoft implementation of the industry standard Web-based Enterprise Management, WBEM, using the Common Information Model, CIM. WMI provides easy access to information about the computer hardware and software via classes like Win32_LogicalDisk. The classes can be queried with a SQL like language called WQL (SQL for WMI).

WMI Data Reader Task

The package uses the WMI Data Reader Task to gather the disk information. It requires a WMI connection and a WQL query to define what information to gather and from where.

Let’s start with the WMI connection configuration. Right click in the BIDS Connection Managers pane and click on New Connection… which will pop up the Add SSIS Connection Manager dialog.

Select WMI and click the Add… button. The WMI Connection Manager Editor will be displayed.

Configuring the WMI connection for the local computer is fairly simple. Set it up as shown above with the name of your choice of course and click OK. This configuration will only work if the account that the package will run under has rights to read data from the WMI provider. If there are security issues reading WMI data, other credentials will need to be provided or the account that runs the package given the rights to query the WMI provider.

In the case where the server being queried for WMI data is not the computer the package is running on, credentials will need to be supplied. If the account that is running the package has access on this other computer, then checking Use Windows Authentication should work. When the other computer is in a different domain or the account running the package does not have access, then a windows user and password will need to be supplied. This can be a domain account or a local windows account on the other computer. Always supply the user name with a domain or the other computer name. In highly secure environments remote WMI security access may need to be configured on the other computer. WMI security configuration will not be covered here.

The Namespace property can usually be set to \root\cimv2. There will be cases when the WMI class to be queried is under a different WMI namespace. These other namespaces can be browsed WMI CMI Studio or Scriptomatic 2.0. I also recommend these tools to browse the classes available and see the properties and values. A sample of the namespaces available is shown below.

Now that the connection is configured, work can begin on the WMI Data Reader Task.

Create a WMI Data Reader Task on the Control Flow designer pane.

Open the WMI Data Reader Editor. The SQL Server 2005 version is displayed. The SQL Server 2008 version has an additional editor called General when the Name and Description of the task can be set.

Let’s setup the WMI Options.

The WmiConnection has been set the one configured above.

WqlQuerySourceType is set to Direct Input, since the query will be statically defined in the WqlQuerySource property. WqlQuerySourceType can also be set to File connection or Variable. Use File connection if the query is defined in a file. Variable would be used if the query needs to be dynamic. A dynamic query is usually required if the WMI Data Reader Task is placed in a looping container or the query is dependent on the results of a previous task in the package.

WqlQuerySource needs to be set to a valid WQL query. In order to get the drive information, there are 2 different WMI classes that could be queried. The first, Win32_LogicalDisk, only seems to handle local attached storage. The query to get the drive information is:

SELECT DeviceId, Size, FreeSpace FROM Win32_LogicalDisk WHERE DriveType = 3

The other class to try is Win32_Volume. This class would probably need to be used to get the information for mount points. (Thanks Dave Levy!) Here is the query:

SELECT DriveLetter, Capacity, FreeSpace FROM Win32_Volume WHERE DriveType = 3

DriveType 3 limits the query to fixed drives. DriveType 2, removable drives, may need to be included. Add OR DriveType = 2 to the queries if all the drives expected are not being returned.

OutputType should be set to Data table so that the results from multiple drives can be returned. The data will end up in a variable that we will define later. The other values for OutputType are Property name and value and Property value.

OverwriteDestination should be set to OverwriteDestination. The other values are Keep Original and Append to destination. The SQL Server 2008 editor disables this property which really makes more sense when writing to a variable.

DestinationType should be set to Variable. The other value is File connection. Use File connection when the data needs to be written to a file. Changing this property changes the options in the next property Destination.

Destination should be set to a variable of type Object. The variable DriveInfo can be created from here or beforehand from the variables browser. Remember to watch the variable scoping. It should scoped at the package level.

That completes the configuration of the WMI Data Reader Task.

Data Flow Tasks

The contents of the DriveInfo variable need to be processed with a Data Flow Task. This will be accomplished with a Script Component used as a data source feeding an Oledb destination that will write the drive information to a table.

Create a Data Flow Task on the Control Flow designer pane and connect the WMI Data Reader Task to it.

The Script Component

Switch over to the Data Flow designer pane and drag a Script Component object from the Data Flow Transformations section of the Toolbox. The Select Script Component Type will appear. The Script Component can be a source of data, a transformer of data or a consumer of data. Choose Source as this Script Component is going to provide drive information in a format that an Oledb provider can consume and click OK. Rename the Script Component Get DriveInfo. All of the images below came from BIDS 2005. BIDS 2008 is slightly different.

Rename the Script Component Get DriveInfo.

Double click on the Script Component to open the editor. In SQL Server 2008 the Script editor is the default. Click on Inputs and Outputs first to display that editor. Now let’s add some output columns. Rename Output 0 to DriveInfo and add the columns:

  • Drive, data type string [DT_STR]
  • DriveSize, data type float [DT_R4]
  • FreeSpace, data type float [DT_R4]

Here are the column property details for the Drive column.

It is important to define the output columns first so they show up in the OutputBuffer object while the coding the script. The output buffer will be named DriveInfoBuffer in this case.

Switch Script Transformation editor to Script mode by clicking on Script in the left pane.

The ReadOnlyVariables property in the Custom Properties section needs to be set to User::DriveInfo so that the Data Table filled by the WMI Data Reader Task can be processed by the Script Component.

Now it’s time to add the code to get the drive information and send it to the output buffer. Click the Design Script… button and add the code below. The sample code is for the Win32_LogicalDisk query. If Win32_Volume is being used, change the column names to match that WMI query. Note that the User::DriveInfo variable has to be converted to a DataTable type. Here is the code in VB.Net for SSIS 2005.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()
        Dim dr As DataRow
        Dim dt As DataTable

        dt = CType(Variables.DriveInfo, DataTable)
        For Each dr In dt.Rows
            DriveInfoBuffer.AddRow()
            DriveInfoBuffer.Drive = dr("DeviceId").ToString()
            DriveInfoBuffer.TotalSize = CType(dr("Size"), Single)
            DriveInfoBuffer.FreeSpace = CType(dr("FreeSpace"), Single)
        Next
    End Sub

End Class

Here is the code in C# for SSIS 2008.

Make sure to set the ScriptLanguage property to Microsoft Visual C# 2008 in BIDS 2008 before clicking the Edit Script… button and pasting in the code below.

using System;
using System.Data;
using System.IO;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void PreExecute()
    {
        base.PreExecute();
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void CreateNewOutputRows()
    {
        DataTable di = (DataTable)Variables.DriveInfo;

        foreach (DataRow dr in di.Rows)
        {
            DriveInfoBuffer.AddRow();
            DriveInfoBuffer.Drive = dr["DeviceId"].ToString();
            DriveInfoBuffer.TotalSize = Convert.ToSingle(dr["Size"]);
            DriveInfoBuffer.FreeSpace = Convert.ToSingle(dr["FreeSpace"]);
        }
    }

}

Now we need to add the destination to store the drive information. Grab the OLE DB Destination from the Data Flow Destinations of the Toolbox and drag it onto the Data Flow designer and name it Put DriveInfo. Connect the output of the Get DriveInfo Script Component to Put DriveInfo.

 

In the OLE DB Destination Editor, set the OLE DB connection manager to the DBA_Tools connection created earlier and pick the DriveInfo table from the table or view drop down.

Here is the create table statement for the DriveInfo table used in the earlier parts of the series.

CREATE TABLE dbo.DriveInfo
( Drive VARCHAR(255)
, DriveSize FLOAT
, FreeSpace FLOAT)

Run the package and all the drive information for the computer should be stored in the DriveInfo table.

That concludes the Drive Information series. This is great pattern to follow to get any information via WMI and store it in a central database for server reporting. Have fun experimenting.

Un-SQL Friday 001 : Branding for Business

I really like Jen’s (MidnightDBA,#sqlawesomesauce) idea for the first Un-SQL Friday. I was struggling with the topic and had to read some of the posts from earlier today. No one had written from a business perspective yet, so I decided to write about my journey and branding for my company, Gerasus Software. I own and operate a single person ISV formally known as a microISV. Just because most of the branding effort is geared toward building the business does not mean I have no interest in helping the SQL Server community.

Let’s go back to 1998. At that time I was a DBA in Clearwater, FL. A group of us decided to start a user group. The Tampa Bay SQL Server User Group was born. Buck Woody (Blog|Twitter) and I worked together at the time and he was involved. I presented quite a bit back then. Information was scare. Support from Microsoft was limited. It was hard work. I was active in the replication news groups. That was defeating because someone from the UK would answer all the questions before I woke up. I also questioned why am I doing this? I moved on to another position and disappeared from the group. Buck took over as President of the group and you know the rest of that story.

Fast forward to 2003. I had moved back home to the Chicago area and was working on a large server migration. There were several hundred DTS packages that needed to be examined to determine how to work around connection objects with ip addresses and names that pointed to the old servers. I needed a tool that would search DTS packages. I start throwing together some VBscript to do the searching. It worked well enough. I looked for a commercial product, but found nothing. I had also been reading how Redgate got started and was inspired. Since there was nothing to search DTS packages on the market, I decided I would write a utility and sell it. Gerasus Software and DTS Package Search were born. I have a strong development background and really enjoy coding UIs. It gives that visual gratification that writing SQL is missing. I finished and launched the version 1.0 of the program in 2006. After adding improvements I went about adding SSIS support. Version 2.0, SSIS-DTS Package Search was released in 2009. Now I could come up for air start focusing a little more on branding.

Seth Godin says you should tell a story and here is the story about the name Gerasus. It is made up word. It’s difficult to come up with a company name that unique. This one is personal and says something about me. My parents are German, there is the “Ger”. I was born in Australia, adding “aus” originally and I grew up and live in the US. So we had Gerausus which was a bit clunky. It was shortened it to Gerasus. People do have trouble pronouncing it. The “a” is short like sat. Not so much of a branding win.

Since there was no other product like that on the market at the time, I could name it the words people would search on Google. Branding win.

The support from Microsoft is huge now. Visit Bizspark and see. I am fortunate to be enrolled in that program and get those benefits. Plus I get to play with all the latest stuff.

I discovered twitter and have a personal and business account. I really enjoy answering question on the #sqlhelp tag. I do some marketing on twitter, but mostly try to help and have fun. I started to blog and keep the posts technical and helpful, not just about the product. I started speaking again with a presentation on SSIS which is definitely about branding. I am also putting one together on Powershell, because I find it interesting and it will help me to learn more about it.

I went to my first SQL Saturday in Chicago this year and gave away copies of SSIS-DTS Package Search. The organizers Wendy Pasterick (Blog|Twitter), Aaron Lowe (Blog|Twitter) and Ted Krueger (Blog|Twitter) were very supportive. I also was fortunate enough to attend SQL Saturday 40. I’ve since started donating to copies of SSIS-DTS Package Search to every SQL Saturday since midsummer. It’s been great working with the organizers. I’m glad that I can support SQL Saturdays with prizes and help make them better and bigger. I will be much more involved in the next Chicago SQL Saturday.

Being the owner of a company has force me to learn many new skills and one of those is branding. It’s important. It’s been a great year for the company and I look forward to continuing to help SQL Saturdays and the SQL community at large.

So now that I’ve done all this brand building for me, my company, Jen and Sean, I have to ask a question. What is the pain point in your work life? Does someone make a solution for it? No? Then start coding, start building your brand and get that product out there. The community is here to help you and Microsoft is here to help you. Hope to catch you in the DBSsAtMidnight UStream chat room.

Follow

Get every new post delivered to your Inbox.