Disaster recovery - Use Replication or Log shipping

Hi,
I have applications using Sql Server 2000 database at a primary location. As
a disaster recovery i.e if Sql server at the primary location fails , i have
setup another setup with Sqlserver 2000 and applications at secondary
location.
I want to know what is best option for disaster recovery ?, is it
transaction replication or Log shipping.
Suppose i use log shipping , when the primary server goes down, manually i
switch the applications to secondary server . Now the seconday server will
have some trasnactions . When the primary server comes back how do i
propogate the transaction chnages of secondary server to primary server?
Thanks.
gdc
That depends on your particular requirements - here are some of the pros and
cons of each
Pros log shipping
all objects and permission are copied
simple to set up
there is a log shipping wizard that ships with SQL 2000 EE, and SQL 7
Resource kit has some scripts
conceptually is very simple to understand
cons
standby server database is in read only mode
standby server database must go off line as you apply the tlogs
latency can be as low as 1 minutes, but practical limit is 5 minutes
certain db operations break the log shipping chain
requires more space
can't partition - its all objects and all data or nothing
not feasible for large databases
Pros trepl
can partition to a single object and a subset of rows
short latency as low as 4 s or less.
only new transactions go across the wire
cons
each table needs a pk
can be complex to troubleshoot and restart, but all in all is almost as
reliable as log shipping
"rajani" <rajaninandula@.hotmail.com> wrote in message
news:eiHeEbbFEHA.3064@.tk2msftngp13.phx.gbl...
> Hi,
> I have applications using Sql Server 2000 database at a primary location.
As
> a disaster recovery i.e if Sql server at the primary location fails , i
have
> setup another setup with Sqlserver 2000 and applications at secondary
> location.
> I want to know what is best option for disaster recovery ?, is it
> transaction replication or Log shipping.
> Suppose i use log shipping , when the primary server goes down, manually i
> switch the applications to secondary server . Now the seconday server will
> have some trasnactions . When the primary server comes back how do i
> propogate the transaction chnages of secondary server to primary server?
> Thanks.
> gdc
>
|||I missed part of your question.
The only ways to reconcile changes which occur when the primary goes down is
to use merge replication, bi-directional transactional, or manually to
stitch together a consistent database.
It is possible to resync both databases manually, but you have to kick
everyone off both systems and then do binary checksums on all tables to
figure out which rows are out of sync.
I've had to do it and it is a chore.
Merge and bi-di trans replication makes life a lot easier.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u$FubBdFEHA.3372@.TK2MSFTNGP10.phx.gbl...
> That depends on your particular requirements - here are some of the pros
and
> cons of each
> Pros log shipping
> all objects and permission are copied
> simple to set up
> there is a log shipping wizard that ships with SQL 2000 EE, and SQL 7
> Resource kit has some scripts
> conceptually is very simple to understand
> cons
> standby server database is in read only mode
> standby server database must go off line as you apply the tlogs
> latency can be as low as 1 minutes, but practical limit is 5 minutes
> certain db operations break the log shipping chain
> requires more space
> can't partition - its all objects and all data or nothing
> not feasible for large databases
> Pros trepl
> can partition to a single object and a subset of rows
> short latency as low as 4 s or less.
> only new transactions go across the wire
> cons
> each table needs a pk
> can be complex to troubleshoot and restart, but all in all is almost as
> reliable as log shipping
>
> "rajani" <rajaninandula@.hotmail.com> wrote in message
> news:eiHeEbbFEHA.3064@.tk2msftngp13.phx.gbl...
location.
> As
> have
i
will
server?
>
|||Although Hilary implies this, it's worth noting that there are a couple of extra points in the list of cons for transactional replication for disaster recovery:
permissions, user-defined datatypes and users not transferred
defaults removed - this may cause code to break or return unexpected results after DR.
Regards,
Paul Ibison
|||Still not clear. Suppose i transaction replication, and the primary server
fails. I disable the replication jobs on the secondary server and point my
applications to secondary server and now my secondary server is primary
server.
The applications creates some transactions on the new primary server. When
the old primary server comes back , suppose i want to revert to old primary
server .
How do i reflect the changes done on the new primary server to old primary
server ?
Suppose by some means i reflect the changes, is there any problem in
enabling the replication which was setup earlier?
thanks
gdc
"rajani" <rajaninandula@.hotmail.com> wrote in message
news:eiHeEbbFEHA.3064@.tk2msftngp13.phx.gbl...
> Hi,
> I have applications using Sql Server 2000 database at a primary location.
As
> a disaster recovery i.e if Sql server at the primary location fails , i
have
> setup another setup with Sqlserver 2000 and applications at secondary
> location.
> I want to know what is best option for disaster recovery ?, is it
> transaction replication or Log shipping.
> Suppose i use log shipping , when the primary server goes down, manually i
> switch the applications to secondary server . Now the seconday server will
> have some trasnactions . When the primary server comes back how do i
> propogate the transaction chnages of secondary server to primary server?
> Thanks.
> gdc
>