Identity in replication

We are going to move Access 2002 database tables to SQL Server 2000. The
current Access tables are replicated. I plan to "unreplicate" them before
converting (otherwise all the repl system fields go over). However, many of
the tables have AutoNumber fields that we want to convert to Identity fields
in SQL. I read an article about Merge Replication (which is our plan) and
they said to set the Identity fields to NOT FOR REPLICATION. Is that true?
It seems the wording is confusing. Thanks.
David
David,
I've seen this setting for so long now that I'd forgotten how unintuitive it
seemed at first . You're right for merge - the correct setting is Yes, Not
for Replication. This means it should function as a normal identity column,
apart from when rows originate from the replication synchronization process,
when effectively identity inserts are allowed. This setting isn't needed for
transactional or snapshot - just Identity - yes, because subscribers can't
change the data. If they are updatable, then the same applies as in merge.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
My concern is with laptop users that will be synching on occasion and
will be creating records in tables with Identity columns. Access
handles this with a random long integer. Does SQL Server do the same?
This is important because I prefer not to have to assign different
unique ID's for each laptop user. Access merely creates a random
negative or positive number. Thanks.
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||David,
SQL Server's identity column has a seed and increment - typically 1 and 1.
There's a description in BOL of its behaviour. With this in mind, after you
convert to SQL Server, use DBCC CHECKIDENT to reseed the identity value at
the publisher to be the highest value selected. After that you can use
automatic range management and have ranges allocated to each subscriber.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)