WSO2 Tutorial: Exchanging the embedded H2 database

In this blog I’ll explain one step that’s needed to get WSO2 ready for production: Changing the default database within your WSO2 stack.

I assume that you already have the database of choice installed either locally on somewhere on a server.

Embedded database

All WSO2 products have an embedded database. In almost all cases that database is H2, only WSO2 Identity Server has an embedded LDAP database.

H2 is not recommended for production and can cause problems when it is shared between products. Therefore, it is needed to replace the H2 database with for instance Oracle, MSSQL, MySQL or another JDBC enabled database in your WSO2 platform.

In many cases you will use the database your company has chosen as the default database. This could be an open source database (e.g. MariaDB, MySQL) or a commercial one (e.g. Oracle, MSSQL).

Supported databases in WSO2

When you look in the DBSCRIPTS directory of the WSO2 products you will find a number of scripts that have been included, this is the list included in the ESB 5.0 (Carbon 4.4.0):

1. Setting up IBM DB2 (db2.sql)
2. Setting up Derby (derby.sql)
3. Setting up H2 (h2.sql)
4. Setting up IBM Informix (informix.sql)
5. Setting up Microsoft SQL (mssql.sql)
6. Setting up MySQL (mysql.sql)
7. Setting up a MySQL Cluster (mysql_cluster.sql)
8. Setting up OpenEdge (openedge.sql)
9. Setting up Oracle (oracle.sql)
10. Setting up Oracle RAC (oracle_rac.sql)
11. Setting up PostgreSQL (postgresql.sql)

Each script will create all tables, indexes and so on for WSO2 products to use that database. Just pick the one you need and in cases your database is not in the list (e.g. MariaDB) pick the one closest and make changes if needed. In case of MariaDB that would by MySQL since MariaDB is a fork of MySql. The files have varying file lengths, depending on the SQL dialect and if it is a clustered setup. For the use of Cassandra as a datastore there is a separate section in the user-mgt.xml that you need to uncomment. Please look at the documentation online from WSO2 for more information.

Referencing the new database

After the creation of the new databases using the method with the script. the databases needs to be referenced to  and the appropriate driver needs to be in place.

This means the JDBC driver (JAR file) needs to be in the /repository/components/lib directory and of course the reference to the current H2 database in the masterdata-sources.xml needs to now point to the new database. This is the setting for a MySQL installed at localhost at the default 3306 port.

This database (REGDB) has both registry and user management tables inside.

<datasource>
       <name>WSO2_CARBON_DB</name>
       <description>The datasource used for registry and user manager</description>
       <jndiConfig>
           <name>jdbc/WSO2CarbonDB</name>
       </jndiConfig>
       <definition type="RDBMS">
           <configuration>
               <url>jdbc:mysql://localhost:3306/regdb</url>
               <username>regadmin</username>
               <password>regadmin</password>
               <driverClassName>com.mysql.jdbc.Driver</driverClassName>
               <maxActive>80</maxActive>
               <maxWait>60000</maxWait>
               <minIdle>5</minIdle>
               <testOnBorrow>true</testOnBorrow>
               <validationQuery>SELECT 1</validationQuery>
               <validationInterval>30000</validationInterval>
           </configuration>
       </definition>
</datasource>

The changes needed concern most notably the following fields:

• URL
• Username
• Password
• Driver Class Name

The example above concerns MySQL with a regdb database running on a default (3306) port on localhost.  This example and the tables in regdb have both user and registry data in them.  The scripts used to create the tables create both within the regdb database (WSO2CarbonDB). For a separated user and registry database, more work is needed.

Separate USER and REGISTRY database

The easiest way is to take the script for the databases / tables and run them on both intended databases so the full set of tables is created in duplicate. This is a bit superfluous but the easiest way.

In the respective config files (user-mgt.xml and registry.xml)  you reference the databases for user (let’s call it WSO2USERDB) and for registry (WSO2REGISTRY).

Of course both need to be defined in the master-datasources.xml like the example in the previous paragraph.

Figure 1 Change the datasource to the separate USERDB

For the user-mgt.xml the changes need to be made as shown below (we use the names defined above):

<Configuration>
    <Property name="dataSource">jdbc/WSO2USERDB</Property>
</Configuration> 

The registry.xml has a similar change that needs to be made:

<dbConfig name="wso2registry">
    <dataSource>jdbc/WSO2REGISTRY</dataSource>
</dbConfig>

Note that we only change the reference to the database (datasource).

Figure 2 Change the datasource to the separate registry database

Alternative setup

If you do not want to run the script yourself there is the possibility to start the wso2server with the –Dsetup parameter and the table will be created for you.

This works for the standard setup (one database for user and registry data) as well as for a situation with a separated database for user and registry. The script is run and creates in both databases the complete set of tables (user and registry).

In case you want to use WSO2server.bat –Dsetup parameter at runtime (.SH on linux) the reference to the database needs to be in place beforehand.

If you have any questions about this blogpost contact us via the comments section of this blog. View also our WSO2 Tutorials, webinars or white papers for more technical information. Need support? We do deliver WSO2 Product Support, WSO2 Development Support, WSO2 Operational Support and WSO2 Training Programs.