We have an OLTP, DSS, and separate Distribution machine. The OLTP serves as
a "HUB" for our 18 server bi-directional replication scenario, and the
replication machine regulates the flow of that "HUB" data. In the event that
our OLTP server becomes non-operational, is anyone familiar with how we could
point multiple remote publishers to the DSS machine without breaking down all
remote publications and subscriptions...we're afraid of the data loss
associated with dropping publications and subscriptions.
Thanks.
Wendy Sporleder
With un-directional replication you can have multiple publishers publishing
to the same subscriber.
If the main publisher went off line a standby publisher could go online and
start replicating to these same subscribers. The number of transactions lost
would be whatever had not made it to the standby server.
With bi-directional replication you would know that all the data is in the
subscriber, and then you would be faced with the same question as to what
data was still in the publisher which has yet to make it to the subscriber.
You would probably be best to do no-sync subscriptions and then validations
to ensure each node is consistent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"wendy.sporleder@.carlson.com"
<wendy.sporleder@.carlson.com@.discussions.microsoft .com> wrote in message
news:FFE2AE1D-7436-402C-9698-140A26A5EFBA@.microsoft.com...
> We have an OLTP, DSS, and separate Distribution machine. The OLTP serves
as
> a "HUB" for our 18 server bi-directional replication scenario, and the
> replication machine regulates the flow of that "HUB" data. In the event
that
> our OLTP server becomes non-operational, is anyone familiar with how we
could
> point multiple remote publishers to the DSS machine without breaking down
all
> remote publications and subscriptions...we're afraid of the data loss
> associated with dropping publications and subscriptions.
> Thanks.
> Wendy Sporleder
>
2012年3月11日星期日
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
>
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
>
订阅：
博文 (Atom)