SQL Server What is enable_broker for SQLTableDependency

Question

In SQL Server, What is enable_broker? What is the risk? SQL Table Dependency wants that.

Answer 1

1) https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-service-broker?view=sql-server-2017

SQL Server Service Broker provides native support for messaging and queuing applications in the SQL Server Database Engine. This makes it easier for developers to create sophisticated applications that use the Database Engine components to communicate between disparate databases. Developers can use Service Broker to easily build distributed and reliable applications.

2) https://www.sqlservercentral.com/Forums/Topic818423-146-1.aspx

One risk being if there are already service brokers setup to use that DB they will probably break, and any live connections will be killed and rollback.

3) Sql Dependency with Service Broker

It's not necessary but if you want to see changes you need it or you can use an other service like periodic polling

Be careful using the SqlDependency class to monitor changes in the database tables - it has the problems with the memory leaks.

I hope it will help you !