Cluster Failovers and activation

So is there something I'm supposed to configure to allow activation to fire after a cluster failover. We have had three where I have noticed that activation does not automatically fire back up. i have to alter the queue to get it going. None of the values on sys.service_queues imply that is disabled, but messages just keep stacking up into the queue and are not being dealt with. Once I alter the queue to turn activation on; everything takes off and starts working normaly.

Obviously, this is less than desireable for an environment that requires High availability.

Regards

After failover when the queue is not being activated:

1. Do you see any Broker:Activation events in the profiler when messages are enqueued?

2. What is the state of the queue monitor? (sys.dm_broker_queue_monitors)

3. Are there any failures reported in the ERRORLOG?

Thanks,
Rushi

CLR Stored Proc Queue activation help

I set up a queue to activate a clr stored procedure upon receiving a message and nothing is happening. I have tried everything listed in this topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=200684&SiteID=1

except for saying "Recieve Top(0)" which didn't make any sense to me. I have set the database to trustworthy (all of this is taking place within a single database on a local server). There are messages on the queue, and I have the queue activation set to max_queue_readers=2, procedure_name=StoredProcedure1, execute as owner. I tried execute as self and that didn't work either. I signed the assembly that contains StoredProcedure1 and the assembly that it references. The only thing that appears in the sql error log is this (I trimmed off the timestamp):

AppDomain 15 (TriggerTest.dbo[runtime].14) is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations.
AppDomain 15 (TriggerTest.dbo[runtime].14) unloaded.
AppDomain 18 (TriggerTest.dbo[runtime].17) created.

If I call the stored procedure manually it works just fine

I also tried signing the procedure (the t-sql stored proc that calls the clr proc) as described here:

http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx

Still no luck... The proc is never activated. The message sits in the queue until I manually retrieve it.

|||

If you issue an EXECUTE AS USER = 'nameofqueueowneruser'; followed by mannualy launching the procedure, do you get an error or is the procedure running fine?

Thanks,
~ Remus

|||The procedure runs fine when executed as you stated above.|||

Are you sure the queue activation is actually enabled? Activation can be set up (procedure name, max_queue_readers, execute as user) but the activation itself might be disabled. sys.service_queues.is_activation_enabled column will show this. If is disabled, then running ALTER QUEUE [queuename] WITH ACTIVATION (STATUS = ON) will enabled it. Note that the clause WITH ACTIVATION (STATUS = ON/OFF) is different from WITH STATUS = ON/OFF.

HTH,
~ Remus

|||Ok, apparently the procedure was running, it just wasn't retrieving the message from the queue. I changed the procedure to simply insert a row into a table and it did do that. Also, I set a breakpoint in the procedure, but VS2005 never stops. Now I need to figure out why it won't retrieve the message. Thanks for you help|||

How are you attempting to debug from Visual Studio?

For debugging, you need to attach to the sqlservr.exe process using remote debugger. We have tested that this works even for internally activated procedures.

Rushi