cool IT Team-Blog

Verwendung von SQL Notifications mit SQL Server 2008 R2

1. Voraussetzungen in der Datenbank

Es müssen folgende DB-Options gesetzt sein, damit Notification überhaupt funktionieren können. Auch die Tabellen müssen mit diesen Options erzeugt worden sein.
  • ANSI_NULLS ON
  • ANSI_PADDING ON
  • ANSI_WARNINGS ON
  • CONCAT_NULL_YIELDS_NULL ON
  • QUOTED_IDENTIFIER ON
  • NUMERIC_ROUNDABORT OFF
  • ARITHABORT ON
Die Datenbank muss einen Besitzer haben. Wir haben Probleme festgestellt, wenn dieser User ein Domain Account ist. Wir empfehlen daher die Benutzung des Users "sa" als Besitzer.

Außerdem haben wir festgestellt, dass der SQL Server Dienst unter einem Domänen-Account laufen muss, sonst kommt es zu sporadischen Fehlern.

2. Die Query

Wie das halt so ist im Leben, kann meistens genau die Query, die man notifizieren lassen will, nicht so verwendet werden. Es gibt eine Menge Einschränkungen. Die wichtigsten hier in Kürze:
  • Keine Outer Joins
  • Kein "*" als Spaltenselektor
  • Keine Subqueries
  • Keine Aggregatfunktionen mit wenigen Ausnahmen
Detailliiert gibt es diese Kriterien unter http://msdn.microsoft.com/en-us/library/ms181122.aspx.

Sollte die Notifizierung einer Query gewünscht sein, die zu komplex ist, kann man überlegen, ob es eine einfache Query gibt, die notifiziert, ob sich etwas geändert hat. Zum Beispiel könnte es in einer Tabelle ein Datumsfeld geben, das beim Update geschrieben wir. Dann reicht im Prinzip für die Notifizierung

SELECT lastUpdt FROM myTable

als Query für die Notification. Was man sich dann tatsächlich holt, muss ja nicht mit der notifizierten Query übereinstimmen.

3. Der Broker

Inder Datenbank muss der Service Broker - Dienst enabled sein, der die Notifications übernimmt. Dieser kann durch den Befehl

ALTER DATABASE myDB SET BROKER_ENABLED

eingeschaltet werden.

Achtung: Beim Restore eines Backups auf eine weitere Datenbank am gleichen Server muss vor der Verwendung dieser DB

ALTER DATABASE myDB SET NEW_BROKER

ausgeführt werden, da jeder Broker eine GUID hat, die am Server eindeutig sein muss. Und das Backup und Restore schreiben diese ID mit und dupliziere sie daher.

4. Der Code

Ein sehr gutes Beispiel für den Code kann unter http://www.codeproject.com/KB/database/chatter.aspx gefunden werden.

Nachdem eine DB-Änderung notifiziert worden ist, kann der Handler ausgehängt und die Dependency gelöscht werden. Es muss nach jeder Änderung eine neue Dependency angelegt werden!

WICHTIG
Für die Notifications darf in der Applikation nur einmal das SqlDependency.Start (beim Programmstart) und einmal das SqlDependency.Stop (beim Programmende) gemacht werden. SqlDependency.Stop setzt alle laufenden Dependencies zurück!

5. Berechtigungen für Benutzer

Die Berechtigungen für die SQL Server Notifications können nach dem CodeProject-Artikel http://www.codeproject.com/KB/database/SqlDependencyPermissions.aspx gesetzt werden. Eine Möglichkeit ist auch, unten stehendes Script auszuführen und dann allen Benutzern diese beiden Rollen zuzuweisen.
 
EXEC sp_addrole 'sql_dependency_subscriber'
EXEC sp_addrole 'sql_dependency_starter'
-- Permissions needed for [sql_dependency_starter]
GRANT CREATE PROCEDURE to [sql_dependency_starter]
GRANT CREATE QUEUE to [sql_dependency_starter]
GRANT CREATE SERVICE to [sql_dependency_starter]
GRANT REFERENCES on
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_starter]
GRANT VIEW DEFINITION TO [sql_dependency_starter]
-- Permissions needed for [sql_dependency_subscriber]
GRANT SELECT to [sql_dependency_subscriber]
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber]
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber]
GRANT REFERENCES on
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_subscriber]

 

6. Conclusio

Der Prozess, Notifications aufzusetzen, ist zwar schlecht dokumentiert und - wenn einem Fehler unterlaufen - reagiert der SQL Server manchmal ein bisschen merkwürdig, aber wenn sie einmal laufen, dann ist die Performance schon optimal.

Ist doch cool, oder?
Verfasst: 12.08.2010 10:22:17 von Sabine Stiller
Tags: Server Broker, SQL Notifications, SQL Server 2008 R2

1


Kommentare
Für diesen Blogbeitrag liegen zurzeit keine Kommentare vor.
Einen Kommentar schreiben



 Security code