SqlDependency changes for RTM [Sushil Chordia]

re: SqlDependency changes for RTM [Sushil Chordia]

Ken — Wed, 28 Sep 2005 07:18:22 GMT

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?

re: SqlDependency changes for RTM [Sushil Chordia]

Anonymous — Wed, 28 Sep 2005 17:42:00 GMT

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?

re: SqlDependency changes for RTM [Sushil Chordia]

sushilc — Wed, 28 Sep 2005 19:49:11 GMT

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

re: SqlDependency changes for RTM [Sushil Chordia]

sushilc — Wed, 28 Sep 2005 19:56:43 GMT

Anonymous, the connection that is called with Start is left open if you dont call the corresponding Stop.The transient resources created on the server are still alive till the connection is open. When the AppDomain gets unloaded, the connection gets closed and all the transient resources are also removed. If the application is rudely aborted (meaning the AppDomain.Unload event is not called) then the server side clean up has to kick in. The Stored proc (After a specific timeout) on the server checks if there is a connection open that is listening to the transient queue. If it doesnt find such a connection , then it drops the queue/service and itself.

Hope this helps.
Sushil

re: SqlDependency changes for RTM [Sushil Chordia]

Sunish Abraham — Mon, 03 Oct 2005 23:38:51 GMT

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?

re: SqlDependency changes for RTM [Sushil Chordia]

Julie Lerman — Sun, 09 Oct 2005 22:49:59 GMT

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.

re: SqlDependency changes for RTM [Sushil Chordia]

Johan Danforth — Tue, 11 Oct 2005 02:10:05 GMT

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

re: SqlDependency changes for RTM [Sushil Chordia]

DataWorksBlog — Thu, 13 Oct 2005 00:26:56 GMT

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

re: SqlDependency changes for RTM [Sushil Chordia]

Mark Nischalke — Tue, 18 Oct 2005 21:23:06 GMT

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.

DataType of SqlDependency.Id

Michael Vanhoutte — Sun, 30 Oct 2005 18:06:31 GMT

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?

re: SqlDependency changes for RTM [Sushil Chordia]

davidw — Sat, 10 Dec 2005 09:01:32 GMT

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.

re: SqlDependency changes for RTM [Sushil Chordia]

Robert Zurer — Mon, 19 Dec 2005 07:05:45 GMT

I haven't been able to get SqlDependency to work at all with CLR procedures. Is it supposed to?

re: SqlDependency changes for RTM [Sushil Chordia]

Adidas — Fri, 23 Dec 2005 10:31:58 GMT

http://www.shoes-warehouse.net/list.html

re: SqlDependency changes for RTM [Sushil Chordia]

Paul Reid — Sat, 24 Dec 2005 02:07:17 GMT

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...

re: SqlDependency changes for RTM [Sushil Chordia]

Julie Lerman — Mon, 26 Dec 2005 18:02:58 GMT

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...

re: SqlDependency changes for RTM [Sushil Chordia]

Eric — Fri, 27 Jan 2006 11:24:40 GMT

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

re: SqlDependency changes for RTM [Sushil Chordia]

Chris — Fri, 27 Jan 2006 22:57:56 GMT

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

re: SqlDependency changes for RTM [Sushil Chordia]

DataWorksBlog — Tue, 07 Feb 2006 04:38:02 GMT

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

re: SqlDependency changes for RTM [Sushil Chordia]

Vishant — Thu, 09 Feb 2006 07:28:54 GMT

Is there anyway to find out which row is affected in OnChange event? Or which data has been modified?

re: SqlDependency changes for RTM [Sushil Chordia]

DaveH — Thu, 16 Feb 2006 20:06:37 GMT

Hi Sushil,

Firstly nice article.

I have taken you code above and gotten a very simple select id, name from dbo.users and everything works fine, once the dep.OnChange is called I set it up again to get constant updates, this all works great.

Although as soon I try to perform a simple join I get the delegate being called multiple times (untill i stop the app) and args.info is Invalid?

Am I missing something or do you have any ideas?

Thanks a lot,

D

re: SqlDependency changes for RTM [Sushil Chordia]

flanger — Wed, 01 Mar 2006 18:23:29 GMT

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

re: SqlDependency changes for RTM [Sushil Chordia]

DataWorksBlog — Wed, 01 Mar 2006 22:22:10 GMT

Hi flanger, Thanks for your comments above.
As per the above code snippet, you set up SqlDependency on variable "cmd"; but you dont execute it at all. Query subscriptions on the server happen only on command execution. You should use the command set up with SqlDependency to fill your dataset and then use it.

Hope this helps,
Sushil Chordia

re: SqlDependency changes for RTM [Sushil Chordia]

FvS — Tue, 07 Mar 2006 14:23:00 GMT

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

re: SqlDependency changes for RTM [Sushil Chordia]

Kevin — Tue, 04 Apr 2006 22:27:58 GMT

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

re: SqlDependency changes for RTM [Sushil Chordia]

Ruth Cook — Fri, 07 Apr 2006 19:36:45 GMT

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

re: SqlDependency changes for RTM [Sushil Chordia]

staceyw — Tue, 25 Apr 2006 07:45:23 GMT

Excellent article. The new pull model makes so much more sence. I did something similar with tcp sockets where socket was put in a list and a new insert invoked a timer. So the user would get a Notify message after N new records or after time expire (which ever comes first) so you can "batch" messages and not send notify after every new record, which can bog the server if you don't need to see every new record right away. Now with this feature, I don't need to roll that myself. I do loose the ability to batch, so that may be a nice feature to add at some point. Also, if you did it yourself for 1000 users, you would still need 1000 sockets, so the tax for sqldependancy on the server is not a net increase. The new dynamic queues for each user is tax.

Maybe you could just "name" the queue in the constructor, and it creates it once on first use for all "N" clients and deletes it after some period of no client activity. That would save new queue overhead, and keep it easy on the client side with no need to know about server side queues - other then the name.

re: SqlDependency changes for RTM [Sushil Chordia]

DataWorksBlog — Tue, 25 Apr 2006 18:25:07 GMT

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.

re: SqlDependency changes for RTM [Sushil Chordia]

Boaz — Thu, 08 Jun 2006 09:18:27 GMT

Hi Sushil,

I have been testing SQLDependency and I have a few questions;

1) When using a stored procedure to run the dependency query, using a "clean" procedure (containing nothing but a select statement), works fine. Adding try..catch (which is a part of our coding standards), results in the subscription firing immediately because of an invalid query. Is this by design? If so, how can I implement error handling.

2) How does SQLDependency handle SQL Server restarts?
I see two options:
a) The subscription is fired listing a server restart message in the service broker queue, but as far as I can tell, SQLDependency has no way of handling these messages given the connection has been broken. (Establishing a new connection and dependency won’t read the said messages).
b) The SQLDependency listener on the client raises an error for the connection being terminated. Can I relay on this event to recreate a connection and dependency?

TXN in advance

Boaz

re: SqlDependency changes for RTM [Sushil Chordia]

Sutikshan — Tue, 13 Jun 2006 12:16:15 GMT

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

SqlDependency

Dan Miser — Tue, 27 Jun 2006 18:08:53 GMT

re:SqlDependency changes for RTM [Sushil Chordia]

Tejas Patel — Thu, 29 Jun 2006 09:16:02 GMT

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

re: SqlDependency changes for RTM [Sushil Chordia]

Sushil Chordia — Mon, 03 Jul 2006 22:59:32 GMT

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.

re: SqlDependency changes for RTM [Sushil Chordia]

Sushil Chordia — Mon, 03 Jul 2006 23:04:27 GMT

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}.