Advice needed on processing data architecture in Sql Server 2005 Express Edition
Posted on 2007-07-25
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?