在SQL Server 2008中启用服务代理

Question

我正在将SqlCacheDependency集成到我的LinqToSQL数据上下文中。

我使用一个扩展类来进行查询，可以在此处找到 - http://code.msdn.microsoft.com/linqtosqlcache

我已经连接了代码，但是当我打开页面时，出现以下异常 -

"当前数据库的SQL Server Service Broker未启用，因此不支持查询通知。如果要使用通知，请为此数据库启用Service Broker。"

异常来自于global.asax中的此事件。

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

    }

我的问题是...

1. 如何在我的 SQL Server 2008 数据库中启用服务代理？我尝试运行此查询... ALTER DATABASE tablename SET ENABLE_BROKER ，但它永远不会结束并且一直运行，我必须手动停止它。

2. 一旦我在 SQL Server 2008 中设置了这个，它会过滤到我的 DataContext 中吗？还是我需要在那里配置一些东西？

感谢任何帮助

Truegilly

- JGilmartin

Answer 1

如果其他人正在寻找解决此问题的方法，以下命令对我非常有效。它释放了所有其他连接到数据库而不是等待。

ALTER DATABASE [DBNAME] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

- Matt

---

如果您在启用代理时遇到了问题，提示已经启用了具有相同ID的代理，如：
“数据库”DatabaseName“中的服务代理无法启用，因为已经存在一个具有相同ID的已启用服务代理。” 您可以通过以下命令重置代理ID：
ALTER DATABASE [DatabaseName] SET NEW_BROKER
感谢：https://www.mssqltips.com/sqlservertip/2789/sql-server-service-broker-error-database-cannot-be-enabled/ - Đonny

Answer 2

在 SQL Server 2012 中，您可以进入 属性 -> 选项 -> Service Broker 来进行操作。

- Dragos Durlut

---

同样适用于SQL 2008 sp3（刚刚测试）和最新的smss。 - JP Hellemons

同样适用于SQL 2016，刚刚测试过。 - Tallal Kazmi

Answer 3

如果你的服务禁用了消息队列或者你需要还原备份来禁用它，可以按照以下步骤操作。

运行此脚本即可终止使用数据库的所有进程（为什么在2008中无法手动终止进程，而在2005中却可以，我不知道为什么），然后设置消息队列。

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

- JGilmartin

---

附加有用信息：要查看是否已启用，请运行以下命令... SELECT is_broker_enabled FROM sys.databases WHERE name = '数据库名称'; -- 其中 '数据库名称' 是您想查询的数据库的名称。 - Myster

Answer 4

所有与数据库的连接必须断开并使用具有权限的用户帐户启用代理服务。

以下是理想情况（替换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;

此外，我建议创建一个具有适当权限的新角色和用户帐户（替换数据库登录）：

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

- Anubhav

Answer 5

由服务代理输出的错误。图像1

要修复，请使用SSMS启用服务代理，图像2

将Service Broker设置为true后，错误应该已经消失，参见我的图像3

- xrak

Answer 6

我们可以通过ALTER DATABASE语句以及从数据库属性>选项>服务代理>Broker Enable: True / False来启用代理服务。

但是在启用代理时存在问题：进程正在使用特定的数据库，我们必须杀死这些进程，然后才能启用代理服务。由@JGilmartin回答的方法完全可行，但请确保在生产环境中使用此脚本之前检查流量或进程严重性并杀死所有正在进行的数据库进程。

- Akash Maru