Inserting records with value outside replication identity ranges

I have a live system that uses merge replication. We are upgrading the
system by setting up a whole new set of servers. When we go live on the new
servers, I would like to copy into the new server database, any changes that
have been made to the old databases. Unfortunately, replication adds check
constraints to identity columns starting with the last identity value in the
database. The old system is still adding id values below the maximum id
(e.g. each region is adding values within their own range which is below the
maximum id in the table.)
I can't just copy the entire database and then setup replication because the
snapshot would take way too long to get to one of the subscribers and we need
to be able to switchover relatively quickly.
Since SQL Server does not let me modify or drop the identity column check
constraint that replication added, how can I add these identity values after
replication has already been set up on the new system?
There could be other issues though. What if the subscriber record is updated
rather than inserted - how will you know which updated rows to take? I think
the simplest way is to sync all subscribers prior to migrating the production
database to the new server. Then using this as the master, set up replication
from the new publisher.
Paul Ibison
|||During the cutover, we will stop all changes to the old database, wait for
replication to sync up, then copy changes from the old publisher over to the
new publisher. then get everyone connected to the new servers.
One of our subscribers is in China where network connectivity is slow and
unreliable. I have tried getting a snapshot with all the data to this
subscriber and it just takes too long - days if it ever succeeds, which so
far it has not. I have to get the snapshot to China with some of the tables
empty, then add the data after.
"Paul Ibison" wrote:

> There could be other issues though. What if the subscriber record is updated
> rather than inserted - how will you know which updated rows to take? I think
> the simplest way is to sync all subscribers prior to migrating the production
> database to the new server. Then using this as the master, set up replication
> from the new publisher.
> Paul Ibison