Setting up ActiveMQ with MSSQL database

Normally the messages which we have sent are stored/retrieved in/from queues via KahaDB which is an inbuilt in ActiveMQ. So in order to save/send them database we need to follow the below process.

1.Create a new database in SQL Server with name activemq
2.If you already have activeMQ, edit or change the configuration in activemq.xml as

activemq.xml
==========
<beans
  xmlns="http://www.springframework.org/schema/beans"
  xmlns:amq="http://activemq.apache.org/schema/core"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
  http://activemq.apache.org/schema/core http://activemq.apache.org/schema/core/activemq-core.xsd">

    <!-- Allows us to use system properties and fabric as variables in this configuration file -->
    <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="properties">
            <bean class="org.fusesource.mq.fabric.ConfigurationProperties"/>
        </property>     
    </bean>
   
     <bean id="mssql-ds" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
      <property name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=activemq"/>
        <property name="username" value="username"/>
        <property name="password" value="password"/>
        <property name="poolPreparedStatements" value="true"/>
      </bean>


    <broker xmlns="http://activemq.apache.org/schema/core"
            brokerName="localhost">

        <destinationPolicy>
            <policyMap>
              <policyEntries>
                <policyEntry topic=">" producerFlowControl="true">
                  <pendingMessageLimitStrategy>
                    <constantPendingMessageLimitStrategy limit="1000"/>
                  </pendingMessageLimitStrategy>
                </policyEntry>
                <policyEntry queue=">" producerFlowControl="true" memoryLimit="1mb">
                </policyEntry>
              </policyEntries>
            </policyMap>
        </destinationPolicy>

        <managementContext>
            <managementContext createConnector="false"/>
        </managementContext>

         <persistenceAdapter>
         <!--   <kahaDB directory="${data}/kahadb"/>  -->
         <jdbcPersistenceAdapter dataDirectory="${activemq.base}/data" dataSource="#mssql-ds" />
      </persistenceAdapter>
 
        <plugins>
            <jaasAuthenticationPlugin configuration="karaf" />
        </plugins>

        <systemUsage>
            <systemUsage>
                <memoryUsage>
                    <memoryUsage limit="64 mb"/>
                </memoryUsage>
                <storeUsage>
                    <storeUsage limit="100 gb"/>
                </storeUsage>
                <tempUsage>
                    <tempUsage limit="50 gb"/>
                </tempUsage>
            </systemUsage>
        </systemUsage>
       
        <transportConnectors>
            <transportConnector name="openwire" uri="tcp://0.0.0.0:0?maximumConnections=1000"/>
        </transportConnectors>
    </broker>

</beans>


I have added an extra spring bean configuration mention and configuring Database properties in it
I have also commented the kahaDB PersistenceAdaptor and have configured our own JdbcPersistenceAdaptor.

3.Add mssql jdbc.jar and apache dbcp2 to the lib folder inside the ActiveMQ
4.Start the amq.bat file and login with the credentials.
5.Create a queue and send a message you will observe the following tables in DB
                         ACTIVEMQ_ACKS
                        ACTIVEMQ_LOCK
                        ACTIVEMQ_MSGS

ID | CONTAINER | MSGID_PROD | MSGID_SEQ | EXPIRATION | MSG | PRIORITY | XID |

+----+-------------------+-----------------------------------------------+-----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+

| 1 | queue://test1 | ID:omkar-laptop-86745-3612547785654-6:2:4:2 | 1 | 0 | � { )ID:omkar-laptop-86745-3612547785654-6:8 d test1 { )ID:omkar-laptop-86745-3612547785654-3:1 I|� �

hi . I|� � | 0 | NULL |

1 row in set (0.00 sec)