SQL Server Event Service

SQL Server Event Service – Quite often, developers of client-server applications need to organize a mechanism that allows the event on the sql-server to notify one or another client. Even more often it is a pink and blue dream of the customer that the developer has implemented such a mechanism.

For example, when shipment limits are exceeded to a customer, managers working with that customer should be immediately notified. Some system customers require (and all customers dream of it without exception) that when some data changes, other system users automatically update this information, and immediately.

The appropriateness of such a requirement will not be discussed here (it has many reasons to criticize it), only solutions will be discussed here. Microsoft sql-server has a standard tool for notification organizations – alerts, but this tool has a very limited application, by and large does not allow you to create a guaranteed working mechanism based on it. And that’s why: Communication with the client program can be done by sending an e-mail or emulating the “net send” parcel. Both of them are inconvenient for receiving a notification.

E-mail is uncomfortable for reasons

• There is no guarantee of delivery, the mail may be lost.
• The mail may “get stuck” at intermediate sites.
• Tcp/ip protocol is required.
• An smtp server and email profile configuration are required.
• Special configuration of the sql-server is required to send mail.
• A mailbox is required for each client waiting for an event.
• It is necessary to organize a mail client in the client program.

Sending by “net send” is inconvenient for the following reasons:

• There is no guarantee of delivery, as it is organized through mailslot, which does not have such guarantee.
• The correct netbios name resolution in the network is required.
• Requires “Client for Microsoft networks” on the client.
• Standard mailslot is used, this may have an overlap with other programs.

And in general, the alerts tool is not comfortable with the need to register each client as an operator and the corresponding configuration. In other words, for the simplest cases, alerts can be applied. But for most cases, it is not applicable.

Known SQL Server Event Service implementations and concepts

The general public is aware of several options for implementing the client server notification mechanism. This:

1. Create an object (extended stored procedure or activex) through which the sql-server notifies the client via tcp/ip sockets. In this case, the client is organized listening, i.e., the client program has become a server tcp/ip.

Disadvantages of this method:

• Binding to the tcp/ip protocol. In networks where only ipx, netbeui or appletalk are used, this mechanism is not applicable.
• No asynchrony. If this event is generated from a trigger, there will be performance problems.

2. Create an object (extended stored procedure or activex) through which the sql-server notifies the client via named pipes or mailslots. At the same time, the client has a wiretap of one or another.

Disadvantages of this method:

• Correct netbios name resolution is required in the network.
• Requires “Client for Microsoft Networks” on the client.
• No guarantee of delivery if mailslot is used.
• In the case of named pipes, this cannot be used on windows 95/98/me client computers, as named pipes can only be created on nt.
• There is no asynchrony. If this event is generated from a trigger, there will be performance problems.

3. Periodical polling of the sql-server by the client (periodic reading of the special Event nameplate). This is a very simple way, but still free from most of the above mentioned disadvantages. Unfortunately, this method has its own specific 2 drawbacks:

• receiving the notification may be delayed by the timeout value of the survey
• with a small timeout significant traffic occurs.

However, with a small number of sessions, this method is quite suitable and undeservedly neglected.

Proposed solution option

We offer you a solution to the problem free of the above problems (all the above!), but at the same time quite simple. The idea is as follows: a binary object is placed on the server, which sql-server can call (and it can only be an extended stored procedure or activex-object), which has two unrelated methods.

The first method creates a win32api createevent kernel object called “event” with a unique name passed by the parameter. Next, the function win32api waitforsingleobject is called. Upon encountering this function, the thread stops and waits until this kernel object signals. Please note that as many such kernel objects as you want can be created. This is limited only by the number of handles in the system.

The second method calls the kernel object event by the name specified by the parameter, using the function win32api setevent and sets it the property “signaled”. As soon as it happens the thread with the first method wakes up and returns control to the called process. The second method does not wait for the result but returns control to its calling process right after setting the “signaled” property. Thus, asynchrony is achieved.

Now it only remains to make the stored procedures t-sql, which control this object and the necessary functionality “in our pocket”. The client program in a separate thread starts the stored procedure of waiting for the event, passing the parameter unique feature address. It can be a user name, computer name, or any string. The main thing is that it is a unique identifier within the client-server system. The stored procedure will only return the result if an event is generated for this recipient. When the event is received, the procedure is restarted. When the program is closed, the event waiting stream is simply nailed down via terminatethread.

At first glance, this method has a “terrible” disadvantage – there is a permanent connection with the sql-server, which does nothing most of the time. But this is only the first impression. Actually, the resources here are only used to maintain the connection – it is something a few kilobytes per session. And that’s it! No more tangible resources are spent, especially with the benefits described below. You don’t have to worry about additional licenses either if you choose the “per server” licensing model. In this case, you can have as many connectors to the sql-server as you want from one machine, it will still take exactly one client license.

Finished solution

The solution consists of an activex object in the form of an algoevt.dll file and two stored spwaitforevent and spraiseevent procedures. Before using this file, you should place it on the server and register the activex-object using the system utility regsvr32.exe.

Further all work will be done through the stored procedures. In the ready solution, a bit more functionality is implemented than in the described concept. In addition to the fact of the event itself, it is possible to transfer arbitrary information in the form of a string of up to 250 characters. Each procedure has two parameters.

The first one is a unique identifier address, which was mentioned above, and the second parameter is additional transmitted information. spwaitforevent must be called from a client from a separate thread (the thread priority can be selected as the lowest). When the event is received, the procedure must be restarted. The request execution timeout must be set to infinity.

Advantages of the solution

1. Independence from the network protocol and network settings. There would be a connection to the sql server.
2. Asynchronous. The event initiator does not wait for the client to receive the event. You can initiate events in a trigger, the return is instantaneous.
3. There is no delay in delivery.
4. Delivery Guarantee.
5. No practical restrictions on the number of customers waiting for the event.
6. No resources are spent, except for minor resources to maintain connections.
7. 7. No “left” network traffic.
8. 8. No additional services and programs are required.
9. No additional settings of the sql-server are required.

Extended Events – SQL Server