Friday, November 18, 2011

Customize Your Setup Experience With an MST (sorry, no funny robots…)

An unfortunate, let’s call it a “mishap”, recently had me digging through an MSI (Windows Installer) package to see if I could find the source of the “mishap”, and while this didn’t really have anything to do with SQL Sentry, it did remind me of helping one of our larger customers with automating deployment of their own flavor of setup for SQL Sentry.

I’m going to go through a method you can use to create your own personalized setup experience for a Windows Installer based setup package, but I do want to start with a short disclaimer. There are tools I’m going to talk about that can be used to really back yourself into a corner if you aren’t careful. Before trying any of this at home just make sure you have good backups of your original files, and a nice test environment to use while you work out the details.

What is an MST?

An MST is a transform that can be applied to a Windows Installer setup package (MSI). MSI packages are their own little database, and with the right tools, you can change or manipulate the data in the various tables of the MSI package. Creating a transform lets you apply differences at runtime vs. altering the package itself.

What you need

First, you will obviously need your MSI package. My examples are going to be using the SQL Sentry setup, and you can download that from www.sqlsentry.net/downloads if you have an existing license, or you can sign up for an evaluation at http://www.sqlsentry.net/download-trial/trial.asp. The setup is wrapped in an exe, but that isn’t a problem as we’ll see in a moment.

Next, you will need a tool that will allow you to peer deep into the soul of the MSI and do funny things to it. Microsoft is very good at that sort of thing, and provides us with a tool called Orca with the Windows SDK. You can find information and download links on the MSDN article that covers Orca.exe and its uses here.

Finally, you need a plan. Firing up Orca with your mind set on modifying the developer’s well tested setup package is just not recommended in most cases. There are a few good reasons to do it, and I’m going to be demonstrating one of those, but make sure that you’re going in with a specific goal in mind.

Our Plan

SQL Sentry comes with all of the software bits rolled into one setup package. You can choose to install just the server, just the client or both. Now and again an administrator will distribute the setup to one of their users intending for them to install the client, and they will accidentally install the server as well. It’s not really a problem for them to uninstall the server, but they could prevent this by providing a setup transform that excludes the server by default. That is what I’m going to provide instructions on.

But, this setup is an exe, not an MSI?

That is very true, so we need to extract the MSI. You can do this from the command line using the setup executable itself with the following syntax (I’m using the x64 install for SQL Sentry, the setup file name can vary based on your platform):

C:\> SQLSentrySetup-x64.exe /extract [path]

For me, the command looks like this: SQLSentrySetup-x64.exe /extract “G:\Files\InstallerTransforms”

There may be different ways for different packages to extract an MSI (among other things), but for SQL Sentry this is how it is done. If you are using something else, you may need to check your documentation or ask the vendor.

This will give you a file called “SQLSentrySetup.msi” in [path]. This is the MSI file we’re going to work with in Orca.

What do the insides look like?

Now you want to open the MSI file with Orca. I was able to simply right click the file and select “Edit with Orca”, but you can certainly use Start-> All Programs –> Orca (it has an icon that looks like a tiny Shamu!) and File-> Open if that is not available.

The first thing you might notice is a list of “Tables” on the left:

image

As I mentioned earlier, an MSI package is really just a small database. Most folks reading this are in the database industry, so this is really great. Already you’re dealing with a familiar adversary.

If you take a minute to click through the tables you’ll see they’re lain out in a familiar way with column headers at the top, and rows of values below in a grid.

Now, find the “Feature” table.

SNAGHTML1e90122

This is what we’re interested in. This tables controls the features to be included and their default behavior. You may recognize this as the following screen during a setup:

SNAGHTML7f98e7

Don’t change anything at this point, as that will be changing the base MSI, and we want to apply our change using a transform.

So what about this MST (again, no robots) thing?

Right, so we know how to get into the MSI, but how do we create a transform? That part is pretty easy. With the totally unaltered MSI file open in Orca, look to the menu options at the top. You will see a menu called “Transform”. Select Transform-> New Transform.

The kicker here is that at this point there is really not any visible difference between editing the transform and editing the actual MSI, so just for a sanity check (which I tend to need often) I go to the file menu. The “Save” and “Save As” options should be disabled, and the “Save Transformed As” should be enabled (this can be used to create a transformed version of the MSI if you want).

Now, we can make changes with confidence that we’re working on a transform and not the actual MSI database.

In the feature table, look for the Feature named “Server_x64” with Title “Server Service”. This is the one we want to fiddle with. The Level column tells us whether the feature is included by default. This is documented here. Sometimes folks have the values for this customized, but SQL Sentry does not. The values we can use are as follows:

    • 0: Not listed at all, and not available for install
    • 1: Installed by default (this is what is set already)
    • 2: Not installed by default

I’m going to set this to 2, so that they have it as an option, but they won’t simply install the server by accident because they didn’t know to unselect it:

image

Now I’ll just use the “Transform” menu to “Generate Transform…” to the same folder as my MSI file. I’ll call this one SQLSentrySetup_NoServer.mst.

Something nice in Orca that I should point out is that when you are working in a transform, the values that are different from the original will have a green border around them like this:

image

If you get into a situation where you are creating a very customized setup (as I was with the customer I mentioned earlier) it’s nice to be able to follow that crumb trail of green borders to know where you’ve been messing about.

So how do I use this?

Well, there you are then. You have a setup transform that will do exactly what I said it would. Please come again!

Oh, yes, there might be one thing left to do. I guess we do need a way to actually run the setup package with the transform applied.

That isn’t too difficult either. We just use a command like the following with msiexec:

C:\> msiexec /I [PathToInstaller] TRANSFORMS=[file1;file2;file(n);]

So for me this command looks like this: msiexec /I SQLSentrySetup.msi TRANSFORMS=”SQLSentrySetup_NoServer.mst”. I did not use a full path here simply because I was already running under the context of the location I wanted to work in. If you are just running your command prompt from the default location, you will want to ensure that you provide the full path to where you want to save the MST file. Also, notice that I only have one transform, but you can actually string them together separated by a “;” to apply several different transforms. Information on the command line parameters for msiexec can be found here.

Now, after running this, my setup wizard starts off like normal, but when I get to the component selection screen it defaults to looking like this:

SNAGHTML187d55f

I can pass this out to folks with the MSI and MST out on the network someplace. I can even schedule it for distribution with SMS or something similar.

Conclusion

With a little imagination, and some patience, you can come up with lots of ways to customize the setup package. Again, just remember to keep good backups, and don’t experiment on any system that matters.

If anyone is using SQL Sentry, and has a question about how to customize something specific in the MSI, just ask here or email me at jhall at sqlsentry.net. I’ll do my best to help out.

Until next time,
-JRH

Monday, April 11, 2011

Use Execute Action Parameters for Feeding Events to a Ticketing System

SQL Sentry Event Manager uses a system of conditions and actions for handling events. Basically, you define one or more actions to be taken as the result of a pre-defined condition being met. There are over a hundred pre-defined conditions that range from a SQL Server Agent job failing to a runtime threshold being exceeded for a T-SQL query, to the minimum value threshold for a performance counter being broken. Applicable actions can then be taken once the condition is met. Whether a condition supports a specific action really just depends on whether or not that action makes sense in that context. For example, “Run QuickTrace” is not an action for the “SQL Server: Deadlock” condition, because when a deadlock is detected the event has already occurred, and the QuickTrace would not contain anything relevant.

Two of the actions that are available for just about every action though, are “Execute Process” and “Execute SQL”. These two actions will allow you to execute either a Windows process, or a T-SQL command sequence on a specified server that is watched by SQL Sentry when the associated condition is met. This can be useful for several reasons, but what makes them even better is that ability to pass all of or specific parts of the event data as parameters to be used by that process or T-SQL batch.

A great use for the above feature is in automatic generation of technical incident records for ticketing systems. You can monitor for an event, and have it sent directly to your ticketing system for review by front line technicians providing all the information they need to make decisions without ever touching a keyboard, or even being present for that matter. For the rest of this post, I’ll go over a way to do just that, and hopefully it will spark some good ideas for anyone reading.

I’m going to assume that we’re already monitoring the server with SQL Sentry Event Manager, so from this point we want to choose a good candidate condition. One that is pretty common for a situation like this is “SQL Server: Agent Job Failure.” This condition is met when a SQL Server Agent job that is watched by SQL Sentry Event Manager logs a failure. We’ll enable the “Execute SQL” action for a backup job, as illustrated below.

image
Job, Condition, Action

At this point we’ve got everything enabled, and the SQL Sentry service will attempt to execute a T-SQL command batch whenever this job fails. We do still have something extremely important missing though, and that is what T-SQL to run, and where to run it.

We want to not only call a stored procedure that will create a support ticket, but also pass information from the event that can be used by a technician to locate and resolve the problem. In addition, we want to pass in a custom value of our own that will help the ticketing system classify and prioritize this incident.

The SQL Sentry feature that is going to allow us to do all of this is called “Execute Action Parameters.” You can read our documentation on this feature in our online help here: http://www.sqlsentry.net/help/ug/?System_Parameters.html, but we’re also going to spend some time setting this up and writing some code below.

For the purpose of this post, we’re going to keep it simple. I’ve created a small database called “Ticketing” and added the following table definition:

CREATE TABLE Tickets
(
    TicketID int NOT NULL identity(1,1)
        CONSTRAINT PK_Tickets_TicketID
            PRIMARY KEY CLUSTERED (TicketID),
    Title nvarchar(128) NOT NULL,
    [Description] nvarchar(2000) NOT NULL,
    ClassCD nchar(3) NOT NULL,
    PriorityHint tinyint NOT NULL
)

Obviously, a real ticketing system will require more information, but the basics are here, as well as two columns (ClassCD and PriorityHint) we can use to demonstrate custom user parameters.

I will also use the following stored procedure to populate the Ticketing table:

USE Ticketing;
GO
 
CREATE PROC InsertTicket
    @Title nvarchar(128),
    @Description nvarchar(2000),
    @ClassCD nchar(3),
    @PriorityHint tinyint = 0
AS
    
INSERT INTO [Ticketing].[dbo].[Tickets]
           ([Title]
           ,[Description]
           ,[ClassCD]
           ,[PriorityHint])
     VALUES
           (@Title
           ,@Description
           ,@ClassCD
           ,@PriorityHint)
GO

To this point it’s all very straight forward. We have a data table for storing ticketing data, and a simple stored procedure for populating it.

The interesting part comes when I define the T-SQL to run for my Execute SQL action. Below I will list the raw T-SQL as well as what it should look like in the actions configuration area of the SQL Sentry console. Then I will explain the special elements we see in the code and what they mean.

Entering the T-SQL command for the Execute SQL command is pretty easy. It’s done in the same area of the console application that was used to enable the “Execute SQL” action.

As seen below, you can select which server to execute the command on, and enter the command itself. Remember that only those servers that are monitored by SQL Sentry Event Manager will be listed in the “Server” drop-down.

image

I’m going to select my local server, since that’s really the only one I am monitoring right now, and enter the following for my “T-SQL Command Text”.

EXECUTE Ticketing.dbo.InsertTicket
    @Title = '<%ObjectType%> <%ObjectName%> Failure'
    ,@Description = '<%ObjectType%> <%ObjectName%> has encountered a failure on server [<%ServerName%>]'
    ,@ClassCD = '<%CLASS%>'
    ,@PriorityHint = 2

At first glance, this is just a normal execute statement, but there are some interesting elements within the string literals that go along with event processing in SQL Sentry, and deserve some additional explanation.

First, any time you see the following format <%…%> in this context, it is telling SQL Sentry that we want to pass some part of the event data as a parameter instead of just keeping the literal text. Take for instance “<%ObjectName%>.” Before this command is executed, any instance of “<%ObjectName%>” will be replaced with the name of the object that caused the event. In this case it will be our backup job “Backup User DBs – FULL.” These parameters are all self-explanatory of what information they will inject into the command, except for one. The “<%CLASS%>” parameter is special. The “<%CLASS%>” parameter is what we call a “User Parameter”, and I will go into more detail about that in a bit.

So, given this command, when my backup job fails, I should get a row in the Tickets table telling me that I need to investigate this tragic event. Now I’ll force the job to fail, and we can see it on the SQL Sentry event calendar, and I’ll also list the contents of the Tickets table.

image

Above you can see the job failure and the detail for the steps in the job. This job simply threw an error with the message “CLASS=PRD.” That really doesn’t mean much, but it becomes important when we look at the user parameter in a bit. Now let’s see what we end up with in the Tickets table below:

image

My execute action parameters were replaced by meaningful information about the event. I know what job failed, and what server it failed on, and I can go investigate what happened using my SQL Sentry calendar view.

If this were a database for a real ticketing system, I’m sure several members of the technical staff would have seen it, and it would be classified and prioritized. Notice the “Class CD” which I mean to stand for “Class Code” and “Priority Hint” which I intend to be used to hint at the priority level.

Let’s assume that “PRD” stands for “Production” in a list of codes somewhere, and that “2” is hinting at a priority of 2. You can see how I could send in a custom parameter from my event output, or just a literal value, such as the priority hint.

The error I raised in the job step is where I got the ClassCD from. I could just as easily have done this with a code comment, print statement, or other mechanism since all I need for this to work is for the NAME=VALUE (in this case CLASS=PRD) set to be somewhere in the output of the event. I put it in the error message here because I knew I was going to make the job fail, and it was just quicker. It’s really just a T-SQL RAISERROR statement. I have it listed below:

RAISERROR ('CLASS=PRD', 16, 1);

This is the user parameters feature that I was discussing earlier. I could embed any number of these into the output of an event, and use them to control processing of the specialized event data upon generating the support ticket. All that is required is that NAME=VALUE pair. Your parameter name will be in the following format: <%NAME%>. In my case it was <%CLASS%>.
 
And that pretty much covers it. You can imagine some of the things you can do with this. You can even get really creative, and pass the entire event message in to parse out pieces that we may not have thought about for pre-defined parameters.
 
Now, there is a second action that these parameters can be used with called “Execute Process”. This action can be used to execute a Windows process, but the parameters work the same way, except in that case they would be command line switches. Again you can find the details on usage in our online documentation.
 
I hope that this tutorial sparks some great ideas for SQL Sentry users, and as always the SQL Sentry support team is happy to help with your implementations of SQL Sentry features, so just ping us if you are interested in trying something like this out.
 
Until next time,
-JRH