Making Service Broker Application Highly Available With AlwaysOn

Published Jan 15 2019 05:23 PM 1,075 Views
Microsoft
First published on MSDN on Jan 26, 2017

If you have a Service Broker (SSB) application connecting to SQL Server using an AlwaysOn availability group listener, in the event of an unexpected failover, some messages may be lost or stuck in the transmission queue on the old primary (new secondary) after the failover. This could be an automatic failover or manual failover when the SSB application is not stopped.

Incoming (SSB) connections are severed during a availability group failover and those connections use the listener IP address which directs connections to the new primary replica. New incoming SSB connections, established through the listener, will go to the new replica. This is the correct and intended behavior.

The availability group fails over and the new inbound SSB connections reset to the new primary replica:

However, outgoing SSB connections from the old primary replica are left open after a failover, still in session with the initiator, and remain so because the IP addresses are still valid. New outgoing SSB connections are established from the new replica to the initiator, but because the connections from the old primary are still open, incoming SSB messages are still sent to the old primary.

Since Service Broker components are not contained within the availability group, special handling is required to handle the Service Broker connections when the availability group moves to a different replica instance. If you feel the post failover handling for the Service Broker connection is not feasible to be built into your application, please submit a Connect item ( http://connect.microsoft.com/sql ) so that we are able to understand the community need for this functionality. As an alternative to modifying the code for your application, you could choose to use a SQL Server Failover Cluster Instance which will prevent the issue described above, due to the IP Address remaining the same on failover.

UPDATE: Enhanced Compatibility When Hosting Service Broker in Availability Groups

This behavior has been improved in the following versions of SQL Server:


SQL 2014 SP1/CU12 & SP2/CU5

SQL 2016 RTM/CU6 & SP1/CU3

SQL 2012 SP4


For more information on these improvements, see the following KnowledgeBase article:

FIX: Service Broker endpoint connections aren't closed after an availability group failover in...


Troubleshooting

If, after the failover, you run the following query on the old primary SSB TARGET and see the value for is_accepted = 0 then you know you have encountered the issue. You will also likely have two or more connections returned here.


--look for is_accept = 0 select is_accept, * from sys.dm_broker_connections with(nolock)

Another side effect are messages stuck in the sys.transmission_queue on the old primary SSB TARGET.

If Profiler trace is run on the old primary SSB TARGET, you would see the following error:

The message has been dropped because the service broker in the target database is unavailable: ‘The database is in read-only mode.’


SQL Server XEvent ‘broker_message_undeliverable’ can also be collected using SQL Server extended events and will also report that messages cannot be delivered. For more information on using SQL Server extended events, see:

Create an Extended Events Session Using the Wizard (Object Explorer)


Workaround

Host the primary replica on a SQL Failover Cluster Instance (SQL FCI). In the event of a failover, SQL Server restarts and the initiator will re-establish connectivity with the availability group listener and the TARGET will re-establish connections with the initiator. There will be no residual broker connections or in-flight transactions that cannot be resolved.

In this scenario, you still have a secondary replica hosted on a standalone instance of SQL Server which provides another means of high availability.

Maintenance

When failing manually over between availability group replicas for rolling upgrade purposes, stop the Service Broker Application before doing the manual availability group failover to prevent any in-flight transactions from getting stuck.

%3CLINGO-SUB%20id%3D%22lingo-sub-318903%22%20slang%3D%22en-US%22%3EMaking%20Service%20Broker%20Application%20Highly%20Available%20With%20AlwaysOn%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-318903%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Jan%2026%2C%202017%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CB%3E%20%3C%2FB%3E%3C%2FP%3E%0A%20%20%3CP%3EIf%20you%20have%20a%20Service%20Broker%20(SSB)%20application%20connecting%20to%20SQL%20Server%20using%20an%20AlwaysOn%20availability%20group%20listener%2C%20in%20the%20event%20of%20an%20unexpected%20failover%2C%20some%20messages%20may%20be%20lost%20or%20stuck%20in%20the%20transmission%20queue%20on%20the%20old%20primary%20(new%20secondary)%20after%20the%20failover.%20This%20could%20be%20an%20automatic%20failover%20or%20manual%20failover%20when%20the%20SSB%20application%20is%20not%20stopped.%3C%2FP%3E%0A%20%20%3CP%3EIncoming%20(SSB)%20connections%20are%20severed%20during%20a%20availability%20group%20failover%20and%20those%20connections%20use%20the%20listener%20IP%20address%20which%20directs%20connections%20to%20the%20new%20primary%20replica.%20New%20incoming%20SSB%20connections%2C%20established%20through%20the%20listener%2C%20will%20go%20to%20the%20new%20replica.%20This%20is%20the%20correct%20and%20intended%20behavior.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68516iEC87F4D269EC4C0C%22%20%2F%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EThe%20availability%20group%20fails%20over%20and%20the%20new%20inbound%20SSB%20connections%20reset%20to%20the%20new%20primary%20replica%3A%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68517i5D978B72A5188672%22%20%2F%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EHowever%2C%20outgoing%20SSB%20connections%20from%20the%20old%20primary%20replica%20are%20left%20open%20after%20a%20failover%2C%20still%20in%20session%20with%20the%20initiator%2C%20and%20remain%20so%20because%20the%20IP%20addresses%20are%20still%20valid.%20New%20outgoing%20SSB%20connections%20are%20established%20from%20the%20new%20replica%20to%20the%20initiator%2C%20but%20because%20the%20connections%20from%20the%20old%20primary%20are%20still%20open%2C%20incoming%20SSB%20messages%20are%20still%20sent%20to%20the%20old%20primary.%3C%2FP%3E%0A%20%20%3CP%3ESince%20Service%20Broker%20components%20are%20not%20contained%20within%20the%20availability%20group%2C%20special%20handling%20is%20required%20to%20handle%20the%20Service%20Broker%20connections%20when%20the%20availability%20group%20moves%20to%20a%20different%20replica%20instance.%20If%20you%20feel%20the%20post%20failover%20handling%20for%20the%20Service%20Broker%20connection%20is%20not%20feasible%20to%20be%20built%20into%20your%20application%2C%20please%20submit%20a%20Connect%20item%20(%20%3CA%20href%3D%22http%3A%2F%2Fconnect.microsoft.com%2Fsql%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20http%3A%2F%2Fconnect.microsoft.com%2Fsql%20%3C%2FA%3E%20)%20so%20that%20we%20are%20able%20to%20understand%20the%20community%20need%20for%20this%20functionality.%20As%20an%20alternative%20to%20modifying%20the%20code%20for%20your%20application%2C%20you%20could%20choose%20to%20use%20a%20SQL%20Server%20Failover%20Cluster%20Instance%20which%20will%20prevent%20the%20issue%20described%20above%2C%20due%20to%20the%20IP%20Address%20remaining%20the%20same%20on%20failover.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68518i4AB2A8F15D2B0C15%22%20%2F%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CH3%20id%3D%22toc-hId-1477867420%22%20id%3D%22toc-hId-1568372478%22%3E%3C%2FH3%3E%0A%20%20%3CH3%20id%3D%22toc-hId--1074289541%22%20id%3D%22toc-hId--983784483%22%3EUPDATE%3A%20Enhanced%20Compatibility%20When%20Hosting%20Service%20Broker%20in%20Availability%20Groups%3C%2FH3%3E%0A%20%20%3CP%3EThis%20behavior%20has%20been%20improved%20in%20the%20following%20versions%20of%20SQL%20Server%3A%3C%2FP%3E%3CBR%20%2F%3E%3CBLOCKQUOTE%3E%0A%20%20%20%3CP%3ESQL%202014%20SP1%2FCU12%20%26amp%3B%20SP2%2FCU5%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20SQL%202016%20RTM%2FCU6%20%26amp%3B%20SP1%2FCU3%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20SQL%202012%20SP4%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3EFor%20more%20information%20on%20these%20improvements%2C%20see%20the%20following%20KnowledgeBase%20article%3A%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%0A%20%20%20%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F4016361%2Ffix-service-broker-endpoint-connections-aren-t-closed-after-an-availab%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20FIX%3A%20Service%20Broker%20endpoint%20connections%20aren't%20closed%20after%20an%20availability%20group%20failover%20in%20SQL%20Server%20%3C%2FA%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3E%3CB%3E%20%3C%2FB%3E%3C%2FP%3E%0A%20%20%3CH3%20id%3D%22toc-hId-668520794%22%20id%3D%22toc-hId-759025852%22%3ETroubleshooting%3C%2FH3%3E%0A%20%20%3CP%3EIf%2C%20after%20the%20failover%2C%20you%20run%20the%20following%20query%20on%20the%20old%20primary%20SSB%20TARGET%20and%20see%20the%20value%20for%20%3CB%3E%20is_accepted%20%3D%200%20%3C%2FB%3E%20then%20you%20know%20you%20have%20encountered%20the%20issue.%20You%20will%20also%20likely%20have%20two%20or%20more%20connections%20returned%20here.%3C%2FP%3E%0A%20%20%3CP%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E--look%20for%20is_accept%20%3D%200%20select%20is_accept%2C%20*%20from%20sys.dm_broker_connections%20with(nolock)%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3E%3CB%3E%20%3C%2FB%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EAnother%20side%20effect%20are%20messages%20stuck%20in%20the%20%3CB%3E%20sys.transmission_queue%20%3C%2FB%3E%20on%20the%20old%20primary%20SSB%20TARGET.%3C%2FP%3E%0A%20%20%3CP%3EIf%20Profiler%20trace%20is%20run%20on%20the%20old%20primary%20SSB%20TARGET%2C%20you%20would%20see%20the%20following%20error%3A%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3EThe%20message%20has%20been%20dropped%20because%20the%20service%20broker%20in%20the%20target%20database%20is%20unavailable%3A%20%E2%80%98The%20database%20is%20in%20read-only%20mode.%E2%80%99%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68519i6AD973CB4AD98D65%22%20%2F%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3ESQL%20Server%20XEvent%20%E2%80%98broker_message_undeliverable%E2%80%99%20can%20also%20be%20collected%20using%20SQL%20Server%20extended%20events%20and%20will%20also%20report%20that%20messages%20cannot%20be%20delivered.%20For%20more%20information%20on%20using%20SQL%20Server%20extended%20events%2C%20see%3A%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%3CA%20href%3D%22https%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fgg471585(v%3Dsql.120).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Create%20an%20Extended%20Events%20Session%20Using%20the%20Wizard%20(Object%20Explorer)%20%3C%2FA%3E%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%20%20%3CH3%20id%3D%22toc-hId--1883636167%22%20id%3D%22toc-hId--1793131109%22%3EWorkaround%3C%2FH3%3E%0A%20%20%3CP%3EHost%20the%20primary%20replica%20on%20a%20SQL%20Failover%20Cluster%20Instance%20(SQL%20FCI).%20In%20the%20event%20of%20a%20failover%2C%20SQL%20Server%20restarts%20and%20the%20initiator%20will%20re-establish%20connectivity%20with%20the%20availability%20group%20listener%20and%20the%20TARGET%20will%20re-establish%20connections%20with%20the%20initiator.%20There%20will%20be%20no%20residual%20broker%20connections%20or%20in-flight%20transactions%20that%20cannot%20be%20resolved.%3C%2FP%3E%0A%20%20%3CP%3EIn%20this%20scenario%2C%20you%20still%20have%20a%20secondary%20replica%20hosted%20on%20a%20standalone%20instance%20of%20SQL%20Server%20which%20provides%20another%20means%20of%20high%20availability.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68520i3B1635BB61DB2694%22%20%2F%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CH3%20id%3D%22toc-hId--140825832%22%20id%3D%22toc-hId--50320774%22%3EMaintenance%3C%2FH3%3E%0A%20%20%3CP%3EWhen%20failing%20manually%20over%20between%20availability%20group%20replicas%20for%20rolling%20upgrade%20purposes%2C%20stop%20the%20Service%20Broker%20Application%20before%20doing%20the%20manual%20availability%20group%20failover%20to%20prevent%20any%20in-flight%20transactions%20from%20getting%20stuck.%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-318903%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jan%2026%2C%202017%20%26nbsp%3B%20If%20you%20have%20a%20Service%20Broker%20(SSB)%20application%20connecting%20to%20SQL%20Server%20using%20an%20AlwaysOn%20availability%20group%20listener%2C%20in%20the%20event%20of%20an%20unexpected%20failover%2C%20some%20messages%20may%20be%20lost%20or%20stuck%20in%20the%20transmission%20queue%20on%20the%20old%20primary%20(new%20secondary)%20after%20the%20failover.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-318903%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EHigh%20Availability%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EService%20Broker%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 05:23 PM
Updated by: