We have an application that needs to store data in a SQL Server 2005
database in real-time. Whilst the instance is up this is not a problem with
the volumes of data we have.

However, if the instance is down for even a short time we lose data and it
cannot be recovered, retrieved or otherwise reconstructed.

Is there an accepted technique for dealing with this situation? I have read
a bit about MSMQ but I don't know if this is the way to go.

What we are looking for is a method for our application to store data -
preferably without having to know whether the instance is up or down - such
that the data is stored in real-time whilst the instance is up, and is
stored somewhere else when the server is down but is automatically loaded
into the database when it comes back up.

A tall order perhaps, but any ideas / pointers welcome.

TIA

Charles

How about High Availibility options? For example Database Mirroring with
Automatic Failover or (if you have budget for investment) Failover
Clustering?

Because your need sounds like a High Availibility to me. You always need a
SQL Server instance up. So, a hot standby server would be good for you I
believe.

--
Ekrem Önsoy



"Charles Law" <blank@nowhere.com> wrote in message
news:OT0mJ0qtIHA.4772@TK2MSFTNGP03.phx.gbl...
> We have an application that needs to store data in a SQL Server 2005
> database in real-time. Whilst the instance is up this is not a problem
> with the volumes of data we have.
>
> However, if the instance is down for even a short time we lose data and it
> cannot be recovered, retrieved or otherwise reconstructed.
>
> Is there an accepted technique for dealing with this situation? I have
> read a bit about MSMQ but I don't know if this is the way to go.
>
> What we are looking for is a method for our application to store data -
> preferably without having to know whether the instance is up or down -
> such that the data is stored in real-time whilst the instance is up, and
> is stored somewhere else when the server is down but is automatically
> loaded into the database when it comes back up.
>
> A tall order perhaps, but any ideas / pointers welcome.
>
> TIA
>
> Charles
>
>

Hi Ekrem

Thanks for the quick reply.

The environment is a SQL Server cluster on external storage in a Windows
cluster, so we already have HA with failover.

The problem is not so much what we do if a server fails, but what the
application does if we need to stop the SQL Server instance for some reason.
We want the application to be able to continue storing data and for it to
find its way automatically to the database when we restart the instance.

Charles


"Ekrem Önsoy" <ekrem@compecta.com> wrote in message
news:6E94FEAD-10F3-4933-B02E-5AA949FB0BC3@microsoft.com...
> How about High Availibility options? For example Database Mirroring with
> Automatic Failover or (if you have budget for investment) Failover
> Clustering?
>
> Because your need sounds like a High Availibility to me. You always need a
> SQL Server instance up. So, a hot standby server would be good for you I
> believe.
>
> --
> Ekrem Önsoy
>
>
>
> "Charles Law" <blank@nowhere.com> wrote in message
> news:OT0mJ0qtIHA.4772@TK2MSFTNGP03.phx.gbl...
>> We have an application that needs to store data in a SQL Server 2005
>> database in real-time. Whilst the instance is up this is not a problem
>> with the volumes of data we have.
>>
>> However, if the instance is down for even a short time we lose data and
>> it cannot be recovered, retrieved or otherwise reconstructed.
>>
>> Is there an accepted technique for dealing with this situation? I have
>> read a bit about MSMQ but I don't know if this is the way to go.
>>
>> What we are looking for is a method for our application to store data -
>> preferably without having to know whether the instance is up or down -
>> such that the data is stored in real-time whilst the instance is up, and
>> is stored somewhere else when the server is down but is automatically
>> loaded into the database when it comes back up.
>>
>> A tall order perhaps, but any ideas / pointers welcome.
>>
>> TIA
>>
>> Charles
>>
>>
>

MSMQ isn't specifically designed to be a real time product but it depends on
what you need.
I am assuming you only need real-time capturing/storing of data and not
real-time reading/processing of that data too.

If you were using MSMQ, the data would be captured and stored locally on
disk ready to be sent through the outgoing queue to the back-end server.
You will need an application on the back-end reading the incoming messages
and writing them to SQL.

If the destination is down then MSMQ just waits until it is back.
The issue here is how much data can you buffer while waiting for the
destination before MSMQ chokes.

http://blogs.msdn.com/johnbreakwell/...very-time.aspx
http://blogs.msdn.com/johnbreakwell/...-run-away.aspx

Cheers
John Breakwell (MSFT)

"Charles Law" <blank@nowhere.com> wrote in message
news:eSD3APrtIHA.576@TK2MSFTNGP05.phx.gbl...
> Hi Ekrem
>
> Thanks for the quick reply.
>
> The environment is a SQL Server cluster on external storage in a Windows
> cluster, so we already have HA with failover.
>
> The problem is not so much what we do if a server fails, but what the
> application does if we need to stop the SQL Server instance for some
> reason. We want the application to be able to continue storing data and
> for it to find its way automatically to the database when we restart the
> instance.
>
> Charles
>
>
> "Ekrem Önsoy" <ekrem@compecta.com> wrote in message
> news:6E94FEAD-10F3-4933-B02E-5AA949FB0BC3@microsoft.com...
>> How about High Availibility options? For example Database Mirroring with
>> Automatic Failover or (if you have budget for investment) Failover
>> Clustering?
>>
>> Because your need sounds like a High Availibility to me. You always need
>> a SQL Server instance up. So, a hot standby server would be good for you
>> I believe.
>>
>> --
>> Ekrem Önsoy
>>
>>
>>
>> "Charles Law" <blank@nowhere.com> wrote in message
>> news:OT0mJ0qtIHA.4772@TK2MSFTNGP03.phx.gbl...
>>> We have an application that needs to store data in a SQL Server 2005
>>> database in real-time. Whilst the instance is up this is not a problem
>>> with the volumes of data we have.
>>>
>>> However, if the instance is down for even a short time we lose data and
>>> it cannot be recovered, retrieved or otherwise reconstructed.
>>>
>>> Is there an accepted technique for dealing with this situation? I have
>>> read a bit about MSMQ but I don't know if this is the way to go.
>>>
>>> What we are looking for is a method for our application to store data -
>>> preferably without having to know whether the instance is up or down -
>>> such that the data is stored in real-time whilst the instance is up, and
>>> is stored somewhere else when the server is down but is automatically
>>> loaded into the database when it comes back up.
>>>
>>> A tall order perhaps, but any ideas / pointers welcome.
>>>
>>> TIA
>>>
>>> Charles
>>>
>>>
>>
>
>

Hi John

Thanks for the reply. That explains some things I hadn't fully appreciated
about MSMQ. You are right that it is only data capture/storing we need. It
also sounds like we could be better off inventing something bespoke as MSMQ
doesn't explicitly address the SQL Server part of our problem.

Cheers.

Charles


"John Breakwell (MSFT)" <GreenGoblin@msn.com> wrote in message
news:0B343590-81D1-4050-B7CF-3A7AB6BFF288@microsoft.com...
> MSMQ isn't specifically designed to be a real time product but it depends
> on what you need.
> I am assuming you only need real-time capturing/storing of data and not
> real-time reading/processing of that data too.
>
> If you were using MSMQ, the data would be captured and stored locally on
> disk ready to be sent through the outgoing queue to the back-end server.
> You will need an application on the back-end reading the incoming messages
> and writing them to SQL.
>
> If the destination is down then MSMQ just waits until it is back.
> The issue here is how much data can you buffer while waiting for the
> destination before MSMQ chokes.
>
> http://blogs.msdn.com/johnbreakwell/...very-time.aspx
> http://blogs.msdn.com/johnbreakwell/...-run-away.aspx
>
> Cheers
> John Breakwell (MSFT)
>
> "Charles Law" <blank@nowhere.com> wrote in message
> news:eSD3APrtIHA.576@TK2MSFTNGP05.phx.gbl...
>> Hi Ekrem
>>
>> Thanks for the quick reply.
>>
>> The environment is a SQL Server cluster on external storage in a Windows
>> cluster, so we already have HA with failover.
>>
>> The problem is not so much what we do if a server fails, but what the
>> application does if we need to stop the SQL Server instance for some
>> reason. We want the application to be able to continue storing data and
>> for it to find its way automatically to the database when we restart the
>> instance.
>>
>> Charles
>>
>>
>> "Ekrem Önsoy" <ekrem@compecta.com> wrote in message
>> news:6E94FEAD-10F3-4933-B02E-5AA949FB0BC3@microsoft.com...
>>> How about High Availibility options? For example Database Mirroring with
>>> Automatic Failover or (if you have budget for investment) Failover
>>> Clustering?
>>>
>>> Because your need sounds like a High Availibility to me. You always need
>>> a SQL Server instance up. So, a hot standby server would be good for you
>>> I believe.
>>>
>>> --
>>> Ekrem Önsoy
>>>
>>>
>>>
>>> "Charles Law" <blank@nowhere.com> wrote in message
>>> news:OT0mJ0qtIHA.4772@TK2MSFTNGP03.phx.gbl...
>>>> We have an application that needs to store data in a SQL Server 2005
>>>> database in real-time. Whilst the instance is up this is not a problem
>>>> with the volumes of data we have.
>>>>
>>>> However, if the instance is down for even a short time we lose data and
>>>> it cannot be recovered, retrieved or otherwise reconstructed.
>>>>
>>>> Is there an accepted technique for dealing with this situation? I have
>>>> read a bit about MSMQ but I don't know if this is the way to go.
>>>>
>>>> What we are looking for is a method for our application to store data -
>>>> preferably without having to know whether the instance is up or down -
>>>> such that the data is stored in real-time whilst the instance is up,
>>>> and is stored somewhere else when the server is down but is
>>>> automatically loaded into the database when it comes back up.
>>>>
>>>> A tall order perhaps, but any ideas / pointers welcome.
>>>>
>>>> TIA
>>>>
>>>> Charles
>>>>
>>>>
>>>
>>
>>
>

Charles,

MSMQ can't "intercept" SQL Server bound data, so you'd have to code your app
to write to MSMQ, and then take the data and store to SQL Server. But what if
the MSMQ server is down? Same issue occurs.

You should amend your application to write somewhere else in the event of SQL
being down - this could be local flat file(s), MSMQ, or potentially the
easiest, a local SQL Server Express edition instance. Your app then needs to
check whether any locally cached info needs to be written away.

In similar cirumstances in the past I've seen flat-files and local SQL Express
be used for this. Not seen MSMQ used, but no reason it shouldn't be.


> We have an application that needs to store data in a SQL Server 2005
> database in real-time. Whilst the instance is up this is not a problem with
> the volumes of data we have.
>
> However, if the instance is down for even a short time we lose data and it
> cannot be recovered, retrieved or otherwise reconstructed.
>
> Is there an accepted technique for dealing with this situation? I have read
> a bit about MSMQ but I don't know if this is the way to go.
>
> What we are looking for is a method for our application to store data -
> preferably without having to know whether the instance is up or down - such
> that the data is stored in real-time whilst the instance is up, and is
> stored somewhere else when the server is down but is automatically loaded
> into the database when it comes back up.
>
> A tall order perhaps, but any ideas / pointers welcome.

Neil Pike. Protech Computing Ltd
http://www.linkedin.com/in/neilpike

Hi Neil

From what you say it looks like there is no better solution than the
home-rolled one. I can see that I will have to look closely at what we
actually need and what we can accept in terms of loss of service, and devise
a bespoke solution.

Thanks.

Charles


"Neil Pike" <neilpike@compuserve.com> wrote in message
news:VA.000065a1.2e5a207e@compuserve.com...
> Charles,
>
> MSMQ can't "intercept" SQL Server bound data, so you'd have to code your
> app
> to write to MSMQ, and then take the data and store to SQL Server. But
> what if
> the MSMQ server is down? Same issue occurs.
>
> You should amend your application to write somewhere else in the event of
> SQL
> being down - this could be local flat file(s), MSMQ, or potentially the
> easiest, a local SQL Server Express edition instance. Your app then needs
> to
> check whether any locally cached info needs to be written away.
>
> In similar cirumstances in the past I've seen flat-files and local SQL
> Express
> be used for this. Not seen MSMQ used, but no reason it shouldn't be.
>
>
>> We have an application that needs to store data in a SQL Server 2005
>> database in real-time. Whilst the instance is up this is not a problem
>> with
>> the volumes of data we have.
>>
>> However, if the instance is down for even a short time we lose data and
>> it
>> cannot be recovered, retrieved or otherwise reconstructed.
>>
>> Is there an accepted technique for dealing with this situation? I have
>> read
>> a bit about MSMQ but I don't know if this is the way to go.
>>
>> What we are looking for is a method for our application to store data -
>> preferably without having to know whether the instance is up or down -
>> such
>> that the data is stored in real-time whilst the instance is up, and is
>> stored somewhere else when the server is down but is automatically loaded
>> into the database when it comes back up.
>>
>> A tall order perhaps, but any ideas / pointers welcome.
>
> Neil Pike. Protech Computing Ltd
> http://www.linkedin.com/in/neilpike
>
>

Hi Charles

It depends. You could write your own app to do what you need.
MSMQ does, though, supply a chunk of the plumbing that you will need to
implement so less coding needed, less testing, lower costs.

Cheers
John Breakwell (MSFT)


"Charles Law" <blank@nowhere.com> wrote in message
news:urSfGg2tIHA.1328@TK2MSFTNGP03.phx.gbl...
> Hi John
>
> Thanks for the reply. That explains some things I hadn't fully appreciated
> about MSMQ. You are right that it is only data capture/storing we need. It
> also sounds like we could be better off inventing something bespoke as
> MSMQ doesn't explicitly address the SQL Server part of our problem.
>
> Cheers.
>
> Charles