March 8, 2010 at 12:22 pm
Hi,
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. We are using SQL Server 2005 Enterprise Ed SP3 32bit for source and SQL Server 2005 Enterprise Ed SP3 64bit for destination. Can someone please help me resolve this issue?
Thanks in advance for your help!
March 8, 2010 at 3:57 pm
You just have duplicate at subscriber. check these steps, it should fix.
http://www.cryer.co.uk/brian/sqlserver/disagnt_cnidkriowui.htm
I wouldn't prefer Back up and Restore method for Initial snap shot of articles, what is the database size?
Enjoy!
March 9, 2010 at 9:12 am
Replication has been running fine. It's just that we can not insert directly into tables in the subscriber db.
DB size is 56GB. We are replicating data from NY to Paris and pipeline between the two DCs is not very good. If I had done it using a snapshot it would have taken us 10 hours plus just to apply the snapshot.
March 9, 2010 at 9:22 am
Thats fine you apply snapshot only once right? Don't you have maintenance window? You cannot insert duplicate key on subscriber table, because there is constraint correct? Did you check the link i had copied in last post?
March 9, 2010 at 11:33 am
We only have 1 hr maintenance window and that's not enough. I looked at the link that you copied in the last post. That doesn't apply to me because I am not getting any errors when distribution agent inserts data into subscriber. It's just when users directly insert data they get an error. I think replication doesn't reseed the identity column after it inserts a new value. I just wanted to know if that's expected behavior. If not, what can we do to resolve this issue.
Thanks a lot for your help.
March 9, 2010 at 2:41 pm
I would say that is not by design as you have turned "Not for replication" on at the publisher. When it inserts at the publisher it should seed the next Identity value as at the subscriber. Very odd! Can you give a more specific example of the IDs in Publisher and Subscriber with a DBCC CHECKIDENT on both? Would help in visualising the issue.
March 10, 2010 at 8:50 am
Sounds like you're not managing Identities Ranges in your Article Properties.
If the subscriber need to be updateable you need to set up separate identity ranges for the publisher and subscriber.
This can be done in the Identity Range Management of the Article Properties. You tell it how many ids to reserve at a time and a percent threshold at which the range should replenish.
Here's a couple of articles that covers this:
http://msdn.microsoft.com/en-us/library/ms146907(SQL.90).aspx
http://technet.microsoft.com/en-us/library/ms152543.aspx
[Edit: added another article]
Hope this helps.
March 10, 2010 at 2:09 pm
Shark Energy, The identity seed value is increasing in the source which is around 89million while it stayed constant in the subscriber which was few thousand less than the source. I think Steve has nailed this issue.
Steve, thanks for responding to my post. Identity range management is set to manual. When we setup replication for an article that has an identity column, is identity column management set to auto by default or manual? Because I have been working with replication for a pretty long time and this is the first time I have seen this.
March 11, 2010 at 8:05 am
I think the feature has been around since at least 2000; if I'm remembering correctly, it defaulted to Automatic in 2005, but I can't swear to it.
One caveat with Automatic managment is that if you do a lot of bulk inserting, especially on the subscriber side, you may deplete your pool before the batch is fully processed -- so size accordingly.
March 11, 2010 at 9:25 am
Got you! Thanks again for your help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply