Prev: How to get name of replication agents
Next: RMO Conflict Resolver - Could not load file or assembly
From: Malik on 8 Mar 2010 14:18
Hi,

I am reposting this from the main SQL Server forum. We are in the process of
migrating from one DC to another. I setup a transactional replication using
SQL backups for a database that only has two tables which are very actively
updated. Here are the steps that I followed in the order:

1) Setup distributor.
2) Restored full backup followed by T-Log backups (from publisher db to
subscriber db) until the production maintenance window started.
3) after the maintenance window started i took last log backup and restored
at the subscriber.
4) Setup publisher and set allow initialization from backup to true.
5) setup subscriber with initialize from snapshot unchecked.

Now transactional replication has been running successfully and data in both
publisher and subscriber is identical but we have been unable to make updates
at the subscriber. Updates from the application have been failing with error
message:

Cannot insert duplicate key row in object 'dbo.XXXXXX' with unique index
'CX_XXXXXXXXX'.
The statement has been terminated.

This index is created on an identity column. I have verified that the column
has Not For Replication specified both at the subscriber and publisher. When
I run DBCC CHECKIDENT it tells me that my current identity value is less than
current column value. I have tried to reseed it with the current value but it
goes out of sync again. This is the first time i have used backup to
initialize a replication and haven't seen this issue before. Can someone
please help me resolve this issue?

Thanks in advance for your help!


From: Ben Thul on 8 Mar 2010 15:01
Even though you restored your subscriber from a backup of the
publisher, my guess is that the wizard set up the subscription in such
a way as to say "the publisher and subscriber are in sync...no
initialization necessary". Also, the fact that you set up the
publication after you took the backup of the (to be) published
database precludes initialization from backup since the distribution
database won't have the necessary transactions to complete the
synchronization. Here's the steps I usually use:

1) Set up publication
2) Take backup of published db (n.b.: no log backup/restores
necessary; the transactions between the backup and when the
subscription is established will come from the distribution db)
3) Restore backup as subscriber db
4) Add subscription with @sync_type='initialize with backup' (and all
of the other parameters that that implies)

Go through the wizard again and use the 'generate script' option and
take a look at what it's doing. Also, take a look at BOL for
sp_addsubscription. HTH.
--
Ben

On Mar 8, 1:18 pm, Malik <Ma...(a)discussions.microsoft.com> wrote:
> Hi,
>
> I am reposting this from the main SQL Server forum. We are in the process of
> migrating from one DC to another. I setup a transactional replication using
> SQL backups for a database that only has two tables which are very actively
> updated. Here are the steps that I followed in the order:
>
> 1) Setup distributor.
> 2) Restored full backup followed by T-Log backups (from publisher db to
> subscriber db) until the production maintenance window started.
> 3) after the maintenance window started i took last log backup and restored
> at the subscriber.
> 4) Setup publisher and set allow initialization from backup to true.
> 5) setup subscriber with initialize from snapshot unchecked.
>
> Now transactional replication has been running successfully and data in both
> publisher and subscriber is identical but we have been unable to make updates
> at the subscriber. Updates from the application have been failing with error
> message:
>
> Cannot insert duplicate key row in object 'dbo.XXXXXX' with unique index
> 'CX_XXXXXXXXX'.
> The statement has been terminated.
>
> This index is created on an identity column. I have verified that the column
> has Not For Replication specified both at the subscriber and publisher. When
> I run DBCC CHECKIDENT it tells me that my current identity value is less than
> current column value. I have tried to reseed it with the current value but it
> goes out of sync again. This is the first time i have used backup to
> initialize a replication and haven't seen this issue before. Can someone
> please help me resolve this issue?
>
> Thanks in advance for your help!

 | 
Pages: 1
Prev: How to get name of replication agents
Next: RMO Conflict Resolver - Could not load file or assembly