5

I'm needing to add serial numbers to most of the entities in my application because I'm going to be running a Lucene search index side-by-side.

Rather than having to run an ongoing polling process, or manually run my indexer by my application I'm thinking of the following:

  • Add a Created column with a default value of GETUTCDATE().
  • Add a Modified column with a default value of GETUTCDATE().
  • Add an ON UPDATE trigger to the table that updates Modified to GETUTCDATE() (can this happen as the UPDATE is executed? i.e. it adds SET [Modified] = GETUTCDATE() to the SQL query instead of updating it individually afterwards?)
  • The ON UPDATE trigger will call my Lucene indexer to update its index (this would have to be an xp_cmdshell call presumably, but is there a way of sending a message to the process instead of starting a new one? I heard I could use Named Pipes, but how do you use named pipes from within a Sproc or trigger? (searching for "SQL Server named pipes" gives me irrelevant results, of course).

Does this sound okay, and how can I solve the small sub-problems?

Dai
  • 141,631
  • 28
  • 261
  • 374
  • I cannot imagine having a trigger call to `xp_cmdshell` will be especially performant. If you're using SQL Server, maybe a FT index would be more appropriate? Also, how will you deal with multiple records being updated in the same statement? – Jeremy Holovacs Apr 10 '13 at 19:55
  • Why can't you call Lucene indexer in your client app as part of the client side process that calls the Stored proc that does the update? – Charles Bretana Apr 10 '13 at 20:02
  • agreed with @CharlesBretana; I wrote a blog entry how to do this with NHibernate and it works pretty well if you're interested... http://aclassicgeek.blogspot.com/2013/03/full-text-searching-in-nhibernate.html – Jeremy Holovacs Apr 10 '13 at 20:05
  • @CharlesBretana there are multiple applications that touch the database, some of them working outside the firewall that prevents that them from communicating with the Lucene indexer directly - which is why I want the SQL Server to do that instead. Plus it also means that the indexer will always be informed when new data is available. – Dai Apr 10 '13 at 22:06

1 Answers1

1

As I understood, you have to introduce two columns to your existing tables and have them processed (at east one of them) in 'runtime' and used by an external component.

Your first three points are nothing unusual. There are two types of triggers in SQL Server according to time when trigger get processed: INSTEAD OF trigger (actually processed before insert happens) and AFTER trigger. However, inside INSTEAD OF trigger you have to provide logic what to really insert data into the table, along with other custom processing you require. I usually avoid that if not really necessary.

Now about your fourth point - it's tricky and there are several approaches to solve this in SQL Server, but all of them are at least a bit ugly. Basically you have to either execute external process or send message to it. I really don't have any experience with Lucene indexer but I guess one of these methods (execute or send message) would apply.

So, you can do one of the the following to directly or indirectly access external component, meaning to access Lucene indexer directly or via some proxy module:

  1. Implement unsafe CLR trigger; basically you execute .NET code inside the trigger and thus get access to the whole (be careful with that - not entirely true) .NET framework
  2. Implement unsafe CLR procedure; only difference to CLR trigger is that you wouldn't call it imediatelly after INSERT, but you will do fine with some database job that runs periodically
  3. Use xp_cmdshell; you already know about this one, but you can combine this aproach with job-wrapping technique in last point
  4. Call web service; this technique is usually marked as experimental AND you have to implement the service by yourself (if Lucene indexer doesn't install some web service on its own)
  5. There surely are other methods I can't think of right now...

I would personally go with third point (job+xp_cmdshell) because of the simplicity, but that's just because I lack any knowledge of how does the Lucene indexer work.

EDIT (another option):

Use Query Notifications; SQL Server Service Broker allows an external application to connect and monitor interesting changes. You even have several options how to do that (basically synchronous or asynchronous), only precondition is that your Service Borker is up, running and available to your application. This is more sophisticated method to inform external component that something has changed.

OzrenTkalcecKrznaric
  • 5,535
  • 4
  • 34
  • 57
  • 1
    I recently found out about the SQL Server Broker service and Query Notifications that external processes can subscribe to. For now I'll shelve the Lucene-based search feature as my `SELECT WHERE LIKE` approach is working acceptably for now. But thank you for the insight! – Dai Jun 11 '13 at 23:41
  • 1
    I see, this is one other possibility. However, you have to know that troubleshooting sql server broker is an advanced topic, so you have to do your homework very carefully or end up with a stuck production. And you're welcome. Feel free to click appropriate thank-you-related buttons at any time. – OzrenTkalcecKrznaric Jun 12 '13 at 04:37