SQL Server 2005 Express Edition - Part 12 - ClickOnce Deployment and Updates

December 10, 2007

In the previous installment of our series dedicated to SQL Server 2005 Express Edition, we presented the publishing options of ClickOnce-capable applications (leveraging functionality built into .NET 2.0 Framework) that involve User Instance-based databases, by reviewing the interface exposed in the Publish section of the project Properties window in Visual Basic 2005 Express Edition. In this article, we will take a closer look at the deployment process, demonstrating the impact of your selections on its characteristics.

As we mentioned earlier, there are two basic methods (referred to as "install modes") of making your database application available to clients. The first one, known as "online only", requires that an original publishing location remains reachable from a computer where the application is launched. Once a user clicks on a link to that location (formatted in the URL or UNC notation), published files are downloaded temporarily to the ClickOnce cache on a local disk and executed immediately afterwards. (This temporary area is located within the Documents and Settings\UserName\Local Settings\Apps\2.0 folder, where UserName is the Windows account of a user that launched the application). This approach considerably simplifies maintenance in a distributed environment and guarantees that only the latest code release is used. Its main drawback becomes obvious in scenarios involving roaming clients, which need to operate when disconnected from a corporate network. The other method (known as "online or offline" mode) addresses this dilemma by storing application and data files on the local computer permanently, according to settings defined in the Application Files dialog box (invoked by clicking on the Application Files command button in the Project Properties window). Since these files also reside in a uniquely named folder within the ClickOnce cache, any potential conflicts with non-compatible software are avoided. Furthermore, their location is specific to the application version (which, by default, is automatically increased every time the corresponding Visual Basic 2005 Express Edition code is published), eliminating the possibility of clashes between subsequent releases. If you work with a very large number of online only applications, you should keep in mind that they are subject to a quota that restricts not only their combined size but also imposes the limit on each of them (individually, none can exceed 50% of the total quota). The behavior is controlled (on a per-user basis) by the OnlineAppQuotaInKB entry under the HKEY_CURRENT_USER\Software\Classes\Software\Microsoft\Windows\CurrentVersion\Deployment registry key (its DWORD value expresses the cache size in KB), with the default set to 250MB, which should be sufficient in most scenarios (since data files are not taken into consideration in this case).

Operating in "online or offline" mode typically implies that, at some point, you will need to deal with an update to the existing version of your application. As you might recall, this is configurable via the Application Updates dialog box, where you can dictate whether the check for a newer release should occur prior or after the code executes (and enforce the minimum allowed revision). The former setting is a bit more disruptive, since it not only introduces a small delay after the application shortcut is selected from the Start->All Programs menu, but downloads and installs the software as soon as it becomes available (assuming you accept the default OK option within the Update Available dialog box). As the result, application assemblies, settings, and the manifest file get stored in their own directory structure, separately from its data (although all of them reside within the ClickOnce Cache).

Application updates introduce an additional challenge, which deals with preserving existing data. While it is possible to customize this process through coding, it is significantly easier to take advantage of built-in ClickOnce behavior. According to its rules, when a deployment of a next version takes place, all existing data files (included in the previous release or created subsequently by a user) are automatically copied over to folders hosting the new installation. If deployed data files have not changed between versions (this is determined by comparing their hash values), data migration is completed. Otherwise, the new ones are used instead of their older equivalents, which, in turn, are moved to a subfolder named .pre, in order to avoid data loss (note, however, that uninstalling an application will automatically remove its data directory).

If this mechanism does not meet your needs (as is frequently the case), you might want to exclude database files from being copied to the local computer (by assigning to them Exclude publish status in the Application Files dialog box of the Publish section in the project Properties dialog box) and employ an alternative means of making them available to your users. One way to accomplish this is by scripting the creation of a database (using the "Script Database as" option in its context sensitive menu within Object Explorer of SQL Server Management Studio Express Edition) and populating it with the desired values. The resulting code can then be incorporated into your application. An upgrade would involve developing a script that modifies the existing database (rather than replacing it with a newer copy); including a version check, that triggers its execution.

In general, your choice of deployment strategy will likely depend on the availability of a network connection (and its bandwidth) between the application source and a target computer. As we explained earlier, you can choose between several different methods incorporated into the Publish Wizard (accessible from the Publish section of the project Properties window):

• Copying installation files to removable media that is subsequently shipped to its destination. In this case, use the first page of the wizard to designate a local file system path of a staging directory, whose content will be burned onto a CD or DVD. When prompted on the next page how users will install the application, select "From a CD-ROM or DVD-ROM" option. You will be given a chance to decide whether you want to include a check for updates and designate either a file system path or a Web site (local or remote) as their source (ensure that this location remains reachable whenever the software is launched). If you decided to enable "For CD installations, automatically start Setup when CD is inserted" in the Publish Options dialog box of the Publish section in the project's Properties dialog box, the destination will include (in addition to application and data content) the autorun.inf file. Note that with this approach, the "online only" option is not available.
• Copying installation files to a local or remote file system share. This is similar to the first method, since you also have to designate a local or remote (in the UNC format) location where installation files will be stored. On the second page of the wizard, when asked how users will install the application, you need to pick the "From a UNC path or file share" entry and provide the same location you specified on the first page (this time, the UNC notation is required). In this case, you have the ability to make the application available in both "online only" and "online or offline" modes.
• Publishing installation files on the local instance of Internet Information Services selected from the Open Web Site dialog box (accessible via the first page of the wizard), which offers a graphical interface where you can point to a specific virtual directory (and provides the ability to use the Secure Sockets Layer protocol, if the Web site supports it). Unlike with two previously described methods, the target folder will include the HTML-formatted publish.htm (its default name can be altered using the "Deployment web page" text box in the Publish Options dialog box), which not only displays information regarding software details, but also allows you to install prerequisites (assuming you defined them in the Prerequisites dialog box) and launch the application. With Web-based deployment, it is possible to make applications available in both "online only" and "online or offline" modes.
• Publishing installation files on a remote instance of Internet Information Services - similar to the local IIS-based method as far as its capabilities are concerned (including support for two deployment modes and SSL encryption) but without an interface allowing browsing for a target.
• Publishing installation files on the local or a remote FTP instance - involves configuring such parameters as the target FTP server's name, port, and virtual directory, connection mode (active or passive), and authentication information (anonymous login can be used if the server supports it). Keep in mind that these settings are applicable strictly to the publishing process so you still need to come up with a method that would allow your users to obtain the source files (via removable media, a file share, or a Web site), which, in turn, allows you to choose between "online only" or "online or offline" installation mode.

With the default options, users will be prompted to install missing prerequisites, such as .NET Framework 2.0, Windows Installer 3.1, or SQL Server 2005 Express Edition (as defined in the Prerequisites dialog box accessible via the Publish section of the project's Properties dialog box) if they are not already present on the target computer. Note, however, that such actions (unlike application execution or access to a user instance and its auto attached databases) require administrative privileges, so plan their deployment accordingly. In addition, when distributing our sample application, it is important to realize that names of SQL Server 2005 Express Edition parent instances on the source and target computers have to match (this is controlled by the connection string defined in the application's config file and can be modified to match your needs).

Once the code is executing, it will initiate a user instance on the local computer, load the database included with the application, and display already familiar form that contains data grid with records exposed by the vProductAndDescription view. If you decided to configure automatic updates (as described earlier), subsequent application launches will be affected by the configuration you chose. For example, if you picked "Before the application starts" option, you might be presented with the Update Available dialog box we mentioned earlier, which gives you an option to install a new version of the application (assuming there is one available at that point). In addition, especially when using ClickOnce software deployment via Web-based methods, you are likely to run into security related issues, caused by insufficient privileges granted to the executing application. We will take a closer look into Code Access Security topics that deal with this behavior in our next article.

» See All Articles by Columnist Marcin Policht