Enable Broker Service

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Hugo Kornelis (10/26/2010)


    Thanks for the question. I missed a point, but learned something new.

    The reason I missed a point is because, when doing some research, I found this text:

    "If you attach a database with the same service broker identifier as an existing database, SQL Server deactivates Service Broker message delivery in the database being attached."

    (This is from http://msdn.microsoft.com/en-us/library/ms166057%28SQL.90%29.aspx).

    The quote specifically mentions attaching, but since the entire page is about attach and restore, I assumed that the restore behaviour would be identical. Obviously not.

    Ah well, I made the same mistake 🙂

    BOL could have been a bit more specific on that point.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • pravin wagh

    SSC Veteran

    Points: 223

    u r right, the question was not phrased very well.

    not mentioned Service broker is enabled or not db1.

    when we restore database from any existing DB Backup.

    we always need to

    1) Enable Broker -- when Backup db is not set broker ON.

    2) New Broker -- When backup db broker is ON.

  • Rune Bivrin

    SSCertifiable

    Points: 7855

    I realized there was probably a 50% risk of getting it wrong which I did. :crying:

    Interestingly in BOL it wasn't specified whether assigning a new identifier with NEW_BROKER actually enabled the Service Broker.


    Just because you're right doesn't mean everybody else is wrong.

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    Good question....got it wrong, but I learned something. Thanks.

  • Dan Guzman - Not the MVP

    Hall of Fame

    Points: 3755

    I guess I'm 'nitpick mode' on as well; backup was to disk 'D:\db1.bak' but the restore was from 'D:\main.bak', technically we have no idea what was restored 😛

  • SQLRNNR

    SSC Guru

    Points: 281252

    thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Trey Staker

    SSCarpal Tunnel

    Points: 4736

    Thanks for the question, this is something I know nothing about and missed the point. I don't mind missing points when I learn something.

    ---------------------------------------------------------------------
    Use Full Links:
    KB Article from Microsoft on how to ask a question on a Forum

  • SanDroid

    SSChampion

    Points: 10068

    Mayank (10/26/2010)


    Normally to enable the Broker Service, you need to simply use : "ENABLE_BROKER"

    But when you restore the back up of database db1 to database db2, and after that if you want to enable broker service on database db2, then what to do? - That was my question.

    The wording of the question and the code was misleading and confusing.

    The code created one backup file db1.bak, and then restored from a differant file called main.bak. This means that it did not restore the backup created for db1 in the first script to to db2 in the second script. Your code restored a backup called main.bak. You quoted comment above does not apply since it assumes we all corrected a mistake in your questions code that was not apparent until after you had made responses here.

    Did the database in the main.bak backup file have the Broker Service enabled when it was created?

    Did you question make that fact clear?

    It was completely unclear if any database had Broker Service enabled, disabled, or if it had ever been set-up when the backup file was created.

    This could have been a really decent high level question had anyone taken time moment to read it and execute the code with it prior to posting.

  • Hardy21

    SSCrazy Eights

    Points: 9708

    SanDroid (10/27/2010)


    Mayank (10/26/2010)


    Normally to enable the Broker Service, you need to simply use : "ENABLE_BROKER"

    But when you restore the back up of database db1 to database db2, and after that if you want to enable broker service on database db2, then what to do? - That was my question.

    The wording of the question and the code was misleading and confusing.

    The code created one backup file db1.bak, and then restored from a different file called main.bak. This means that it did not restore the backup created for db1 in the first script to to db2 in the second script. Your code restored a backup called main.bak. You quoted comment above does not apply since it assumes we all corrected a mistake in your questions code that was not apparent until after you had made responses here.

    Did the database in the main.bak backup file have the Broker Service enabled when it was created?

    Did you question make that fact clear?

    It was completely unclear if any database had Broker Service enabled, disabled, or if it had ever been set-up when the backup file was created.

    This could have been a really decent high level question had anyone taken time moment to read it and execute the code with it prior to posting.

    that's really good observation - I think most of us didn't check the backup file which author has used for restoration.

    Thanks

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Hardy21 (10/27/2010)


    that's really good observation - I think most of us didn't check the backup file which author has used for restoration.

    Well I did 🙂 I just ignored it because it was an obvious typo (and the answers didn't have any error messages)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • SanDroid

    SSChampion

    Points: 10068

    da-zero (10/28/2010)


    Well I did 🙂 I just ignored because it was an obvious typo (and the answers didn't have any error messages)

    As did I, but it explains why some people that don't have "Question ESP", but do know SQL, would have made the wrong answer.

    Had the answer been SET ENABLE instead of SET NEW, it also would have been correct given the code in the example.

    Just another instance for the QOTD needs a frakin' Moderator pile. :hehe: 😛

  • rafael lenartowicz

    SSC Veteran

    Points: 231

    Mayank P - Clarion, India (10/26/2010)


    Yes Phil,

    ... you restore the back up of database db1 to database db2, and after that if you want to enable broker service on database db2...

    there is nothing in this question that indicates if db1 was previously enabled for service broker, which makes the question invalid. I'm with Phil on that one.

  • Dhruvesh Shah

    SSCrazy

    Points: 2303

    Good Question Didn't know anything about NEW Broker until now.

  • TomThomson

    SSC Guru

    Points: 104773

    Hugo Kornelis (10/26/2010)


    Thanks for the question. I missed a point, but learned something new.

    The reason I missed a point is because, when doing some research, I found this text:

    "If you attach a database with the same service broker identifier as an existing database, SQL Server deactivates Service Broker message delivery in the database being attached."

    (This is from http://msdn.microsoft.com/en-us/library/ms166057%28SQL.90%29.aspx).

    The quote specifically mentions attaching, but since the entire page is about attach and restore, I assumed that the restore behaviour would be identical. Obviously not.

    But surely it is identical? The broker is deactivated, in both cases, because there is an identifier clash. It can't be reactivated using ENABLE, whether the deactivation happened as part of attach or as part of restore, because of that identity clash, so a new identity has to be substituted using NEW.

    Tom

  • zymos

    SSCommitted

    Points: 1960

    Great pick on the question. I did have not dealt much with the Service Broker but have now learnt about it.

    Thank you.

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply