• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

Advice needed on processing data architecture in Sql Server 2005 Express Edition

Hello all,
In our project we need to develop some kind of transformation process that will basically take data from some tables process it and save it into some other tables.
This needs to happen fast, as soon as the source tables are filled with data and it should be available 24/7. It should have the necessary error logging features to make sure we know about possible problems ocurring in production.
The database is Sql Server 2005 Express Edition, so I think we won't be able to use SSIS (Integration Services).

After some research the solution I can think of is to use .NET managed code stored procedures to do the processing and data moving, these stored procedures would be invoked by a windows service or maybe a Console Application inserted within Windows schedule, using a job on Sql Server to invoke the SP is another option.
The system will be deployed on the client machines so we want to make sure that they wouldn't disable the process easilly.

What we need isn't exactly a schedule but a way to trigger the processing when some table receives data. I'm not talking about an update trigger, because we want to run the code after the insertion occurs and in a different transaction and process.

Dear experts, what are your advices on this?
  • 3
  • 2
1 Solution
SanctusAuthor Commented:
Hi kvimal, thanks for the comment.

Unfortunately it seems Express Edition does not include Notification Services.
See http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Actually it doesn't include "SQL Agent Job Scheduling Service" neither so there goes the ideia for using a scheduled job on the database.
Hello Sanctus,

you can still use an update trigger ... just use it to update a table saying that work exists to be processed...

your scheduled external task can then just check that table  and perform the necessary transactions...
having an sqlcmd task scheduled every 5-15minutes during your online day maybe the way to go...


What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

SanctusAuthor Commented:
Hello Lowfat,
I processed your ideia and it makes sense as an easier way to schedule a job in Express Edition!
I would create an SP and invoke it with sqlcmd.

I like the ideia of using the trigger to update a simple table that indicates if there is work.
The next best thing would be to have a light way to check this table frequently, to make sure that the process starts almost imediately when needed. I wouldn't want the machine processor to rise when there is nothing to do, though...

Would it make sense to have a schedule task on windows every second? :P
SanctusAuthor Commented:
We decided to use a windows service that will check periodically if there is work to be done. We implement a CLR stored procedure that will take care of all the calculations and data moving.
good luck ... every second seems excessive ... i'd suggest starting at 5 -10 seconds at least... (the task can process all available work ... and only complete when all work is processed)...

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now