DataBase

[puissancei]

Hello,

I want to configure 2 servers for loadbalancing.
I saw it's possible to use the following DB
Oracle, IBM DB2, Microsoft SQL Server, MySQL, SQLite, PostGre

But what version of each product ?

is it possible (and recommanded) to use xTour with MS SQL Express ?

Can I use Derby database from only one of the 2 servers ?

Thank you

[Bernd Welter]

Hello Michel,

I think we can only rely on a meta level:
I used MS SQL Server in my local environment as JOB database and it worked fine.

I recommend to configure the RDBMS you have for being the JOB database. If this doesn't work: get in touch with PTV techn staff.

You can also use the DERBY database of your "primary" xServer, the question is, whether you want to build a load balancing structure because you want to ensure

• failover handling, then the DERBY is still a single point of failure. If the primary xServer crashes any further async transaction from xTour2, xTour3, ... will also crash. Therefore it might be better to use a database with an overaverage availability.
• increased throughput, then the DERBY can satisfy that requirement

If you need help for the configuration of the job database get in touch with Clement.

Best regards,
Bernd

Here's just a sample of a /conf/job-management-db.xml:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<beans default-lazy-init="true"
	xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:tx="http://www.springframework.org/schema/tx" xmlns:jdbc="http://www.springframework.org/schema/jdbc"
	xmlns:util="http://www.springframework.org/schema/util"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
	http://www.springframework.org/schema/tx
	http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
	http://www.springframework.org/schema/jdbc
	http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd">

	<bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource"
		destroy-method="close">
		<property name="driverClass" value="org.apache.derby.jdbc.ClientDriver" />
		<property name="jdbcUrl" value="jdbc:derby://localhost:50096/job" />
		<property name="username" value="JOB" />
		<property name="password" value="JOB" />
        <property name="lazyInit" value="true" />
        <property name="maxConnectionsPerPartition" value="32" />
        <property name="connectionTimeoutInMs" value="5000" />  <!-- In case of Windows Azure SQL set connectionTimeoutInMs to 2000 -->
        <property name="acquireRetryDelayInMs" value="1000" />
        <property name="connectionTestStatement" value="select 1" />
       <!-- In case of Windows Azure SQL enable this setting
          <property name="maxConnectionAgeInSeconds" value="30" />
        -->
	</bean>

	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<constructor-arg type="javax.sql.DataSource" ref="dataSource" />
	</bean>

</beans>

[puissancei]

It's clear

Thank you

[puissancei]

Can we imagine put the unique table XSERVER_JOBS in the DB used for our product ?
How is it solicited for exemple when we have 10 simultaneous optimization ?

[Bernd Welter]

Hello Michel,

I recommend to separate the JOB DATABASE from your application database. Otherwise you'd produce too many constraints with an impact on the architecture as a whole.

Here's an example of how to create the MS SQL database table (I used varbinary(max) instead of BLOB for some of the types).

Code: Select all

CREATE TABLE XSERVER_JOBS(
ID varchar(36) PRIMARY KEY NOT NULL,
  XSERVER varchar(18),
  METHOD varchar(50),
  STATUS varchar(20) NOT NULL,
  ELAPSED bigint,
  PROGRESS varbinary(max),
  RESULT varbinary(max),
  FINISHTIME bigint,
  FETCHTIME bigint,
  LASTUPDATETIME bigint,
  USERID varchar(36)
);
CREATE INDEX XSERVER_JOBS_IDX1 ON XSERVER_JOBS(STATUS);
CREATE INDEX XSERVER_JOBS_IDX2 ON XSERVER_JOBS(FINISHTIME);
CREATE INDEX XSERVER_JOBS_IDX3 ON XSERVER_JOBS(FETCHTIME);
CREATE INDEX XSERVER_JOBS_IDX4 ON XSERVER_JOBS(LASTUPDATETIME);

If you

• use several xServers configured to use the same job database or
• set the number of backend modules to a value greater than 1 or
• both

this is going to work fine. A new transaction will get a new JOB ID and the module will create a new record in the job database. You can easily track the transactions in progress using MS SQL Profiler.

Here's a little sample video:

Screenshot from the video. Shows 3 active transations in the JOB database table and 3 successfully ended ones. https://www.youtube.com/watch?v=XAaY1qd7J4A

Best regards,
Bernd