Disable Publishing And Distribution Wizard
Courtland Boland
Delete all subscriptions to the publications. Data in the publication and subscription databases will not be deleted; however, it loses its synchronization relationship to any publication databases. If you want the data at the Subscriber to be deleted, you must delete it manually.
At each Subscriber on the subscription database, execute sp_removedbreplication to remove replication objects from the database. This stored procedure will not remove replication jobs at the Distributor.
At the Distributor, execute sp_dropdistributiondb to delete the distribution database. This stored procedure should be run once for each distribution database at the Distributor. This also removes any Queue Reader Agent jobs associated with the distribution database.
If all replication publishing and distribution objects are not dropped before you execute sp_dropdistpublisher and sp_dropdistributor, these procedures will return an error. To drop all replication-related objects when a Publisher or Distributor is dropped, the @no_checks parameter must be set to 1. If a Publisher or Distributor is offline or unreachable, the @ignore_distributor parameter can be set to 1 so that they can be dropped; however, any publishing and distributing objects left behind must be removed manually.
(Optional) Call the LoadProperties method to get the properties of the object and verify that the Publisher exists. If this method returns false, the Publisher name set in step 4 was incorrect or the Publisher is not used by this Distributor.
Call the Remove method. Pass a value of true for force if the Publisher and Distributor are on different servers, and when the Publisher should be uninstalled at the Distributor without first verifying that publications no longer exist at the Publisher.
Call the UninstallDistributor method. Pass a value of true for force to remove all replication objects at the Distributor without first verifying that all local publication databases have been disabled, and distribution databases have been uninstalled.
I Configured a distribution in SQL Server 2008 using both Wizard and T-SQL but after it when I want to remove it Using Wizard (right clicking on Replication and choosing 'Disable Publishing and Distribution...') or executing following command with and without its parameters:
and found that the session_id of current session (which contains the distribution configuration script) doesn't allow to disable distribution so i suggest this script to kill running spid to drop distribution:
My guess would be that the distribution cleanup job is causing the problem. But, to check, prepare to execute the sp_dropdistributor in one window in SSMS and note the session_id of the window. In a second, prepare to run select session_id from sys.dm_os_waiting_tasks where blocked_session_id = . Back in window 1, run the proc and then switch back to window 2 and run the select. it'll tell you the session_ids of the sessions blocking the drop of the database.
SQL Server could not connect to the Distributor, 'Server_Distributor'. Do you want to disable publishing on 'Server_Publisher' without removing entries for this Publisher from the distribution database?
I have inherited a SQL 2016 server and am trying to set up replication. I looking at the server it appears that the distributor has already been set up, so I attempted to publish a database (transactional publication). I was getting "random" errors, so I thought I would disable distribution and start over.
Second I right-clicked on the SSMS Replication node and selected "Disable Publishing and Distibution". At the end of the wizard I got the message "SQL Server could not disable publishing and distribution on ''." with the additional message "Invalid object name 'dbo.MSreplservers'."
Please provide the exact full error(s) which you get as a starting point. Errors are not for fun but for practical information and should provide some information which might help us solve the issue.
I'm glad to hear you solved the problem.
If the answer helped, please do "Accept Answer" and upvote it. This can be beneficial to other community members reading the thread.
Thanks for your contribution.
According to your query result, the Distributor is indeed configured on this SQL Server instance. What error did you encounter when configuring Publisher next? What is the "random" error? You can refer to this article to configure transactional replication.
-replication-basic-setup-and-configuration/
You encounter an error when using the Disable Publishing and Distribution Wizard. It may be that there are publishing and distribution objects that have not been completely removed. In my test, if I only configure the distributor on a SQL Server 2019 instance, and then use Disable Publishing and Distribution Wizard, I can simply successfully delete this configuration.
TITLE: Publisher Properties
An error occurred while saving publication database.
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Invalid object name 'dbo.MSreplservers'.
The Distributor has not been installed correctly. Could not enable database for publishing.
Changed database context to ''.
The replication option 'publish' of database '' has been set to false. (Microsoft SQL Server, Error: 208)
TITLE: Microsoft.SqlServer.ConnectionInfo
SQL Server could not disable publishing and distribution on ''.
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Invalid object name 'dbo.MSreplservers'.
Could not find the Distributor or the distribution database for the local server. The Distributor may not be installed, or the local server may not be configured as a Publisher at the Distributor.
Changed database context to 'master'. (Microsoft SQL Server, Error: 208)
I have checked and the MSreplservers table is missing from the distribution database. I don't know what else is supposed to be there to say if anything else is missing, but there are quite a few other tables there.
Please try to configure another instance of the same SQL Server version. After configuring the distribution, back up the distribution database, and then restore the backup on the distribution database of the instance which have issues.
Msg 15329, Level 16, State 1, Line 1
The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.
The service master key can only be decrypted by the service account under which it was created or by a principal that has access to the machine's credentials. Try to use a windows account with relevant permissions to login in SQL Server .
We will configure distribution at the Publisher and set the required permissions on the publication and distribution databases. If you have already configured the Distributor, you must first disable publishing and distribution before you begin this process. Do not do this if you must retain an existing replication topology.
This is referred to as publishing a page. When you want to remove a page from the publish environment is referred to as unpublishing. When publishing and unpublishing the page remains available on the author environment for further changes until you delete it.
Quick Publish is for simple cases and publishes the selected page(s) immediately without any further interaction. Because of this, any non-published references will also be published automatically.
In the Quick Publish dialog, confirm the publication by clicking on Publish or cancel by clicking on Cancel. Remember that any unpublished references will automatically be published as well.
Manage Publication offers more options than Quick Publish, allowing for the inclusion of child pages, customization of the references, and starting any applicable workflows and offering the option to publish at a later date.
Publishing later starts a workflow to publish the selected page or pages at the specified time. Conversely, unpublishing later starts a workflow to unpublish the selected page or pages at a specific time.
In the next step of the Manage Publication wizard, Scope, you can define the scope of the publication/un-publication such as including to include child pages and/or including references.
The Published References dialog displays the references for the selected content. By default, they are all selected and are published/unpublished, but you can un-check to deselect them so that they are not included in the action.
Activate the required options and confirm with OK to add the children pages to the list of pages to be published or unpublished based on the selection options. Click Cancel to cancel the selection and return to the wizard.
Today, many companies are living in a hybrid world in which data resides notonly on-premises in a local data center, but in the cloud at a Microsoft Azure datacenter. There is an increasing need to share data with not only internal resources,but with our customers. How can we keep an on-premises SQL Server databasein synch with an Azure SQL database?
4a15465005