I recently was engaged in updating the Compatibility Mode of our production databases to 110 (2012) in preparation for upgrading them all to SQL Server 2014. One of the production servers had a database named ‘distribution’ and, since I am most familiar with log-shipping, was unaware what the database was for I need to do some Googling. I found out the it is the meta database for replication, which someone had turned on when trying to work on an HADR scenario. It had not ever been used and no databases were ever published with it (and because of that, I only needed to run a subset of the T-SQL in the referenced blog-post). It made sense then to tear it all down. I found the following post and walked through the script, which worked like a charm.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- Set up the variables to be used DECLARE @publisher AS sysname; DECLARE @distributionDatabase AS sysname; SET @publisher = N'MyServer\MyNamedInstance'; SET @distributionDatabase = N'distribution'; -- Drop the registration of the local Publisher at the Distributor USE master EXEC sp_dropdistpublisher @publisher; -- Drop the distribution database EXEC sp_dropdistributiondb @distributionDatabase; -- Drop the local server as the Distributor EXEC sp_dropdistributor; GO |
How to: Disable Publishing and Distribution (Replication Transact-SQL Programming)
View and Modify Publication Properties
Views – 2350