JDBC with Failover MS SQL

[abrasha70 <ya...@gmail.com>]

Hi,

I set an ActiveMQ service based on database using JDBC to a Failover MS SQL
Server Database.

When the SQL server fails-over, the AMQ service goes down for some reason,
and doesn't go up back again.

How can I prevent it?
Do I need to configure anything?
I'm using version 1.11.1

***

Here's what I configured in the Jetty.xml:
<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://localhost:1433;databaseName=AMQDB;user=MyUserName;password=MyPassword"/>
</bean>

Here's what I configured in the ActiveMQ.xml:
<persistenceAdapter>
	<jdbcPersistenceAdapter dataDirectory="${activemq.base}/data"
dataSource="#mssql-ds" createTablesOnStartup="true">
		<adapter>
			<transact-jdbc-adapter />
		</adapter>
		<databaseLocker>
			<transact-database-locker/>
		</databaseLocker>
	</jdbcPersistenceAdapter>
</persistenceAdapter>



--
View this message in context: http://activemq.2283324.n4.nabble.com/JDBC-with-Failover-MS-SQL-tp4702654.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.

Re: JDBC driver with Failover (Master-Slave) MS SQL database

[Tim Bain <tb...@alumni.duke.edu>]

Thanks for clarifying about the behavior when manually restart the broker
after a failover.

Are you using Tanuki's ability to restart a process automatically?  If so,
does Tanuki retry repeatedly while the database is in the process of
failing over?
On Nov 2, 2015 7:39 AM, "Abrasha70" <ya...@gmail.com> wrote:

> Hi Tim,
>
> Thank you for your assistance and your quick response.
>
> As for the questions, let me try to explain better:
>
> 1. We have 2 ActiveMQ Services installed (A, B) on different machines
> (Amq1,
> Amq2)
>    which are configured in ActiveMQ Failover configuration-
>    Both relay on a single SQL database.
>    This single database is configured in SQL Failover configuration
>    using 2 Microsoft SQL Servers (Server1, Server2)
>
> 2. When shutting one of A or B,
>    it fails-over *properly* to the other one.
>
> 3. However when the database fails-over-
>    both ActiveMQ *services* A and B go down.
>
> 4. When starting again the services they connect properly.
>
> So to sum it up:
> The problem is that when there's SQL Failover, the ActiveMQ can't recover
> by
> itself,
> It won't reconnect by itself until we start manually back the Service.
>
> I can't believe that this is the normal behavior of ActiveMQ,
> Especially since the SQL Failover starts and ends within a very short
> period
> of 30 seconds or so.
>
> * When I say "Service" - I infer to the installed ActiveMQ Windows Service.
>
>
>
> --
> View this message in context:
> http://activemq.2283324.n4.nabble.com/Crushing-JDBC-driver-with-Failover-Master-Slave-MS-SQL-database-tp4702654p4703584.html
> Sent from the ActiveMQ - User mailing list archive at Nabble.com.
>

Re: JDBC driver with Failover (Master-Slave) MS SQL database

[Tim Bain <tb...@alumni.duke.edu>]

My last question in that three-year-old thread applies to you too: are you
using a wrapper such as Tanuki that would detect the broker process exiting
and respond by restarting the ActiveMQ process? This would be the expected
way to handle this situation, so if you're not doing that, it's what I'd
recommend.

Tim

On Thu, Feb 14, 2019, 1:58 PM ramapr00 <prabhakaransr@hotmail.com wrote:

> I have the same issue with ActiveMQ. I have two nodes of AMQ and it is
> configured to use MS SQL with database cluster. When the database cluster
> fails over, my AMQ stops working and I need to re-start it manually to fix
> the issue. Could you please post how did you fix your issue?
>
>
>
> --
> Sent from:
> http://activemq.2283324.n4.nabble.com/ActiveMQ-User-f2341805.html
>

Re: JDBC driver with Failover (Master-Slave) MS SQL database

[ramapr00 <pr...@hotmail.com>]

I have the same issue with ActiveMQ. I have two nodes of AMQ and it is
configured to use MS SQL with database cluster. When the database cluster
fails over, my AMQ stops working and I need to re-start it manually to fix
the issue. Could you please post how did you fix your issue?



--
Sent from: http://activemq.2283324.n4.nabble.com/ActiveMQ-User-f2341805.html

Re: JDBC driver with Failover (Master-Slave) MS SQL database

[Abrasha70 <ya...@gmail.com>]

Hi Tim,

Thank you for your assistance and your quick response.

As for the questions, let me try to explain better:

1. We have 2 ActiveMQ Services installed (A, B) on different machines (Amq1,
Amq2)
   which are configured in ActiveMQ Failover configuration-
   Both relay on a single SQL database.
   This single database is configured in SQL Failover configuration 
   using 2 Microsoft SQL Servers (Server1, Server2)

2. When shutting one of A or B, 
   it fails-over *properly* to the other one.

3. However when the database fails-over- 
   both ActiveMQ *services* A and B go down.

4. When starting again the services they connect properly.

So to sum it up:
The problem is that when there's SQL Failover, the ActiveMQ can't recover by
itself,
It won't reconnect by itself until we start manually back the Service.

I can't believe that this is the normal behavior of ActiveMQ,
Especially since the SQL Failover starts and ends within a very short period
of 30 seconds or so.

* When I say "Service" - I infer to the installed ActiveMQ Windows Service.



--
View this message in context: http://activemq.2283324.n4.nabble.com/Crushing-JDBC-driver-with-Failover-Master-Slave-MS-SQL-database-tp4702654p4703584.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.

Re: JDBC driver with Failover (Master-Slave) MS SQL database

[Tim Bain <tb...@alumni.duke.edu>]

This is normal and expected behavior for ActiveMQ with KahaDB as well.
I've never used LevelDB, so I can't speak to its behavior, though I have no
reason to believe it would behave differently than the other two.

ActiveMQ can't work without a backing store if you've configured it to
require one.  What would it do while it was up without the ability to
accept messages?  Should it keep accepting connections when it can't accept
or deliver messages on them, depriving clients of the ability to fail over
to a broker that was actually alive?  This is how ActiveMQ works, and I'm
not aware of a way to configure it to behave otherwise (nor do I think such
an ability should exist).

If you need a workaround that allows you to handle periods of time (short
or long) when your database is unavailable, you could script a process to
watch for the ActiveMQ process to not be running and restart it...  It
wouldn't be unreasonable to allow the ability to configure how long to
retry the connection before giving up on it and halting the server, so you
could submit an enhancement request for that if you wanted.  But for right
now, monitoring and restarting the process seems like your best bet.

Also, you haven't answered my question: can ActiveMQ connect successfully
after the database failover?  Is the problem simply that ActiveMQ is
re-testing the connection sooner than the failover process makes it
available again?  Or is the problem that your broker can't connect to a
failed-over database no matter how long you wait?

Tim

On Sun, Nov 1, 2015 at 1:26 AM, Abrasha70 <ya...@gmail.com> wrote:

> Of course it fails, but it should not put the service down.
> The service should keep running until it can reconnect.
>
> I believe this is not a normal behavior of ActiveMQ,
> and if it is, how can I config it otherwise?
>
>
>
> --
> View this message in context:
> http://activemq.2283324.n4.nabble.com/Crushing-JDBC-driver-with-Failover-Master-Slave-MS-SQL-database-tp4702654p4703577.html
> Sent from the ActiveMQ - User mailing list archive at Nabble.com.
>

Re: JDBC driver with Failover (Master-Slave) MS SQL database

[Abrasha70 <ya...@gmail.com>]

Of course it fails, but it should not put the service down.
The service should keep running until it can reconnect.

I believe this is not a normal behavior of ActiveMQ,
and if it is, how can I config it otherwise?



--
View this message in context: http://activemq.2283324.n4.nabble.com/Crushing-JDBC-driver-with-Failover-Master-Slave-MS-SQL-database-tp4702654p4703577.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.

Re: JDBC driver with Failover (Master-Slave) MS SQL database

[Tim Bain <tb...@alumni.duke.edu>]

So the logs pretty clearly say that the broker shut down because the broker
lost the JDBC lock (because it couldn't talk to the database) and that it
failed to restart a little later because it still couldn't talk to the
database.

When the database has failed over and ActiveMQ fails to reconnect, will
ActiveMQ connect successfully if you stop the ActiveMQ process and then
restart it?  Is the problem only that ActiveMQ can't reconnect if the
ActiveMQ process stays up?
On Oct 19, 2015 7:39 AM, "Abrasha70" <ya...@gmail.com> wrote:

> This is what I get in the log files when it happens:
>
>
>
>
>
> --
> View this message in context:
> http://activemq.2283324.n4.nabble.com/JDBC-driver-with-Failover-Master-Slave-MS-SQL-database-tp4702654p4703112.html
> Sent from the ActiveMQ - User mailing list archive at Nabble.com.
>

Re: JDBC driver with Failover (Master-Slave) MS SQL database

[Abrasha70 <ya...@gmail.com>]

This is what I get in the log files when it happens:





--
View this message in context: http://activemq.2283324.n4.nabble.com/JDBC-driver-with-Failover-Master-Slave-MS-SQL-database-tp4702654p4703112.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.

Re: JDBC driver with Failover (Master-Slave) MS SQL database

[Tim Bain <tb...@alumni.duke.edu>]

Bug reports get submitted in JIRA:
https://issues.apache.org/jira/browse/AMQ/?selectedTab=com.atlassian.jira.jira-projects-plugin:summary-panel

With that being said, your original description said that "the AMQ service
goes down for some reason". I've never seen ActiveMQ go down without
logging, often verbosely, about what it's doing and why, and if for some
reason it's not doing that in this case you should be able to get it to by
setting the logger level to DEBUG.  Can you please post the log lines that
you get when this happens?  Without that info it's hard to know whether
this is a bug (ActiveMQ did something wrong) that should be reported in
JIRA, or not a bug (another piece of software, such as SQL Server or
something you wrote, did something wrong) and there's no point submitting a
bug report in JIRA.

Tim
On Oct 18, 2015 9:16 AM, "Abrasha70" <ya...@gmail.com> wrote:

> How can I report this as a bug?
>
>
>
> --
> View this message in context:
> http://activemq.2283324.n4.nabble.com/JDBC-driver-with-Failover-Master-Slave-MS-SQL-database-tp4702654p4703098.html
> Sent from the ActiveMQ - User mailing list archive at Nabble.com.
>

Re: JDBC driver with Failover (Master-Slave) MS SQL database

[Abrasha70 <ya...@gmail.com>]

How can I report this as a bug?



--
View this message in context: http://activemq.2283324.n4.nabble.com/JDBC-driver-with-Failover-Master-Slave-MS-SQL-database-tp4702654p4703098.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.