SQL Server 2005 Transaction Replication and Failover

[p***@associateddynamics.com]

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.

[Hilary Cotter]

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.

Post by p***@associateddynamics.com
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.

[Paul Ibison]

The simplest option is to set it up for queued updating subscribers. That
way you can assign a completely different range of identity values to the
subscriber and there will be no need for a manual process when failover
occurs. You can also use the queue reader agent to sync back to the
publisher when it comes up, or alternatively just remove replication from
the subscriber. Also, if you need to reinitialize at any time, there is no
need to use custom scripts to generate the subscriber's schema.
Rgds,
Paul Ibison (www.replicationanswers.com)

[Louis Scherr]

Can replication be setup between SQL Server 2005 64bit and 32bit? I want the 32bit machine to be the failover database. Thanks in advance for your reply.

Post by p***@associateddynamics.com
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.

Post by Hilary Cotter
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.
ing as

Post by Paul Ibison
The simplest option is to set it up for queued updating subscribers. That
way you can assign a completely different range of identity values to the
subscriber and there will be no need for a manual process when failover
occurs. You can also use the queue reader agent to sync back to the
publisher when it comes up, or alternatively just remove replication from
the subscriber. Also, if you need to reinitialize at any time, there is no
need to use custom scripts to generate the subscriber's schema.
Rgds,
Paul Ibison (www.replicationanswers.com)
Submitted via EggHeadCafe - Software Developer Portal of Choice
ComponentOne Studio for ASP.NET AJAX - Free License Giveaway
http://www.eggheadcafe.com/tutorials/aspnet/ce98ce1f-2b5d-4ec8-b6d5-a1049651514e/componentone-studio-for-aspnet-ajax--free-license-giveaway.aspx

[Ben Thul]

There are no issues with having a mixed environment for the purposes
of replication.
--
Ben

Can replication be setup between SQL Server 2005 64bit and 32bit?  I want the 32bit machine to be the failover database.  Thanks in advance for your reply.

Post by p***@associateddynamics.com
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.

Post by Hilary Cotter
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.
ing as

Post by Paul Ibison
The simplest option is to set it up for queued updating subscribers. That
way you can assign a completely different range of identity values to the
subscriber and there will be no need for a manual process when failover
occurs. You can also use the queue reader agent to sync back to the
publisher when it comes up, or alternatively just remove replication from
the subscriber. Also, if you need to reinitialize at any time, there is no
need to use custom scripts to generate the subscriber's schema.
Rgds,
Paul Ibison (www.replicationanswers.com)
Submitted via EggHeadCafe - Software Developer Portal of Choice
ComponentOne Studio for ASP.NET AJAX - Free License Giveaway
http://www.eggheadcafe.com/tutorials/aspnet/ce98ce1f-2b5d-4ec8-b6d5-a...