How sql dependency works for passing data back and forth

Question

1) I like to know how the sql server establishes a channel between the client and db. I assume that there should be a channel, and so the sql server can send a notification to the client through this channel. Please discuss this issue in detail. because I saw a lot of articles about sql dependencies, but each authority gives the code, but does not explain how it works in detail. What is a Service Broker?

The Service Broker architecture allows you to create loosely coupled instances of SQL Server so that instances can talk to each other using a regular messaging form. Service Broker uses TCP / IP to send messages from the network and therefore allows messages to be encrypted. This is both for applications that use an instance of SQL Server, and for applications that extend this work to multiple instances of SQL Server. Service Broker allows you to use Queue to store messages, and therefore messages are processed one by one, without the caller waiting to receive the message.

1) I like to know that the service broker always sends the message in an encrypted format?

2) Service Broker allows you to use Queue to store messages. what name of this queue is used by the service broker. How can I see what is stored in this queue?

3) I saw how many people create a queue, but do not mention why they created it? they also did not use this queue in their code. here is one url and sample code

http://www.dreamincode.net/forums/topic/156991-using-sqldependency-to-monitor-sql-database-changes/

CREATE QUEUE NameChangeQueue; CREATE SERVICE NameChangeService ON QUEUE NameChangeQueue ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]); GRANT SUBSCRIBE QUERY NOTIFICATIONS TO YourUserName; ALTER DATABASE YourDatabaseName SET ENABLE_BROKER; 

they never use the NameChangeQueue , why? How to find out who will use this queue?

4) even I saw how people create a role, but they never know why a role is needed in this case?

so please discuss ALL of my questions in detail, because I need to understand all the points. thanks

Answer 1

Read the Cryptic Notification first to understand how query notifications work. SqlDependency is just a .Net shell that uses query notifications. This should answer most of your questions.

Request notifications deliver notifications using Service Broker (SSB) locally to the queue in the database. Although SSB can encrypt traffic, it is not related to SqlDependency, since the delivery is local, as part of the server process. The client application receives notifications by sending WAITFOR(RECEIVE) to the queue using the usual SqlConnection.

In the above example, NameChangeQueue never used. Using the SqlDependency object, the author actually uses a temporary queue deployed right on time when it is called SqlDependency.Start() . Instead, the author could use a lower level of SqlNotificationRequest , as described in Using SqlNotificationRequest to Subscribe to Request Notifications , which allows you to specify which queue to use.

Required permissions are described in Query notification permissions , but if you use SqlDependency , you will also need permissions to create a temporary queue and stored procedure used by SqlDependency.

Read related articles, and if you have other questions, ask them new questions (don't leave more questions in the comments).