Replication In Database Having Auto incrementing Field

Hi,

If i were to replicate a database with another where in both the databases have an auto incrementing field. What could be the problems that's goin to arise out of it. How to deal with such a scenario...?

I'm not sure I understand your question. Is one database the publisher and one database the subscriber? Or do you have two publishers, which won't work.

In a Publisher/Subscriber scenario, replication takes care of auto-incrementing fields, in that, different ranges of numbers are assigned to the auto-incrementing fields so that you don't get duplicate numbers during replication.

Also, it depends on your replication method. I am referring to Merge replication, and from the sounds of it, so are you. I have no experience with other forms of replication (such as Transactional), but I think you need to explain a little bit more of your environment before we can give an educated answer.

|||The problem is that you will have the same values assigned to rows inserted on the publisher and subscriber between syncs. These will cause primary key violations when the replication agent syncs the rows (unless you are using immediate updating).

Microsoft recommends you use automatic identity range mangement or use different seeds on both sides. Please refer to this document for more information.
http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/|||

I think i need to elaborate a little more.

The senario is such that i have two server with same database on both the server , i need to setup replication methord in such a way that both the database should get updated with recent records.

I see a single change here is few tables have Auto Increment Field which may get violated while data replication.

Is there any way to get around this problem.

regards

|||Either extent your primary key to include a location specific field as well as the identity key, use different seeds on both sides, or use automatic identity range management.|||

Yes i agree but the identity field value is being used datawase wide, now while replicating if i were to use the method sugested by Hilary i'll get into big time trouble.

While replicating the data from one server to another suppose table A is residing on one server to table B residing on another server, it will change the identity values of table B and henceforth all the other tables that are dependant on that particular field of table B would be fudged.

Regards.

|||I am not sure what you are saying here. The identity ranges are applicable per table.

If I do this:

insert into tableA

The identity column will be assigned the next value, ie the increment+the current value. For example from 1 to 3 (assuming a seed of 1 and an increment of 2).

If I am using the not for replication option and a replication process does the insert, the value inserted will be what it was on the publisher, ie the identity property will not be inforced. So if a 2 was assigned on the publsiher a 2 would be inserted on the subscriber. The next subscriber insert would be a 3 (current value) +2 (increment)=5.

Using different seeds and the same increment can partition ranges so you don't get collisions.