ActiveMQ - SQL Server - Change default schema

When using ActiveMQ JBDB Persistence Adapter with SQL Server, all tables are created in the default dbo schema. If you use a Master/Slave configuration (http://activemq.apache.org/jdbc-master-slave.html), all brokers will share this schema and use these tables to synchronize.

Then, I was thinking of having a network of brokers with two pairs of Master/Slave brokers. There would be the following brokers:

• Broker AM. Master broker of "subnetwork" A
• Broker AS. Slave broker of "subnetwork" A
• Broker BM. Master broker of "subnetwork" B
• Broker BS. Slave broker of "subnetwork" B

Because of you have two pairs of Master/Slave, you need two databases to syncronize them, each one with its default dbo chema. But if you set two diferent connection users and, for each user, you set a different default schema, SQL Server will use this schema in all SQL Sentences. So you can use the same database and all the extra config will be handled by SQL Server.

The scenario will be as follows:

In SQL Server Management Studio you create two users:

• ActiveMQ1
• ActiveMQ2

Then you associate these users with the storage database.

Next, you create the two schemas and set the corresponding user as owner. Schemas are created in [database] > Security > Schemas option.

Finally, set the default schema for each user. This is done in [database] > Security > Users > [user].

Done. When brokers start, the master of each network (the first one to start) will create its tables in its own schema.

ActiveMQ - SQL Server - JDBC Master-Slave

These are the basic instructions to configure a Shared JDBC Persistence Adapter with SQL Server. The objective is to prepare a master/slave configuration with two or more Brokers.

Tested with ActiveMQ 5.14.4.

Add the following dependencies to ACTIVEMQ_HOME/lib/extra in all brokers:

• Apache commons dbcp2
• Apache commons pool 2
• sqljdbc4

Edit the broker config in ACTIVEMQ_HOME/conf/activemq.xml, for all nodes:

<beans ... >

 (...)
  
 <bean id="sqlServerDS" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
   <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
   <property name="url" value="jdbc:sqlserver://windowsServer-R2:1433;DatabaseName=ActiveMQ"/>
   <property name="username" value="activemq"/>
   <property name="password" value="activemq"/>
   <property name="poolPreparedStatements" value="true"/>
 </bean>
 
 (...)
  
 <broker xmlns="http://activemq.apache.org/schema/core" brokerName="brokerA" dataDirectory="${activemq.data}">  
     (...)
  
     <persistenceAdapter>
        <jdbcPersistenceAdapter dataDirectory="${activemq.base}/data" dataSource="#sqlServerDS" />          
     </persistenceAdapter>
  
     (...)
 </broker>
</beans>

The database should be empty and the first time the first broker starts, all tables will be automatically created (you could get some warnings at this point)