Enabling Service Broker in SQL Server 2008_问答_开发者

I am integrating SqlCacheDependency to use in my LinqToSQL datacontext.

I am using an extension class for Linq querys found here - http://code.msdn.microsoft.com/linqtosqlcache

I have wired up the code and when I open the page I get this exception -

"The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications."

its coming from this event in the global.asax

       开发者_开发百科 protected void Application_Start()
    {
        RegisterRoutes(RouteTable.Routes);
        //In Application Start Event
        System.Data.SqlClient.SqlDependency.Start(new dataContextDataContext().Connection.ConnectionString);

    }

my question is...

how do i enable Service Broker in my SQL server 2008 database? I have tried to run this query.. ALTER DATABASE tablename SET ENABLE_BROKER but it never ends and runs for ever, I have to manually stop it.
once I have this set in SQL server 2008, will it filter down to my DataContext, or do I need to configure something there too ?

thanks for any help

Truegilly

In case anyone else is looking for a solution to this problem, the following command worked great for me. It releases all other connections to the database instead of waiting.

ALTER DATABASE [DBNAME] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

In Sql Server 2012 you can go to Properties-> Options -> Service Broker

Enabling Service Broker in SQL Server 2008

ok here is how to do this if yours is disabled or you need to restore a backup, which seems to disable it.

just run this script, it will kill all the process's that a database is using (why you carnt in 2008 manually kill process's unlike 2005 is beyond me) and then set the broker

USE master
go

DECLARE @dbname sysname

SET @dbname = 'YourDBName'

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END


ALTER DATABASE @dbname SET ENABLE_BROKER

All connections to the DB must be be dropped and user account with permissions used to enable the broker service.

The following would be ideal (replace databasename):

     IF ((SELECT is_broker_enabled FROM sys.databases WHERE name = '%DATABASE_NAME%') = 1)
      BEGIN
        ALTER DATABASE %DATABASE_NAME% SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
     END

    ALTER DATABASE %DATABASE_NAME% SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Also I suggest creating a new role and user account with the proper permissions (replace database login):

  --DBA creates a new role 
   if not exists (select 1 from sys.database_principals where name='sql_dependency_subscriber' and Type = 'R')
  begin
   EXEC sp_addrole 'sql_dependency_subscriber' 
  end

  --Minimum Required  Permissions needed for SQLDependancy Notification to work
   GRANT CREATE PROCEDURE to sql_dependency_subscriber;
   GRANT CREATE QUEUE to sql_dependency_subscriber; 
   GRANT CREATE SERVICE to sql_dependency_subscriber;
   GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to sql_dependency_subscriber ;
   GRANT VIEW DEFINITION TO sql_dependency_subscriber;

  --Minimum Required  Permissions  needed for SQLDependaney Notification to work
   GRANT SELECT to sql_dependency_subscriber;
   GRANT SUBSCRIBE QUERY NOTIFICATIONS TO sql_dependency_subscriber;
   GRANT RECEIVE ON QueryNotificationErrorsQueue TO sql_dependency_subscriber;
   GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to sql_dependency_subscriber ;
   EXEC sp_addrolemember 'sql_dependency_subscriber', '%DATABASE_LOGIN%';
   EXEC sp_addrolemember 'sql_dependency_subscriber', 'sqldp';

We can enable Broker services by ALTER DATABASE statement and also from Database properties > Options > Service Broker > Broker Enable: True/False.

But issue here while enabling the broker: processes are running with the specific database we have to kill those and only after that we can enable broker service. answered by @JGilmartin will perfectly work, but make sure it'll kill all ongoing processes of your database, before using this script on production kindly check traffic or severity of process.