1

I would appreciate Your help:

We are planing to buy SQL Server 2008 R2 Web Edition (we must this version), however we would also like to use a functionality of Service Broker for SqlCacheDependency - Notification Mode (NOT polling mode).

http://msdn.microsoft.com/en-us/library/cc645993%28v=sql.105%29.aspx is said that Web Edition support for Service Broker (messaging) is : "Client only" - so some kind of short version.

Our server-environment is:

  • one Http server with two web-application
  • one DataBase server with single instance of SQL Server 2008 R2 Web Edition.

The idea is that: when new record is added to database table, sql server should notify both web-apllications and reset their caches (so we will have two integral caches). We are not planing to create a distributed cache (MemCache or sth)

Is there any way to combine these two demands (SqlCacheDependency via Service Broker Notification in a WebEdition of SqlServer 2008 R2) ? Buy a module, or some server architecture tricks. Any ides ?

Thanks in advance.

Barney
  • 154
  • 1
  • 3
  • 12

1 Answers1

4

The "client only" is kind of a misnomer. It means that two Service Broker services that exchange services must route the message through at least a higher edition version. An Express Edition cannot exchange messages with another Express Edition unless a Standard (or higher) edition server routes the messages between them.

While this is educational, is also completely irrelevant for you since Query Notifications (the feature behind the SqlCaheDependency modus operandi), never has to send a message remotely to another SQL Server instance. Is all local, so the 'client-only' restriction never applies. To be 100% accurate I should add that the way SqlCacheDependency uses Query Notifications never requires remote messaging, but this is splitting hairs.

I recommend you read the article linked, your post shows quite a few misunderstanding on how this technology works.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Yes, as you said - phrase "client only" is misleading. Thank You for thoroughgoing answer. From your information derives that WebEdition will not limit functionality of Service Broker Notifications in SqlCacheDependency. I'm glad to hear that :) – Barney Feb 14 '13 at 10:52
  • 1
    You should test SqlCacheDependency carefully. Invalidating the cache via Query Notification is a costly operation, it involves *several* writes in the database. It works great for relatively static data that changes seldom, but can be problematic if the data changes frequently. – Remus Rusanu Feb 14 '13 at 11:05
  • I see You write answers in lot of topics about Service Broker Notifications, and have big knowledge about it. I would like to ask some more questions. 1) Is it possible to define a Notification on SELECT, but in that way: the Broker would reset Cache only if columns written in select has changed. So Column Specific approach. I don't want the cache resets if some unimportant column in the table are changed. I will have a SELECT with INNER JOIN. From my search its unfortunately impossible. – Barney Feb 14 '13 at 13:10
  • 2)Should my Select look so - I mean the SET options: "SET ANSI_NULLS ON; SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; SET CONCAT_NULL_YIELDS_NULL ON; SET QUOTED_IDENTIFIER ON; SET NUMERIC_ROUNDABORT OFF; SET ARITHABORT ON; SELECT dbo.table1.id AS id_table1, dbo.table1.col_table1, dbo.table2.id AS id_table2, dbo.table2.col_table2, dbo.table2.table1_id FROM dbo.table1 INNER JOIN dbo.table2 ON dbo.table1.id = dbo.table2.table1_id; " – Barney Feb 14 '13 at 13:17
  • 1
    Ask separate questions please. This way stackoverflow Google SEO does it's magic, they get theyr ranking and we get our votes ;) – Remus Rusanu Feb 14 '13 at 13:21
  • ohh OK. Sorry, I'm newbie :) I'm creating two new Question. – Barney Feb 14 '13 at 13:32
  • http://stackoverflow.com/questions/14876079/sqlcachedependency-via-service-broker-notifications-select-sensitive-only-on-p Hope for answers – Barney Feb 14 '13 at 13:38
  • and http://stackoverflow.com/questions/14876444/sqlcachedependency-via-service-broker-notifications-select-command-good-manner – Barney Feb 14 '13 at 14:00