Upgrade Guide

This guide describes the steps required to perform the CData Virtuality Server upgrade by transferring all the settings to the new version. The steps are numerous, and some may be time-consuming, but the server downtime is much shorter than the upgrade process!

The process below is based on the assumption that the new version of CData Virtuality will be installed on the same server instead of the old one (the lowest version of the source CData Virtuality Server the upgrade was tested for is 2.0.51).

For complex installations, to minimize the downtime of the productive server, it may be advisable to install the CData Virtuality Server on a new server instance and swap the instances once the upgrade is done. This option is not covered in this guide, but you are welcome to contact our support team for more information!

Some steps are common and mandatory, and some steps are required only if your installation has certain features (to check if your installation has them, please see the Pre-update Checks section below):

It uses a non-embedded configuration database;
It uses LDAP authentication;
There were customizations to the old server's settings.

The process may seem complicated, but our support team will be here for you! We encourage you to notify our support engineers about the planned time for the upgrade and reach out to them in case of any issues or if something in the guide is unclear.

For your convenience of planning, all steps are divided into three stages: before downtime, during downtime, and after downtime.

Pre-requisites

Here's what you will need to upgrade your CData Virtuality Server:

CData Virtuality Server release package for the new version (ask our support team for it);
What's New document (our support team will provide it along with the release package);
Credentials for your CData Virtuality Server admin account (you need admin access to perform the upgrade);
You may also need a facade (a specially designed set of queries) to export configuration from older versions. To check if you need a facade file and get the correct file for your version, please see our documentation on the CData Virtuality Upgrade Utility (Exporter) and ask our support team.

Pre-update Checks

Configuration Database Type

The CData Virtuality Server comes with a bundled PostgreSQL database. However, your installation may be using an external database. You can check which database your installation uses by opening the dvconfig.conf.props(.bat) file in the dvserver/bin/ folder. If a database with port 54322 is configured, it is the embedded one; otherwise, it is an external one.

From Version 2.4, we recommend using the embedded database as default. But using an external PostgreSQL database also continues to be a possibility.

LDAP Authentication

Here is the procedure you can use to check which authentication type is being used:

SQL

SELECT * FROM (CALL "SYSADMIN.getAuthMechanism"()) a;;

History and Log Tables

All history and log tables (everything in the SYSLOG schema) will be reset after the upgrade. If you want to keep the old logs, back up the history tables by replicating them into tables in the Analytical Storage.

You can find detailed information on history tables in the Logs and History section of System Tables.

Customized Settings

If your server has been patched, the upgrade may need additional steps depending on the patch(es). Please consult our support team before proceeding with the upgrade.

Upgrade Process

Before Downtime
1. Check and review the What's New document for breaking changes (they are highlighted in red). No need to read through the entire document - look up your current version and compare it with the latest. If you are using the CData Virtuality Synchronization Tool, please note that syncing may stop until both systems are updated in case the JDBC driver changes.
2. This step is applicable only if you are using Netsuite, SAP ADS, SAP HANA, Teradata, or Vertica
  Due to license restrictions, some drivers are not distributed with the release package. Check the list of such drivers, download the drivers you need, and put them in place. For more detail, please see our documentation on JDBC drivers.
3. Check for failed data sources, connections, views, procedures, recommended optimizations (RecOpts), and jobs, and export them to a DWH table (e.g. dwh.update_log_failed_objects). This is important because the output of this check will be used after the upgrade for verification - to identify if any new objects got broken.
  To do this, you can use one of the following commands depending on your version of the CData Virtuality Server:
  1. Below 2.1.19:
    SQL
```
SELECT name, translator AS details, 'DataSource' AS type 
FROM "SYSADMIN.DataSources" 
WHERE failed = true 
UNION 
SELECT name, properties AS details, 'Connection' AS type 
FROM "SYSADMIN.Connections" 
WHERE failed = true 
UNION 
SELECT name, failureReason AS details, 'View' AS type 
FROM "SYSADMIN.ViewDefinitions" 
WHERE state <> 'READY' 
UNION 
SELECT name, failureReason AS details, 'Proc' AS type 
FROM "SYSADMIN.ProcDefinitions" 
WHERE state <> 'READY' 
UNION 
SELECT "MatchDescriptor" AS name, 'sourceState: ' || "sourceState" || ', sourceStateComment: ' || "sourceStateComment" || ', ' || "dwhStateComment" || ', lastReplicationState: ' || "lastReplicationState" || ', lastReplicationStateComment: ' || "lastReplicationStateComment" AS details, 'RecOpt' AS type 
FROM "SYSADMIN.RecommendedOptimizations" 
WHERE "sourceState" <> 'OK' OR "dwhState" <> 'OK' OR "lastReplicationState" <> 'OK' 
UNION 
SELECT "description" AS name, 'jobType: ' || "jobType" || ', lastExecutionStatus: ' || lastExecutionStatus || ', lastExecutionFailureReason: ' || "lastExecutionFailureReason" AS details, 'Job' AS type 
FROM "SYSADMIN.ScheduleJobs" 
WHERE "lastExecutionStatus" <> 'SUCCESS' 
ORDER BY type;;
```
  2. 2.1.19 and above:
    SQL
```
SELECT * FROM SYSADMIN.FailedObjects;;
```
4. Download the new CData Virtuality Server release and extract it to a temporary directory. If upgrading a CData Virtuality Server running on Windows using the zip archive, unblock the archive file before extracting it (right-click on the file properties and select 'Unblock' from the context menu).
5. Go to <new CData Virtuality Server release folder>/dvserver/bin/cli-export-1.0 and execute export.sh (on Linux) or export.bat (on Windows). This will generate the CLI file and the SQL files for export.
  Here are the calls for both operating systems:
  1. For Linux:
    BASH
```
./export.sh --username <account with admin permissons> --password <password> --host localhost --export-jboss-settings true --use-model-file true --use-maintenance-mode true [--dv-facade-file <path to facade file>]
```
  2. For Windows:
    BASH
```
export.bat --username <account with admin permissons> --password <password> --host localhost --export-jboss-settings true --use-model-file true --use-maintenance-mode true [--dv-facade-file <path to facade file>]
```
    After executing the command, remember to check that the files were produced and are not empty. If no export is produced, please contact our support team before continuing.
6. This step is applicable only if you are using an older version of the Data Virtuality Server, below 2.3.3
  Get the JBoss settings and export them to CLI files for buffer, concurrency, JDBC, and ODBC (if these are customized) and the login module (if LDAP authentication is used).
  Here's the command for this:
  SQL
  
  BEGIN DECLARE string result; DECLARE string smtpCli=''; DECLARE string bufferCli=''; DECLARE string concurrencyCli=''; DECLARE string jdbcdbcCli=''; DECLARE string loginCli=''; --Buffer Service settings bufferCli='connect'; LOOP ON (SELECT a.setting FROM TEXTTABLE('"buffer-service-connector-batch-size" "buffer-service-inline-lobs" "buffer-service-max-buffer-space" "buffer-service-max-file-size" "buffer-service-max-open-files" "buffer-service-max-processing-kb" "buffer-service-max-reserve-kb" "buffer-service-max-storage-object-size" "buffer-service-memory-buffer-off-heap" "buffer-service-memory-buffer-space" "buffer-service-processor-batch-size" "buffer-service-use-disk"' COLUMNS setting string )as a) as cur BEGIN result = SELECT reply FROM ((EXEC "SYSADMIN.executeCli"( "script" => '/subsystem=teiid:read-attribute(name=' || cur.setting || ')' ))as a); result = '/subsystem=teiid:write-attribute(name='||cur.setting || ',value="' || (SELECT XPATHVALUE(JSONTOXML('setting', REPLACE(REPLACE(result,'=>',':'),'L','')),'setting/result')) || '")'; bufferCli = bufferCli || ' ' || result; END --Concurrency settings concurrencyCli='connect'; LOOP ON (SELECT a.setting FROM TEXTTABLE('"max-active-plans", "max-threads" "time-slice-in-millseconds" "thread-count-for-source-concurrency"' COLUMNS setting string )as a) as cur BEGIN result = SELECT reply FROM ((EXEC "SYSADMIN.executeCli"( "script" => '/subsystem=teiid:read-attribute(name=' || cur.setting || ')' ))as a); result = '/subsystem=teiid:write-attribute(name='||cur.setting || ',value="' || (SELECT XPATHVALUE(JSONTOXML('setting', REPLACE(result,'=>',':')),'setting/result')) || '")'; concurrencyCli = concurrencyCli || ' ' || result; END --JDBC and ODBC settings jdbcdbcCli='connect'; LOOP ON (SELECT a.setting FROM TEXTTABLE('"jdbc" "jdbc-ssl" "odbc" "odbc-ssl"' COLUMNS setting string )as a) as cur BEGIN result = SELECT reply FROM ((EXEC "SYSADMIN.executeCli"( "script" => '/subsystem=teiid/transport=' || cur.setting || ':read-resource' ))as a); result=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(result,'>',''),'{',''),'}',''),' ',''),'"outcome"="success",',''); result=REPLACE(REPLACE(REPLACE(REPLACE(result,'"result"=',''),'"',''),CHAR(10),''),CHAR(13),''); --result=SUBSTRING(result,0,LOCATE(',response-headers',result)-1); result = '/subsystem=teiid/transport=' || cur.setting || ':remove' || ' /subsystem=teiid/transport=' || cur.setting || ':add(' || result || ')'; jdbcdbcCli = jdbcdbcCli || ' ' || result; END --Login Module settings loginCli='connect'; result = SELECT reply FROM (EXEC "SYSADMIN.executeCli"( "script" => '/subsystem=security/security-domain=teiid-security/authentication=classic:read-attribute(name=login-modules)'))as a; result = REPLACE(REPLACE(result,CHAR(10),''),CHAR(13),''); result = REPLACE(result,' ',''); declare integer startIndex = LOCATE('[',result); result = select SUBSTRING(result,startIndex,LENGTH(result)-startIndex); result = '/subsystem=security/security-domain=teiid-security/authentication=classic:write-attribute(name=login-modules, value=' || result; loginCli = loginCli || ' ' || result; SELECT bufferCli as Buffer, concurrencyCli as Concurrency, jdbcdbcCli as JdbcOdbc, loginCli as Login; END;;
7. If you use LDAP authentication, get the default admin role from dvconfig. To do this, connect to dvconfig:
  1. Connect to the config db (the password is "dvconfig"): <CData Virtuality Installation Folder>/ dvserver/pgsql/bin/psql -h localhost -p 54322 -U dvconfig dvconfig (Linux) or <CData Virtuality Installation Folder>\ DVServer\pgsql\bin\psql.exe -h localhost -p 54322 -U dvconfig (Windows);
  2. Run the SELECT statement:
    1. For source versions 2.3.x and 2.4.1:
      SQL
      
      SELECT * FROM <dv configuration schema>.dv__ldap_permissions WHERE id = 1;
    2. For source version 2.4.2:
      SQL
      
      SELECT * FROM <dv configuration schema>.dv__ldap_permissions WHERE deleteonrestart=true;
      The <dv configuration schema> is public for embedded configuration database. For non-embedded config, it is usually dvconfig, but can be different for each configuration. Please consult dvconfig.conf.props(.bat) or standalone.conf.props(.bat) to see the configuration of your system.
8. This step is applicable only if you plan to continue using non-embedded configuration database
  Check if max_connections is set to >= 100 and set it to 200 if your data warehouse is on the same PostgreSQL instance.
9. This step is applicable only if you plan to continue using non-embedded configuration database
  Create a new database on the PostgreSQL instance for the configuration database (the usual pattern is dv_r<release>_config, e.g., dv_r2039_config) under the user already used by CData Virtuality according to the old dvserver-standalone.xml file.
10. Delete the license.lic file from the new release folder <new CData Virtuality Server release folder>/dvserver/standalone/data/datavirtuality (you do not need it as you already have your license which will be valid for the updated CData Virtuality Server as well).
11. This step is applicable only if you plan to continue using non-embedded configuration database
  Copy the old dvconfig.conf.props(.bat) file and adapt the database (e.g. dv_r231_config) in the file. The setting to look for is Ddv.dvconfig.db=.
12. Copy the standalone.conf.props(.bat) file from the old to the new server, and remove config db settings if present.
13. Adapt the standalone.conf(.bat) file with any additional settings from your previous version (check the old analytic_storage_on_different_host_dv value).
14. If the old server had imported non-standard SSL certificates, it would be necessary to also add these certificates to the new server, to the certificate store file called <CData Virtuality Installation Folder> /dvserver/JDK/lib/security/cacerts. There are two options for doing so:
  1. If you have copies of the original certificate files, please import them into the certificate store as described in the documentation;
  2. Copy the bundled Java keystore file named cacerts from the old release (please back up the existing cacerts file first). In versions below 2.4, it is located in the <CData Virtuality Installation Folder> /dvserver/JDK/jre/lib/security/cacerts folder, and in 2.4, its location is <CData Virtuality Installation Folder> /dvserver/JDK/lib/security/cacerts. Please copy the file into the <CData Virtuality Installation Folder> /dvserver/JDK/lib/security/ folder of the new server. It is recommended to use the first approach if possible because, with the second approach, some updated certificates which came with the newer Java version would not be used.
15. In case of any custom or not covered above changes to the DV components like embedded JDK, embedded Postgres database, WildFly configuration, etc., transfer these settings to the new system.
16. Change permissions on the dvserver folder and child items (check against permissions on the old release folder for comparison). This is mandatory for Linux (you can use chown -R datavirtuality:datavirtuality dvserver) and normally not required on Windows, unless there are specific requirements on your side.
17. The upgrade does not preserve audit log information. If necessary, export the audit log tables from the old server into a separate database, as the content of the audit tables will not be transferred during the upgrade. The audit tables are located in the SYSLOG schema in the CData Virtuality server. Please export or copy any tables containing the information you need to preserve for the future.
During Downtime
1. Shut down the CData Virtuality Server.
2. Check that the embedded PostgreSQL database (the process is <CData Virtuality Installation Folder> /dvserver/pgsql/bin/postgres on Linux and postgres.exe on Windows) has stopped after the old server has been shut down.
  If not, shut it down manually using <CData Virtuality Installation Folder> /dvserver/pgsql/embeddedPg_stop.bat or <CData Virtuality Installation Folder> /dvserver/pgsql/embeddedPg_stop.sh . If the PostgreSQL database is still running after that, use the kill or kill -9 command on Linux or the taskkill command on Windows.
3. Rename the old server folder (for example, by adding the version number to its name), then move the new server to the final location.
4. Start the CData Virtuality Server.
5. If you are using standard authentication: At this point, the CData Virtuality Server is configured with standard credentials (admin/admin). Please change the admin password before proceeding.
6. If you are using LDAP authentication: Connect to the config db (the password is "dvconfig"): <CData Virtuality Installation Folder>/ dvserver/pgsql/bin/psql -h localhost -p 54322 -U dvconfig dvconfig.
7. Import JBoss settings via CLI files from the command line. These are the files you need:
  BASH
```
<Data Virtuality Installation Folder>/dvserver/bin/jboss-cli.sh --file=<...>/yyyy-mm-dd-hh_mm_ss_dv_jboss_config_buffer.cli
<Data Virtuality Installation Folder>/dvserver/bin/jboss-cli.sh --file=<...>/yyyy-mm-dd-hh_mm_ss_dv_jboss_config_concurrency.cli
<Data Virtuality Installation Folder>/dvserver/bin/jboss-cli.sh --file=<...>/yyyy-mm-dd-hh_mm_ss_dv_jboss_config_jdbc_odbc.cli
<Data Virtuality Installation Folder>/dvserver/bin/jboss-cli.sh --file=<...>/yyyy-mm-dd-hh_mm_ss_dv_jboss_config_login_module.cli
```
8. If you are using LDAP authentication: Edit the dvserver-standalone.xml file: search for the correct login module, and add the <defaultAdminGroup> element. The value should be the role read in step 2.6.
  For more detail, please see our guide on LDAP authentication.
9. Restart the CData Virtuality Server.
10. Import data sources with DSQL (the CData Virtuality command-line client). The file you need is yyyy-MM-dd_hh_mm_ss_dv-export-datasources.sql, and here's an example call for the import:
  1. For Linux:
    SQL
```
./dsql.sh -u <account with admin permissions> -p <password> -h localhost --sourcefile <path to SQL file>
```
  2. For Windows:
    SQL
```
./dsql.bat username=<account with admin permissions> password=<password> host=localhost sourcefile=<path to SQL file>
```
11. Restore schemas, views, and procedures with DSQL (file yyyy-MM-dd_hh_mm_ss_dv-export-views-and-procedures.sql).
12. If the old CData Virtuality Server version is older than 2.4.15:
  1. Open the file yyyy-MM-dd_hh_mm_ss_dv-export-etc.sql with a text editor
  2. and search for the command EXEC SYSADMIN.setDefaultOptionValue("opt" => 'ALLOW_CARTESIAN', "val" => '...') OPTION $NOFAIL.
  3. If val is INTERNAL or EXPLICIT, please comment out or remove that command.
13. Restore optimizations, jobs, and schedules with DSQL (file yyyy-MM-dd_hh_mm_ss_dv-export-etc.sql).
14. Please check the schedules of system jobs (all jobs having job type "backup", "cleanup", or "performance") for duplicated schedules. Delete duplicate schedules of system jobs if present.
15. Delete performance counters using this command:
  SQL
```
EXEC "SYSLOG.clearPerformanceLogs"();;
```
  This is the end of the downtime and, effectively, the end of the upgrade. The rest of the steps are different checks to ensure that everything worked well and some cleaning up.
After Downtime
1. Check that the backup job runs and produces the backup SQL files. Also, make sure that backup SQL files are not stored directly in the dvserver directory, for example, not <CData Virtuality Installation Folder>/dvserver/backups but rather /home/<username>/backups, C:\Backups or similar location outside of CData Virtuality folder). Note that starting from version 2.3.13, the CData Virtuality Server has a default backup system job. You may want to configure this job (using SQL) and deactivate any previous backup jobs.
2. Manually run jobs that were not executed because of the server downtime (or all, if the number of jobs is reasonable).
3. Install the new CData Virtuality Studio.
4. Connect to the server via the CData Virtuality Studio.
5. If necessary, compare failed objects to the pre-update state and repair in the following order: data sources (modular connector deployment), procedures, views, jobs, and optimizations.

That's all! Some additional steps are required if you are using LDAP authentication or if there were certain custom changes to the old server:

If you are not using LDAP authentication, change the admin password from the default one;
If customizations were applied to the old server to configure HTTPS for the embedded web server, migrate them to the new server;
If customizations were applied to default ports, migrate them to the new server;
If anonymous SSL mode was changed to 1-way or 2-way SSL, migrate those settings to the new server;
If customizations were applied for a proxy configuration, migrate them to the new server.

Rollback

In this section, we describe how to revert the upgrade, but before proceeding, we would like to point out that a rollback should be a last resort. If something has come up during the upgrade, please contact our support team - they will be here to help!

That being said, this is how to perform the rollback:

Stop the server if it is running.
Check that the embedded PostgreSQL database (the process is <CData Virtuality Installation Folder> /dvserver/pgsql/bin/postgres on Linux and postgres.exe on Windows) has stopped after the old server has been shut down.
If not, shut it down manually using <CData Virtuality Installation Folder> /dvserver/pgsql/embeddedPg_stop.bat or <CData Virtuality Installation Folder> /dvserver/pgsql/embeddedPg_stop.sh . If the PostgreSQL database is still running after that, use the kill or kill -9 command on Linux or the taskkill command on Windows.
Rename the dvserver folder to dvserver_<newRelease>_<date> (e.g., dvserver_241_2021-03-01).
Unzip the old dvserver folder by running this command on Linux or similar operations in Windows Explorer:
SQL
```
cd /opt/datavirtuality
unzip dvserver.zip
```
Start the CData Virtuality Server.