How to use SQL Server 2008 R2 as persistent storage for ActiveMQ 5.9

Question

My IT infrastructure requires persistence based on a SQL Server 2008 R2 database.

I'm trying to configure SQL Server persistence for ActiveMQ 5.9 on windows, but can't figure out a correct configuration schema with or without journalling.

The default configuration uses kahadb, and it works:

<persistenceAdapter>
    <kahaDB directory="${activemq.data}/kahadb" />
</persistenceAdapter>

I'm using this documentation a reference to configure SQL Server persistence: http://activemq.apache.org/sqlserver.html

<persistenceAdapter>
  <journaledJDBC journalLogFiles="5" dataDirectory="../activemq-data" dataSource="#mssql-ds">
    <adapter><imageBasedJDBCAdaptor/></adapter>
  </journaledJDBC>
</persistenceAdapter>

<bean id="mssql-ds" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
  <property name="url" value="jdbc:sqlserver://HOST:PORT;databaseName=DBNAME;user=USER;password=PASSWORD"/>
  <property name="username" value="USER"/>
  <property name="password" value="PASSWORD"/>
</bean>

This is the error in xml parser I get:

ERROR | Failed to load: class path resource [activemq.xml], reason: Line 88 in XML document from class path resource [activemq.xml] is invalid; nested exception is org.xml.sax.SAXParseException; lineNumber: 88; columnNumber: 95; cvc-complex-type.2.4.a: Invalid content was found starting with element 'journaledJDBC'. One of '{"http://activemq.apache.org/schema/core":jdbcPersistenceAdapter, "http://activemq.apache.org/schema/core":journalPersistenceAdapter, "http://activemq.apache.org/schema/core":kahaDB, "http://activemq.apache.org/schema/core":levelDB, "http://activemq.apache.org/schema/core":mKahaDB, "http://activemq.apache.org/schema/core":memoryPersistenceAdapter, "http://activemq.apache.org/schema/core":replicatedLevelDB, WC[##other:"http://activemq.apache.org/schema/core"]}' is expected.

Please help me to figure out the configuration schema for SQL Server persistence, or provide a documentation link specific to version ActiveMQ 5.9

select @@version -> Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) — George Polevoy
OK thanks - updated body of your message, and the tags accordingly — marc_s
The document you are referencing appears to be outdated. If you look at the xsd then you see that the tag was renamed to journalPersistenceAdapter and also the attributes have somewhat changed. The xsd tells you (in an inconvenient format) how the XML document has to look like in order to get parsed without error. — Ralf
@Ralf Thanks, please post a working config and not just a valid xml snippet that satisfies the parser, it would be too easy. — George Polevoy
@GeorgePolevoy I would if I had it. Just thought that as the documentation is outdated you can help yourself with the xsd. — Ralf

Leo Leo · Accepted Answer · 2014-02-11T13:10:37

this is what I use for oracle. Maybe it's just a matter of changing the jdbc information from the datasource (as any other datasource)

(sorry, no SQL Server here to test)

<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.xsd
      http://activemq.apache.org/schema/core http://activemq.apache.org/schema/core/activemq-core.xsd">

    <broker xmlns="http://activemq.apache.org/schema/core" brokerName="localhost" dataDirectory="${activemq.data}">
        <destinationPolicy>
            <policyMap>
                <policyEntries>
                    <policyEntry topic=">" producerFlowControl="true">
                        <pendingMessageLimitStrategy>
                            <constantPendingMessageLimitStrategy limit="1000" />
                        </pendingMessageLimitStrategy>
                    </policyEntry>
                    <policyEntry queue=">" producerFlowControl="true" memoryLimit="1mb">
                    </policyEntry>
                </policyEntries>
            </policyMap>
        </destinationPolicy>

        <persistenceAdapter>
            <jdbcPersistenceAdapter dataSource="#oracle-ds" />
        </persistenceAdapter>

        <systemUsage>
            <systemUsage>
                <memoryUsage>
                    <memoryUsage limit="128 mb" />
                </memoryUsage>
                <storeUsage>
                    <storeUsage limit="100 gb" />
                </storeUsage>
                <tempUsage>
                    <tempUsage limit="50 gb" />
                </tempUsage>
            </systemUsage>
        </systemUsage>

        <transportConnectors>
            <transportConnector name="tcp" uri="tcp://0.0.0.0:61616" />
        </transportConnectors>
    </broker>

    <bean id="oracle-ds" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="oracle.jdbc.OracleDriver" />
        <property name="url" value="jdbc:oracle:thin:@localhost:1521:XE" />
        <property name="username" value="xxx" />
        <property name="password" value="xxx" />
        <property name="poolPreparedStatements" value="true" />
        <property name="maxActive " value="30" />
    </bean>

</beans>

How to use SQL Server 2008 R2 as persistent storage for ActiveMQ 5.9

1 Answers