0

I'm currently developing an application which uses SqlDependency which relies on the Service Broker.

I'm running into an issue every time I move the database from development to staging in that I'm getting a YSOD stating that

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.

I'm not a SQL Server expert and so I don't understand why this is happening. I've tried a bunch of things such as running 

ALTER DATABASE [DbName] SET ENABLE_BROKER

on the newly restored database. This doesn't fix the issue. Also calling

ALTER DATABASE [DbName] SET NEW_BROKER WITH ROLLBACK IMMEDIATE

doesn't help.

I've also run DISABLE_BROKER on my development database, then performed the backup. Restored to the staging server and then run both ENABLE_BROKER and SET NEW_BROKER hoping that something will give.

Both servers are running SQL 2005 on Windows 2003.

My application is using SqlDependency.Start(connectionString) so I believe I am using the default Service Broker Endpoint rather than a named one(?)

The databases are named different on the development and staging server but some configuration ensures the application is using the correct connection string.

What am I missing? What am I doing wrong?

What is the correct way to move (backup/restore) a database that uses Service Broker / SqlDependency?

Greg B
  • 1,548
  • 5
  • 17
  • 32

1 Answers1

1

You need to restore the database with ENABLE_BROKER option (by using the WITH ENABLE_BROKER clause of RESTORE DATABASE command). Keep in mind that the database will retain its unique broker ID, so after you do that, you need to be careful there are no conflicts between the restored db and the one that's left in the development environment.

When you called NEW_BROKER, a new unique broker ID has been assigned to the database, so the existing conversations are doomed (they can't reach the destination broker ID anymore).

Here's more info on managing broker identities: MSDN: Managing Service Broker Identities

Pawel Marciniak
  • 205
  • 1
  • 4
  • Could you expand a little more on what you mean by "restore the database with ENABLE_BROKER" Do you mean restore the database then execute the ALTER DATABASE or is there a special way of restoring the database with the broker enabled? – Greg B Nov 18 '10 at 21:56
  • Edited my answer to address your additional question. I meant enabling broker while restoring the database, not a separate command. – Pawel Marciniak Nov 19 '10 at 18:04