SQL Server 2005 Transaction Replication and Failover

We are testing Transactional replication from 2GB database to a
standby servers , one local and one across the wan. Transaction
replication is offering 1 - 3 seconds latency, which seems a great
improvement on our current log shipping which is set to every 3
minutes. Whilst we can increase the frequency to 1 minute,
transactional replication seems to be more resilient. Due to network
constraints we are using a backup of the production database to
perform the initial subscriber synchronisation.

We are now testing whether the replicated database can be enabled with
our application, i.e. to simulate a failover scenario by dropping the
subscription. I understand this will be a manual process. We have
encountered problems with identity columns, where the seed is still
set to the value when replication started and needs to be reset to the
max value. We have set triggers set to Not For Replication, but have
made no other database changes. All tables have primary keys. We do
not require updating at the replicated databases.

The questions I would like to ask are

1. Is transaction replication a viable alternative to log shipping as
a way of maintaining standby servers in the environment described
above?
2.. Are there any further steps we should carry out to make the
replicated database suitable for use as the primary database in a
failover scenario.

what you need to do is make the identity columns odd on one side, even
on the other, do a dbcc checkident to make sure the next value
asssigned on the odd side will be odd, and even on the other side.
Then make sure that the not for replication property is in place for
all identity columns.

On Feb 12, 12:59 pm, p....TakeThisOut@associateddynamics.com wrote:
> We are testing Transactional replication from 2GB database to a
> standby servers , one local and one across the wan. Transaction
> replication is offering 1 - 3 seconds latency, which seems a great
> improvement on our current log shipping which is set to every 3
> minutes. Whilst we can increase the frequency to 1 minute,
> transactional replication seems to be more resilient. Due to network
> constraints we are using a backup of the production database to
> perform the initial subscriber synchronisation.
>
> We are now testing whether the replicated database can be enabled with
> our application, i.e. to simulate a failover scenario by dropping the
> subscription.  I understand this will be a manual process. We have
> encountered problems with identity columns, where the seed is still
> set to the value when replication started and needs to be reset to the
> max value. We have set triggers set to Not For Replication, but have
> made no other database changes. All tables have primary keys. We do
> not require updating at the replicated databases.
>
> The questions I would like to ask are
>
> 1.      Is transaction replication a viable alternative to log shipping as
> a way of maintaining standby servers in the environment described
> above?
> 2..     Are there any further steps we should carry out to make the
> replicated database suitable for use as the primary database in a
> failover scenario.