Verify Database Process

Running the Verify Database Process for the first time

Overview

As of version 6.0.1408, database tables, fields and views can be created using the Govern New Administration (GNA) Verify Database process. This will facilitate implementation while centralizing the information of tables and fields into one location (MSGDatabaseStructureMods.xml) with TFS source safe control.
In addition, all indexes created and used by the Govern applications will be identified as such and will start with the letters GV or PK when the index is a primary key. This will enable the application to manipulate these indexes when needed. Indexes created by the customer will not be modified or manipulated in any way.

Preliminary Steps

Prior to beginning the database creation process, decide on a name that reflects the current version of the Govern, i.e. DB_NAME60. The 60 is an indication of the Govern release version. This will prove helpful in a multiple database environment.

Main Steps

1. Create an Empty Database

Using an application like SQL Server Management Studio, create an empty database. During the database creation process, make sure that a default username and password has been created. In addition this default username and password should have db_owner access permissions.
Security > Users > “userName” (double-click)
Click Memberships to display DB role memberships
These credentials will be used during the creation of the connection key in DeployEZ.

2. Generate a Deployment with DeployEZ

NOTE: Always ensure that you are using the latest version of the DeployEZ application. Current versions can be located at http://deployez.msgovern.com/

Using the DeployEZ application, create a connection key (CK) to the database that was created in Step 1. Select the applications that you are licensed to use in your deployment. During the generation of the connection key, access to the database will be tested.

NOTE: Ensure that the Govern New Administration (GNA) and the Govern Security Manager (GSM) are two of the applications that are included in the deployment.

Before Running GNA Verify Database Script…

Prior to the running of the Verify Database process, Release 6.0.1408, depending on your requirements, custom scripts incorporating the changes to datatypes of columns key tables may be required to be run.

NOTE: The following is an example of the type of script that may be run on the database. As these scripts are often customized to meet specific user requirements it will be necessary to contact Govern Technical

– START –
ALTERTABLE MA_MRA
ALTERCOLUMN FINAL_ADJ_PERCENT REAL
ALTERTABLE MA_MISC_STRUCTURES
ALTERCOLUMN FINAL_ADJ_PERCENT REAL
ALTERTABLE MA_LD_INFO
ALTERCOLUMN FINAL_ADJ_PERCENT REAL
ALTERTABLE MA_BLDG_INFO
ALTERCOLUMN FINAL_ADJ_PERCENT REAL
– STOP –

3. Run GNA

Once creation of the deployment is complete, locate the deployment folder and install the Govern Net Administration (GNA) release 6.0.
Typical Location:
\\%System_Root%\DEPLOYMENTS\Deployments\DBName_60_ServerName_SQL\GNAA

Running the GNA for the first time…

When the GNA is run for the first time a “Reset to Default” process is initiated. This process consist of the following steps:

• • Any required tables are created and empty rows are initialized with NULL’s.
  • A comparison between the version of the launched application and the database is performed; this is done by looking at the value in the NET_DB_VERSION column of USR_KEY_MASTER, i.e. (Table: USR_KEY_MASTER.NET_DB_VERSION).

Prior to fully launching the application, a message box is displayed warning of an inconsistency between the application and the database. A click on Yes on the dialog box will launch a Database Verification Process. and the creation of a “Govern Super User” role and a security synchronization.

Tables & Fields

During the initial Verify Database process the following verification steps will occur:

• “Govern Users” role exists, if not it is created
• “govern” User exists, if not create it with the password govern
• “govern” is in the “Govern Users” role
• “Govern Users” role has full access to GSM AND GNA

Example – Verify Database Process (ref. 6.1)

During this process the Verify Database process will create all tables and fields listed in MSGDatabaseStuctureMods.xml, and not present in the database used by the Connection Key (CK).

• Table info:
  • table name=”AC_AUDIT_INFO”
  • description=”Aircraft Audit Table”
  • action=”add” gnaaversion=”5.1.1304.0″ dbversion=”10.8″
  • neededbyesearch=”False”
• Field(s) info:
  • column name=”AC_AUDIT_ID”
  • type=”int”
  • length=”0″
  • nonull=”false” <!– if true, do not accept NULL values… –>
  • identity=”true”
  • action=”add” gnaaversion=”5.1.1304.0″ dbversion=”10.8″
  • description=”Primary Key – TX_AUDIT in USR_KEY_MASTER”
  • description=”Default …

Deleting a Non-Govern Index (unique index)

• The system will not allow the deletion of any Non-Govern indexes. Deletion can only be effected manually or through the Delete Indexes option in the Database Verification process in the Govern New Administration (GNA).

Manually Altered Data Types are not Changed

• In the database, columns with data types that were changed manually will not be changed during a Verify Database process; these fields must be altered manually. A warning will be thrown

4. Run the GSM

When the GNA process has been completed, run the GSM in order to give rights to users and create any other required users.
Note that the initial default setting of the GSM are as follows:
Full access is set to ALL applications, i.e. GIS, QT, etc.

 

What’s New!

NOTE: Changes are presented in reverse chronological order; i.e. newer to older.

Addition of VB6 Fail safe Notification for Verify Database Process

[Release 6.1.1803] Users that are prompted with a notification when they are upgrading from Govern for Windows to OpenForms are prompted with a dual confirmation message. See Addition of VB6 Failsafe Notification for details.

[Release 6.1.1706.0418 and 6.1.1707.0537]
RE / PP Tax Conversion Script to add Cycle Codes to RE and PP – See RE/PP Tax Conversion Scripts in the What’s New! section.

[Release 6.0.1412]
The following will occur when a new database is created:

• Make sure “Govern Users” role exists, if not create it
• Make sure “govern” User exists, if not create it with the password govern
• Make sure “govern” is in the “Govern Users” role
• Make sure “Govern Users” role has full access to GSM AND GNA

 

TROUBLESHOOTING

The following section contains information regarding issues that may occur during this process.

Error Screen starting GNA

ISSUE: Error screen displayed when launching GNA. When an error screen is displayed after an attempt to run the GNA, thoroughly review the error messages. Note the lines in the Database Alteration Errors screen. There is a permission denied error, an indication that the user access credentials do not provide Read/Write access to the database.

RESOLUTION: verify that the log-in has db_owner access permissions.
TIP: Users that are re-initializing a previously converted dataset will need to ensure that the NET_DB_VERSION value is set to NULL or else any further operations with the database will fail.

 

Related Topics

Database Verification

User Key Dynamics

System Data Dictionary

 

[/expand]

What’s New!

NOTE: Changes are presented in reverse chronological order; i.e. newer to older.

Implementation of Clustered Indexes (Release 6.1.1811)

NEW! As of Release 6.1.1811, clustered indexes have been implemented on some system tables. These indexes will be recreated during the next Database Verification Process. Due to the nature of the procedure, a significant amount of time will be required to perform the process. The recommendation is that this procedure should be performed over a week-end, or a period of low activity. Although all applications are able to perform without the use of clustered indexes, the implementation of this new methodology can improve overall system performance.

 

Addition of VB6 Fail safe Notification for Verify Database Process

Users that are prompted with a notification when they are upgrading from Govern for Windows to OpenForms need to be made aware that upon running the Verify Database Process, their database will be irreversibly changed. This means that they will not be able to perform a rollback that will allow them to once again run Govern for Windows on their database. As a result of the impact of this action, they must be prompted with a dual confirmation message. The message will remind them of the importance of performing a full backup of their database prior to running the process. They must also confirm that they are about to proceed with an irreversible process. Suitable notification to this effect have been added. Now when the user clicks START [>], and the system performs a compatibility check, they will first be prompted with the following message:

Versions of the application and the database are not compatible.
You must run a Database Verification before continuing. This will update your database to <updateVersionNumber>

WARNING: Ensure that you have made a backup of this database. This action is irreversible. Do you want to proceed? Their selection options are YES or NOUpon clicking on YES, they are presented with a confirmation message that reads as follows:WARNING: You have clicked on YES. After running this Verify Database process, you will not be able to perform a rollback, and you will no longer be able to run Govern for VB6 with this database. Click ‘YES’ to proceed. A click on YES will initiate the process.

RE / PP Tax Conversion Scripts to add required “RE” Cycle Codes

In Govern for Windows a Cycle Code was not mandatory. As a result of the architectural change in Govern for .NET, a Cycle code in Real Property (RE) and Personal Property (PP) is mandatory. The default Cycle Code is “RE”. A script will need to be provided in order to create a Cycle code and change all existing Data to this cycle code. See document below for details on the scripts.
103-ut-005_DB_Verification-RE_PP_Tax_Conversion_Script.pdf

New Process for Addition of System Columns
[Pending validation of release versions] When system columns have been added to the database, during the GNA Verify Database process, after the columns have been added, the process must scan all entities, and add a new attribute in all entities that use tables with added columns.
These changes are available in the following releases:

• 6.0.1608.x (6.0 Dev.)
• 6.0.1606.x (6.0 Rel.)
• 6.1.1608.x (6.1 Dev.)
• 6.1.1606.x (6.1 Rel.)

Standardized Storage of Boolean Data Types in Govern

In all releases of Govern, Boolean datatypes were stored in the database with three possible values:
FALSE = 0
TRUE = 1 or -1 (dependent on the version of Govern)
Newer tables predominantly used 1, whereas older tables used -1. In accordance with Governs’ ongoing standardisation process henceforth Boolean datatypes will be stored, WITHOUT EXCEPTION, as follows:
FALSE = 0
TRUE = -1
This standard will be carried out for all tables. This standard will be effective as of the following versions:

• • 6.0.1608.0019 (6.0 Dev.)
  • 6.0.1606.0023 (6.0 Rel.)
  • 6.1.1608.0043 (6.1 Dev.)
  • 6.1.1606.0042 (6.1 Rel.)

BA’s and Developers please refer to Confluence for additional details.

New Process in Database Verification Process

[6.0 / 6.1 Multiple Versions]

During the Database Verification process, at the ‘Check Column’ subprocess the application will now manage the description of fields in (Table:SY_DICT_DESC). The process is as follows, the Database Verification process reads the MSGDatabaseStructMods.xml file and reviews the SY_DICT_DESC. Each column is checked in the table to see whether the (ENG / FRA) descriptions exist, and if it needs to create (add) or remove (drop) them. In situations when a complete removal of a table is required, the process will remove any descriptions that are not connected.
Only the following fields are updated

• LANGUAGE
• TABLE_NAME
• FIELD_NAME
• DESCRIPTION
  • Uses the property ‘description’ limited to 50 characters; all additional characters are truncated
• DETAIL
  • Uses the property ‘description’

These changes are available in the following releases:

• 6.0.1603.0410
• 6.0.1606.0010
• 6.0.1608.0006
• 6.1.1603.0481
• 6.1.1606.0021
• 6.1.1608.0016

[Release 6.1.1509.117]

Need to perform a verify database process. This is to apply a correction made to change the NULL 0 in (Table: VT_SYSTEM) INVISIBLE.

[Release 6.0.1412]

The following will occur when a new database is created:
.Make sure “Govern Users” role exists, if not create it
.Make sure “govern” User exists, if not create it with the password govern
.Make sure “govern” is in the “Govern Users” role
.Make sure “Govern Users” role has full access to GSM AND GNA

Property to Re-create Indexes for Extended Tables

A new property called hasextendedtables has been added in the Tables section of the MSGDatabaseStructureMods.xml. This property is used to recreate indexes for extended tables like (Table: MA_BUILDINGS) that can support additional tables like MA_BUILDINGS_2 to MA_BUILDINGS_9.

Below is an example of the property in the MA_BUILDINGS table definition in the MSGDatabaseStructureMods.xml file.
<table name=”MA_BUILDINGS” description=”Mass Appraisal Building.” action=”add” gnaaversion=”4.7.3.0″ dbversion=”10.7″ hasextendedtables=“True“>

Currently this property has been added for the following tables:
· MA_BUILDINGS
· MA_LAND
· MA_MRA
· MA_INCOME PP_ASSESSMENT”

6.0 R and 6.1 R and in their development branches( 6.0 dev and 6.1 dev).
This new attribute is available in the following releases:

• 6.0.1606.x
• 6.0.1608.x
• 6.1.1606.x
• 6.1.1608.x

 

 

103-ut-005a

 

(0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this.Loading...