Dana Epp has posted a request for information about the MSDE on his blog. He wants to install an app that that requires a named instance of the MSDE and wants to include the smallest footprint of the MSDE installer bits. On machines that already have the MSDE, he wants to install an additional named instance and wanted to avoid including the MSDE bits since it was already installed. With SQL Server named instances, you can’t do that. Adding a second instance isn’t a reconfiguration of the existing install of SQL Server/MSDE/Express, it’s a completely separate install. It’s considered to be a separate application by the installer and the OS.

I’m not sure why Microsoft went that way, but it’s tied to how the Windows Installer works. A Windows Installer file (.msi) installs just one install of itself. If you try running it again, the installer locates itself on the machine and brings the installer up in maintenance mode. It’s not designed to let you install multiple copies of a single application. If you want to do that, you have to go down the multiple instance transform route. A multiple instance transform file (.mst) is basicly the difference between two .msi files. You use the .mst file to change some of the values in a .msi file so that it gets considered to be a different installer by the Windows Installer runtime. By that’s a whole ‘nother story in and of itself.

Because each instance is essentially a separate installation, you don’t want to mess around with the source installer bits. Otherwise, you’ll make it difficult to install patches or upgrades. I’m pretty sure that’s why Microsoft prefers that you to use their executable to install SQL Server/MSDE/Express. If you use the .msi files, then the patches have be generated against the .msi files that you used. If you merged the SQL Server .msi into your .msi, then upgrades to SQL Server on that machine will required your .msi file. Ouch.

Each instance has to have a .mst file, it’s a one to one relationship. I’m not sure what MS is doing under the hood in the SQL Server install process, but my guess is that it’s creating .mst files at runtime when you specify an instance name. Otherwise it have to include X number of .mst files in the package, and if they did that some twisted person would try to install X+1 instances and break the installer. Creating a .mst file on the fly is doable, but it’s not something I would want to implement. When the user specifies a new instance you need to take your .msi installer file, copy it to a new .msi file. Then you modify that .msi file with the instance name, increment a property, and generate a new product GUID. You then generate the .mst file from the difference between the original .msi file and the new one. After that, you delete the new .msi file, it’s no longer needed. Then you call msiexec with the .msi file and the .mst with the appropriate parameters. I’m not sure that you could reliably generate installer patches (.msp files) under that scenario.

I’m not a fan of how Microsoft implemented the multiple instances. When I wrote the server end of e-Link, I used the Windows Installer to install the service. If the user wants to install a second instance of e-Link, they can use an administration utility that I wrote that can register additional instances of the service, but using the same executable. It provides the same functionality to the user, but only requires one set of files. This makes updating the service much easier as the patch only has to target one installed instance.

I can see why MS went along the single instance route with Windows Installer. It prevents the user from accidently installing 8 copies of “Hunt the Wumpus”. It makes it easy to allow installers to repair existing installations or modify the original setup. It does makes some tasks extremely difficult for installing. The big thing that I can see what would require multiple installs are web sites. There you could have multiple installations as people usually install a web a couple of times to try different settings, of have “live” and “staging” versions of a site.

If you want to install a web site with Windows Installer multiple times on the same machine, you have a few choices.

  • You could limit the install to a single instance. If the user wants multiple copies of the web site files, they have to do that part manually. We do that with the web gui end of e-Link and we document how to do it. Another way (which I have not tried) would be to rename the web site or virtual directory and it the physical name of the folder that it was installed into. Then run the installer again. It will come up in maintenance mode and you would select repair and it should install another set of files into the previously selected location. The draw to both methods is that the installer will repair or uninstall only the files it knows about. If you add anything manually, then you must remove it manually.

  • You could supply a fixed number of instance transforms. If you do this, you’ll need an intelligent front end application to your installer so that you can prompt the user for the instance name. Then you have to check to see if that instance is already installed. If not, then you get the next available instance number and see if you have enough transfoms to use one for this install. Lots of error checking required here, it’s not something I would want to do.

  • You could generate instance transforms at run time. Much of the same logic as the previous option, plus the additional work of creating the transform. I definitely don’t want to ever have to do this.

</ul>

This is why you don’t see many .msi files for installing web sites.
Trackback to Dana’s post