I have worked in development as a release manager, build engineer, setup developer and as an application packager in large corporations.
This is a review of the best (and worst) conceptual and real-world features of MSI.
The most common design problems found in MSI files are presented as a separate answer below.
In plain language the really important benefits of MSI are: 1) uninstall is always included and available, 2) logging is great and standardized, 3) what an MSI file does is (semi-)transparent, 4) setup customization is done in a standardized way (transforms), 5) there is no need to mess with temporary admin-rights since the install runs elevated via advertisement, 6) silent install / uninstall via management tools works well, 7) there is full rollback support for failed installs (some qualifications, read below), 8) the MSI file lends itself to both inspection and validation for consistency and logical validity (see example), 9) updates are standardized types (though complex), 10) the extraction of files from the msi is a built-in feature, 11) the Windows Installer command line, msiexec.exe, features very fine grained control of how the installation sequence should be performed, and all options work with all standards compliant MSI files.
In the real world I have found less successful aspects to include patching (very complex), MSI-GUI (plain features, quite complex, lacks flexibility), resiliency (may cause hard to debug self-repair problems), and the overall complexity of dealing with the technology for beginners (high complexity of basic operations at times - for example upgrades, GUI and the many interacting details cause unexpected results, etc...).
MSI provides a comprehensive framework for the installer, which crucially also includes the uninstall and options for silent running. Certain deployment smells are actively discouraged by design, such as 1) downgrading versioned files, 2) not providing a proper uninstall routine, 3) not supporting silent installation properly or 4) other crucial and recognized deployment flaws seen with older deployment technology. The installer GUI is standardized with built-in features to support silent installation and uninstallation which can be triggered remotely.
These features alone constitute a massive improvement over previous installation technologies which treated uninstall and silent running haphazardly - perhaps the most important features for corporate deployment along with remote package management.
Transparency (open installer format)
An MSI file is essentially a stripped down SQL-Server database stored as a COM-structured storage file - essentially a file system in a file or a collection of data streams. This is the file type used in Microsoft Office documents, and it yields a standard format that can be reviewed and inspected - a huge issue for large corporations.
With the exception of compiled custom actions an MSI file is a white box. If the setup changes something crazy such as the system-wide network settings, you can actually see it using the appropriate tools. The notable exception is compiled custom actions - which are black box. Windows logo requirements require custom actions to be annotated to explain what they are doing, but this is often ignored by setup developers.
To determine what such compiled custom actions actually do in a technical sense, a setup capture is necessary. This is hardly ever done in my experience. It is more common to contact the vendor for information if the software needs approval for corporate deployment, and then it might be the application itself which prevents its use, and not just the setup.
An MSI can be customized via transforms to fit an organization's needs and standards whilst still allowing interoperability with the vendor's installer updates. You don't change the installer itself, you create your customization in a separate, organization-specific file called the transform (.mst file). You are free to disable custom actions and in general anything in the installer, and "black box" custom actions can be approved by contacting the vendor for explanation or even monitored during installation using setup capture technologies to verify what is happening. The transform files are also sometimes used to localize an MSI file to different languages. Several transforms can be applied to a single MSI.
Management and reporting
Windows Installer maintains a comprehensive database of all items a product has installed in the registry (HKEY_CLASSES_ROOT\Installer - never change anything here directly! Inspection only - that goes for experts too). You can reliably determine if a product is installed, what features were installed, and what file versions were installed. In addition you can get a list of any patches that have been applied to the base product, if any. You can access this database via API's supporting Win32, COM or .NET using a variety of scripting, configuration and admin tools.
Security (temporary elevated rights)
MSI also encompasses "elevated rights" principles which allows a restricted user to trigger the install of a product that requires admin-rights to install. This is part of the "advertisement feature" which allows an administrator to make installers available to users without actually installing them on all workstations. The users may trigger the install of the product themselves, or the install might be controlled by a dedicated deployment system such as SCCM (larger companies normally). There is no need to mess with temporary admin-rights to get things working which is often the case with legacy installers.
The comprehensive installation database also ensures that you have full overview of installed patches and therefore a possibility to detect security vulnerabilities.
MSI files can be checked with validation rules to ensure it is in compliance with a number of internal consistency rules (referred to as ICE). Corporations can create their own ICE checks to enforce special corporate rules and requirements. This helps greatly with QA. The reason validation is possible is due to the self-referencing nature of relational databases and the associated database schema. The database has to be internally consistent and compliant with its own schema with regards to foreign keys, data types, field width, schema version, etc... Validation also goes beyond this and is capable of detecting genuine logical flaws and errors in the package, not just formatting and type flaws.
The admin install feature of Windows installer provides a standard way to extract the source files from an MSI (here is some additional information on this topic). These source files can then be put on a share and be available to all workstations for installation. This ensures repair, uninstall and modify operations complete without requesting the installation media on CD or similar. This is particularly important for patching and update operations which may require access to the old versions source files in special circumstances.
There are also common problems with this resiliency feature. Most administrators have experienced machines with cyclical self-repair cycles that never seem to stop. Follow the link for a long list of causes of this problem.
The installation of an MSI file will normally trigger the creation of a restore point. Furthermore all files and registry items replaced or overwritten during the installation will be saved and restored if the install fails to complete, barring any changes done in custom actions.
Custom actions must implement their own rollback support for Windows logo compliance. This is often ignored, but involves creating a second custom action to undo the changes made by the main custom action.
Rollback ensures that the workstation is left in a stable state even if the install should fail. The actual rollback script is stored in a hidden folder directly on the system drive - generally C:\Config.MSI, and it contains files with the extensions .RBS and .RBF - Rollback Script Files. As you might expect poorly designed MSI files can violate the built-in features of Windows here, see my other post in this thread for more details.
There are ways to disable rollback and speed up installation. Not generally recommended, but here are details on the MSIFASTINSTALL property and DISABLEROLLBACK. This is a complicated feature, but here is a quick rollback overview.
Patching & Updates
Though highly complex, patching in Windows installer is fully managed and registered on the system so that a systems security state can be determined by checking what has been installed. Updates are standardized to a few basic variants, and this allows updates to be performed with a higher degree of certainty provided you are able to handle the complexity involved. Deployment systems will be able to report what updates failed and why.
In a subjective view patching works well for 2 basic uses: 1) small hotfixes for delivered products, and 2) patching an installed product to fix its faulty uninstall sequence that prevents a products clean uninstall.
A patch is just a delivery mechanism for an update that is already working. As such it is just a container that is more complicated and error prone than the original setup itself. The number one rule for a patch is that it must be smaller than the original MSI or there is no obvious reason to deliver a patch at all. A patch can get huge quickly if it targets multiple product versions.
Logging (verbose indeed)
Windows Installer provides a standardized logging feature which is greatly superior to previous incarnations, though almost excessively verbose. Log files can be deciphered using log analyzers, and custom log levels can be used to eliminate generating too large log files with unnecessary information. For debugging purposes verbose logging is extremely useful. See Rob Mensching's blog for a good manual way to read an MSI log file. Here is a sample command line that performs verbose logging:
msiexec.exe /I "C:\Installer.msi" /QN /L*V "C:\msilog.log"
Not everything is superb about Windows Installer. Its complexity can be baffling at times, but for large corporations MSI files are vastly superior to any other form of deployment when you take into account the list of benefits above.
New installer paradigm (the huge SQL statement)
To understand the new "paradigm" it is important to understand that MSI is intended as a declarative description of what is going to happen on the target system, rather than a fixed sequence of events. I suppose you can think of it as a huge SQL-statement. For example you declare items you want added or modified to an INI file. As the installation runs changes are tracked and rollback is available so that changes can be reverted if the installation fails. This really works like "automagic", and is reliable when done right.
Custom Actions (the usual suspects)
It is a huge headache for experienced MSI developers to see people rely on complex, unreliable custom actions for functionality that is better implemented with built-in MSI features. A significant share of all MSI errors and rollback problems are caused by erroneous custom actions, and most other errors are caused by erroneous use of the MSI design (see separate answer for list of common MSI errors).
In addition to the built-in MSI features, more and more custom functionality is now available via new framework such as Wix - the XML way to compile MSI files, so there is less and less need for complex custom action logic for most operations.
Wix (the new and best MSI solution)
Read this Wix quick introduction for a description of the new XML-based way to compile MSI files. The text based source files provide much better source control than before. This is a free, open source toolkit that is highly recommended. There is no better way available to make MSI files.
N.B: See elsewhere in the thread for a quick rundown of the common design problems with MSI files - it is very incomplete, but should be worth a read. I didn't want to add that to this reply since it isn't 100% related, but for real world use it is a crucial topic.