Update Deployed Database Applications

The downloadable example includes a sample Contacts application. As the Contacts application is improved and new application versions are released, deployed sites can be maintained by creating and publishing a server update. Application owners at each site can initiate an update process that will check the public location for a published server update. If an update exists, the site owner can choose to apply the update. Server updates modify the site database, and place a client update in the site database server. As each site client starts the application, new client updates are automatically discovered and applied.

The example consists of a single SQL Server database and the client Contacts .Net application. The Contacts application has no other network resources available at the site besides the SQL database connection to the site database.

The Updating Scheme

To implement an update solution, the Contacts application is extended to provide a feature that allows the application administrator to check for server updates from a public web server over the internet, where server updates are published. The Contacts application will also check for client updates from the site database server.

Server updates maintain the database, and also place new client update packages into the database. To support the updating scheme, two tables are added to the database. A DbVersion table which maintains the version stamp of the database, and a ClientUpdates table which stores updates for the client application. In addition to performing the database maintenance necessary to support the new application version, each database update will update the version stamp and place any new client application update packages into the ClientUpdates table.

Database Security

A privileged connection is required to perform database maintenance during an update. The method used to provide the update process a privileged connection will vary based on the security environment. In this example application, a privileged connection string is passed into the update from the host Contacts application as the update is applied.

The following actions are performed during a server update. This update process is defined in the ContactsServer.aup AppLife Update project file.

1. Create a db Connection
2. Enter Single User Mode
3. Backup the database
4. Create a SQL Transaction
5. Update the database version stamp
6. Execute the update.sql maintenance script
7. Add the client update to the database
8. Commit the transaction
9. Exit Single User Mode
10. Close the db Connection

Step 1 – Create a db connection. The first step in the update reads the connection string that was passed from the host application and creates a SQL Database connection. This connection object is added to the Shared Properties collection so that it can be used by other actions in the update.

Step 2 – Enter Single User Mode. The database is placed into single user mode, forcibly disconnecting current connections. With exclusive access, the update can successfully update the database. In the event of an update failure, the database is placed back into multi-user mode in the update engine Rollback stage.

Step 3 – Backup the database. This is an optional step that should be done if any non-transactable maintenance is performed on the database. If the update fails, all of the work performed will be rolled back by the SQL transaction. This backup is made as a safety precaution to prevent any possibility of data loss.

Step 4 – Create a SQL Transaction. The transaction is added to the Shared Properties collection and is used by the actions that perform the database work. In the event of an update failure, the SQL transaction is rolled back in the Rollback stage.

Step 5 – Update the database version stamp. The DbVersion table is updated using the transaction created in step 4 to reflect the new version stamp.

Step 6 – Execute the update.sql maintenance script. This action reads the update.sql file from the update package directory, parses the file, and executes the T-SQL using the transaction created in Step 4. While preparing for an update, all database changes are scripted and placed into the update.sql file. When the server update package is built, the update.sql file is packaged into the update.

Step 7 – Add the client update to the database. The client update is built before the server update, and consists of a Director.Xml file and an update package file (x.x.x.x.zip). These two files are added to the ClientUpdates table using the transaction created in step 4. Note: If other network resources are available in your application scenario, placing these files on a local server share or intranet http folder location would be a preferable alternative.

Step 8 – Commit the transaction. The transaction created in step 4 is committed.

Step 9 – Exit Single-User Mode. The database is placed back in multi-user mode. Clients can now check for, and find the new client update package just placed in the database.

Step 10 – Close the database connection created in step 1.

When the server update is built, the appropriate previous version(s) are set as the versions to update. The previous versions that the update targets is determined by the database version that the update.sql script expects.

The Client Update replaces the Contacts.exe application assembly. The AppLife Update controller requires an update folder with which to check for updates from. The update folder can be any local or network folder, so the client update process creates a temporary update folder on the client system, retrieves the Director.Xml file from the server ClientUpdates folder and places it in the local update folder. The AppLife Update controller is configured to use the local update folder. If an update is discovered, the update package is retrieved from the ClientUpdates table and placed in the local update folder. The AppLife Update controller can then apply the update.

The client application checks for updates immediately upon start up, and if an update is present, the client is updated without user intervention. This keeps the client version synchronized with the database version.

With AppLife Update, you can quickly and easily implement a reliable automatic updating solution for deployed database applications. The flexibility of the AppLife Update Controller, as well as the Update Actions provides the infrastructure to design and quickly implement an update process for just about any .Net application environment.

The server update project makes use of the Dynamic Code action to perform much of its updating activity. This is primarily because the built-in provider-independent database actions do not support non-transacted database work (such as backups and single user mode). Using the Dynamic Code action, the limitation of the built-in database actions is easily overcome. To support easy-reuse of the actions in this example, we use the custom action infrastructure. Each of the dynamic code actions has been converted to a custom action and added to a custom action assembly. The assembly, source code, and a functionally equivalent update project that uses the actions are included in the addendum download.

For answers to questions about this example, please contact Kinetic Jump tech support. techsupport@kineticjump.com

Download AppLife Update Here