Scada-LTS with MSSQL?

[fabiodurao]

I would like to know if there is any possibility of integrating Scada-LTS with MSQQL, making it a native historian instead of MySQL.

Justification: Two clients placed the use of MSSQL as a condition for Scada-LTS to be approved, a client from Brazil and a multinational from Portugal.

So I decided to do something similar to what I do to create a new application with MySQL (when I want to have 2 Scada-LTS on the same server) within Tomcat, adapting it to MSQQL, and see if it would work.

I followed these steps:

1 - I installed SQL Server 2022 Express (MSSQL free version);
2 - I released the port and registered a password (for the "sa" user) and tested the connection using Dbeaver, and it worked;
3 - In Dbeaver, with this same user, I created a new DB;
4 - I stopped Tomcat;
5 - And in \tomcat\webapps I copied the Scada-LTS.war file and pasted it in the same folder and renamed it to Scada-LTS_MSSQL.war
6 - I started Tomcat so it would generate the Scada-LTS_MSSQL folder and after the start was finished, I stopped Tomcat again;
7 - I searched for some mssql .jar driver inside the Tomcat folder and didn't find it, so I downloaded the mssql-jdbc-12.8.1.jre11.jar version compatible with Java 11 and pasted it in the \tomcat\lib folder;
8 - I changed the content of the \tomcat\conf\context.xml file by adding a new with the following drive and credentials;

<Resource name="jdbc/scadalts3"
          auth="Container"
          type="javax.sql.DataSource"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          testWhileIdle="true"
          testOnBorrow="true"
          testOnReturn="false"
          validationQuery="SELECT 1"
          validationInterval="30000"
          timeBetweenEvictionRunsMillis="30000"
          maxActive="100"
          minIdle="10"
          maxWait="10000"
          initialSize="10"
          removeAbandonedTimeout="1000"
          removeAbandoned="true"
          abandonWhenPercentageFull="75"
          logAbandoned="true"
          minEvictableIdleTimeMillis="30000"
          jmxEnabled="true"
          jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
                            org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;
                            org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer;
                            org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReport(threshold=1500)"
          username="sa"
          password="KDAS73MBjlzE7ZRZ"
          driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
          url="jdbc:sqlserver://localhost:1433;databaseName=scadalts;encrypt=true;trustServerCertificate=true"
          defaultTransactionIsolation="READ_COMMITTED"
/>

9 - I changed the env.properties file inside the new application folder pointing to the new ; 10 - I started Tomcat and looked in Dbeaver to see if any tables would be created - I saw that there was a file \WEB-INF\db\createTables-mssql.sql and I imagined that it would create the tables automatically, as happens with MySQL.

db.type=mssql

db.datasource=true

db.datasourceName=java:comp/env/jdbc/scadalts3

But in the end, the tables were not created and the application consequently did not go up, the other pre-existing applications worked normally.

Any help would be welcome.

[Limraj]

Hi @fabiodurao,
Let's check this topic, but the closest topic regarding MS SQL database support concerns special components in the graphical view that enable integration with such a server and display a given table.
There could definitely be an issue with SQL query compatibility, so even if it creates a basic table schema, there's a high risk that some features won't work. However, even the process of creating tables, procedures, triggers and functions may not work on this server due to syntax differences.

If you have clients and need support that requires a lot of time or implementation, we encourage business cooperation.

Regards,
Kamil Jarmusik