•Significant EQuIS Database Changes
New releases of EQuIS often include schema updates with changes to the functions and tables within the EQuIS Database Schema (in Microsoft SQL Server). Check the Schema Updates section of the build summary page in the Release Notes chapter for any changes to a build.
An administrator should apply any database updates during an upgrade, using the SQL Database login with the full read/write credentials and the default schema set to 'dbo'. EQuIS Online clients must contact EarthSoft (their Account Manager or Support) to perform an upgrade; the below instructions do not apply for those databases.
This article explains the database update process using EQuIS Professional.
Notes: •Prior to updating the schema, back up the database. A backup on the same day is required to perform the EQuIS Professional Schema update. Database updates cannot be reversed or undone, so it is essential to have a current backup before performing an 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). •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. |
Required Permissions
EQuIS Online clients must contact EarthSoft (their Account Manager or Support) to perform an upgrade.
A database administrator must use a SQL Database login with the following credentials:
•db_datareader, db_datawriter, and db_owner roles on the database
•default schema set to 'dbo'
•the ability to view, modify, and update tables in the database through Windows Active Directory (when Windows logins are being used)
Any of the following login types can apply the update provided that the criteria above are met:
•a Windows login via SQL Server (e.g. ORGANIZATION\MyUser)
•a SQL login via SQL Server (e.g. MyEQuISUser)
•an active (ST_USER.STATUS_FLAG = ‘A’) EQuIS Enterprise administrator login via an Application Level Security (ALS) role, where the SQL login used to set up the connection string matches the above criteria
Updating from a SQL Server login, if available, is preferred over updating from an Enterprise login. An Enterprise user may not know the database permissions associated with an ALS role. SQL Server logins can also specify Connection String Options such as increasing the connection timeout, which can help avoid timeout errors related to large changes.
If unable to identify your login type, consult the EQuIS Professional Login page.
Prepare to Update
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. The VW_DB_VERSION view shows this information without needing to filter or be an EQuIS administrator.
or
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 7.0.0.19214 build, released on the 214th day of 2019. The first number, 19176, relates to the final VERSION_DATE from the previous update for that schema.
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 builds 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 or, for per-user installation, %localappdata%\Programs\EarthSoft\EQuIS\).
6.Back up the database the day of the updates prior to updating.
Update a Database
1.If you have not already done so, launch EQuIS Professional and connect to a database server/site in the Backstage view.
2.Connect to a facility, then access the Backstage from the File button on the EQuIS ribbon.
3.On the Connect tab, right-click on a database in the database list on the left.
4.Select Update by clicking from the context menu. The Update Database(s) window opens and lists the databases for which available updates can be performed.
a.The available updates reflect the schema files (*.xme / *.xml) present in the EQuIS Professional directory (typically C:\Program Files\EarthSoft\EQuIS\db\ or, for per-user installation, %localappdata%\Programs\EarthSoft\EQuIS\db ).
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.
Note: Databases will not be listed in the "Update Database" window if: •The database is fully up to date •If the database does need an update, older schema files in the installation's "db" subfolder |
5.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"> |
6.Right-click within 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.
7.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 Update.
9.After clicking the Update button, a warning message explaining that the update is irreversible will display. This warning must be acknowledged (Yes) in order to complete the database update process. Click Yes on the warning prompt if you are certain about proceeding with the update.
10.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.
11.Close the Update Database(s) window when complete.
12.Check the Significant EQuIS Changes over Time section for any follow-up actions needed.
Note: Significant updates may need an extended connection. If a timeout issue occurs, extend the timeout, as shown in Connecting EQuIS Professional To a Database. |
How to Fix Failed Updates
1.Hover over each column of the red line in the Database Update Form to review the error message(s) in the tooltips. For further information, consult the equisdebug.log file, typically located in Documents\My EQuIS Work. Common issues include:
a.insufficient permissions, leading to an error in the first line of the update (see Required Permissions section)
b.updates time out prior to completion (see note box above)
c.fields required by that line of the update are not populated.
2.Correct the issue(s). If uncertain of the issue, send the equisdebug.log file and screenshots of the error to Support for assistance.
3.Repeat the update process.
Significant EQuIS Database Changes
These EQuIS 7 changes may involve extra administration or checks:
•Administrators should remove legacy license levels and roles, such as EQuIS Enterprise Gold, prior to an upgrade. Contact EarthSoft Support to confirm which licenses are no longer used and to request removal keys.
•Build 7.0.0.19046 and up require Microsoft .NET Framework v4.7.2 with ASP.NET.
•Build 7.0.0.19144 added requirements for the END_DEPTH field in DT_LITHOLOGY. Please peruse the EarthSoft Community Center Forum post, "Adding END_DEPTH as a new (required) column in the DT_LITHOLOGY table", and run the SQL query in the comments to find any problem records.
oIf no records are returned, then no action needs to be taken. If records are returned, correct the problem by populating either DT_LOCATION.TOTAL_DEPTH or DT_WELL.DEPTH_OF_WELL for the given location (i.e. FACILITY_ID and SYS_LOC_CODE).
•In build 7.0.0.19144+, Application Level Security (ALS) roles may need Viewer permissions added for the new Modules object type added for EQuIS Professional button control. Otherwise, buttons will be disabled for the EQuIS Professional users within that role.
•EQuIS Enterprise 7.20.2+ requires Owner permissions on Groups/Folders or Admin permissions to add or remove objects from folders.
•In EQuIS Enterprise 7.20.3+, users in the Admin role without an Application Level Security (ALS) role assigned will need that to access EQuIS Professional. Please ensure that admin users requiring Professional access have a single ALS role assigned.
•In EQuIS Professional 7.20.3+, any custom reports or formats must be recompiled with strong naming, as described in Approve Strongly Named Custom Assemblies.
•In EDP 7.22.1+, any custom formats must be recompiled with strong naming, as described in Securing EDD Formats.