none
Management Point Database Replica - Service Broker setup issue RRS feed

  • Question

  • Hi, I've set up a site database replica locally on a second site server (MP) following the article http://technet.microsoft.com/en-us/library/hh846234.aspx and Kent Agerlund's guide.

    However following the setup, I received errors in bgbserver.log. Looking back through the setup I realised I didnt specify the instance name for sql when running sp_BgbConfigSSBForReplicaDB.

    I've deleted the publication, subscription and replica database as per the article to start the process again, however when I get to the point of running sp_BgbConfigSSBForRemoteService on the site database, I'm getting the error certificate already exists.

    Is there a way to clean this up so I can continue configuring the service broker?

    Thanks.

    Carl

    Thursday, April 4, 2013 9:44 AM

Answers

  • Hi, an update on this service broker issue, this has now been resolved.

    There is a step in the MP Replica instructions (http://technet.microsoft.com/en-us/library/hh846234.aspx) to create a self-signed certificate for the database replica server.

    From what I read, this was only required for MP's that were remote from the SQL replica. However it looks as though you still need to generate the self-signed certificate but not the importing of the certificate if the MP is local to replica.

    Once we did this I then received the error "ERROR: The queue for BGB server doesn't exist." as per DDC1's reply.

    We fixed this up by running a SQL script to fix up the conversation endpoints and now there are no errors being reported in the bgbserver.log.


    Tuesday, April 16, 2013 5:49 AM

All replies

  • I too had a typo when I ran through the same guide. I had to re-import the certificate.  I ran the following command "DROP CERTIFICATE certname" against the Master DB and was able to re-import the cert.  However that did not solve my bgbserver.log errors though.... I am still getting "ERROR: The queue for BGB server doesn't exist.".  Hope this helps

    Thursday, April 4, 2013 12:57 PM
  • Hi thanks for the reply. The errors I'm currently getting in the bgbserver.log on the replica MP are as follows. I'm not getting the "ERROR: The queue for BGB server doesn't exist" error anymore however.

    ERROR: Can't retreive SQL connection. Exception: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

    ERROR: Don't have SQL connection when get resync flag

    Thursday, April 4, 2013 10:56 PM
  • Hi, an update on this service broker issue, this has now been resolved.

    There is a step in the MP Replica instructions (http://technet.microsoft.com/en-us/library/hh846234.aspx) to create a self-signed certificate for the database replica server.

    From what I read, this was only required for MP's that were remote from the SQL replica. However it looks as though you still need to generate the self-signed certificate but not the importing of the certificate if the MP is local to replica.

    Once we did this I then received the error "ERROR: The queue for BGB server doesn't exist." as per DDC1's reply.

    We fixed this up by running a SQL script to fix up the conversation endpoints and now there are no errors being reported in the bgbserver.log.


    Tuesday, April 16, 2013 5:49 AM
  • Would you mind sharing the SQL Script you ran to fix up the conversation endpoints?  It would be greatly appreciated.

    Thursday, April 25, 2013 12:55 PM
  • No worries, here's the sql query from Microsoft. Need to update with your CM database - this was run on the main site database and the management point refers to the management point using the replica database.

      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

    Friday, May 3, 2013 6:41 AM
  • I’ve got message on MP “Cannot find either column “dbo” or the user-defined function or aggregate “dbo.fnDrsGetPrioritySysName”, or the name is ambiguous.”.

    There is still error in bgbserver.log (Error:The queue for BGB server doesn't exist)

    Client Notification\download computer policy (console sccm)  -  doesn't work

    Thursday, May 22, 2014 9:01 AM
  • Despite that message notification works
    Friday, May 23, 2014 7:18 PM
  • Thanks a lot! You save me. I got "ERROR: The queue for BGB server doesn't exist." And script, provided here fix it.
    Monday, October 24, 2016 3:49 PM