Enable Broker Service

Question

Enable Broker Service

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

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

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 More actions · Answer 2

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 SSCrazy Eights Points: 8510 More actions · Answer 3

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 More actions · Answer 4

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

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 5

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: 281334 More actions · Answer 6

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: 4796 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

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: 259105 More actions · Answer 10

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 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 13

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

TomThomson SSC Guru Points: 104773 More actions · Answer 14

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 More actions · Answer 15

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

Thank you.