Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Enable Broker Service Expand / Collapse
Author
Message
Posted Tuesday, October 26, 2010 10:14 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:28 PM
Points: 17,729, Visits: 15,597
thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1010966
Posted Tuesday, October 26, 2010 11:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 4:48 PM
Points: 1,142, Visits: 2,687
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
Post #1011025
Posted Wednesday, October 27, 2010 7:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
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.

Post #1011512
Posted Wednesday, October 27, 2010 10:53 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:27 AM
Points: 1,130, Visits: 1,390
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
Post #1012062
Posted Thursday, October 28, 2010 12:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
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)




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1012092
Posted Thursday, October 28, 2010 7:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
da-zero (10/28/2010)
[quote]
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.
Post #1012277
Posted Friday, October 29, 2010 6:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 2:57 PM
Points: 61, Visits: 262
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.
Post #1012952
Posted Sunday, October 31, 2010 8:35 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 17, 2013 11:53 AM
Points: 623, Visits: 237
Good Question Didn't know anything about NEW Broker until now.
Post #1013661
Posted Tuesday, December 7, 2010 7:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 7,745, Visits: 9,493
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
Post #1031198
Posted Monday, February 27, 2012 10:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:52 PM
Points: 483, Visits: 244
Great pick on the question. I did have not dealt much with the Service Broker but have now learnt about it.

Thank you.
Post #1258402
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse