4

I want to add a trigger whenever a row is inserted into a certain table. The trigger will fire off a CLR stored procedure which will do things other than database work, such as write a JSON string to a txt file and update a system log of a different type of database.

My hands are tied to using this technology. Originally we were going to write a windows service that will periodically scan the table and then run the program if new records were inserted.

My question is:

Can I host the DLL on a different server so it will get run there, or when I deploy a CLR Stored Procedure dll, will it always run within the SQL Server DB instance?

The reason I ask is that we don't want this code slowing down the database server, which it likely would by performing these other tasks within the code.

6

Can I host the DLL on a different server so it will get run there?

How would this work? If you want to run code on a remote server, you would still need code on the local server to be able to make that remote call.

Although, you could set up an instance of Express Edition to host the SQLCLR code (it would need to be a Stored Procedure(s) and/or Function(s) instead of a Trigger), then create a Linked Server to that Express instance, and then have a T-SQL Trigger on the local table execute the SQLCLR Stored Procedure(s) and/or Functions over the Linked Server. In this manner, the local code calling the remote code is pure T-SQL. You wouldn't even need to enable CLR Integration on the local instance.

The reason I ask is that we don't want this code slowing down the database server, which it likely would by performing these other tasks within the code.

Whatever work you inside of a Trigger, be it a T-SQL or SQLCLR Trigger, will hold up the completion of that DML statement. Using SQLCLR does not really change anything there.

What are the exact requirements of the Trigger with regards to:

do things other than database work, such as write a JSON string to a txt file and update a system log of a different type of database.

Do those steps / actions need to be bound by the transaction, such that if the INSERT operation fails, the txt file does not exist and the log entry is not there?

Finding a way to do this asynchronously would be ideal (as also mentioned by Jonathan). However, making a Web Service call removes the transactional aspect so you might get files and log entries for rows that do not actually exist.

Using an NT service would be the least impacting on the INSERT operation, and unless you are using WITH (NOLOCK) (which you shouldn't do), it will only see the results of successful INSERT operations. But, the service is disconnected and will have more latency than the Trigger model, and you will need to find a means of tracking which entries are "new" (the NT service will need to keep track of either a DATETIME or maybe could use a ROWVERSION value and compare to @@DBTS each time). If your table already has a DATETIME or DATETIME2 column being set upon INSERT, then you can leverage that existing column, else you will need to add a DATETIME / DATETIME2 / ROWVERSION column. OR, if you have a PK based upon either an IDENTITY or Sequence, then you can use that value instead of adding a column, and the NT service would just need to keep track of the most recently used ID. Where would you store that value? Text file? Registry? The "Cloud"? (just kidding about that last one ;-)

On the other hand, you could probably use Service Broker to match the two pieces together. When the Trigger fires, it can add a message to the Service Broker queue, including any necessary info from the INSERTED table. This allows it to be asynchronous. Then, on the other end, the queue reader can use a SQLCLR stored procedure as the "activation procedure" to take that info and take the appropriate actions (creating the JSON file, logging the entry on the other system, etc).

This model might not require Service Broker. You can set up your own queue table, have a T-SQL Trigger dump entries into it, and then create a SQL Server Agent job that reads from the queue table and calls the SQLCLR Stored Procedure(s) and/or Function(s) asynchronously (whether they are located on the local server or across a Linked Server), and then removes the records when it has processed them successfully.

1

Short answer, no.

Possible alternative for you are to make the trigger/clr fire off an asynchronous web service call that is hosted on another server. This would allow your process to be executed on another server and have minimal impact on the SQL Server.

1

To my knowledge, there's no practical way of running CLR code (in the database) on a different machine than the SQL Server itself.

Given your requirements, I would recommend an alternative similar to the Windows service solution. Polling a table regularly may not be very good from a performance perspective. Rather, could you use CLR code in your trigger to just append some output data to a .txt file, and let a Windows service on a different machine intermittently poll that text file? That way, the Windows service could reside on another server, run asynchronously, and use a network share. As a bonus, the Windows service won't place locks on the table that has the trigger.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.