Application as service inside SQL possible?

I would like to run an application inside SQL CLR as a service so that once started it continues to run until explicitly shut down. The application would sit idle and only wake up in response to data coming in from data acquisition devices over serial ports (COM ports). Is this possible? I read somewhere that this would be supported, but I have not been able to find any information on how to do it. Any help or pointers would be greatly appreciated!Well, by default when a CLR Proc/Function has been called, it will stay up until the server goes down (hopefully never) or the appdomain is torn down. However, I do not think this is what you had in mind.
Not knowing how the data ends up in the database, I would suggest you take a look at SQL Server Service Broker, which allows you to have a "service program" monitoring a queue and when a message appears on the queue, the service program is automatically started (on a separate thread) and handles the message. The message can be anything, and the service program can be written in either T-SQL or .NET.
If you post some more about how the data is shipped to the database, maybe we can help some more.
Niels|||Hi Niels,

Thank you for your reply.

Just think of the database being the first entry point into the system for the data. there are some data acquisition boards that expose themselves as serial ports on the system and I would like to have all the code runing inside SQL sto make it as efficient as possible and to avoid any type of marshalling or serialization coming into or out of SQL, so I desire to have the code that opens the COM ports and waits for activity to run in SQL as well.

I do understand that the CLR will stay up once code has run in it, so what I want to do is have a service (application) running continually inside the SQL CLR without it being in a request/response mode where it is only executing in response to an external event, or message in a queue, but sits idle until a delegate notifies that there is activity in the COM ports.

Somehow, from what I have read, I don't think service broker is what I want but I will look further into it.

Any ideas?

Gus|||

Something like this would require your SQL CLR assembly to be registered as unsafe which is not recommended. Why not have an intermediate service that would monitor the activity on the ports and calls SQL CLR routines to do data processing as required.

Thanks,
-Vineet.

|||

Why would it need to be unsafe if it can be entirely implemented with the use of managed classes. As far as I know the SerialPort class is not marked unsafe. Am I missing something here? In any event, the reason to do it whithin SQL would be for performance and to avoid the overhead marshalling in and out of the SQL CLR.

Still hoping for an answer

|||

I guess you can use a CLR object as a service for SAFE assemblies.
(it would be great to get a comment from Microsoft developers, especially concerning multithreading problems)

The easiest way is to create a class that will represent a service and create a static readonly field that will store the instance of a service (SQL Server assemblies have a limitation: they may contain only readonly static fields).

public class MyService
{
private static readonly MyService _instance = new MyService();

public static Instance
{
get { return _instance; }
}
}

Using this code you should be aware that the service would be started on the first invoke and will remain in memory until SQL Server shut down (restart).

Make things a bit more complicated, we can make the service to shut down after a specified amount of time of user inactivity.
First of all, we need a "static" field that we can assign to null. As we cannot use not-readonly static fields, the workaround is to create a helper class to store that field.

public class MyService
{
private class Storage
{
private MyService _instance;

public MyService Instance
{
get { return _instance; }
set { _instance = value; }
}
}

private static readonly Storage _storage = new Storage();

public static Instance
{
get
{
if (_storage.Instance == null)
_storage.Instance = new MyService();

return _storage.Instance;
}
}
}

Now we have editable field that we can assign to null when service is not needed. I'm not sure if this workaround is what Microsoft was intended to allow developers to do (because otherwise I cannot understand the reason of limiting static fields to readonly)... but it works.
Then we have to add DateTime timestamp to our class to trace user activity (don't forget to refresh it in your CLR code whenever the user's action should be treated as "activity").

public class MyService
{
private DateTime _timestamp;

public MyService()
{
RefreshTimestamp();
}

public void RefreshTimestamp()
{
_timestamp = DateTime.Now;
}
....
}

And the last thing we have to do is to unload service after specified amount of time of user inactivity. We cannot use another thread to do it, but we have Service Broker , another workaround that allows us to create a new thread. We have to create a chain of SQL objects (Message Type/Contract/Service/Queue) and a stored procedure that will handle that queue. All it does is waits for xx seconds for a message in a loop, on timeout checks the _timestamp field, and if it differs from DateTime.Now more than xx seconds - unloads the service and exits.
And the last thing is to activate this "thread". We can do it in a MyService's constructor by sending an empty message to queue.

P.S. I didn't touched the multithreading problem (as I foresee) that may occur in a service when several users access same resources. This problem actually even bigger if we take into account that SAFE assemblies cannot use standard locking mechanism. I haven't investigated this problem deeply, but for now the only way I see is to create semaphore class ourselves using atomic operations from System.Threading.Interlocked and control access to all resources with corresponding semaphores.

|||I'm not sure it's possible, so I just ask

Is it possible to "inform" a Windows application or a Windows service when a message on the SQL Service Broker is coming up?

Could U post any example?

Thanks