How sql dependency works for passing data back & forth

Question

1) i like to know how sql server establish a channel between client & db. i guess there must be a channel and that why sql server can send notification to client through that channel. please discuss this issue in detail. because i saw many article on sql dependency but every body gives the code but no body explaining how it works in details. What is Service Broker?

Service Broker architecture allows you to build loosely coupled SQL Server instances so that the instances talk with each other using normal form of messaging. Service Broker uses TCP/IP to transmit messages form the network and hence allows encrypted messaging. It is both for applications which use SQL Server instance or for applications that distribute the work into more than one SQL server instance. Service Broker allows to use Queue to hold messages and hence the messages are processed one by one without the caller to wait to receive the message.

1) i like to know service broker pass the message always in encrypted format?

2) Service Broker allows to use Queue to hold messages. what is the name of that queue used by service broker. how can i see what is stored in that queue?

3) i saw many people create queue but did not mention why they created? they also did not use that queue in their code. here is one url & 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 NameChangeQueue queue why ?? how do i know who will use this queue?

4) even i saw people create role but never know why role would be require in this case?

so please discuss ALL my points in detail because i need to understand all the points. thanks

Answer 1

First read The Mysterious Notification to understand how Query Notifications work. SqlDependency is just a .Net wrapper leveraging Query Notifications. This should answer most of your questions.

Query Notifications deliver the notifications using Service Broker (SSB) locally to a queue in the database. While SSB can encrypt traffic, this is irrelevant for SqlDependency since the delivery is local, within the server process. The client application gets the notifications by posting a WAITFOR(RECEIVE) on the queue using an ordinary SqlConnection.

In the example you posted the NameChangeQueue is never used, indeed. By using a SqlDependency object the author is actually using the temporary queue deployed just-in-time when it called SqlDependency.Start(). The author could had used instead the lower level SqlNotificationRequest as described in Using SqlNotificationRequest to Subscribe to Query Notifications which allows you to specify the queue to be used.

The permissions required are described in Query Notification Permissions, but if you use SqlDependency then you will also need permissions to create the temporary queue and stored procedure used by SqlDependency.

Read the articles linked and if you have more questions, ask them as new questions here (don't post more questions as comments please).