Enabling Service Broker in SQL Server 2008

Question

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...

1. 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.

2. 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

Conias

Answer 1

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

Conias

additional useful information: to see if it is enabled run... SELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name'; -- Where 'Database name' is the name of the database you want to query. – Dietitian

Answer 2

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

Totalitarianism

If you have problems enabling the broker complaining that broker with same ID is already enabled, like The Service Broker in database "DatabaseName" cannot be enabled because there is already an enabled Service Broker with the same ID. You can reset broker ID by this command: ALTER DATABASE [DatabaseName] SET NEW_BROKER Credits to: mssqltips.com/sqlservertip/2789/… – Olatha

Answer 3

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

Sag

Same for SQL 2008 sp3 (just tested) with latest smss – Locality

Same for SQL 2016, just tested. – Chandra

Answer 4

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';

Tonatonal

Answer 5

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.

Rana

Answer 6

1. Error Outputted by Service Broker. Image 1

2. To Fix, enable Service broker using SSMS, Image 2

3. After Setting to true Service Broker error should gone now, see my Image 3

Microfilm

Answer 7

If the broker is already enabled try this:

ALTER DATABASE <DBName> SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

Lives