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.