Technical Specification

MN914021EN, version 6

Integrating Visual T&D

to a database server
For use with Visual T&D software version 5.3R3 or later.
All brand and product names appearing in this document are the trademark or registered trademark
of their respective holders.

© 2024 Eaton – All rights reserved.

The information in this document is subject to change without notice.

Eaton’s Energy Automation Solutions

1990 5th Street
Suite 220
Levis, Quebec
Canada G6W 5M6
Phone: +1.877.834-0009 or +1.800.815-2258
Fax: +1.514.227.5256
Email: PSMO-sales@eaton.com
Web: http://www.Eaton.com/smartgrid

Technical Support: eas-support@eaton.com

MN914021EN, version 6
(Template: EAS-SA QF-MKT-02 Rev.04)
 MN914021EN, version 6

Contents
1 Introduction 1
1.1 Getting Assistance .................................................................................................1
1.2 Related Documentation .........................................................................................1

2 The Visual T&D Datalog 3

2.1 Functional dependancy between Visual T&D server and the database
server .....................................................................................................................3
2.2 The importance of deadbands ................................................................................4
2.3 Planning access rights............................................................................................5

3 Database requirements and references 7

3.1 Database engines supported by Visual T&D .........................................................7
3.2 Information references and help regarding database .............................................7
3.3 Security requirements ............................................................................................7
3.4 Hardware requirements .........................................................................................8

4 Creating Visual T&D datalog database 11

5 Preparing the database 13

5.1 Preparing a Microsoft SQL Server database .......................................................13
5.1.1 Running instance ...................................................................................13
5.1.2 SQL Server Service account .................................................................13
5.1.3 SQL Server Authentication Mode .........................................................13
5.2 Preparing a PostgreSQL database........................................................................14
5.2.1 PostgreSQL Service account .................................................................14
5.2.2 Initializing PostgreSQL datastore .........................................................14
5.2.3 Allowing a PostgreSQL server to accept Visual T&D connection .......15
5.2.4 Creating a PostgreSQL database login ..................................................16
5.3 Data migration from an existing datalog .............................................................16
5.3.1 Importing data from a version 3.X of Visual T&D datalog ..................17
5.3.2 Migrating Visual T&D 4.1R1 data to a Visual T&D 4.1R2 datalog .....18
5.3.2.1 Procedure for a datalog hosted in a Microsoft
SQL Server 18

Integrating Visual T&D to a database server Technical Specification ●i

MN914021EN, version 6

5.3.2.2 Procedure for a datalog hosted in a PostgreSQL

database server 18
5.3.3 Importing data from a version between 4.1R2 and 5.0 of Visual
T&D datalog .........................................................................................19

6 Connecting Visual T&D to the database 20

6.1 Custom connection string on Microsoft SQL Server...........................................21
6.2 Custom connection string on PostgreSQL ...........................................................21

7 Best practices for optimizing the system resources usage 23

7.1 Best practices for Microsoft SQL Server database ..............................................23
7.1.1 Using minimal privileges ......................................................................23
7.1.2 Adjusting the Microsoft SQL Server memory parameters ....................23
7.2 Best practices for PostGreSQL database .............................................................23
7.2.1 Using minimal privileges ......................................................................23
7.2.2 Adjusting the PostgreSQL memory parameters ....................................25
7.2.3 Adjusting the PostgreSQL vacuum parameters .....................................26

8 Best Practices to manage the datalog 27

8.1 Managing backups ...............................................................................................27
8.1.1 Managing backups for a PostgreSQL database .....................................27
8.2 Managing the datalog size ...................................................................................28
8.2.1 Selecting the data points to record ........................................................28
8.2.2 Using deadbands to filter transitions .....................................................29
8.2.3 Datalog configuration information ........................................................29
8.2.4 Datalog monitoring ...............................................................................29
8.2.5 Disk space usage ...................................................................................30
8.2.6 Limiting the size of the transaction log file for a Microsoft SQL
Server database......................................................................................31
8.3 Managing the datalog ..........................................................................................31
8.3.1 Keep historical data after changing the data point source .....................31

9 Database performance optimization 35

9.1 Adjusting the bulk size to optimize data writing performance ............................35
9.2 Adjusting the number of table partitions of the Visual T&D database ................38

Appendix A: Resetting the password of a PostgreSQL database A-1

Appendix B: Moving the Visual T&D Datalog database to another

disk drive B-1

Appendix C: Deleting transitions manually C-1

ii ● Contents
 MN914021EN, version 6

C.1 Deleting in PostgreSQL .................................................................................................. C-1

C.2 Deleting in SQL Server .................................................................................................. C-1

Appendix D: Migrating PostgreSQL 9 to 10 D-1

Appendix E: Enforcing SHA256 hashing on PostgreSQL E-1

E.1 About PostgreSQL password challenge configuration .................................................... E-1
E.2 Securing authentication with SHA256............................................................................. E-1

Integrating Visual T&D to a database server Technical Specification ● iii

 MN914021EN, version 6

Figures
Figure 1 Simplified functional dataflow – data historization ...................................................... 4
Figure 2 Simplified functional dataflow – historical query ........................................................ 5
Figure 3 Database monitoring - load indicators ....................................................................... 30

Integrating Visual T&D to a database server Technical Specification ●v

 MN914021EN, version 6

Tables
Table 1 MS SQL - Time estimation to fill disk space (no compression) ................................... 9
Table 1 PostgresSQL - Time estimation to fill disk space (no compression) .......................... 10
Table 2 PostgreSQL memory parameters ................................................................................ 26
Table 3 PostgreSQL vacuum parameters................................................................................. 26
Table 4 Example – Monitoring writing performances of the database .................................... 37
Table 5 Number of table partitions of the Visual T&D database ............................................ 38

Integrating Visual T&D to a database server Technical Specification ● vii

 MN914021EN, version 6

1 Introduction

This document presents all necessary instructions to integrate Visual T&D to a database server as
well as the technical aspects to consider for a successful integration.

1.1 Getting Assistance

If you have any question regarding the performance, application or testing of any component of
this Eaton product, do not hesitate to contact us. Our staff will be pleased to assist you.
Technical Support
Eaton’s Energy Automation Solutions

Email: eas-support@eaton.com
Phone: +1.877.834-0009 or +1.800.815-2258

Business hours are from 8 a.m. and 5 p.m. CST, Monday to Friday.

1.2 Related Documentation

The following Eaton documents contain additional information on other Visual T&D components:
 Visual T&D Installation Guide, MN914009EN.
This document contains all the information required to install, configure and perform the
maintenance of a Visual T&D server.
 Visual T&D Diagram Editor User Guide, MN914002EN.
This document presents the Diagram Editor, a Visual T&D application allowing the creation
of interactive diagrams used to monitor and operate a substation or an electric grid.
 Visual T&D Explorer User Manual, MN914001EN.
This document presents the Visual T&D Explorer, an application providing immediate access to all
Visual T&D data points in real-time, in a variety of formats, with minimum configuration effort. It also
explains how to configure these data points and the various real-time and historical views, but also how
to set up alarms and events based on these data points' values.
These documents are found in the Visual T&D installation package. They are installed on the
computer hosting Visual T&D along with the software applications. Some additional documents
are also provided.

Integrating Visual T&D to a database server Technical Specification •1

 MN914021EN, version 6

2 The Visual T&D Datalog

The Visual T&D server can record data on a continuous basis in a database called the Visual T&D
Datalog.
Visual T&D is often used as a critical system or as a part of a critical system, as such, its
deployment should be planed carefully to avoid functional disruption. All recorded data is time-
stamped and tagged for quality. The datalog allows you to reconstruct the sequence of events and
to see the data point transitions, using the Historical Data display.
More specifically, the Visual T&D Datalog records the following data items:
 All events
 All transitions (binary point state changes and sampled analog point values) on data
points for which the Need Log setting is selected

Note: By default, the Need Log setting is selected for all binary points added to a site
configuration; this is not the case for newly added analog points, which can generate a large
amount of transitions.

The Visual T&D server components and the Visual T&D datalog database can be installed on the
same computer. However, it is recommended to install them on different computers or, at least, on
different drives.

2.1 Functional dependancy between Visual T&D

server and the database server

Visual T&D will operate normally when the database server is unavailable but will raise an alarm
to inform the users.
Visual T&D server connects to the database server at startup, or upon restart. Any change to the
database server affecting the connection parameters requires a mandatory restart of Visual T&D,
leading to a temporary loss of functionality.
Visual T&D server will buffer data to be written to the database when the database server is
temporarily unavailable. However, the buffered data size being limited there is no guarantee that
data will not be loss. Visual T&D will actively try to reconnect to the database server and save
buffered data. After reconnection, the buffered data will be written in block, which size can be
adjusted to optimize write performance. See section Adjusting the bulk size to optimize data
writing performance, page 35.

Integrating Visual T&D to a database server Technical Specification •3

MN914021EN, version 6

The diagram below shows a simplified functional dataflow picturing data sources, acquisition
filtering, active site configuration, transitions, alarms and events recording into the historian
database of the loaded site.

Figure 1 Simplified functional dataflow – data historization

2.2 The importance of deadbands

The approximate required disk space can be planed using the amount of transitions to be recorded.
A transition is recorded when a value change exceeds the deadband limit configured in Visual
T&D. The deadband settings are crucial for successful long term historic data management and
maximizing Visual T&D uptime. The deadband is assigned independently for each point but can
be configured in bulk. Refer to section Configuring Analog Points of Visual T&D Explorer User
Manual.
One measured point recorded every 1ms will take as much space as 1000 points recorded every 1s.
In most installations, less than 20% of points will take more than 80% of space. Most of the time,
adjusting deadbands on only a handful of points that are changing frequently can have drastic
impacts on drive space, performance and system uptime.

4● Chapter 2: The Visual T&D Datalog

 MN914021EN, version 6

2.3 Planning access rights

The diagram below shows a simplified functional dataflow of an historical query, initiated from
two different Visual T&D Explorer sessions.

Figure 2 Simplified functional dataflow – historical query

Integrating Visual T&D to a database server Technical Specification •5

 MN914021EN, version 6

3 Database requirements and

references

3.1 Database engines supported by Visual T&D

The Visual T&D Datalog requires a database engine.
The following database engines are supported by Visual T&D:
 Microsoft SQL Server 2008 R2, 2012 or 2016
 PostgreSQL 9.2, 9.3 or 10.7
While Visual T&D supports PostgreSQL for the historian database, EATON recommends using
Microsoft SQL Server for all new deployments. If PostgreSQL is chosen, EATON recommends
using the EnterpriseDB distribution, available at www.enterprisedb.com.

3.2 Information references and help regarding

database
For security reasons, from Visual T&D 5.0R1 upwards, EATON does not distribute a database
with Visual T&D. It is the responsibility of the customer or its integrator to plan, deploy and
ensure the security of the database deployment.
All primary database aspects that are not answered in this guide or in Visual T&D documentation
can be found in the documentation concerning each database engine:
For Microsoft SQL at: https://docs.microsoft.com/en-us/sql/
For PostgreSQL at: https://www.postgresql.org/docs/
This present document or other Visual T&D documentation may use references to PostgreSQL
9.2, 9.3 or 10.7, and PGAdmin III or PGAdmin 4. Please consider the documented steps as
guidelines to be followed along with the database documentation related to your installed version.

3.3 Security requirements

All Microsoft SQL Server deployment should use the latest security patches from Microsoft on the
database server side. On the client side (Visual T&D server), use TLS 1.2 authentication with the
latest SQL Native Client. EATON redistributes Microsoft SQL Native client version
2011.110.7001.00 with TLS 1.2 capability since Visual T&D 5.0R1.

Integrating Visual T&D to a database server Technical Specification •7

MN914021EN, version 6

All Postgres deployments should use version 10.7 (or later minor versions of version 10) with
SHA256 authentication encryption. PostgreSQL 10.7 is supported with Visual T&D from Visual
T&D 5.0R1 upwards. The default MD5 encryption need to be changed to SHA256, the related
instructions are provided at section Enforcing SHA256 hashing on PostgreSQL, page E-1.

Note: Before enforcing SHA256 hashing in PostgreSQL, upgrading to PostgreSQL 10.7 from an
earlier version (typically 9.2) need to be performed. Refer to section Migrating PostgreSQL 9 to
10, page D-1, for details.

It is the responsibility of the customer or its integrator to ensure a secure database deployment
within a customer-specific infrastructure.
Regardless of the database engine used, please ensure to apply the principle of least priviledge,
immediately after the first functional tests. For PostgreSQL, refer to section Using minimal
privileges, page23, for details.

3.4 Hardware requirements

The Visual T&D server components and the Visual T&D Datalog database can be installed on the
same computer. However, it is recommended to install them on different computers or, at least, on
different drives.

Note: When determining the amount of disk space required by the Visual T&D Datalog
database, consider that the new data model takes four (using Microsoft SQL database) to
eight times (using PostgreSQL database) as much disk space as the proprietary data
model used by version 3.7 or earlier.

Before proceeding with the installation of a new Visual T&D server, or the migration of an
existing Visual T&D server to version 4.0 or later, verify the availability of existing corporate
Microsoft SQL Server or PostgreSQL database servers with your IT department. Please ensure
that periodic backup of the database is in place, especially if the historic data is critical for
operations or needed by law or internal business rules.
The disk space can be planned using the following approximations:
 One recorded transition (either analog or binary) requires about 40 bytes of logic space.
 In Microsoft SQL Server, 40 bytes of logic space in the current Visual T&D database
structure requires approximately 160 bytes of physical drive space (4 times logic space).
 In PostgreSQL, 40 bytes of logic space in the current Visual T&D database structure
requires approximately 320 bytes of physical drive space (8 times logic space).
The drive space (bytes) for one year of recording can be approximated by:
𝑆𝑦𝑒𝑎𝑟 = 𝐾𝑠𝑝𝑎𝑐𝑒 × 𝑁𝑡𝑠 × 40 × 31 536 000
Where:
 Syear: drive bytes for a year.
 Kspace : approximately 4 for Microsoft SQL Server and 8 for PostgreSQL. It will depend
on the number of points, number of partitions, efficiency of the vacuum process to
recover space when transitions are deleted, and how frequently the vacuum process is
allowed to run.
 Nts: number of transition per second for all points.
For example, 1000 points recorded at 1s frequency on Microsoft SQL Server would give:

8● Chapter 3: Database requirements and references

 MN914021EN, version 6

 Nts of 1000
 Kspace of 4
 Resulting S of 4.6 TB of drive space for one year
Several other factors need to be considered when using the formula stated above, such as:
 Bytes per sector on the partition holding data
 Compression at the file system level (such as NTFS compression)
 Compression at database engine level
EATON does not recommend using compression at database level. Generally, compression at file
system level can be used if the CPU has at least four logical cores on Intel i5 Gen 8 (or later) or on
equivalent processing power on AMD. In such case, compression’s extra CPU processing is being
offset by reduced reads and writes. This general assumption is to be verified by the customer in his
own environment before using compression. The raw data stored for Visual T&D transitions can
generally be compressed at NTFS level by as much as 50%; that is reduced by half.
The following tables show an estimation of the expected time to fill disk space when no
compression is used at file system level or database level.

Size of 1 transition: 40 bytes

MS SQL typical file level Time to fill available disc space

space/ database logic space
factor: + x4

Avail. disc Total 1 1 1 2 5 10

week month year years years years
recordable
space
transitions in
transitions per second

avail. space
Number of recorded

50 GB 3.44E+08 568 128 11 5 2 1

100 GB 6.87E+08 1136 257 22 11 4 2

500 GB 3.44E+09 5681 1283 109 54 22 11

1 TB 6.87E+09 NA* 2566 218 109 44 22

2 TB 1.37E+10 NA* NA* 436 218 87 44

5 TB 3.44E+10 NA* NA* 1090 545 218 109

Table 1 MS SQL - Time estimation to fill disk space (no compression)

Integrating Visual T&D to a database server Technical Specification •9

MN914021EN, version 6

Size of 1 transition: 40 bytes

PostgresSQL typical file level Time to fill available disc space

space/ database logic space
factor: + x8

Avail. disc Total 1 1 1 2 5 10

week month year years years years
recordable
space
transitions in

transitions per second

avail. space

Number of recorded
50 GB 1.72E+08 284 64 5 3 1 1

100 GB 3.44E+08 568 128 11 5 2 1

500 GB 1.72E+09 2841 641 54 27 11 5

1 TB 3.44E+09 NA* 1283 109 54 22 11

2 TB 6.87E+09 NA* 2566 218 109 44 22

5 TB 1.72E+10 NA* NA* 545 272 109 54

Table 2 PostgresSQL - Time estimation to fill disk space (no compression)

*: NA: Recording performance typically not supported.

10 ● Chapter 3: Database requirements and references

 MN914021EN, version 6

4 Creating Visual T&D

datalog database

When a new site is created, Visual T&D server will automatically create the historian database, if
it has the right to do so using provided credentials.

Important note: The following procedure only applies to version 5.0 and earlier of Visual
T&D.

To manually initiate and create the historian database, use the .SQL script files located at:
C:\Program Files (x86)\Cooper Power Systems\Visual T&D\Server\Bin
Use:
 Create Database SQL Server.sql for SQL Server
 Create Database PostgreSQL.sql for PostgreSQL
Before executing the script, you may want to adjust the database partitioning; refer to section
Adjusting the number of table partitions of the Visual T&D database, page 38, for details.
The database name must be the same as the Visual T&D site name, with prefix identical as the one
configured on the Visual T&D Server. Refer to section Connecting Visual T&D to the database,
page 20.

Integrating Visual T&D to a database server Technical Specification • 11

 MN914021EN, version 6

5 Preparing the database

5.1 Preparing a Microsoft SQL Server database

5.1.1 Running instance
Visual T&D can connect and operate on SQL default instance or on a named instance. Many
instances can coexist on the SQL Server without functional impact on Visual T&D as long as the
resources are allowing it. Visual T&D does not strickly require its own instance, however EATON
recommends to assign one instance for Visual T&D since the data acquisition throughput can
become significant in some installations and have impacts on other databases.

5.1.2 SQL Server Service account

Planing a service account is important for a database server. When someone installs SQL Server
with the default setup, it will install with the default service accounts. Almost all mid-sized
companies and especially large enterprises use Active Directory or a similar tool to handle user
accounts. If SQL Server is installed with local service accounts there is a chance that SQL Server
doesn’t recognize the domain accounts as trusted domain accounts, because SQL Server will not
be able to register the SPN in the domain.
On the other hand, some people are using a domain admin account as the service account. This can
create quite a problem and, as an example, if xp_cmdshell is enabled, a malicious user could
shut down all the computers of the entire organization.
Most SQL Server service account have explicit write access to database files. If the database data
files are moved from their default location, their containing folders must allow the SQL Server
service account control over the files.

5.1.3 SQL Server Authentication Mode

SQL Server provides two authentication modes, Windows authentication mode on which you can
login to the database instance directly with an authorized Windows login and Mixed mode, that
includes the possibility to use SQL Server logins.
By default SQL Server uses Windows authentication mode, but to use this security mode, the
instance usually must be part of a domain, something that happens when you select a domain
account as a service account which is mentioned above, is not the default option.
EATON recommends using Windows authentication whenever possible, and let the Visual T&D
Server service user be the SQL user.
Then, proceed to configure Visual T&D Datalog connection within Visual T&D Server
Configuration tool. See section Connecting Visual T&D to the database, page 20, for details.

Integrating Visual T&D to a database server Technical Specification • 13

MN914021EN, version 6

Optimize SQL Server performance and memory usage by consulting section Database
performance optimization, page 35.

5.2 Preparing a PostgreSQL database

5.2.1 PostgreSQL Service account
PostgreSQL most likely created a local Windows user named “postgres” to run the PostgreSQL
service. This user should be renamed to a specific name to provide better security.
Most PostgreSQL service account have explicit write access to database files. If the PostgreSQL
data files are moved from their default location, their containing folders must allow the
PosgreSQL service account control over the files.
To change the PostgreSQL Windows user:
1. Allow complete access to PostgreSQL data folder where Visual T&D database resides to
the new Windows account that runs the PostgreSQL service.
2. Allow the new Windows account to execute the PostgreSQL service and programs
installed in Program Files.
3. Set up the PostgreSQL service to be executed by this account.
4. Verify that the PostgreSQL service registration was done properly, using the correct
startup command. This is done by using sc.exe create on a command line.
Refer to the PostgreSQL documentation for more details about registering PostgreSQL
service on Windows. Also refer to sc.exe documentation from Microsoft.
Example of a proper registration:
"C:\Program Files (x86)\PostgreSQL\9.2\bin\pg_ctl.exe"
runservice -N "Visual_TD_Database" -D "C:/Dev/Visual TD
Datalog" -w

Where:
- pg_ctl.exe is the PostgreSQL service executable for your PostgreSQL installation
- -N “Visual_TD_Database” specifies the name of the service
- -D "C:/Dev/Visual TD Datalog" specifies the path of the data files

5.2.2 Initializing PostgreSQL datastore

Once PostGreSQL is installed, a datastore must be initialized to be able to connect, configure and
create a database. This is performed by the initdb command tool in the PostgresSQL installation
folder. Refer to the PostgreSQL documentation for the initdb command syntax.
For Visual T&D, it is strongly recommended to initiate the datastore with English 1252 locale.
Example of a valid initdb command:
initdb -D "C:\vtd_historian_data" --locale="English_United
States.1252" --encoding="UTF8

Where:
C:\vtd_historian_data" is the folder where the data will reside

14 ● Chapter 5: Preparing the database

 MN914021EN, version 6

5.2.3 Allowing a PostgreSQL server to accept Visual T&D

connection
If the PostgreSQL server is located on a different computer than the Visual T&D server, an
additional record must be added to the pg_hba.conf file, which is the configuration file that
controls client authentication for this database.
1. Using any text editor, open the pg_hba.conf file located in the data folder of the
database server.

Note: If the PostgreSQL database was installed using the Visual T&D installer (version 4.1
to 4.4), the default data folder is C:\Visual TD Datalog\.

2. At the end of the file, add a record for each Visual T&D server computer that
requires access to this database server.

Note: Refer to the content of the pg_hba.conf file and PostgreSQL documentation for
complete record syntax. To authenticate with the database using a PostgreSQL account, use
the md5 or scram-sha256 keyword for the authentication method; otherwise, to
authenticate using a Windows account, use the sspi keyword. EATON recommends using
SHA256. Refer to section Securing authentication with SHA256, page E-1,for details.

For example, the following record provides remote access from a Visual T&D server at
address 10.106.121.73, using a local PostgreSQL account:

# TYPE DATABASE USER ADDRESS METHOD

# IPv4 local connections:
host all all 10.106.121.73/32 md5

The following record provides remote access to the same Visual T&D server, at the same
address IP, but using a Windows account:

# TYPE DATABASE USER ADDRESS METHOD

# IPv4 local connections:
host all all 10.106.121.73/32 sspi

Restrictions:
 As specified in the PostgreSQL documentation, the first record with a matching
connection type, client address, requested database, and user name is used to perform
authentication. There is no "fall-through" or "backup": if one record is chosen and the
authentication fails, subsequent records are not considered. If no record matches,
access is denied.
 If you want to keep the access to pgAdmin with the initial superuser PostgreSQL
account, thus using md5 method, use field USER to precise usernames for sspi, md5
and scram-sha256. That way, you won’t fall into the trap described in the previous
point. The md5 entry should be removed once scram-sha256 is configured. See
section Securing authentication with SHA256 for details.
3. Save and close the pg_hba.conf file.
4. Restart the PostgreSQL database service to apply the configuration changes.

Integrating Visual T&D to a database server Technical Specification • 15

MN914021EN, version 6

5.2.4 Creating a PostgreSQL database login

1. Open the program pgAdmin III, or pgAdmin4 (depending on your version of

PostgreSQL)

Important note: PgAdminIII has the option to save your password. DO NOT
ACTIVATE THIS OPTION as it saves it in clear text in the user profile files. Consider
updating to PgAdmin4 for enhanced security.

2. Login to the database server.

3. From the top menu, in the User menu, select Edit > New Object > New Login Role
4. Select the Properties tab, under Role name, type the account name. It must be the
same name as the one used for Visual T&D Server Service.
5. Select the Definition tab, type a password and the password confirmation.
6. Select the Role privilege tab, check the following items:

 Inherits rights from parent role

 Superuser
 Can create databases (unless you prefer to create it manually using the provided script)
7. Click OK.

Then, proceed to configure Visual T&D Datalog connection within Visual T&D Server
Configuration tool. See section Connecting Visual T&D to the database, page 20, for details.
To optimize PostgreSQL performance and memory usage, refer section Database performance
optimization, page 35.

5.3 Data migration from an existing datalog

Suggested migration paths according to the existing datalog version:
 Version 3.X
 Upgrade first to version 5.0 by following instructions in section 5.3.1
 Then upgrade to version 5.1 or later by following instructions from section 5.3.3
 Version 4.0 to 4.1R1
 Upgrade first to version 4.1R2 by following instructions in section 5.3.2
 Then upgrade to version 5.1 or later by following instructions from section 5.3.3
 Version 4.1R2 to 5.0
 Upgrade to version 5.1 or later by following instructions from section 5.3.3

16 ● Chapter 5: Preparing the database

 MN914021EN, version 6

5.3.1 Importing data from a version 3.X of Visual T&D datalog

Important note: The following procedure only applies to version 5.0 and earlier of
Visual T&D.

When upgrading a Visual T&D server from version 3.7 or earlier, Visual T&D system
administrators must transfer the content of the existing datalog to the selected database server.
Visual T&D Server Configuration provides access to a tool that can perform this data migration
operation.
Visual T&D version 4.2R2 offers new deadband settings for analog points to reduce the amount of
data recorded. The percentage of analog points with deadbands is displayed on the access tool
window for importing datalog files.

Note: Migrated content may take up to eight times the disk space used by the existing
data on a PostgreSQL database, and up to four times on a Microsoft SQL database.

For example, a 1.25 GB Visual T&D 3.7 datalog will approximately use 10 GB of disk space
in a Visual T&D 4 PostgreSQL database, and approximately 5 GB of disk space on a
Microsoft SQL database. Therefore, using the deadband settings for both real-time
acquisition and data migration is strongly recommended

The Visual T&D Datalog Import Tool is available from the Visual T&D Server Configuration.
1. Start Visual T&D Server Configuration.
2. Select the Data Logging tab.
3. Click Import Data Log Files.
4. Decide if the percentage of Analog points with deadband corresponds to the
desired level of filtering. If it’s not the case:
a. Close Visual T&D Server Configuration.
b. Open Visual T&D Explorer and load the site to import.
c. Modify the deadband settings (raw, percentage and/or time) for the displayed
analog points as required to suit your needs.
d. Update the configuration to the server.
e. Re-open Visual T&D Server Configuration.
5. Under Data Logging, click Import data log files
6. Under Source, set the oldest date you want your imported data to start from.
7. Under Upon import completion action, select what you want to do with the legacy
datalog files following the import process (either leave the data source unchanged,
copy the legacy data source to a new subdirectory or simply delete it).
8. Click Start Import.
Data will be imported in reverse chronological order, which means that the most
recent data will be imported first.

Note: Be patient, as the process can take a long time, depending on the size of the legacy datalog.

Integrating Visual T&D to a database server Technical Specification • 17

MN914021EN, version 6

Note: If the database folder differs from the default value (C:\Program Files (x86)\Cooper Power
Systems\Visual T&D\Server\Param\<SITE NAME>\Datalog\), you can create the string registry
key DataLog Path under :
\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Cybectec\Visual Substation
Server\DataLog\

5.3.2 Migrating Visual T&D 4.1R1 data to a Visual T&D 4.1R2

datalog
The Visual T&D datalog was modified from version 4.1R1 to version 4.1R2, for better
performance.
As a result, when upgrading a Visual T&D server from version 4.1R1 to version 4.1R2, the
Visual T&D services must update the database schema. However, recorded analog and binary data
is not automatically migrated to the new schema, and this operation must be performed manually
by the user.

Note: During the migration process, the size of the database will increase significantly: make sure
there is enough free space on the database server.

5.3.2.1 Procedure for a datalog hosted in a Microsoft SQL Server

1. Start Microsoft SQL Server Management Studio.
2. Using appropriate credentials, connect to the Microsoft SQL Server configured to
host the datalog.
3. From the Available Databases drop-down menu, select the datalog database.

Note: The name of the datalog database is made of the computer name of the Visual T&D
server, followed by the “Visual T&D Server” text and the site name, all separated by backslashes
characters (‘\’).

In the Query window, type the following SQL command:

EXECUTE [dbo].[sp_RestoreTransitionV001]

5.3.2.2 Procedure for a datalog hosted in a PostgreSQL database server

1. Start pgAdmin III.
2. Connect to the PostgreSQL Server using appropriate credentials as configured in the
Data Logging tab of Visual T&D Server Configuration.
3. Using the tree, navigate to the datalog database.

Note: The name of the datalog database is made of the computer name of the Visual T&D server,
followed by the “Visual T&D Server” text and the site name, all separated by backslashes
characters (‘\’).

4. Open a Query window

5. Type the following SQL command:
SELECT * FROM RestoreTransitionV001()

18 ● Chapter 5: Preparing the database

 MN914021EN, version 6

Upon completion of the migration operation, data will be available again and the legacy database
tables should no longer exist in the database.

Note: Be patient, as the process can take a long time, depending on the size of the legacy datalog.

5.3.3 Importing data from a version between 4.1R2 and 5.0 of

Visual T&D datalog
When upgrading a Visual T&D server from version between 4.1R2 and 5.0, Visual T&D system
administrators must transfer the content of the existing datalog to the selected database server.
Visual T&D Database Migration Tool can perform this data migration operation.

Note: If you migrating data to a version 5.1 of Visual T&D, proceed with the same steps
presented in this current section however, use the following application (run as VTD user) :
C:\Program Files (x86)\Cooper Power Systems\Visual T&D\Engine\VTD.DatabaseMigrations.Tool.exe

1. Access Visual T&D Manager

2. Select the Data logging tab, then select Migration.
3. Configure the source datalog settings by clicking Configure in the Source section.
The source datalog type can either be PostgreSQL or Microsoft SQL Server.

Note: The database name can be found in a previous version of the Visual T&D Server
Configuration application, under the Data Logging tab, in the Database Name Prefix
section. Typically, il is set to <servername>\Visual T&D\<sitename>.

4. Configure the destination database settings by clicking Configure in the

Destination section. The destination datalog type can either be PostgreSQL or
Microsoft SQL Server. By default, the active datalog is selected.

Note that the migration is not allowed if the destination datalog type is Microsoft
SQL LocalDB because of the database size limitation.
5. Select the Visual T&D site to migrate. There is also the possibility to manually
select the .MDB file from the site to migrate. Repeat the same process for all
Visual T&D sites to migrate.

Note that the site selection is only required for informational purposes. Only points
present in the .MDB file will be migrated to the destination datalog.
6. Start the migration process by clicking Start. The migration process can be
interrupted by clicking Stop. If the migration process is restarted, it will restart at
the point from which it was interrupted.
7. When you are executing the migration process, a visual log displays the details of
each migration steps and errors. A log is also be available under:
C:\ProgramData\Eaton\Visual T&D\Logs

Note: Be patient, as the process can take a long time, depending on the size of the legacy datalog.

Integrating Visual T&D to a database server Technical Specification • 19

MN914021EN, version 6

6 Connecting Visual T&D to

the database

Refer to section The Visual T&D Datalog of Visual T&D Installation Guide to configure the
database connection.
While being simple and efficient, using Visual T&D Server Configuration tool has the following
limitations:
 Only for version 5.0 and earlier of Visual T&D, the datalog database name needs to be
the same as the Visual T&D site name.
 All the sites need to be on the same database server, unless the server database
connection configuration is manually changed when another site is loaded.
 All the sites need to have the same database connection credentials, unless the server
database connection configuration is manually changed when another site is loaded.

Important note: The following procedure only applies to version 5.0 and earlier of Visual
T&D.

You may need to manually create and edit a connection string to bypass those limitations; in doing
so, a registry key needs to be created under:
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Cybectec\Visual Substation
Server\DataLog\<SITE NAME>]

Here, <SITE NAME> is the Visual T&D site name for which the connection string will be used.
Several sites can have their specific connection strings.
The string to create must be named ”connectionstring” (without quotes).
The string value must be <SERVER NAME>@<DATABASE NAME>. See next section for more
details.
An example of the .reg file content (replace highlights with your values):
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Cybectec\Visual Substation
Server\DataLog\<SITE NAME>]
"connectionstring"="<MS SQL SERVER NAME>@<DATABASE NAME>"

When a string is specified for a site, the configuration from the graphical interface is ignored.
When no username and password is provided, Visual T&D Server will use Windows integrated
security to authenticate. This identity is the one running the Visual T&D Server service.

20 ● Chapter 6: Connecting Visual T&D to the database

 MN914021EN, version 6

6.1 Custom connection string on Microsoft SQL

Server
For SQL Server, the connection string follows the format:
[<server_name>@][<database_name>][;<driver_connection_option_list>]
 <server_name>: connects to a specified server; if this field is omitted, Visual T&D tries
to connect to the default local server instance.
 <database_name>: connects to a database with the specified name; if this field is omitted,
Visual T&D tries to connect to default database.
 <driver_connection_option_list>: SQL Server Native ODBC driver specific
option list.
To connect to a named instance of SQL Server use <server name\instance name> instead of
<server_name>.
Since SQLNCLI (SQL Server 2005 and later) the protocol can be specified before the server name
part of the connection string:
 lpc:<servername>[\instancename]: using shared memory
 tcp:<servername>[\<instancename>],<port> or
tcp:<IPAddress>[\<instancename>],<port>: using TCP/IP
 np:\<computer_name>\pipe\<pipename> or np:\<IPAddress>\pipe\<pipename>:using
named pipes
 via:<servername>[\instancename],<nic_number>:<port>: using VIA

6.2 Custom connection string on PostgreSQL

For PostgreSQL, the connection string follows the format:
[<server_name>@][<database_name>][;<options>]
 "" or "@" (empty string or '@' character): connects to a local server

 <database_name> or @<database_name> : connects to a database with the specified

name on local server
 <server_name>@: connects to the specified server

 <server_name>@<database_name>: connects to a database with the specified name

on the specified server
 <server_name> can have the following formats:
 hostname[,port]
 pathname of the Unix socket that is used to connect to the server
 <options>: string used for PQsetdbLogin function pgoptions parameter (these are the server
process parameters, refer to PostgreSQL documentation).
After setting the connection string, restart the Visual T&D Server service.

Integrating Visual T&D to a database server Technical Specification • 21

 MN914021EN, version 6

7 Best practices for

optimizing the system
resources usage

7.1 Best practices for Microsoft SQL Server

database
7.1.1 Using minimal privileges
If you have let Visual T&D Server initiate the database with administrative rights, revert its rights
to read, write and delete records once the setup is completed and proven functional. After the first
database initialization, Visual T&D Server does not alter tables, thus it does not need
administrative priviledges.

7.1.2 Adjusting the Microsoft SQL Server memory parameters

If the SQL Server is installed on the same computer as the Visual T&D Server, set the maximum
SQL Server memory to 50% of the total system memory.
If the SQL Server is installed on a dedicated computer, set the maximum SQL Server memory to
leave at least 4 GB of the total system memory for other system processes, up to using 90% of the
total system memory.
Refer to the SQL Server documentation to perform this configuration.

7.2 Best practices for PostGreSQL database

7.2.1 Using minimal privileges
To restrict to a minimum the operating system resources usage by Visual T&D components, some
actions can be taken; one of them involves setting the database server account with minimum
privileges.
1. From the Windows Start menu, select PostgreSQL and then click on PgAdminIII
(PostgreSQL configuration console).

Integrating Visual T&D to a database server Technical Specification • 23

MN914021EN, version 6

Important note: PgAdminIII has the option to save your password. DO NOT
ACTIVATE THIS OPTION as it saves it in clear text in the user profile files. Consider
updating to PgAdmin4 for enhanced security.

2. In the tree control, double-click on PostgreSQL 9.2 (x86).

3. In the Connect to Server dialog box, enter your PostgreSQL administrator
password and press OK.
4. In the left pane, right-click on Login Roles and click New Login Role
5. Select the Properties tab, give the login role a name and then click OK.

6. Select the Definition tab and set the login role a password. Verify that the Account
expires box is unchecked. Click OK.

7. Select the Role privileges tab, verify that the following role privileges boxes are checked:
a. Can login
b. Can create database
c. Inherits rights from parent role

24 ● Chapter 7: Best practices for optimizing the system resources usage

 MN914021EN, version 6

8. Select the Role membership tab, add the following group roles to the Member list:
d. VTDDataReaders
e. VTDDataWriters

7.2.2 Adjusting the PostgreSQL memory parameters

In most installations, the PostgreSQL memory parameters need to be adjusted in order to reach
adequate performance. For more information, please refer to the PostgreSQL documentation.
Eaton recommends adjusting the memory parameters by starting with the lowest recommended
value and increasing them only if required. Adjust the following parameters in postgres.conf
file:

Integrating Visual T&D to a database server Technical Specification • 25

MN914021EN, version 6

Memory parameter Minimum Maximum recommended value

recommended value
shared_buffers 256 MB 20% of physical system memory

work_mem 128 MB 10% of physical system memory

maintenance_work_mem 256 MB 10% of physical system memory, up to 1 GB

Table 3 PostgreSQL memory parameters

7.2.3 Adjusting the PostgreSQL vacuum parameters

The PostgreSQL vaccum process is responsible of managing free rows (“tuples”) and indexes. It
must run periodicaly in order to maintain a manageable database size. If it runs too often or for too
long, it will impact performance and may freeze the database. For more information, please refer
to the PostgreSQL documentation.
Eaton recommends adjusting the following parameters in postgres.conf file, the other
autovacuum parameters can be left to their default values;

Default PostgreSQL value Recommended value

(make sure to remove the “#” character at the
beginning of the line)

#log_autovacuum_min_duration = -1 log_autovacuum_min_duration = 250

#autovacuum_vacuum_scale_factor = 0.2 autovacuum_vacuum_scale_factor = 0.01

#autovacuum_analyze_scale_factor = 0.1 autovacuum_analyze_scale_factor = 0.01

#autovacuum_vacuum_cost_limit = -1 autovacuum_vacuum_cost_limit = 1000

Table 4 PostgreSQL vacuum parameters

26 ● Chapter 7: Best practices for optimizing the system resources usage

 MN914021EN, version 6

8 Best Practices to manage

the datalog

8.1 Managing backups

Making frequent backups of your Visual T&D site configurations and datalog should be part of
your Visual T&D maintenance strategy, in order to be able to restore them on a new computer or
hard drive in case of hardware failure.
To back up the corresponding datalog of the site, you must use the management tool that is
provided with the database engine you use.
Refer to the documentation of the selected database engine to learn how to back up and restore
your data.

8.1.1 Managing backups for a PostgreSQL database

To backup a PostgreSQL database, use pgAdmin.

Important note: PgAdminIII has the option to save your password. DO NOT ACTIVATE
THIS OPTION as it saves it in clear text in the user profile files. Consider updating to
PgAdmin4 for enhanced security.

You can also use the pg_dump and pg_restore command-line programs to respectively back up
and restore a Visual T&D datalog PostgreSQL database.
Database management is out of the scope of this document. The information provided in this
section is intended for Visual T&D system administrators who are not very familiar with
databases.
Backup and restore operations can also be performed using the pgAdmin utility program.
To back up the database:
1. Open a command window.
2. At the command prompt, type the pg_dump command as follows to back up the database:
pg_dump -h <host> -p <port> -U <username> -W -Fc "<databaseName>" >
<backupName>.dump
Where:
 <host>: is the host name of the computer running the PostgreSQL server (use localhost
if you execute the command from the server).

Integrating Visual T&D to a database server Technical Specification • 27

MN914021EN, version 6

 <port>: is the TCP port on which the PostgreSQL server is listening for connections
(default port for a Visual T&D installation is 5433).
 <username>: is the user name of the PostgreSQL account that will be used by the
command to connect to the server (you will be prompted for the password at the
command execution).
 <databaseName>: is the name of the of the PostgreSQL database of a specific site,
including the name of the PostgreSQL server. The character “&” in the database name
can cause pg_dump command to fail. The database can be renamed to remove this
character before performing the pg_dump command.
 <backupName>: is the name of the backup file.

To restore the database:

1. Open a command window.
2. At the command prompt, type the pg_restore command as follows at the prompt:
pg_restore -h <host> -p <port> -W -C -d <username> <backupName>.dump
 If a database with the same name already exists on the PostgreSQL server, you must
delete it before proceeding it with the restore operation, using the dropdb command as
follows: dropdb -h <host> -p <port> -U <username> -W -Fc
"<databaseName>"

Make sure to stop the Visual T&D server service before proceeding with the database
deletion to prevent Visual T&D from recreating the database automatically.
 If you restore the database in a brand new PostgreSQL server installation, make sure
that the Visual T&D server has connected at least once with the PostgreSQL server in
order to create the required users and roles.

Examples for the tutorial site:

 The following command backs up the datalog database of the Tutorial site, on the WIN-
9IF5K PostgreSQL server: pg_dump -h localhost -p 5433 -U postgres -W -
Fc "WIN-9IF5K\Visual T&D Server\Tutorial" > backup.dump

 The following command restores the datalog database: pg_restore -h localhost -

p 5433 -W -C -d postgres backup.dump

8.2 Managing the datalog size

The Visual T&D datalog, if not managed properly, can grow fast and become hard to manage.
This section presents the best practices to follow in order to use the Visual T&D datalog in the
most effective way.

8.2.1 Selecting the data points to record

Some data sources publish a large number of analog and binary data points. However, some of
these points might not be critical, at least, from an historical perspective.
The Visual T&D datalog only records transitions on data points for which the Need Log setting is
selected. A good way to save disk storage is to ensure that the data point transitions that are
recorded in the datalog really need to be recorded. By default, the Need Log setting is selected for

28 ● Chapter 8: Best Practices to manage the datalog

 MN914021EN, version 6

all binary points added to a site configuration; this is not the case for newly added analog points,
which can generate a large amount of transitions.
It is recommended to keep these default Need Log setting values as much as possible, and to
activate data logging only on specific points, as required.

8.2.2 Using deadbands to filter transitions

If you really need to record analog data point transitions in the datalog, but want to filter non-
significant transitions, you can setup three different Deadband settings to specify the minimum
change to be considered significant:
 Deadband: in engineering units
 Deadband %: in percentage
 Time Deadband: minimum time period, in seconds, which need to elapse for the next
transition to be significant
As soon as one of these conditions is met, the data point will be recorded (if Need Log is
selected).
The Deadband settings are specific to each analog data point.

8.2.3 Datalog configuration information

The Datalog Configuration Information window provides information about the datalog-
specific settings of the data points.
This window is displayed before each site configuration update to remind the user of the impact of
archiving analog point transitions. It can also be opened via the Site menu.

8.2.4 Datalog monitoring

Visual T&D provides multiple ways to monitor the current state of the datalog:
 Transition counters are provided to display the number of transitions that occur on each
point, in real-time; system administrators can easy detect analog data points that generate
large numbers of transitions and adjust their Deadband setting, if necessary.
 Administrative tools provided with the database engine can also be used to generate
usage and performance statistics.
The Visual T&D server can publish system data points providing real-time information about the
size and load of the datalog. System administrators can configure alarms on these points to be
warned in case of datalog problems.

The list of the system points is detailed in the Visual T&D Explorer User Manual, at Appendix A;
System Data Points. Active monitoring is particulary useful on load indicators, notably:
_vss__logWriteLoad, _vss__logReadLoad and _vss__logMaintenanceLoad.
The following figure shows an example of usefull database monitoring using database load
indicators, on a 10 minutes timeframe in Visual T&D Explorer real-time window.

Integrating Visual T&D to a database server Technical Specification • 29

MN914021EN, version 6

Figure 3 Database monitoring - load indicators

 For any load indicator, value should not exceed 25% over a sustained amount of time. It is
normal to have peaks during events or queries. If needed, adjust deadbands and database
dedicated working memory. Refer to section Database performance optimization, page 35,
for details.

8.2.5 Disk space usage

Visual T&D allows users to configure the number of days datalog files need to be kept on the
server. By default, data are kept without being deleted, so it is recommended to determine what
would be the optimal number of days you want to keep the datalog files in the database to prevent
overfilling the database with information that is no longer necessary.
To control disk space usage for Visual T&D Datalog:
1. Using a text editor, open the following file:
C:\Program Files (x86)\Eaton\Visual T&D\Engine\ VTD.Engine.exe.config
Visual T&D provides the following configuration parameter:
<add key="DatalogMaxDays" value="90" />
2. By default, Visual T&D will automatically delete data older than 90 days. If don't want to
delete old data, enter the value 0.
3. Restart the Visual T&D Engine service for the new parameters to take effect.

30 ● Chapter 8: Best Practices to manage the datalog

 MN914021EN, version 6

8.2.6 Limiting the size of the transaction log file for a Microsoft
SQL Server database
The transaction log file for a Microsoft SQL database can grow unexpectedly large. In such a
situation you need to shrink the file size.
To limit the size of the transaction log file:
1. Start Microsoft SQL Server Management Studio.
2. Using appropriate credentials, connect to the Microsoft SQL Server configured to host the
datalog.
3. Right click on the datalog database that you want to shrink and select Tasks > Shrink > Files.
4. Select Log as the File type.
5. Select the option Release unused space.
6. Click OK.

8.3 Managing the datalog

8.3.1 Keep historical data after changing the data point source
Important note: This section is only applicable for datalogs managed by Visual T&D
versions 4.x and 5.0.

After changing a data point source in Visual T&D, the old historical data for this point will no
longer be accessible. This is caused by the unique relation between the point and its historical data.
Running the following SQL scripts will take the previous (oldest) historical data that is bound to
the old point coming from the previous source and copy it to the new point. After the script is
successfully executed, the historical data of the old point will be shown as historical data of the
new point. This is basically the action of copying of the data from the old point to the new point.
This approach has the advantages:
- It does not alter the generic product schema and remains compatible with future Visual T&D
upgrades or downgrades.
- It allows to revert to previous site configuration or to a previous source for the points data to
be merged. Old data is still present.
However, you have to consider the following factors:
- Depending on how much data you will migrate, this will increase the size of the database.
You can eventually delete the old irrelevant data once the migration has been verified.
- The scripts are only copying historical data for analog points.
The scripts can be found in C:\Program Files (x86)\Eaton\Visual T&D\Server\Bin\

Note: For Visual T&D version prior to 5.3R1, the scripts can be found in C:\Program Files
(x86)\Cooper Power Systems\Visual T&D\Server\Bin\.

Script name Description

Integrating Visual T&D to a database server Technical Specification • 31

MN914021EN, version 6

Script name Description

Manage Database - 1 - This script creates stored procedures and types in

CreateProceduresAndTypes.sql preparation for the migration.

Manage Database - 2 - SetAnalogPoints.sql This script defines the analog point list to
migrate.

Manage Database - 3 - Migration.sql This script migrates from historical tables the
analog transitions that have been made orphan
by removing/moving their point from Visual
T&D configuration.
It iterates through the table named
points_migration_table that must have been
populated before.
Historical transitions of old_point_name will be
attributed to new_point_name.

Manage Database - 4 - DeleteOrphans.sql This script deletes the orphaned analog

transitions after data migration.

How to prepare:
1. Go through the four (4) scripts and replace the following generic tags:

Name Instructions

<DATABASE_NAME> Replace by the database instance name

The name of the database must match your
database name as specified in Visual T&D server
configuration. It may be prefixed with
“[SERVER_COMPUTER_NAME] \Visual
T&D Server\” before the site name.

<START_TIME> Replace by the start and end dates for the

modifications in the database.
<END_TIME> Try it for small period first, as you may reach the
limit of the SQL server memory if you try to
migrate years of data at once. If migrating one
(1) month of data succeeds, you can increase to
six (6) months, then a year and going iteratively
to reach the history needed to fulfill your data
retention requirements.
Do not overlap the periodEnd with the new point
historical data being currently already available
in Visual T&D.
periodEnd should be at most the oldest
timestamp of the new points historical data.

2. Modify the script Manage Database - 2 - SetAnalogPoints.sql to define the old

and new names for points for which you need to retrieve historical data. Those points can

32 ● Chapter 8: Best Practices to manage the datalog

 MN914021EN, version 6

have the same name if the point name itself did not change in the configuration but only their
source changed, which changed their prefix displayed in Visual T&D. Do not use prefix when
specifying the point name, just the name as specified in the Visual T&D site configuration.

How to execute:
1. Run the script Manage Database - 1 - CreateProceduresAndTypes.sql. This
creates required stored procedures and types. Make sure there are no errors during the
execution of the script.
2. Run the script Manage Database - 2 - SetAnalogPoints.sql. This creates the table
points_migration_table that includes all the points defined in the script. Make sure there are
no errors during the execution of the script.
3. Run the script Manage Database - 3 - Migration.sql. Look at the SQL output
messages and save it for points processed. This will show the errors, if any, and the KeyTags
IDs for further operations (eventual deletion, etc.). For example, an output should look like:

Old point name: 495/21-22_MW Old keytag: 6320000

New point name: 495/21-22_MW New keytag: 9520003
Records found for the point 495/21-22_MW: 736

4. Run the script Manage Database - 4 - DeleteOrphans.sql. This deletes orphaned

analog transitions after the data migration. It iterates through the table named
points_migration_table that must have been populated before. If the script is being halted, set
@pointMigrationListIndex to resume at this index.
5. Verify the availability of historical data using Visual T&D Explorer by running a query for
the specified period before iteratively going further in the migration process.

Integrating Visual T&D to a database server Technical Specification • 33

 MN914021EN, version 6

9 Database performance
optimization

Important note: This section is only applicable for datalogs managed by versions 5.0
and earlier of Visual T&D.

The Visual T&D datalog has been designed to operate efficiently both in terms of reading and
writing operations. Many factors influence the performance of the database, among other things:
 Hardware environment (e.g. hard drive; CPU and RAM; disk partitioning; etc.)
 Visual T&D data (e.g. quantity of tags; quantity of data transitions per period of time;
data query size and frequency; etc.)
 Visual T&D software (e.g. database tables design; real-time data reading and writing
strategies; etc.)
Recommended Visual T&D hardware requirements and default Visual T&D settings should be
sufficient for most of our customers’ sites requirements.
If necessary, to improve database performance, the following optimization operations can be
performed:
 Deploy the Visual T&D database on a dedicated server, or at least a dedicated drive.
 Contact your IT department to ensure the database server host machine is optimized in
terms of hardware parts and server environment settings.
 Keep the Visual T&D datalog small in size according to your requirements, by keeping
only the last X days of historical data. To learn how to do so, refer to the section Setting
up the Visual T&D Datalog of the Visual T&D Installation Guide.
 You can also adjust the bulk size writes and number of table partitions per your
requirements, as described in sections Adjusting the bulk size to optimize data writing
performance, page 35, and Adjusting the number of table partitions of the Visual T&D
database, page 38.

9.1 Adjusting the bulk size to optimize data

writing performance
In order to optimize writing performances, Visual T&D buffers a specific number of data
transitions before writing in the database. This number of transitions defines what we call a “bulk

Integrating Visual T&D to a database server Technical Specification • 35

MN914021EN, version 6

size”. The bulk size’s default value is hardcoded and overridable. However, the maximum amount
of time Visual T&D server shall wait before writing data to the database is harcoded and
definitive.
Since writing performances depend on incoming real-time transitions per second, the bulk size
value is defined according to three factors:
 The anticipated number of transitions per second from data sources characteristics,
denoted T. (e.g. 10,000 transitions per second)
 The number of transitions per second that Visual T&D server can write on the database,
considering the database capabilities on the hardware where it is installed and also
according to the T value.
 The period of time, in seconds, it takes to write the maximum bulk size of data transitions
on the database system.
On a fresh system installation, the bulk size’s default value is defined as a Visual T&D server
parameters, in the registry, and is set to 5000 transitions. This value is considered as an optimal
value for a PostgreSQL database. A Microsoft SQL database can gain better performances when
the bulk size value increases.
To determine the best bulk size value for your system, using Microsoft MS SQL:
1. Install Visual T&D with the target site activated, and ensure MS SQL is the active database.
Data sources should be connected and producing data.
2. Let the system run for a few hours.
3. Execute a graphical query on the system point _vss___logCacheSize.
4. Analyze the historical data:
a. If the cache is always at zero or the cache regularly returns to 0, you don’t need to modify
your system.
b. If usage of the cache is sustained or doesn’t stop increasing, change the bulk size for a
higher value.

To change the bulk size value:

1. Stop the Visual T&D server via the Windows Services panel.
2. Open the registry editor and edit/create the DWORD value BulkWriteMaxSize, located in
HKEY_LOCAL_MACHINE\SOFTWARE\[Wow6432Node]\Cybectec\Visual Substation
Server\DataLog\.
Notes:
 The maximum value that should be used is 15000.
 The drawbacks of increasing this value are:
 Increasing memory of the VTDServer.exe process
 Database occupancy (lock) for a longer period of time when writing data at once.
Visual T&D Server logs writing performances, in terms of transitions per second, only when the
number of transitions to write exceeds the bulk size quantity. The following steps can be
performed to monitor the database under stress.

36 ● Chapter 9: Database performance optimization

 MN914021EN, version 6

To monitor the current writing performance of the database:

1. Force Visual T&D Server to use transitions cache.
a. The quantity of transitions in the cache can be monitored by looking at the internal system
point _vss___logCacheSize, via Visual T&D Explorer.
b. Use of the cache can be triggered by momentarily having the database server inaccessible
by the Visual T&D server. For example, modify database credentials to prevent Visual
T&D Server from establishing communication with the database server.
2. Return to normal state of writing data into the database and let the system run, by letting the
cache down to 0.
3. Open the file \Program Files (x86)\Cooper Power Systems\Visual
T&D\Server\Bin\DatalogWriter.txt located on the Visual T&D Server.
4. Locate lines where you can read “Wrote XXXX items in YYYY ms (ZZZZ tr/s), type N”, where
N corresponds to the data type:
a. 0: Events
b. 1: Binary
c. 2: Analog
5. The average value of “ZZZZ” corresponds to your average data writing performance, in
terms of transitions per second.
Notes:
1. The system point _vss___logAvgTrPerSec can also be used to monitor database
writing performance. But because this is an instantaneous value, it does not
guarantee that the Visual T&D Server is writing the maximum number of data in
one operation. The value cannot be considered as the current maximum rate of
transitions that the database can achieve.
2. If you restart the Visual T&D server, the rates logged in the DatalogWriter.txt
file are below the maximum capability of the database. It is important to let the
Visual T&D server run until most of the points have been written at least once. After
that ramp up time, performance rates will be accurate.

As an example, we provided some data to help better understand the numbers explained
previously. The data was taken from an internal test bench.
Please note that many factors can influence database writing performances, like hard disk
controllers, firmware versions, whole system RAM, database number of client/connections, etc…
System specifications used for the example:
 Windows 7, 64 bit, I5-3340M @ 2.7 Ghz, 4Gb Ram
 SSD Hard Drive, rated 150Mb/sec

Default
Bulk Size 1000 5000 10000 15000
Rate(tr/sec) 4526 3930 2626 1114
PostgreSQL Typical delay to write the
0,2 1,4 4,4 13,7
bulk(s)
Microsoft Rate(tr/sec) 1461 1447 2700 4191
SQL Typical delay write the bulk(s) 0,7 3,4 4,4 5,2

Table 5 Example – Monitoring writing performances of the database

Integrating Visual T&D to a database server Technical Specification • 37

MN914021EN, version 6

9.2 Adjusting the number of table partitions of the

Visual T&D database
The Visual T&D database schema has been designed to store values in multiple table partitions.
The number of partitions will directly influence the performance of read and write operations. A
high quantity will advantage read operations, a small quantity will advantage write operations.

Note: Do not change this parameter on a system currently in production.

Please test your new settings in a laboratory environment; then, contact our technical assistance
team in order to validate these settings before proceeding with the reconfiguration of the actual
database.

To adjust the number of table partitions of the Visual T&D database:

1. If the Visual T&D site configuration already exists:
a. Stop the Visual T&D server service.
b. Rename the active Visual T&D database.
2. On the Visual T&D server computer:
a. In Windows Explorer, browse to the following folder:

[INSTALLATION PATH]\Program Files (x86)\Cooper Power

Systems\Visual T&D\Server\Bin

b. Open the Create Database SQL Server.sql script in a text editor (or Create
Database PostgreSQL.sql, if you are using a PostgreSQL database).
c. In the “CREATE OR REPLACE FUNCTION PartitionCount() RETURNS integer AS
$$” method, locate the “Return 900” command and replace “900” by a new value
determined in function of your requirements per read and write operations, and based on
the limits presented in the table below:

Minimum Maximum recommended

recommended value value (Higher reading
(Higher writing performance)
performance)
Microsoft SQL Server 2 900 (default value)
PostgreSQL 1 900 (default value)

Table 6 Number of table partitions of the Visual T&D database

3. Restart the Visual T&D Server.

Note: The number of transitions included in a single database write operation can also be adjusted.
For additional information, contact our Technical Assistance team.

38 ● Chapter 9: Database performance optimization

 MN914021EN, version 6

Appendix A: Resetting the password of a PostgreSQL

database
It is not possible to retrieve the password of the PostgreSQL database: when it is lost, it must be
reset.
To reset the password of a PostgreSQL database:
1. Temporarily change the local authentication method of the database:
a. Using any text editor, open the pg_hba.conf file located in the data folder of
the database server.

Note: If the PostgreSQL database (md5 method) was installed using the Visual
T&D installer, the default data folder is \Visual TD Datalog\.

b. For the local IPv4 connection (localhost address), replace the md5 or scram-
sha-256 authentication method by trust.
c. Save and close the pg_hba.conf file.
d. Restart the PostgreSQL database service to apply the configuration changes.
2. Start pgAdmin and connect to the database; note that no password is required, as the
database is currently into trust authentication mode.

Important note: PgAdminIII has the option to save your password. DO NOT
ACTIVATE THIS OPTION as it saves it in clear text in the user profile files. Consider
updating to PgAdmin4 for enhanced security.

3. Under the Login Roles branch, select the postgres branch; then, from the Edit
menu, click Properties

4. Type the new password in the Password and Password (again) boxes, and then
click OK; close the pgAdmin application.

In the pg_hba.conf file, change back the authentication method of the local connection to md5 or
scram-sha-256 (as it was), then restart the PostgreSQL service.

Integrating Visual T&D to a database server Technical Specification • A-1

 MN914021EN, version 6

Appendix B: Moving the Visual T&D Datalog database

to another disk drive
You can improve the performance of the Visual T&D system by putting the Visual T&D Datalog
database and the server operating system on different disk drives.

Important note: The following procedure only applies to the default PostgreSQL
database that has been deployed by the Visual T&D installer from version 4.1 to 4.4.
When applicable, the database appears in the database list as “Visual T&D”.

1. Access the Visual T&D Server Configuration.

2. Select the Data Logging tab.

3. Select the Visual T&D database, and then click Modify.

4. Type the path and name of the destination folder on the disk drive where the
database must be moved, or use the corresponding Browse button to locate and
select that folder.

Note: The destination folder must not exist prior to the move operation: it will be
created automatically by the Visual T&D Server Configuration application.

5. Click Finish.

Integrating Visual T&D to a database server Technical Specification • B-1

 MN914021EN, version 6

Appendix C: Deleting transitions manually

Important note: The following procedure only applies to version 5.0 and earlier of Visual
T&D.

Refer to document Visual TD Historian Database Data Dictionary for detailed information about
database schema. You may use the AnalogTransitions and BinaryTransitions views to
perform SQL delete statements, given you have resolved the points keys from the
DataPointDetail table.

C.1 Deleting in PostgreSQL

To delete all transitions, for all points for a specific timeframe, you may use the statement:
DELETE FROM "AnalogTransition"
WHERE
"Timestamp" >= TIMESTAMP '2019-10-06 00:00:00-00' AND
"Timestamp" < TIMESTAMP '2019-10-07 00:00:00-00';

For Analog transitions, and:

DELETE FROM "BinaryTransition"
WHERE
"Timestamp" >= TIMESTAMP '2019-10-06 00:00:00-00' AND
"Timestamp" < TIMESTAMP '2019-10-07 00:00:00-00';

For binary transitions. This example deletes transitions for october 6th 2019 at GMT.
To delete a specific point, you may use:
DELETE
FROM "AnalogTransition"
USING "DataPointDetail"
WHERE
"DataPointDetail"."KeyTag" = "AnalogTransition"."KeyTag" AND
"DataPointDetail"."Name" = 'L226.AA' AND
"AnalogTransition"."Timestamp" BETWEEN
TIMESTAMP '2020-01-31 15:00:00.000-05' AND
TIMESTAMP '2020-01-31 16:00:00.000-05';

The above example deletes transitions for point 'L226.AA' between 15:00 and 16:00 for
January 31st 2020 in GMT-5 time zone.

C.2 Deleting in SQL Server

To delete transitions for analog points named L225.AA, L225.AB or L225.AC for a
specific timeframe, you may use the statement:
DELETE at
FROM [dbo].[AnalogTransition] at
INNER JOIN [dbo].[DataPointDetail] dr
ON at.KeyTag = dr.KeyTag
WHERE
at.Timestamp >= CAST('2020-01-01 17:44:00 -05:00' AS DATETIMEOFFSET) AND
at.Timestamp <= CAST('2020-01-30 17:51:00 -05:00' AS DATETIMEOFFSET) AND
dr.Name LIKE 'L225.A%';

Integrating Visual T&D to a database server Technical Specification • C-1

MN914021EN, version 6

Similar delete statement can be used on binary transitions by replacing AnalogTransition with
BinaryTransition.

C-2 ● Appendix C: Deleting transitions manually

 MN914021EN, version 6

Appendix D: Migrating PostgreSQL 9 to 10

Upgrading to a newer major version of PostgreSQL requires to migrate the data using the
pg_upgrade tool included in PostgreSQL. This tool reforms the datastore in a much faster and
easier fashion than manual dump and restore.

Note: Please refer to pg_upgrade documentation at

www.postgresql.org/docs/10/pgupgrade.html to better understand the procedure.

Before performing the migration, prepare the roles in PostgreSQL 9.2 (or any version prior to 10).
Some Unicode characters in role descriptions are not properly processed, perform following steps
in order to redo those roles based on existing ones:
1. Open a pgsql command prompt, or a PgAdmin command window. For pgsql, the command
should look like:

psql --port=5433 --username:postgres

For which the result should look like:

psql (9.2.19)
Type "help" for help.
postgres=#

2. On this postgres=# prompt, enter the following commands to create new temporary roles
(omit to type the “postgres=#” part:
a. postgres=# CREATE ROLE "VTDAdministrators2" SUPERUSER CREATEDB
CREATEROLE INHERIT NOLOGIN REPLICATION VALID UNTIL 'infinity'
b. postgres=# COMMENT ON ROLE "VTDAdministrators2" IS 'Members are
owners of all objects in the VT&D Server database'
c. postgres=# CREATE ROLE "VTDBackupOperators2" NOSUPERUSER CREATEDB
CREATEROLE NOINHERIT NOLOGIN NOREPLICATION VALID UNTIL 'infinity'
d. postgres=# COMMENT ON ROLE "VTDBackupOperators2" IS 'Members have
access to backup/restore privileges and other maintenance
operations'
e. CREATE ROLE "VTDDataReaders2" NOSUPERUSER NOCREATEDB NOCREATEROLE
NOINHERIT NOLOGIN NOREPLICATION VALID UNTIL 'infinity'
f. COMMENT ON ROLE "VTDDataReaders2" IS 'Members can read data'
g. CREATE ROLE "VTDDataWriters2" NOSUPERUSER NOCREATEDB NOCREATEROLE
NOINHERIT NOLOGIN NOREPLICATION VALID UNTIL 'infinity'
h. COMMENT ON ROLE "VTDDataWriters2" IS 'Members can write data'
i. CREATE ROLE "VTDSecurityAdmins2" NOSUPERUSER NOCREATEDB CREATEROLE
NOINHERIT NOLOGIN NOREPLICATION VALID UNTIL 'infinity'
j. COMMENT ON ROLE "VTDSecurityAdmins2" IS 'Members can modify data
access privileges to other groups/roles'

Integrating Visual T&D to a database server Technical Specification • D-1

MN914021EN, version 6

3. Then reassign current roles to new temporary ones with the following commands:
a. REASSIGN OWNED BY "VTDAdministrators" TO "VTDAdministrators2"
b. DROP OWNED BY "VTDAdministrators"
c. REASSIGN OWNED BY "VTDBackupOperators" TO "VTDBackupOperators2"
d. DROP OWNED BY "VTDBackupOperators"
e. REASSIGN OWNED BY "VTDDataReaders" TO "VTDDataReaders2"
f. DROP OWNED BY "VTDDataReaders"
g. REASSIGN OWNED BY "VTDDataWriters" TO "VTDDataWriters2"
h. DROP OWNED BY "VTDDataWriters"
4. Set the names of new temporary roles to previous ones with the
following commands:
a. ALTER ROLE "VTDAdministrators2" rename TO "VTDAdministrators"
b. ALTER ROLE "VTDBackupOperators2" rename TO "VTDBackupOperators"
c. ALTER ROLE "VTDDataReaders2" rename TO "VTDDataReaders"
d. ALTER ROLE "VTDDataWriters2" rename TO "VTDDataWriters"

Then proceed with the migration by following those steps:

1. Install PostgreSQL 10.7 or a later 10 minor version.
2. Configure the new installation of PostgreSQL 10 per your needs by adjusting its parameters
as described in this document. Since PostgreSQL 9 is likely on port 5433, chose another port
for PostgreSQL 10. You may use the default 5432 but changing it is a good security practice.
3. Set the new PostgreSQL 10 installation and PostgreSQL 9 security to ‘trust’ for the time of
the migration (see the pg_hba.conf file and documented usage).
4. Stop Visual T&D Service.
5. If a Visual T&D historian database was created automatically (named with the Visual T&D
site name) in PostgreSQL 10, delete it.
6. Stop PostgreSQL 9 and PostgreSQL 10 services.
7. Set (or verify) the %PATH% environment variable to contain the ‘bin’ directory for
PostgreSQL 10. Typically: C:\Program Files (x86)\PostgreSQL\10\bin.
8. Open a cmd.exe command prompt executing as the PostgreSQL windows user running the
PostgreSQL server service. This user is usually “postgres” but Eaton recommends using
another name for security reason. This command prompt can be opened with the Windows
run: runas /user:postgres cmd.exe
9. On this command prompt, initialize the new PostgreSQL datastore by executing (on one
line):
initdb -D "C:\Program Files (x86)\PostgreSQL\10\data" --
locale="English_United States.1252" --encoding="UTF8"
You may use another data location than Program Files\... to ease later configuration of
datastore security.

D-2 ● Appendix D: Migrating PostgreSQL 9 to 10

 MN914021EN, version 6

10. Supposing PostgreSQL 9 data files are located in C:\Visual TD Datalog and PostgreSQL
10 data files in C:\Program Files (x86)\PostgreSQL\10\data, begin data migration
by executing:
pg_upgrade.exe --old-datadir "C:\Visual TD Datalog" --new-datadir
"C:\Program Files (x86)\PostgreSQL\10\data" --old-bindir "C:\Program
Files (x86)\PostgreSQL\9.2\bin" --new-bindir "C:\Program Files
(x86)\PostgreSQL\10\bin" --verbose

11. Configure Visual T&D to make it connect to the new database. The connection parameters in
Visual T&D will typically be the same as before, but using the new PostgreSQL 10 port. You
may keep the PostgreSQL 9 settings, but reactivating them only in case of problem with
PostgreSQL 10.
12. Start the newly installed PostgreSQL 10 service.
13. Start Visual T&D Server service to verify proper connection with the database.
Now that the historian data have been migrated, proceed to secure the database with the
following steps:
14. Stop Visual T&D Server service
15. Proceed to section Securing authentication with SHA256, page E-1.

Integrating Visual T&D to a database server Technical Specification • D-3

 MN914021EN, version 6

Appendix E: Enforcing SHA256 hashing on

PostgreSQL
Visual T&D version 4.4R2 or earlier can install PostgreSQL as default database but only with
default MD5 password hashing. This hashing algorithm is considered weak and should be changed
to SHA256.
The distributed version of PostgreSQL 9.2 with Visual T&D 4.1 to 4.4 did not support SHA256.
Therefore, new installations and upgrades should now use PostgreSQL 10.7.

E.1 About PostgreSQL password challenge

configuration
In Visual T&D version 4.4R2 or earlier, PostgreSQL is included in the installer. This PostgreSQL
package is built and shipped by EnterpriseDB with MD5 password encryption as default; EATON
is not allowed to alter this package and can only redistribute it as is. However, Eaton and the end
user’s IT support personnel can configure it after installation.
For the authentication process, the passwords are challenged with the encryption specified in the
pg_hba.conf file, located in the database folder. PostgreSQL uses this file for every
authentication process to select which hashing to use for verifying passwords provided by users.
After a modification, there is no need to restart the PostgreSQL service to enforce new settings.

For saving the user passwords, PostgreSQL uses the hashing system provided by the
“password_encryption” parameter of the postgresql.conf file. When a new password
encryption is specified, this parameter is enforced only after a restart of PostgreSQL service. Then,
only the new passwords will be hashed using the specified method.

E.2 Securing authentication with SHA256

Changing the way PostgreSQL hashes passwords requires to change stored passwords hashing and
authentication methods. To minimize operational downtime, proceed with following instructions
(section 1 AND 2 below):
1. Change the stored passwords hashing system:
a. Change how the new passwords will be stored from now on:
i. Change the value of the password encryption parameter in
postgresql.conf file to scram-sha-256. That is, change:

#password_encryption = md5 # md5 or scram-sha-256

Change it to (make sure to remove the leading “#” character)

password_encryption = scram-sha-256 # md5 or scram-sha-256

ii. Restart the PostgreSQL service. If the service was installed by Visual
T&D, this service name is Visual T&D Datalog, otherwise it is most
likely called PostgreSQL.

Integrating Visual T&D to a database server Technical Specification • E-1

MN914021EN, version 6

b. Alter the Visual T&D database administrator password to store it in SHA256

instead of MD5:
i. Open a command prompt at:
C:\Program Files(x86)\PostgreSQL\[MAJOR_VERSION]\bin
Where [MAJOR_VERSION] is the instance used for Visual T&D.
ii. In that command prompt, open a PSQL prompt by executing:
psql -U vtdpostgresadmin -d postgres -p 5433
Where vtdpostgresadmin is the administrator user name of Visual
T&D database. This is most likely ‘postgres’ , by default. Type in
the current password when prompt.
-p 5433 specifies the port to connect to. Your installation may be 5432.
iii. At the postgres=# prompt, execute the SQL command:
ALTER USER vtdpostgresadmin WITH password 'PASSWORD';
Where vtdpostgresadmin is the administrator user name of the Visual
T&D database. Replace ‘PASSWORD’ by your new password, or the
same as your previous one. Repeat this command for all other
PostgreSQL users if needed. For Visual T&D, there is only one needed;
most likely ‘postgres’.
iv. Verify proper password storage method by executing the SQL command:
SELECT usename, passwd FROM pg_catalog.pg_shadow;
The result should look like in the following example, notice SHA256 at
the beginning of the password string:
usename passwd
vtdpostgres SCRAM-SHA-
256$4096:EwXIQ+Lz4QNmRl3ckmC9Ag==$jbCBu9FT5FmTSRucGxKfgdM+JD
6qAmFggBBitjPWnOw=:f4510ceK6mvf6Ateekk6ciWRePIEkU0FRCgpuHvuQ
Xo=

2. Change the authentication method to fit the new stored passwords hashing system:
a. Now that the password is stored using SHA256, change the md5 values of the
pg_hba.conf file to scram-sha-256 in order to be able to authenticate.

host all all 127.0.0.1/32 md5

Change it to
host all all 127.0.0.1/32 scram-sha-256

host all all ::1/128 md5

Change it to
host all all ::1/128 scram-sha-256

b. There is no need to restart the PostgreSQL service to enforce the new settings.
c. To verify the new settings, restart Visual T&D Server service. It should now
connect to the database using the SHA256 hashing system.

E-2 ● Appendix E: Enforcing SHA256 hashing on PostgreSQL