Home / server / Questions / 47611
Accepted
Matt Dawdy
Asked: 2009-07-30 07:43:43 +0800 CST

Breaking SQL Server Replication

  • 772

I asked this question on stackoverflow.com, and they recommended that I ask it here, too.

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?

sql-server replication

2 Answers

  1. Best Answer

    Remove the replication from the publisher, then completely remove the replication config. This will get rid of the blocks of Ids that are assigned and the single server which is left will use all IDs.

    • 2

  2. What the identity insert reservation being done by the replication or tied to it somehow? I'm going suspect that they are using merge replication, which means the there is the publisher and the subsubscriber. Also likely the insert identity is not tired to how the publication is setup. So one of two things will happen when you delete the replication, it will continue to use only the reserved block, so now you need to expand that. Or it will detect the other DB is gone, and give the one that is left access to all the numbers. I suspect it will just keep using the reserved block so you'll be ok until it runs out of numbers.

    If the data you want to keep is all on the home server then you can pretty much just open up the SQL enterprise manager, drill down into the replication node, you'll find the replication for the server in there, you should be able to right click and delete. That will kill that.

    Then if I recall you then delete the publication (represented by the sharing hand on the DB itself) and then if you want you can disable replication on the entire server.

    Sorry I'm not being a little more specific but its been a number of years since I had any replication to do.

    • 1