Asynchronous Batch Processing

Currently we have a process that does synchronous batch processing that can
take a long time and the user cannot do anything while this is happening. W
e
want to encapsulate this process into an asynchronous operation. I imagine
that the processing itself will now reside in a DTS package (SSIS package
actually, since we will be using SQL Server 2005).
Scheduling a package to run asynchronously is no problem, but it would be
nice if it notified the calling app (which is UNIFACE btw - I don't know a
lot about it, so don't ask me) that the batch process is complete. We could
have the UNIFACE app poll to check the status, but I was wondering if Servic
e
Broker could help in this capacity or would that be overkill?
Ultimately, we will be replacing the UNIFACE app with our own suite, and
want the process to be as modular as possible, to facilitate a painless
conversion.
Thanks,
BrandonBrandon,
There are a number of reasons which I believe make Service Broker specially
qualified for this kind of jobs (i.e. asynchronous execution). Being
entirely contained in the database and is running inside the SQL Server
process allows Service Broker based apps to benefit from backup/restore (the
state of your jobs is backed up as part of the database), from
failover/clustering and from database mirroring (the job schedule just fails
over along with the database). Service Broker also gives you a mean to
communicate back from this jobs to the calling app (dialogs are always
bidirectional, the job can reply back on the same dialog that started the
job). Also you'll benefit from the poll free model of the Service Broker:
WAITFOR (RECEIVE ...) does not poll, it blocks until a message becomes
available.
Another nice feature of Service Broker is that it can give you persisted
timers (BEGIN CONVERSATION TIMER ...), stored in the database (again,
benefiting from all the backup/restore and availability benefits of
databases)
What are you afraid of when you say that Service Broker would be overkill?
HTH,
~ Remus
"Brandon Lilly" <avarice@.nospam_swbell.net> wrote in message
news:0F6D5B45-7A24-4539-BE76-E5B8675A3E83@.microsoft.com...
> Currently we have a process that does synchronous batch processing that
> can
> take a long time and the user cannot do anything while this is happening.
> We
> want to encapsulate this process into an asynchronous operation. I
> imagine
> that the processing itself will now reside in a DTS package (SSIS package
> actually, since we will be using SQL Server 2005).
> Scheduling a package to run asynchronously is no problem, but it would be
> nice if it notified the calling app (which is UNIFACE btw - I don't know a
> lot about it, so don't ask me) that the batch process is complete. We
> could
> have the UNIFACE app poll to check the status, but I was wondering if
> Service
> Broker could help in this capacity or would that be overkill?
> Ultimately, we will be replacing the UNIFACE app with our own suite, and
> want the process to be as modular as possible, to facilitate a painless
> conversion.
> Thanks,
> Brandon|||Mainly I am hesitant for two reasons... I am not that familiar with the
capabilities of UNIFACE (from what I understand it would have to poll instea
d
of using a blocking call like WAITFOR to determine whether job
completed/status of job) and also that I have only had minimal experience
with Service Broker (in the form of the several very simply demos out there)
.
Since the UNIFACE interface will eventually be replaced by a Delphi.NET app,
I can more easily see how that would work better in the long term.
Have you seen any Service Broker examples that communicate with a SSIS
package?
Thanks,
Brandon|||I'm gonna have to do some research about SSIS to see how it integrates with
SSB
What kind of asynchronous batch is gonna be processed? Are talking about
launching an external process, calling a stored proc, running a t-sql batch,
calling an CLR stored procedure?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
"Brandon Lilly" <avarice@.nospam_swbell.net> wrote in message
news:ACEE6EC9-ADE7-4FEE-B6C0-EDBFE6DDAFF6@.microsoft.com...
> Mainly I am hesitant for two reasons... I am not that familiar with the
> capabilities of UNIFACE (from what I understand it would have to poll
> instead
> of using a blocking call like WAITFOR to determine whether job
> completed/status of job) and also that I have only had minimal experience
> with Service Broker (in the form of the several very simply demos out
> there).
> Since the UNIFACE interface will eventually be replaced by a Delphi.NET
> app,
> I can more easily see how that would work better in the long term.
> Have you seen any Service Broker examples that communicate with a SSIS
> package?
> Thanks,
> Brandon|||Two Connect has a sample of Service Broker custom tasks for SSIS:
[url]http://www.twoconnect.com/pages/product_solutions/sqlserver.enhancements.ASPX[/url
]
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Brandon Lilly" <avarice@.nospam_swbell.net> wrote in message
news:ACEE6EC9-ADE7-4FEE-B6C0-EDBFE6DDAFF6@.microsoft.com...
> Mainly I am hesitant for two reasons... I am not that familiar with the
> capabilities of UNIFACE (from what I understand it would have to poll
> instead
> of using a blocking call like WAITFOR to determine whether job
> completed/status of job) and also that I have only had minimal experience
> with Service Broker (in the form of the several very simply demos out
> there).
> Since the UNIFACE interface will eventually be replaced by a Delphi.NET
> app,
> I can more easily see how that would work better in the long term.
> Have you seen any Service Broker examples that communicate with a SSIS
> package?
> Thanks,
> Brandon