|
|
|
|
|
|
One of the more common problems you will have to encounter at some point is when you disable replication, not all of the settings are cleaned up. This can cause problems if you want to drop a database or alter table structures.
As you might have figured out, the reason this tip even exists is because the wizards to disable replication are not perfect. The good news is that there isn't anything magical about replication. Replication is run entirely via a set of applications that are external to SQL Server. These applications are executed by the SQL Server Agent, Windows Synchronization Manager, or directly via some other means. The same functionality is performed when using the replication ActiveX controls. When executed, the replication agents or the ActiveX controls connect to the appropriate SQL Server and utilize data stored there to determine what to do. All you have to do is eliminate all of that data and turn off any associated settings.
If you don't take anything else away from this article, remember the following fact. If you can perform an operation in a pretty, little GUI, then you can perform that same operation by typing in and executing the commands. Too many people have grown accustomed to the "point and click" mentality of the increasingly powerful GUIs. What they fail to remember is that these GUIs do nothing more than execute a set of commands that you could type yourself if you knew what they were.
Technically speaking, when you configure publishing, you do absolutely nothing to the SQL Server. Bound up within that GUI is where you create settings for publishers, distributors, and subscribers. It is these portions of the GUI that actually have commands that get executed behind them.
When you configure a database as a publisher, what are you doing? You are simply setting a database option. This option is either "published", "merge publish", or both depending upon the replication method you specified. If you issue either of the following commands, you accomplish exactly what the GUI is doing in that portion.
exec sp_dboption 'pubs','published',true
exec sp_dboption 'pubs','merge publish',true
It is this setting that has a decision point within the core SQL Server code. If either of the options are turned on for publication, then you will not be allowed to drop that database.
When you specify a distribution database in the GUI, it simply executes the appropriate create database commands. The final step of the configure publishing GUI adds a set of system tables to track the replication setup.
When you create a publication, entries are written to these tables describing the objects you have published, any partitions, and any custom settings for each article. It also modifies a flag in sysobjects indicating that an object is participating in replication. This column is called replinfo and is set to a value of 128. Any objects that have a value of 128 are participating in replication and those with a 0 are not. This flag is the other decision point in the core SQL Server code that will prevent an alter/drop statement from executing against a published object.
When the snapshot agent runs for the first time, it will add a whole slew of objects to your database. These are any combination of one or more of the following:
When you subscribe to a publication, you are also simply turning on a database setting. In this case, it is subscribed. The snapshot is then applied and you get all of the schema elements necessary to make replication functions such as metadata tables, triggers, views, procedures, etc.
So you now have a replication environment setup and running. You then come back and decide to remove it, but something goes wrong and you leave all kinds of stuff behind. Note: Even if the wizard runs perfectly, it will still leave elements behind.
The most common items that are left behind are:
database options
replication objects
sysobject settings
rowguid column
conflict tables
distribution database
To remove the database options, you simply execute sp_dboption for published, merge publish, or subscribed, and set it to false. Removing the sysobject settings is more involved in that you have to turn on allow updates, update the replinfo column and set it to 0 where it is 128, and then turn off allow updates. At this point, you should have a fully functional database that allows you to alter/drop objects and drop databases. You could stop here without having any side effects. But, we might as well take everything out. You get rid of the rowguid column, if it exists, by altering the table and dropping the column. Conflict tables are simply user tables and can be removed with a simple drop command. The same goes for any triggers, views, or procedures that replication created.
At this point, you should be able to drop the distribution database.
I STRONGLY recommend stopping there and not touching any of the replication system tables or system stored procedures that were created when you setup replication. They do not have any effect on your environment, but you never know if something got left behind in the bowels of the SQL Server code that will cause something to blow up if you remove these. Leave the system objects intact on the machines and just ignore them.
You are now back to a clean system that functions just like it did before you setup and ran replication across it.
