Issue reported: Management Point Database Replica – Service Broker setup issue

After installing Management point by referring url :Click Here Getting error.

Step 1: Verify bgbserver.log.  You might get Error : The queue for BGB server doesn’t exist.”

Execute procedure on sql site database : sp_BgbConfigSSBForReplicaDB and validate.

Solution : Execute below query provided by MS more details refer Click Here  Make sure change below Highlight color code.

use CM_<Site Code>

go

  –end conversation ‘3EF0E925-E1A0-E211-95AB-14FEB58ADE09’ with cleanup

  –go

                Declare @ServerID INT

                select @ServerID=ServerID FROM BGB_Server where ServerName = ‘<Management Point FQDN>’

                select ‘serverID=’,@ServerID

                DECLARE @Err    INT

                DECLARE @ErrMsg NVARCHAR(MAX)

                DECLARE @ProcedureName NVARCHAR(128) = (SELECT OBJECT_NAME(@@PROCID));

                DECLARE @Ret    INT = 0

                                DECLARE @GroupID UNIQUEIDENTIFIER

                                DECLARE @DBID NVARCHAR(256)

                                SELECT @GroupID = GroupID, @DBID = ISNULL(DBID, N”) FROM BGB_Server WHERE ServerID = @ServerID

                                 select ‘groupID’,@GroupID

                                IF @GroupID IS NULL OR @DBID IS NULL

                                BEGIN

                                               — @GroupID can be null if BGB server is an old version which doesn’t contain GroupID

                                                SET @ErrMsg = ‘ERROR: BGB server does not exists or is obsoleted’

                                                select ‘groupID is NULL’, @ErrMsg

                                                SET @Ret = 1

                                                GOTO EXITTRY

                                END

                                DECLARE @LocalService    NVARCHAR(128);

                                DECLARE @TargetService   NVARCHAR(128);

                                DECLARE @Contract        NVARCHAR(50);

                                DECLARE @InitiatorHandle UNIQUEIDENTIFIER

                                SET @LocalService = N’ConfigMgrBGB_Site’ + dbo.fnGetSiteCode();

                                SET @TargetService = N’ConfigMgrBGB_Site’ + @DBID;

                                SET @Contract = dbo.fnDrsGetPrioritySysName(7); — Fix priority to 7

                                select ‘local service, target servcie and contact’, @LocalService,@TargetService,@Contract

                                EXEC @Err = dbo.spGetSSBDialogHandle @FromService=@LocalService, @ToService=@TargetService, @OnContract=@Contract,

                                                                                                                                                                    @ConversationGroupID=@GroupID, @Handle=@InitiatorHandle OUTPUT;

                               — spGetSSBDialogHandle only use 1 to indicate error

                               IF @Err = 1 OR @InitiatorHandle IS NULL

                                BEGIN

                                                SET @ErrMsg = ‘ERROR: Can not get a valid conversation handle for BGB server ‘ + CAST(@ServerID AS NVARCHAR(MAX))

                                   select ‘spGetSSBDialogHandle error:’, @ErrMsg

                                                SET @Ret = 2

                                                GOTO EXITTRY

                                END

                                DECLARE @OldConversationID UNIQUEIDENTIFIER;

                                DECLARE @NewConversationID UNIQUEIDENTIFIER;

                                SELECT @NewConversationID = conversation_id FROM sys.conversation_endpoints WHERE conversation_handle = @InitiatorHandle

                                SELECT @OldConversationID = ConversationID FROM BGB_Server WHERE ISNULL(DBID, N”) = @DBID

                                IF  @OldConversationID IS NULL OR @NewConversationID != @OldConversationID

                                BEGIN

                                                SET @ErrMsg = ‘INFO: Conversation ID for BGB server ‘ + CAST(@ServerID AS NVARCHAR(MAX)) + ‘ changed.’;

                                                  select  ‘conversation ID change:’,@ErrMsg

                                                UPDATE BGB_Server SET ConversationID = @NewConversationID WHERE ServerID = @ServerID

                                END

                                        DECLARE @MsgXml XML

        SET @MsgXml = (SELECT 0 AS ‘@ID’, 0 AS ‘@Type’ FOR XML PATH(‘BgbPushMessage’), TYPE, ELEMENTS XSINIL)

         select ‘MsgXml=’, @MsgXml

        select @Err

                                 ;SEND ON CONVERSATION @InitiatorHandle MESSAGE TYPE  [BGB_ChannelStart] (@MsgXml)

                                goto SUCCESS

EXITTRY:

                                SET @ErrMsg = N’ERROR: Failed to send message to BGB server ‘ + CAST(@ServerID AS NVARCHAR(MAX)) + ‘.’ +

                                                                                  N’ SQL Error: ‘ + CONVERT(NVARCHAR(MAX), ERROR_NUMBER()) + N’ SQL Message: ‘ + ERROR_MESSAGE();

                                select ‘Exit’,@ErrMsg

SUCCESS: select ‘Sucess.’

go

Select @@servername

select ep.* from BGB_Server server join sys.conversation_endpoints ep ON

ep.conversation_id=server.conversationID

go

select * from BGB_Server

Happy Learning!!!

Thanks & Regards,
Haresh Hiran

Advertisements