Recommended Stretagy for Bi-Directional SQL DB Synchronization

Hi,

Our company is an independent Voice applications solution provider with
number clients using our suite. We have a CT application suite which is
running with Application Server and SQL Server 7 / 2000 as DB Engines
at the back end.

The SQL server has two databases configured:

Logging Database - Massive updates every second, the data grows
rapidly,

Configuration Database - Generally small-sized and updated
occasionally.

Now we want to have the reslience implemented on the server. We have to
synchronize the two databases 'real-timely' and in 'efficient'
manner, so that if Primary server or its Databases gets unavailable,
the users are seamlessly switched over to the Secondry server that will
have its own set of data updated and well synchronized.

Typically, it can be explained as follows:

1. We will have 2 database servers A - Primary (acting as publisher)
and B - Secondary (acting as subscriber). Our application will be
initially connected to A.

2. When A becomes unavailable (for whatever reason), the application
will fail-over to B.

3. All the users will be switched to server B and the updates are being
done accordingly without being replicated on Server A temporarily.

4. When A is back on-line, A needs to be brought up-to-date with B
automatically (In other words, I shouldn't have to manually export all
the data from B to A ).

Our requirements are:

- The system should support Bi-directional Synchronization
between both the servers for their set of databases (the logging and
configuration).

- There will be constant and heavy activity in Logging
Database, thus if one server gets down the data should be logged and
maintained as it is on second server and on fail-back no data-loss
should occur with minimum latency time.

- There could be a scenario when a server fails-over for a
week's time, there will be constant logging each second! Once it
fails-back the system should rapidly synchronize the data without
noticeable delay among the two server database sets.

- The system should also work fine if certain amount of
records are purged over a time period.

Our concern is, observing the above scenario, how any of your SQL
server replication strategy can help us achieve the requirements.


Thanks
John

re: Recommended Stretagy for Bi-Directional SQL DB Synchronization

Well you certainly have a challenge. It's the "for whatever reason" clause
that has me concerned. You can only mitigate foreseeable issues. I
recommend you start by determining a list of events that would cause outages
or failures and work the list. Clustering with a good SAN and multiple
network paths will handle most of the situations but thre are a few that
clustering is not a solution for like Site DR, SAN issues, and data
manipulation errors.

<omeropee@gmail.com> wrote in message
news:1108130396.568266.325900@c13g2000cwb.googlegr oups.com...[color=blue]
> Hi,
>
> Our company is an independent Voice applications solution provider with
> number clients using our suite. We have a CT application suite which is
> running with Application Server and SQL Server 7 / 2000 as DB Engines
> at the back end.
>
> The SQL server has two databases configured:
>
> Logging Database - Massive updates every second, the data grows
> rapidly,
>
> Configuration Database - Generally small-sized and updated
> occasionally.
>
> Now we want to have the reslience implemented on the server. We have to
> synchronize the two databases 'real-timely' and in 'efficient'
> manner, so that if Primary server or its Databases gets unavailable,
> the users are seamlessly switched over to the Secondry server that will
> have its own set of data updated and well synchronized.
>
> Typically, it can be explained as follows:
>
> 1. We will have 2 database servers A - Primary (acting as publisher)
> and B - Secondary (acting as subscriber). Our application will be
> initially connected to A.
>
> 2. When A becomes unavailable (for whatever reason), the application
> will fail-over to B.
>
> 3. All the users will be switched to server B and the updates are being
> done accordingly without being replicated on Server A temporarily.
>
> 4. When A is back on-line, A needs to be brought up-to-date with B
> automatically (In other words, I shouldn't have to manually export all
> the data from B to A ).
>
> Our requirements are:
>
> - The system should support Bi-directional Synchronization
> between both the servers for their set of databases (the logging and
> configuration).
>
> - There will be constant and heavy activity in Logging
> Database, thus if one server gets down the data should be logged and
> maintained as it is on second server and on fail-back no data-loss
> should occur with minimum latency time.
>
> - There could be a scenario when a server fails-over for a
> week's time, there will be constant logging each second! Once it
> fails-back the system should rapidly synchronize the data without
> noticeable delay among the two server database sets.
>
> - The system should also work fine if certain amount of
> records are purged over a time period.
>
> Our concern is, observing the above scenario, how any of your SQL
> server replication strategy can help us achieve the requirements.
>
>
> Thanks
> John
>[/color]

re: Recommended Stretagy for Bi-Directional SQL DB Synchronization

On 2/12/05 1:34 PM, in article
v_rPd.6076$ng6.1918@newssvr17.news.prodigy.com, "Ray" <someone@nowhere.com>
wrote:
[color=blue]
> Well you certainly have a challenge. It's the "for whatever reason" clause
> that has me concerned. You can only mitigate foreseeable issues. I
> recommend you start by determining a list of events that would cause outages
> or failures and work the list. Clustering with a good SAN and multiple
> network paths will handle most of the situations but thre are a few that
> clustering is not a solution for like Site DR, SAN issues, and data
> manipulation errors.
>
> <omeropee@gmail.com> wrote in message
> news:1108130396.568266.325900@c13g2000cwb.googlegr oups.com...[color=green]
>> Hi,
>>
>> Our company is an independent Voice applications solution provider with
>> number clients using our suite. We have a CT application suite which is
>> running with Application Server and SQL Server 7 / 2000 as DB Engines
>> at the back end.
>>
>> The SQL server has two databases configured:
>>
>> Logging Database - Massive updates every second, the data grows
>> rapidly,
>>
>> Configuration Database - Generally small-sized and updated
>> occasionally.
>>
>> Now we want to have the reslience implemented on the server. We have to
>> synchronize the two databases 'real-timely' and in 'efficient'
>> manner, so that if Primary server or its Databases gets unavailable,
>> the users are seamlessly switched over to the Secondry server that will
>> have its own set of data updated and well synchronized.
>>
>> Typically, it can be explained as follows:
>>
>> 1. We will have 2 database servers A - Primary (acting as publisher)
>> and B - Secondary (acting as subscriber). Our application will be
>> initially connected to A.
>>
>> 2. When A becomes unavailable (for whatever reason), the application
>> will fail-over to B.
>>
>> 3. All the users will be switched to server B and the updates are being
>> done accordingly without being replicated on Server A temporarily.
>>
>> 4. When A is back on-line, A needs to be brought up-to-date with B
>> automatically (In other words, I shouldn't have to manually export all
>> the data from B to A ).
>>
>> Our requirements are:
>>
>> - The system should support Bi-directional Synchronization
>> between both the servers for their set of databases (the logging and
>> configuration).
>>
>> - There will be constant and heavy activity in Logging
>> Database, thus if one server gets down the data should be logged and
>> maintained as it is on second server and on fail-back no data-loss
>> should occur with minimum latency time.
>>
>> - There could be a scenario when a server fails-over for a
>> week's time, there will be constant logging each second! Once it
>> fails-back the system should rapidly synchronize the data without
>> noticeable delay among the two server database sets.
>>
>> - The system should also work fine if certain amount of
>> records are purged over a time period.
>>
>> Our concern is, observing the above scenario, how any of your SQL
>> server replication strategy can help us achieve the requirements.
>>
>>
>> Thanks
>> John
>>[/color]
>
>[/color]

Active/Active Cluster.

-Greg

re: Recommended Stretagy for Bi-Directional SQL DB Synchronization

I usually recommend active/passive. Active/active only buys you the ability
to run SQL on both servers and if one fails all instances of SQL Server will
switch to running on the remaining servers. But if you have a need to run
more databases it's fine.



"Gregory Dean" <gdean@datapex.com> wrote in message
news:BE33C842.4E3D%gdean@datapex.com...[color=blue]
> On 2/12/05 1:34 PM, in article
> v_rPd.6076$ng6.1918@newssvr17.news.prodigy.com, "Ray"
> <someone@nowhere.com>
> wrote:
>[color=green]
>> Well you certainly have a challenge. It's the "for whatever reason"
>> clause
>> that has me concerned. You can only mitigate foreseeable issues. I
>> recommend you start by determining a list of events that would cause
>> outages
>> or failures and work the list. Clustering with a good SAN and multiple
>> network paths will handle most of the situations but thre are a few that
>> clustering is not a solution for like Site DR, SAN issues, and data
>> manipulation errors.
>>
>> <omeropee@gmail.com> wrote in message
>> news:1108130396.568266.325900@c13g2000cwb.googlegr oups.com...[color=darkred]
>>> Hi,
>>>
>>> Our company is an independent Voice applications solution provider with
>>> number clients using our suite. We have a CT application suite which is
>>> running with Application Server and SQL Server 7 / 2000 as DB Engines
>>> at the back end.
>>>
>>> The SQL server has two databases configured:
>>>
>>> Logging Database - Massive updates every second, the data grows
>>> rapidly,
>>>
>>> Configuration Database - Generally small-sized and updated
>>> occasionally.
>>>
>>> Now we want to have the reslience implemented on the server. We have to
>>> synchronize the two databases 'real-timely' and in 'efficient'
>>> manner, so that if Primary server or its Databases gets unavailable,
>>> the users are seamlessly switched over to the Secondry server that will
>>> have its own set of data updated and well synchronized.
>>>
>>> Typically, it can be explained as follows:
>>>
>>> 1. We will have 2 database servers A - Primary (acting as publisher)
>>> and B - Secondary (acting as subscriber). Our application will be
>>> initially connected to A.
>>>
>>> 2. When A becomes unavailable (for whatever reason), the application
>>> will fail-over to B.
>>>
>>> 3. All the users will be switched to server B and the updates are being
>>> done accordingly without being replicated on Server A temporarily.
>>>
>>> 4. When A is back on-line, A needs to be brought up-to-date with B
>>> automatically (In other words, I shouldn't have to manually export all
>>> the data from B to A ).
>>>
>>> Our requirements are:
>>>
>>> - The system should support Bi-directional Synchronization
>>> between both the servers for their set of databases (the logging and
>>> configuration).
>>>
>>> - There will be constant and heavy activity in Logging
>>> Database, thus if one server gets down the data should be logged and
>>> maintained as it is on second server and on fail-back no data-loss
>>> should occur with minimum latency time.
>>>
>>> - There could be a scenario when a server fails-over for a
>>> week's time, there will be constant logging each second! Once it
>>> fails-back the system should rapidly synchronize the data without
>>> noticeable delay among the two server database sets.
>>>
>>> - The system should also work fine if certain amount of
>>> records are purged over a time period.
>>>
>>> Our concern is, observing the above scenario, how any of your SQL
>>> server replication strategy can help us achieve the requirements.
>>>
>>>
>>> Thanks
>>> John
>>>[/color]
>>
>>[/color]
>
> Active/Active Cluster.
>
> -Greg
>[/color]

re: Recommended Stretagy for Bi-Directional SQL DB Synchronization

Yes, actually what we need is not a hot/wram standby system but
completely an automated fail-over system which seamlessly switches to
the secondary server and maintain all the transactions as they were
being handled on primary without having an effect at the clients' end.
Moreover, once the primary server is functional again, all the changes
and transactions that were done while it was offline will be
replicated/incremented to the primary server and the users should be
automatically and seamlessly switched back to the primary server.

This is what our requirements are. Do you still think that
active/active cluster is best for the scenario that we have?

Furthermore, what are the minimum hardware and software requirements in
case we go for a Fail-over Clustering solution with 2 nodes (i.e. 2 SQL
server machines)?

Thanks
John