HA ActiveMQ brokers running against a SQL Server 2008 database > large transaction logs

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

HA ActiveMQ brokers running against a SQL Server 2008 database > large transaction logs

magellings
Greetings,

DBA with here with some questions for the ActiveMQ development and user community, hopefully you can provide some insight or relate experiences...

We have HA ActiveMQ brokers running against a SQL Server 2008 database that is mirroring to secondary site.  During a recent failover we found that the oldest of the ActiveMQ databases took almost 35 minutes to open. The transaction log had grown to 13 GB despite regular backups.  

Our environment has dozens of servers and hundreds of databases, ActiveMQ is relatively new I our environment but has been running for months before a failover. (We are 24x7x365 shop and production requires HA, DR and fast recovery on system failures)

We determined this was due to ActiveMQ broker keeping a open transaction and issuing a UPDATE statement every 30 seconds. It became clear that ActiveMQ brokers must commit those transactions or rollback sometime… to maintain log file size and allow speedy database failover to mirror. ActiveMQ is quickly becoming a HUB for communication between applications  and production processes.

We are not using a witness in our standard mirroring setup so we do a manual failover utilizing DNS alias for maintaining client connectivity.

We can set the broker lock UPDATE frequency lower or to once…but the transaction remains open and the transaction log would continue to grow.

The other thought was to regularly cycle between the two brokers. This would close one broker server and transactions would commit or rollback and then the log file could be backed up and truncated. (My favorite idea…)

What is the communities take on SQL Server Mirroring and has anyone utilized ActiveMQ HA persistent  broker configuration with SQL Server Mirroring?

Thanks!
Reply | Threaded
Open this post in threaded view
|

Re: HA ActiveMQ brokers running against a SQL Server 2008 database > large transaction logs

gtully
There are a few options.
 - use a separate database for the lock table, it is possible to
configure a separate datasource for the locker via config
- if you manually manage broker restart and can ensure exclusive
access to the activemq tables, you could disable locking via
configuration.
- you can implement a variation of the locker that uses over lapping
transactions or some update mechanism using timestamps and configure
it as the database locker
- you can implement a variation of the locker that uses some other
shared resource as a semaphore

some relevant issues that may give some more inspiration
 https://issues.apache.org/activemq/browse/AMQ-1244
 https://issues.apache.org/activemq/browse/AMQ-2520

On 10 August 2010 17:46, magellings <[hidden email]> wrote:

>
> Greetings,
>
> DBA with here with some questions for the ActiveMQ development and user
> community, hopefully you can provide some insight or relate experiences...
>
> We have HA ActiveMQ brokers running against a SQL Server 2008 database that
> is mirroring to secondary site.  During a recent failover we found that the
> oldest of the ActiveMQ databases took almost 35 minutes to open. The
> transaction log had grown to 13 GB despite regular backups.
>
> Our environment has dozens of servers and hundreds of databases, ActiveMQ is
> relatively new I our environment but has been running for months before a
> failover. (We are 24x7x365 shop and production requires HA, DR and fast
> recovery on system failures)
>
> We determined this was due to ActiveMQ broker keeping a open transaction and
> issuing a UPDATE statement every 30 seconds. It became clear that ActiveMQ
> brokers must commit those transactions or rollback sometime… to maintain log
> file size and allow speedy database failover to mirror. ActiveMQ is quickly
> becoming a HUB for communication between applications  and production
> processes.
>
> We are not using a witness in our standard mirroring setup so we do a manual
> failover utilizing DNS alias for maintaining client connectivity.
>
> We can set the broker lock UPDATE frequency lower or to once…but the
> transaction remains open and the transaction log would continue to grow.
>
> The other thought was to regularly cycle between the two brokers. This would
> close one broker server and transactions would commit or rollback and then
> the log file could be backed up and truncated. (My favorite idea…)
>
> What is the communities take on SQL Server Mirroring and has anyone utilized
> ActiveMQ HA persistent  broker configuration with SQL Server Mirroring?
>
> Thanks!
>
> --
> View this message in context: http://old.nabble.com/HA-ActiveMQ-brokers-running-against-a-SQL-Server-2008-database-%3E-large-transaction-logs-tp29399881p29399881.html
> Sent from the ActiveMQ - User mailing list archive at Nabble.com.
>
>



--
http://blog.garytully.com

Open Source Integration
http://fusesource.com