New releases of EQuIS often include schema updates with changes to the functions and tables within the EQuIS Database Schema (in Microsoft SQL Server). These database updates need to be applied as part of the upgrade by an administrator, using the SQL Database login with the full read/write credentials and the default schema set to 'dbo'.
The following article explains the database update process using EQuIS Professional. Alternatively, an API is available for updating databases. The API may be particularly useful if you have many EQuIS Databases hosted in Microsoft SQL Server. The API is available as a static/shared .NET method that can be called from .NET code or PowerShell. See the EQuIS Enterprise documentation for more details on using the API.
•Prior to updating the schema, backup the database. A backup on the same day is required to perform the EQuIS Professional Schema update.
•The ST_LOG table is intended to store logging information on a short-term basis. If your ST_LOG table is large, you may consider periodically truncating that table (e.g. prior to a database upgrade).
•Users must have db_owner permissions on the database in order to successfully update the EQuIS Schema.
•When a schema update is applied, records with the details are added to ST_VERSION and ST_MODULE (MODULE_TYPE=db). Records are only created when updates to a schema are available and applied.
1.While logged in to the database in EQuIS Professional as an EQuIS administrator, review the ST_MODULE system table with the MODULE_ID sorted by descending order (so that newer modules appear at the top). Additionally, filter MODULE_TYPE either by:
a.MODULE_TYPE = 'db' to see which schemas will be required. Except where the module associated with a schema is no longer in use, updates should involve applying each unique schema listed. If adding a new module for the first time, it will not have an entry in this table
b.MODULE_TYPE = 'Report.class' to see which reports may require republishing. See When to Republish Reports for a discussion of when this is required.
The VERSION_NUMBER column for 'db' modules shows strings structured as 'yyddd.yyddd', where ddd represents a calendar day number within a year, and the numbers after the period represent the build release date. For example, '19176.19214' would relate to the EQuIS 188.8.131.5214 build, released on the 214th day of 2019.
If a module's most recent VERSION_NUMBER has a '.yy' value indicating a release date of 2016 or earlier (e.g. containing '.16', '.15', '.14', etc.), additional files and steps may be required for your upgrade; please contact EarthSoft Support.
If unsure what within the database will require an upgrade, please send an export of your ST_MODULE table to Support.
2.Check the Database Schema page to find the source of your required modules.
3.Download the latest versions of all the required modules (except for Enterprise, if an EQuIS Online site is being used) from the EarthSoft Community Centre (ECC) Downloads Dashboard.
4.Unblock the files, then extract/install as appropriate.
5.Copy the schema XML/XME files from the requisite modules to the EQuIS Professional db folder (C:\Program Files\EarthSoft\EQuIS\db in a typical installation).
6.Back up the database the day of the updates prior to updating.
1.Launch EQuIS Professional and connect to a database server in the Backstage view. If you are already connected to a facility, access the Backstage from the File button on the EQuIS ribbon.
2.On the Connect tab, right-click on a database in the database list on the left.
3.Select Update by clicking from the context menu. The Update Database(s) window opens and lists the databases for which updates are available.
a.The available updates reflect the schema files (*.xme / *.xml) present in the directories below.
i.EQuIS Professional - C:\Program Files\EarthSoft\EQuIS\db\
ii.EQuIS Enterprise - C:\Program Files\EarthSoft\Database#\
b.By default, databases without a current backup are listed, but not available for selection. Backing up the database will enable its selection. A header message and tooltip provide information about the current update status of the selected database, and also warn users to backup databases within 24 hours of updating the schema.
4.Expand the database row of interest to review the available updates.
a.When each database row in the grid is expanded, each table, stored procedure, and view or function that needs to be updated for that database is listed. Each is listed by date and includes a comment providing more detailed information regarding required updates. Each object definition or update in the schema is defined by a tag similar to that shown below.
<version date="24 Aug 2004 08:10:12">
5.Right-click on the Module column or anywhere in the grid and select Module(s).
a.Check or uncheck the modules listed to select only the appropriate schema(s) for the update to apply. The Update function will only process those schema files with check marks in the corresponding check box.
b.Professional and Enterprise Schemas are required; both must be checked when updates are available.
6.Select and highlight the database of interest. Multiple databases can be selected using the Shift+click or Ctrl+click common to multiple selections with Windows.
8.Click Yes on the warning prompt if you are certain about proceeding with the update.
9.Click OK on the Update Status window that pops up to indicate success.
a.When working with multiple databases, the Database Update screen only shows a single confirmation message, regardless of how many databases are selected. The error message is: Successfully updated X of Y database(s). Databases that update successfully are shown with a light gray background.
b.Databases that do not update successfully are shown with a red background and the tooltip of the row shows the error message. Expand the row to see exactly what update caused the error.
10.Close the Update Database(s) window when complete.
Note: Significant updates may need an extended connection. If a timeout issue occurs, extending the timeout may be needed as shown in the Connection String Options section of the help article Connecting EQuIS Professional To a Database.
1.Review the error message(s).
2.Correct the issue(s).
3.Repeat the update process.
Copyright © 2020 EarthSoft, Inc • Modified: 29 Oct 2020