Breaking SQL Server Replication

Question

I've got a new client, blah blah blah, I need to break SQL Server 2000 replication from their home office to another database that they are going to completely drop (at some hosting place, it is no longer needed).

The one at the home office is the "Publisher", I believe. Honestly, I think we could just delete the other database and the home office one would run fine, but it would be nice to clean it up a bit and get rid of the publications/subscriptions stuff.

The one issues I'm concerned about is the identity insert. On tables with an auto-assigned integer primary key, each database would "reserve" a block of ids that it would use so they wouldn't trample on each other when synching up.

So, if I just dump the replication stuff, will the database insert happily until it hits the upper bound of its auto-number range? What happens then?

Answer 1

You need to clearly identify the roles that each Database and Server are playing in your replication topology before you start rolling out any changes.

As a further precaution, I would also suggest that you script out your Replication Configuration, so that you have a reference of it for safe keeping.

For details on how to correctly remove a specific replication role i.e. Subscriber / publisher / Distributor.

See Books Online: Removing Replication