Enable Broker Service

  • Mayank Parmar

    Ten Centuries

    Points: 1019

    Comments posted to this topic are about the item Enable Broker Service

    --------------------------------------------------------------------------------
    Mayank Parmar
    Software Engineer

    Clarion Technologies
    SEI CMMI Level 3 Company

    8th Floor, 803, GNFC info Tower,
    SG Highway, Ahmedabad - 380 054,
    Gujarat, India.
    www.clariontechnologies.co.in

    Email: mayank.parmar@clariontechnologies.co.in
    MSN : mayank.parmar@clariontechnologies.co.in
    Mobile: +91 9727748789
    --------------------------------------------------------------------------------

  • UMG Developer

    SSChampion

    Points: 13482

    Nice question on something most people probably don't know anything about. Thanks!

  • Iulian -207023

    SSCertifiable

    Points: 7509

    Nice question, thanks.

    Here is also a discussion on this topic: http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/eca47504-9a8c-4c0f-a4a4-3a56fb5a012b

  • Imran Ashraf-452633

    SSCommitted

    Points: 1969

    Another Good question, one which I needed to read up about as we do not use the Service Broker where I work.

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Good question with nice/tricky options.

    I was thinking for following options are the candidates:

    ALTER DATABASE db2 SET DISABLE_BROKER ; ALTER DATABASE db2 SET ENABLE_BROKER;

    ALTER DATABASE db2 SET NEW_BROKER

    But after reading some links got the right answer - NEW_BROKER.

    Thanks

  • Nakul Vachhrajani

    SSChampion

    Points: 10221

    Great question!

    The Enable broker and new broker were obvious candidates, but it took some Books On line reading to get to NEW_BROKER.

    Got to learn something new on Service Broker today. Thank-you!

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Nice question about an unknown subject.

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

  • Phil Jackson

    SSChasing Mays

    Points: 653

    I'm sorry, but I do not believe that the question was phrased very well. To 'enable' Service Broker for the database, you use the ENABLE_BROKER option, as stated in the msdn article you posted

    ENABLE_BROKER

    Specifies that Service Broker is enabled for the specified database. Message delivery is started, and the is_broker_enabled flag is set to true in the sys.databases catalog view. The database retains the existing Service Broker identifier.

    To reset/assign a new Service Broker Id for a restored database, you would use the NEW_BROKER option.

    NEW_BROKER

    Specifies that the database should receive a new broker identifier. Because the database is considered to be a new service broker, all existing conversations in the database are immediately removed without producing end dialog messages. Any route that references the old Service Broker identifier must be re-created with the new identifier.

    Phil

    Although all answers are replies, not all replies are answers.
    Blog: http://philjax.wordpress.com

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    <nitpick mode="on" />

    I would issue a simple ALTER DATABASE db2 SET ENABLE_BROKER, since noone said that the broker service had already been enabled in db1

    <nitpick mode="off" />

    Ok, common sense told me that I should assume the service had already been enabled in db1 as well, so I chose the right answer.

    Best Regards,

    Chris Büttner

  • Mayank Parmar

    Ten Centuries

    Points: 1019

    Yes Phil,

    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.

    Regards,

    Mayank

    --------------------------------------------------------------------------------
    Mayank Parmar
    Software Engineer

    Clarion Technologies
    SEI CMMI Level 3 Company

    8th Floor, 803, GNFC info Tower,
    SG Highway, Ahmedabad - 380 054,
    Gujarat, India.
    www.clariontechnologies.co.in

    Email: mayank.parmar@clariontechnologies.co.in
    MSN : mayank.parmar@clariontechnologies.co.in
    Mobile: +91 9727748789
    --------------------------------------------------------------------------------

  • Mayank Parmar

    Ten Centuries

    Points: 1019

    Hi Christian

    You are right, I should mention that : "Service had already been enabled in db1"

    Regards,

    --------------------------------------------------------------------------------
    Mayank Parmar
    Software Engineer

    Clarion Technologies
    SEI CMMI Level 3 Company

    8th Floor, 803, GNFC info Tower,
    SG Highway, Ahmedabad - 380 054,
    Gujarat, India.
    www.clariontechnologies.co.in

    Email: mayank.parmar@clariontechnologies.co.in
    MSN : mayank.parmar@clariontechnologies.co.in
    Mobile: +91 9727748789
    --------------------------------------------------------------------------------

  • Nils Gustav Stråbø

    SSChampion

    Points: 11259

    I agree with Christian, the question should have stated whether service broker was enabled or not in db1.

  • Chris Houghton

    SSCrazy

    Points: 2497

    Yeah good question. Knew the answer wasn't "enable" so I took a choice between "create" and "new" since I couldn't remember the syntax. I picked wrong 🙁

  • Phil Jackson

    SSChasing Mays

    Points: 653

    To create a new identifier (or as I view it, reset service broker) for a restored database, yes, I would use the NEW_BROKER. However the question could have been phrased a little better; for example, there was no mention that Service Broker was enabled previously; and although the assumption could be made, you specifically asked how would you 'enable' Service Broker.

    Phil

    Although all answers are replies, not all replies are answers.
    Blog: http://philjax.wordpress.com

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

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

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