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

Posted on 2007-07-25
Last Modified: 2013-11-30
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?
Question by:Sanctus
    LVL 5

    Expert Comment

    LVL 3

    Author Comment

    Hi kvimal, thanks for the comment.

    Unfortunately it seems Express Edition does not include Notification Services.

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

    Accepted Solution

    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...


    LVL 3

    Author Comment

    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
    LVL 3

    Author Comment

    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.
    LVL 50

    Expert Comment

    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)...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now