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.
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:
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.
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:
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:
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:
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:
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.
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,