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?
Best Answer
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