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
I've also run
DISABLE_BROKER on my development database, then performed the backup. Restored to the staging server and then run both
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?