We have a process (a Windows application) that receives data over the
network. After a couple of validity checks, the data must be saved to a
table in a SQL Server 2005 database. This is fine when the database is up,
and reachable, but if it is unreachable for a short time we still need to be
able to save the data in the table.

Obviously, we can't actually save the data to the table while the database
is down, so some mechanism needs to be employed to cache the data until the
database comes back, and then save it when it next can.

This seems like something that might be commonly required, so I wonder if
there is a tried and tested technique that I could employ?

TIA

Charles

Why not write the data to log files *always*, and have the database pick up
and bulk insert the log files on a schedule. This way the write load on the
database will not be at the mercy of volume patterns, and you can control
how often the log files are loaded (including turning off the process when
you want to perform maintenance on the database).


On 5/23/09 1:48 PM, in article #(E-Mail Removed),
"Charles" <(E-Mail Removed)> wrote:

> We have a process (a Windows application) that receives data over the
> network. After a couple of validity checks, the data must be saved to a
> table in a SQL Server 2005 database. This is fine when the database is up,
> and reachable, but if it is unreachable for a short time we still need to be
> able to save the data in the table.
>
> Obviously, we can't actually save the data to the table while the database
> is down, so some mechanism needs to be employed to cache the data until the
> database comes back, and then save it when it next can.
>
> This seems like something that might be commonly required, so I wonder if
> there is a tried and tested technique that I could employ?
>
> TIA
>
> Charles
>
>

Why not write the data to log files *always*, and have the database pick up
and bulk insert the log files on a schedule. This way the write load on the
database will not be at the mercy of volume patterns, and you can control
how often the log files are loaded (including turning off the process when
you want to perform maintenance on the database).


On 5/23/09 1:48 PM, in article #(E-Mail Removed),
"Charles" <(E-Mail Removed)> wrote:

> We have a process (a Windows application) that receives data over the
> network. After a couple of validity checks, the data must be saved to a
> table in a SQL Server 2005 database. This is fine when the database is up,
> and reachable, but if it is unreachable for a short time we still need to be
> able to save the data in the table.
>
> Obviously, we can't actually save the data to the table while the database
> is down, so some mechanism needs to be employed to cache the data until the
> database comes back, and then save it when it next can.
>
> This seems like something that might be commonly required, so I wonder if
> there is a tried and tested technique that I could employ?
>
> TIA
>
> Charles
>
>

On Sat, 23 May 2009 18:48:32 +0100, "Charles" <(E-Mail Removed)>
wrote:

>We have a process (a Windows application) that receives data over the
>network. After a couple of validity checks, the data must be saved to a
>table in a SQL Server 2005 database. This is fine when the database is up,
>and reachable, but if it is unreachable for a short time we still need to be
>able to save the data in the table.
>
>Obviously, we can't actually save the data to the table while the database
>is down, so some mechanism needs to be employed to cache the data until the
>database comes back, and then save it when it next can.
>
>This seems like something that might be commonly required, so I wonder if
>there is a tried and tested technique that I could employ?
>

I stash the data in a thread-safe queue and start a worker thread to
process it. It works well but making sure that the queue gets
properly saved somewhere if the main process shuts down and then gets
properly restored when it starts up again is a real pain. I would
love to see a better solution.

On Sat, 23 May 2009 18:48:32 +0100, "Charles" <(E-Mail Removed)>
wrote:

>We have a process (a Windows application) that receives data over the
>network. After a couple of validity checks, the data must be saved to a
>table in a SQL Server 2005 database. This is fine when the database is up,
>and reachable, but if it is unreachable for a short time we still need to be
>able to save the data in the table.
>
>Obviously, we can't actually save the data to the table while the database
>is down, so some mechanism needs to be employed to cache the data until the
>database comes back, and then save it when it next can.
>
>This seems like something that might be commonly required, so I wonder if
>there is a tried and tested technique that I could employ?
>

I stash the data in a thread-safe queue and start a worker thread to
process it. It works well but making sure that the queue gets
properly saved somewhere if the main process shuts down and then gets
properly restored when it starts up again is a real pain. I would
love to see a better solution.

Hi Aaron

Thanks for the quick reply. The data need to be pushed to the database
because it is a [near] realtime system. When the database is up, the data
need to be saved in the database immediately. The times when it is not
possible to reach the database are very rare, but we need to design for that
eventuality so that we don't lose data on those rare occasions.

> ... including turning off the process when
> you want to perform maintenance on the database

This would be a very attractive feature when we have the solution in place,
but, as I say, when the database is up the data need to be saved as it
arrives, and not on batch.

Charles


"Aaron Bertrand [SQL Server MVP]" <(E-Mail Removed)> wrote in message
news:C63DB228.2C2C7%(E-Mail Removed)...
> Why not write the data to log files *always*, and have the database pick
> up
> and bulk insert the log files on a schedule. This way the write load on
> the
> database will not be at the mercy of volume patterns, and you can control
> how often the log files are loaded (including turning off the process when
> you want to perform maintenance on the database).
>
>
> On 5/23/09 1:48 PM, in article #(E-Mail Removed),
> "Charles" <(E-Mail Removed)> wrote:
>
>> We have a process (a Windows application) that receives data over the
>> network. After a couple of validity checks, the data must be saved to a
>> table in a SQL Server 2005 database. This is fine when the database is
>> up,
>> and reachable, but if it is unreachable for a short time we still need to
>> be
>> able to save the data in the table.
>>
>> Obviously, we can't actually save the data to the table while the
>> database
>> is down, so some mechanism needs to be employed to cache the data until
>> the
>> database comes back, and then save it when it next can.
>>
>> This seems like something that might be commonly required, so I wonder if
>> there is a tried and tested technique that I could employ?
>>
>> TIA
>>
>> Charles
>>
>>
>

Hi Aaron

Thanks for the quick reply. The data need to be pushed to the database
because it is a [near] realtime system. When the database is up, the data
need to be saved in the database immediately. The times when it is not
possible to reach the database are very rare, but we need to design for that
eventuality so that we don't lose data on those rare occasions.

> ... including turning off the process when
> you want to perform maintenance on the database

This would be a very attractive feature when we have the solution in place,
but, as I say, when the database is up the data need to be saved as it
arrives, and not on batch.

Charles


"Aaron Bertrand [SQL Server MVP]" <(E-Mail Removed)> wrote in message
news:C63DB228.2C2C7%(E-Mail Removed)...
> Why not write the data to log files *always*, and have the database pick
> up
> and bulk insert the log files on a schedule. This way the write load on
> the
> database will not be at the mercy of volume patterns, and you can control
> how often the log files are loaded (including turning off the process when
> you want to perform maintenance on the database).
>
>
> On 5/23/09 1:48 PM, in article #(E-Mail Removed),
> "Charles" <(E-Mail Removed)> wrote:
>
>> We have a process (a Windows application) that receives data over the
>> network. After a couple of validity checks, the data must be saved to a
>> table in a SQL Server 2005 database. This is fine when the database is
>> up,
>> and reachable, but if it is unreachable for a short time we still need to
>> be
>> able to save the data in the table.
>>
>> Obviously, we can't actually save the data to the table while the
>> database
>> is down, so some mechanism needs to be employed to cache the data until
>> the
>> database comes back, and then save it when it next can.
>>
>> This seems like something that might be commonly required, so I wonder if
>> there is a tried and tested technique that I could employ?
>>
>> TIA
>>
>> Charles
>>
>>
>

Do you persist the queue in any way? I had thought of some kind of managed
FIFO, but if it would have to be saved locally to disk to make it safe.

Charles


"r norman" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Sat, 23 May 2009 18:48:32 +0100, "Charles" <(E-Mail Removed)>
> wrote:
>
>>We have a process (a Windows application) that receives data over the
>>network. After a couple of validity checks, the data must be saved to a
>>table in a SQL Server 2005 database. This is fine when the database is up,
>>and reachable, but if it is unreachable for a short time we still need to
>>be
>>able to save the data in the table.
>>
>>Obviously, we can't actually save the data to the table while the database
>>is down, so some mechanism needs to be employed to cache the data until
>>the
>>database comes back, and then save it when it next can.
>>
>>This seems like something that might be commonly required, so I wonder if
>>there is a tried and tested technique that I could employ?
>>
>
> I stash the data in a thread-safe queue and start a worker thread to
> process it. It works well but making sure that the queue gets
> properly saved somewhere if the main process shuts down and then gets
> properly restored when it starts up again is a real pain. I would
> love to see a better solution.
>
>

Do you persist the queue in any way? I had thought of some kind of managed
FIFO, but if it would have to be saved locally to disk to make it safe.

Charles


"r norman" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Sat, 23 May 2009 18:48:32 +0100, "Charles" <(E-Mail Removed)>
> wrote:
>
>>We have a process (a Windows application) that receives data over the
>>network. After a couple of validity checks, the data must be saved to a
>>table in a SQL Server 2005 database. This is fine when the database is up,
>>and reachable, but if it is unreachable for a short time we still need to
>>be
>>able to save the data in the table.
>>
>>Obviously, we can't actually save the data to the table while the database
>>is down, so some mechanism needs to be employed to cache the data until
>>the
>>database comes back, and then save it when it next can.
>>
>>This seems like something that might be commonly required, so I wonder if
>>there is a tried and tested technique that I could employ?
>>
>
> I stash the data in a thread-safe queue and start a worker thread to
> process it. It works well but making sure that the queue gets
> properly saved somewhere if the main process shuts down and then gets
> properly restored when it starts up again is a real pain. I would
> love to see a better solution.
>
>

Even if you load the files every 30 seconds or every minute?

Well you could certainly have a flag that the app looks at that says "write
to DB" or "write to file"... When the flag is set to the latter, it writes
to the file instead. When the database comes back up, you change the flag
back, and load any files that were written in the meantime.



On 5/23/09 2:14 PM, in article (E-Mail Removed),
"Charles" <(E-Mail Removed)> wrote:

> Hi Aaron
>
> Thanks for the quick reply. The data need to be pushed to the database
> because it is a [near] realtime system. When the database is up, the data
> need to be saved in the database immediately. The times when it is not
> possible to reach the database are very rare, but we need to design for that
> eventuality so that we don't lose data on those rare occasions.
>
>> ... including turning off the process when
>> you want to perform maintenance on the database
>
> This would be a very attractive feature when we have the solution in place,
> but, as I say, when the database is up the data need to be saved as it
> arrives, and not on batch.
>
> Charles