Enabling service broker

SQL2K5 SP1
I found this much in BOL...
alter database msdb
set ENABLE_BROKER
But when I run this on either of my 2 test servers the query never
completes...I let it run all weekend, it was still running this morning.
Am I missing something in my install? Has anyone ever seen this?
I am supposed to be getting our backup plans into place today and still
cannot get the operator alert tasks working because of this. Any help is
graciously appreciated!!
See if this helps:
http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
Chances are you are logged into the database when you are trying to run the
command so it will run until everyone logs out.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> SQL2K5 SP1
> I found this much in BOL...
> alter database msdb
> set ENABLE_BROKER
>
> But when I run this on either of my 2 test servers the query never
> completes...I let it run all weekend, it was still running this morning.
> Am I missing something in my install? Has anyone ever seen this?
>
> I am supposed to be getting our backup plans into place today and still
> cannot get the operator alert tasks working because of this. Any help is
> graciously appreciated!!
>
|||Hmm...tried that and got this error:
Msg 9776, Level 16, State 1, Line 1
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does not
match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
How can I force this GUID to match?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
> See if this helps:
> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
> Chances are you are logged into the database when you are trying to run
> the command so it will run until everyone logs out.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>
|||Used the SET NEW_BROKER
Although, I DID follow procedure on this db. msdb was backed up from one
server and restored on this one. This is proper as I was told.
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Hmm...tried that and got this error:
> Msg 9776, Level 16, State 1, Line 1
> Cannot enable the Service Broker in database "msdb" because the Service
> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
> not match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> How can I force this GUID to match?
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>
|||Moving system datbases ([master], [model], [temp] and [msdb]) between
instances of the is never proper, no matter the steps involved.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
> Used the SET NEW_BROKER
> Although, I DID follow procedure on this db. msdb was backed up from one
> server and restored on this one. This is proper as I was told.
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
>
|||But this is supposedly the only way to move maintenance plans from one
server to the other WHILE RETAINING the ability to modify them graphically.
If you import/export through integration services it never allows you to
use the designer on the resulting package.
Is there some other way around this restriction? I need to design these
backup plans on my development machine and then deploy them. But they
should still be maintainable from the server no?
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:O$p$cU%23FHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Moving system datbases ([master], [model], [temp] and [msdb]) between
> instances of the is never proper, no matter the steps involved.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
>
|||I know KB224071 gives steps on how to move system databases, but those steps
ignore new functionality, like server level event notifications, dbMail and
Service Broker. Not to mention any usage of the secret storage facilities
(keys, certificates, encrypted data)...
What you did (NEW_BROKER) will solve the issue of starting up the broker in
msdb after a move, but at the cost of loosing any active dialog in in the
database, thus loosing any pending mail sent through dbMail and any pending
servel level notifications. I understand that is highly questionable if you
would have such active items and still move the database...
You have to be aware that when moving the msdb you are moving and
ovewrwritting way more than just your maintenance plan graphical designer
state. By overwriting the other's server msdb with yours, you are
overwriting the state of any feature that relies on msdb, and there are
plenty (I just mentioned server level event notifications and dbMail).
You could use the SQL Feedback at
https://connect.microsoft.com/SQLServer/Feedback to mention the issue that
the graphical designer uses a system database to store it's state, thus
tying it to a specific instance.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OABOpZ%23FHHA.3976@.TK2MSFTNGP05.phx.gbl...
> But this is supposedly the only way to move maintenance plans from one
> server to the other WHILE RETAINING the ability to modify them
> graphically. If you import/export through integration services it never
> allows you to use the designer on the resulting package.
> Is there some other way around this restriction? I need to design these
> backup plans on my development machine and then deploy them. But they
> should still be maintainable from the server no?
>
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:O$p$cU%23FHHA.5104@.TK2MSFTNGP03.phx.gbl...
>