SqlDependency changes for RTM [Sushil Chordia]

Article
09/27/2005

As mentioned in my previous blog, SqlDependency is a new feature in .Net framework 2.0, which provide a mechanism to notify an app when a cache is invalidated. We got enough feedback from customers in Beta 2 with regards ease of deployment (some issues here) and security that we decided to make some changes for the final release. These new changes are now available as part of the September CTP. Following is a quick example on how to get Notification working on the September CTP bits. (Things new in September CTP are marked in RED)

using System;
using System.Data;
using System.Data.SqlClient;
class QuikExe
{
public static string connectionstring = "Get Connection String From The Config File";
public void DoDependency()
{
using (SqlConnection conn = new SqlConnection(connectionstring))
{
conn.Open();
Console.WriteLine("Connection Opened...");

SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select i from dbo.test";

//Notification specific code
SqlDependency dep = new SqlDependency(cmd);
dep.OnChange += delegate(Object o, SqlNotificationEventArgs args)
{
Console.WriteLine("Event Recd");
Console.WriteLine("Info:" + args.Info);
Console.WriteLine("Source:" + args.Source);
Console.WriteLine("Type:" + args.Type);
};

SqlDataReader r = cmd.ExecuteReader();
//Read the data here and close the reader
r.Close();
Console.WriteLine("DataReader Read...");
}
}

public static void Main()
{
try
{
//Start the listener infrastructure on the client
SqlDependency.Start(connectionstring);
QuikExe q = new QuikExe();
q.DoDependency();
Console.WriteLine("Wait for Notification Event...");
Console.Read();
}
finally
{
//Optional step to clean up dependency else it will fallback to automatic cleanup
SqlDependency.Stop(connectionstring);
}
}
}

You can compare it with the old Beta 2 example to get a feel of what has changed. One thing to note is that the APIs are not much different from Beta2, the internal implementation of SqlDependency is what that has changed a lot. Here are some changes will help you to get an idea of how dependency works.

Push Model to Pull Model
In Beta2 we had an HTTP/TCP listener that was active on the client that would listen on changes pushed by the server. The client for the first time acting as a listener added additional level of complexity. It was difficult to get the firewall set up right. Also, both the HTTP/TCP opened ports that were not secure.
In RTM, we have static method on SqlDependency called Start that

opens a new non-pooled connection
creates a new queue with a unique name is created
creates a new service with a unique name is created for that queue
creates a new procedure is created on the server to clean up the transient queue and service created in case the client is no longer listening
listens on the newly created queue for change notifications

One thing to note that there is only one connection that is opened per process (even if there are multiple calls to the default Start() method). This connection then pulls the change notification from the server queue and raises the appropriate event. Since the underlying listener infrastructure uses the normal connection, it doesn’t add any additional deployment nightmares and provides the same level of security as the underlying connection (SSL/Integrated Authentication). As expected a corresponding static method Stop is also implemented for closing that connection.

Ability to listen on custom queues
In Beta2, there was no way to listen on custom queue and services created by the user using the SqlDependency infrastructure. This had to solely be done using SqlNotificationRequest
In RTM, we have added SqlDependency.Start() to take a custom queue name as a parameter which will listen on the this new queue. In addition, when creating the SqlDependency the options property should be set to specify the SSB service, so that the resultsets associated with the dependency can bind to this service.

SqlNotificationPermission removed
In Beta2, we had SqlNotificationPermission that was used to restrict the listeners (HTTP/TCP) that could be opened on the client.
In RTM, since there is no concept of opening ports on the client, this is no longer necessary. SqlClientPermission should be sufficient for dependency infrastructure at the CAS level.

Event name change
In Beta2, The SqlDependency class had an event OnChanged which has been renamed to OnChange to RTM.

Required Database Permissions
The underlying implementation change has caused the minimum required database permission to change as well. Below is the list of permissions needed. Consider that you have two users: a user that calls SqlDependency.Start() (startUser) with no custom queue options and user that execute a command set up for Dependency(executeUser). In practice, these can be same as well. SQL statements in RED are the new permission changes for RTM.

--DBA creates a new role

EXEC sp_addrole 'sql_dependency_subscriber'

--Permissions needed for startUser

GRANT CREATE PROCEDURE to startUser

GRANT CREATE QUEUE to startUser

GRANT CREATE SERVICE to startUser

GRANT REFERENCES on CONTRACT::[https://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to startUser

GRANT VIEW DEFINITION TO startUser

--Permissions needed for executeUser

GRANT SELECT to executeUser

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO executeUser

GRANT RECEIVE ON QueryNotificationErrorsQueue TO executeUser

GRANT REFERENCES on CONTRACT::[https://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to executeUser

EXEC sp_addrolemember 'sql_dependency_subscriber', 'executeUser'

Lets go over the permissions one by one.

Line 1: Creates a new role sql_dependency_subscriber
Lines 5,6,7: Call to Start creates a queue, procedure and service as discussed earlier, hence we need these permissions for the startUser.
Line 8: The service created references the PostQueryNotificationContract and hence it requires REFERENCES permission for the startUser.
Line 9: Call to Start refers the system views for seeing if the role sql_dependency_subscriber is available. If it is available it grants the SEND permission on the transient queue that is created to this role.
Line 12: The executeUser should have SELECT permissions to issue SELECT command that is bound with SqlDependency
Lines 13,14,15: Since the executeUser binds a command to notification, it needs Subscription permission
Lines 16: To receive notifications on the transient queue, executeUser needs to be part of a new role sql_dependency_subscriber

The above just illustrates the permissions required. It might be much more manageable to create roles and then assign database permission to the role instead of the user. Then adding users to this role will automatically provide SqlDependency access to the users.

Conclusion: The above is an overview of changes that went in for the final release. The idea was to make the deployment and security story of SqlDependency much more simpler. We are looking forward to your experiences with these new changes. Do send in your comments or feedback.

Sushil Chordia, ADO.Net team, Microsoft.

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Comments

Anonymous
September 27, 2005
Nice post. I have two questions: Can I use SQL Server 2000 for using this feature? We are developing a windows client application with about 1000 users using it concurrently. Can we set up change tracking from each of the result queried from the client? Hopw well does it scale?
Anonymous
September 28, 2005
I downloaded the September CTP and got it working. This post helped in understanding the invalidation functionality. What happens when to the connection that is opened for listening,if I dont close it? What happens to the queue/service/sp created in that case?
Anonymous
September 28, 2005
Ken, This feature is not supported on SQL Server 2000, as it works on top of Queue mechanism that is new for 2005. SqlDepenedncy should ideally be used for mid-tier scenarios. The problem with having 1000 concurrent dependenciers is:
1. Opening of 1000 connections from different client - this should cause resource issues on the server
2. Each connection will then open 1000 queue/services/procs on the server. This can be alleviated by providing custom queues
3. Each notification request on the server will consume resources on the server

I would recommend against developing dependency apps at the client tier. It would make more sense to have your cache on the middle tier and then use SqlDependency for notifying on changes.

Hope this helps,
Sushil
Anonymous
September 28, 2005
The comment has been removed
Anonymous
October 03, 2005
I used the code above to "watch" the Products table in the AdventureWorks database and the OnChange was called once but after that it wouldn't display modifications to the table ; I was using the following SQL (SELECT ProductID, [Name] AS ProductName FROM Production.Product) ; do you have do something to "re-enable" the SqlCacheDependency?
Anonymous
October 09, 2005
Sunish
the SqlDependency has a one to one relationship with the command. You will only get one notification returned. When you refresh your data, you need to register a new SqlDependency to get an additional notification - so that happens each time.
Anonymous
October 10, 2005
Sushil, great blog - just found it and will note my buddies.

I was wondering about the underlying architecture for notifications and problems with firewalls. It seems that the architecture changed from server push to client pulls, right?

Also, if the client is controlling the dependency connection, what happens if the connection is dropped due to network probs or something? Exception in the client, or does the provider framework handle it and tries to establish the connection again?

Finally, how is performance. How often does the client go ahead and pull stuff from the server and how on earth does the server keep up with the load that can build up, even if you use a middle tier design?

Keep up the good work!

/JOhan
Anonymous
October 12, 2005
Johan, These are some very good questions. Answer follows:

-Yes, the architecture has changed from Server push to Client pulls. Since we open connections under the hood to monitor notifications, the firewall configuration would be no different from configuring a normal DataAccess application

-If connection that listens for notifications fails, on first failure, we release commands and connections, and immediately attempt to re-connect.If retry also fails, then we invalidate all pending SqlDependency objects, wait 1 minute and try again.

-Performance: The client pulls notification from the server only when they are available else nothing happens from the client pespective. From the server perspective, it uses SQL Server Service Broker(/SSB) technology which is new for SQL Server 9.0. The whole notification mechanism is based on Asynchronous, queued messaging. More information on how SSB works can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqlsvcbroker.asp

Hope that helps,
Sushil Chordia
ADO.Net
Anonymous
October 18, 2005
The required database permissions section was just what I was looking for, thank you. I'm confused though as to why it appears that the sysadmin role does not seem to have the required permissions.

I have an application that uses a sqldependency to monitor a table and uses intergrated security=sspi in the connection string. If I run the app as a system admin it won't work, unless connected to the network. Changing the connection string to use sa won't work either, unless connected to the network. If I create a user and add the permissions mentioned everything works fine, connected or disconnected from the LAN.

Database and app are on one laptop btw.
Anonymous
October 30, 2005
In Beta 2, SqlDependency.Id contained a string representation of a Guid. At that time I already wondered why this wasn't simply a Guid instead of a string, but I didn't spend much time on it.
Today when I tried to run my code after compiling it with the RTM build I noticed that it fails because SqlDependency.Id now contains a semi-colon separated list of Guids:
ceaf3eb4-0d63-492f-b433-c13482ab21c8;d7d6445d-03b8-4c5c-82cc-95a230a0690a

I was wondering what the idea and purpose behind this is?
Anonymous
December 09, 2005
I have tried the SqlDependency for a while. In RTM version, it finally works for me. It only works for a period of time, then I have to restart my IIS. So it doesn't look like it is the Sql side problem. But I have no idea how to debug on client side, maybe you can tell me a way to do some debug on client side.
Anonymous
December 18, 2005
I haven't been able to get SqlDependency to work at all with CLR procedures. Is it supposed to?
Anonymous
December 22, 2005
http://www.shoes-warehouse.net/list.html
Anonymous
December 23, 2005
Like davidw, I also get it to work for about 10 minutes or so in IIS and then it fails from then on. If I restart IIS, it is fixed for about another 10 minutes or so.

Looks like SqlDependency is not ready for prime time...
Anonymous
December 26, 2005
RobertZ-
In "Creating Query Notification Subscriptions" in the docs there is a note that says "Transact-SQL does not provide a way to subscribe to notifications. The CLR data access classes hosted withing SQL Server do not support query notifications."

Hope that helps...
Anonymous
January 26, 2006
Is it possible to use SqlDependency with SQL Server Express User Instances? In that case how do you enable the broker? My ALTER DATABASE statement always fails.

Thanks for a good post, Eric
Anonymous
January 27, 2006
I have executed the above TSQL for my new user on the database that contains the data, but I am still getting an error.

"System.Data.SqlClient.SqlException: The specified schema name "dbo" either does not exist or you do not have permission to use it."

I am missing something simple, but I am not sure what it is.

I did not give the user any special permissions except for the ones stated above and permission to use the database with the default schema set to dbo.

Note that SqlDependency.Start() works fine when I give the user sysadmin rights.

Any help would be greatly appreciated.
Chris
Anonymous
February 06, 2006
Hi Chris, this is becase we are missing one more permission in the blog. The TSQL in blog was written with the assumption that the logins have alter permission to the dbo schema.

In order to create a procedure (or a function or a table or … any other schema-scoped object), you need at least ALTER permission on the target schema. Here is the TSQL syntax, hope this helps:

grant alter on schema :: some_schema to least_privileged_user

Thanks,
Sushil Chordia
Anonymous
February 08, 2006
Is there anyway to find out which row is affected in OnChange event? Or which data has been modified?
Anonymous
February 16, 2006
The comment has been removed
Anonymous
March 01, 2006
This page is the bible (currently) for implementing SqlDependency - thanks! However, I haven't seen any examples about running this in the Application_Start event of Global.asax.cs, which would seem the obvious place to set up caching static tables in the Application cache.
For the .NET 2.0 example below I have not been able to get a table change to fire the delegate anon method (which I want to null out the cache for testing). I see that the Queue and Service are being created in SQL 2005, but never firing to this client when I modify the table. I have been very careful to setup all of the SQL permissions as you've defined above. Should I be looking to SQL2005 or the Application_Start method to figure out why this isn't working?:
protected void Application_Start(Object sender, EventArgs e)
{
using (SqlConnection conn = SqlHelper.GetSqlConnection(SqlHelper.enUser.WTJournal, SqlHelper.enDataBase.EnterpriseDB))
using (SqlCommand cmd = new SqlCommand("SELECT MARKET_ID, DESCRIPTION FROM dbo.MARKETS",conn))
{
try
{
// create dependency associated with cmd
SqlDependency depend = new SqlDependency(cmd);
SqlDependency.Start(conn.ConnectionString);
// register handler
depend.OnChange += delegate(Object o, SqlNotificationEventArgs args)
{
Application["MARKETS"] = null;
};
// .. load up the dataset ds here
Application["MARKETS"] = ds;
}
catch (Exception ex)
{ Console.WriteLine(ex.Message); }
}

Thanks for any insight,
flanger
Anonymous
March 01, 2006
The comment has been removed
Anonymous
March 06, 2006
Hi,

first I have to agree that this is a great reference for using SqlDependency.

Can you tell me if there's a System SP or something to tell the SSB to process all current events?
Because when I do an update or insert on a table and then right away call a select(with an SqlDep assigned) on that table the SqlDep.OnChange fires right away for the previous update/insert.
I guess that's because the SSB is asynchronous and does take some time to process these events - when I put a Thread.Sleep(1000) between the update and the select it works just right, but I'm not quite happy with that solution...

Thanks

Florian
Anonymous
April 04, 2006
I also agree with the post’s above. This is the best one-stop shop of information regarding using SqlDependency I’ve found so far. However, I’m having some problems…

My multi-tiered application (SQLServer 2005/.NET 2.0) makes use of several different query notifications. All data related calls are made from the data-access tier. Calling SqlDependency.Start works just fine on some calls and the related notifications are delivered without problem. However, following the same pattern, other calls to SqlDependency.Start result in a System.Runtime.Serialization.SerializationException with the message "Unable to find assembly...". The assembly that is mentioned is actually upstream from the data-tier and should have no ties whatsoever to the database, only to the data-tier. Can you help me understand what is happening here? Any way to work around this?

Thanks,
Kevin
Anonymous
April 07, 2006
I have two questions:

- how resource and time heavy is setting up an individual dependency

- Is it necessary to create a new SqlConnection, SqlCommand, SqlDependency object each and every time, as shown in flanger's post?

I'm trying to use a dependency to monitor a table that could be written (a few) times a second, from one (possibly a few) clients. I'm hoping this isn't going to cause a problem...

Thanks,

Ruth
Anonymous
April 24, 2006
The comment has been removed
Anonymous
April 25, 2006
Stacey, Thanks for your comments. Yep, I agree that having a new Queue created for 1000 users is a tax. The idea of custom queues that you proposes is already part of v2.0. For case as such, you can always use custom queues. Use SqlDependency.Start(ConString,QueueName) to start a listener to a queue and a service already created. Once you start the SqlDependency with the queue name. You will have to create othe SqlDependency objects by specifying these custom service. Let me know if you have questions.

Thanks,
Sushil.
Anonymous
June 07, 2006
The comment has been removed
Anonymous
June 12, 2006
Hi Sushil,
Could you please suggest me on following:
If I have around 70-80 clients having my Winform Application running, and there is only one SQL Server being shared by all the client. How should I implement the SQL Server Notification Services:
-Will it raise the performance issue on server, if I use SQLDependency Object.
-What are the other ways to design Notification Services solution from better performance perspective, if the target is to solve the stale cache problem only.

Thanks in Advance!
Sutikshan Dubey
Anonymous
June 28, 2006
Hi Sushil
I face one problem but i don't know is it problem or not.
I create SqlDependency Object using following code.In my dbo.Users table there are two columns.one is Id and other is Name
Now if i change in Name column value its call OnChangeEventHandler.
I think it is wrong because in Sqlcommand query I write only one column ID so I think its only call on changes of ID column value
SqlCommand oCommand = new SqlCommand(
"SELECT ID FROM dbo.Users",
oConnection);
SqlDependency oDependency = new SqlDependency(oCommand);
oDependency.OnChange += new OnChangeEventHandler(OnNotificationChange);

Give me answer it is write?
I just want to Notifaction on ID column
Anonymous
July 03, 2006
Sutikshan, When you have 70-80 clients connecting to the server you have following options:
1. Create a mid-tier that process the requests from the client. The Cache on the mid-tier is queried from the backend server and change tracking enabled using one default implementation of SQLDependency.

2. If the above is not an option, then at the very minimum create a custom queue and a service on the Server and have all the client listen to this Queue to track for change notification. This will remove the overhead of creating unnecessary objects on the store. There will still be some overhead on the server because of ~80 different client continously polling for a notification message sent on the queue.

hope this helps,
Sushil.
Anonymous
July 03, 2006
Hi Boaz, Thanks for visiting this blog. Here are answers to your question.
1. As per Torsten Garbs "Unfortunately, the behavior for TRY/CATCH and SqlDependency is by design. Please check a recent MSDN Forum discussion on this topic. You can find it at:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=464435&SiteID=1 "
2. When the Server is down, the underlying connection that polls for Notification on the queue, tries to reconnect to the server:
a. If the server responds to the second re-connect, then the SBS (Broker Service) has the Restart notification message on its queue and
the SqlClient should just raise this event.
b. If the server doesn't respond to the re-connect, then the client throws a notification of the form {SqlNotificationType.Change, SqlNotificationInfo.Error, SqlNotificationSource.Client}.

Share via

SqlDependency changes for RTM [Sushil Chordia]

Comments

Additional resources