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)

Sending Log4j Logger Messages to Database[SQL SERVER]

Usually we use log4j to append the logger messages to log files in server but we can send the logger messages to Database by the following process

Here I developed an application to send logger messages to Database.

SaveLLogsToDB.java
====================

package com.omu;
package  com.omu;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;
import java.sql.*;
import java.io.*;
import java.util.*;

    public class SaveLLogsToDB {
       /* Get actual class name to be printed on */
       static Logger log = Logger.getLogger(SaveLLogsToDB.class.getName());
      
       public static void main(String[] args){
           String log4jConfPath = "src/resources/log4j.properties";
           PropertyConfigurator.configure(log4jConfPath);
          log.debug("Debug");
          log.info("Info");
          log.debug("amicorp");
          System.out.println("finish");
          System.out.println("Logs are saved to Database. Please open your Database and check the results");
          System.out.println("The logs are saved according to your system timings");
       }
    }















Log4j.properties
===================
   
# Define the root logger with appender file
log4j.rootLogger = DEBUG, DB
#
## Define the DB appender
log4j.appender.DB=org.apache.log4j.jdbc.JDBCAppender
#
## Set JDBC URL
#Database name i mentioned here as test1
log4j.appender.DB.URL=jdbc:sqlserver://localhost:1433;DatabaseName=test1
#
## Set Database Driver
log4j.appender.DB.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
#
## Set database user name and password
log4j.appender.DB.user=sa
log4j.appender.DB.password=Admin@123
#
## Set the SQL statement to be executed.
log4j.appender.DB.sql=INSERT INTO LOGGER VALUES('%x','%d','%C','%p','%m')
#
## Define the layout for file appender
log4j.appender.DB.layout=org.apache.log4j.PatternLayout
#
#

Attach the log4j and sqljdbc.jar files in build path.





Run SaveLLogsToDB.java you can see the logger messages by opening the DataBase.