Troubleshooting SQL Server Replication Installation
When you attempt to configure a SQL Server distributor or create a publication in SQL Server Replication, an issue you can come across, is that the SQL Server Replication feature it is not installed! Maybe you didn’t think you would need it, so you chose not to install it, or perhaps another DBA setup this SQL Server and they chose not to install SQL Server Replication. If you try to add a publication or distributor in SSMS you will get this error:
Microsoft SQL Server Management Studio is unable to access replication components because replication is not installed on this instance of SQL Server. Replication components are not installed on this server. Run SQL Server Setup again and select the option to install replication. (Microsoft SQL Server, Error: 21028)
And if you try and execute a T-SQL command to enable a component of replication you will see an error like this:
Msg 22001, Level 1, State 1 Msg 21028, Level 16, State 1, Procedure sp_MS_replication_installed, Line 35 (Batch Start Line 0) Replication components are not installed on this server. Run SQL Server Setup again and select the option to install replication.
So, the error message is actually very helpful, and it tells us what we need to do.
"Replication components are not installed on this server. Run SQL Server Setup again and select the option to install replication."
It is telling us we need to run the setup wizard again and add the replication feature. So, let’s do that now.
Install SQL Server Replication Components
First, we need to locate our installation media, this could be a DVD (highly unlikely these days) or the media may still exist on the server, more than likely you have a file share where you keep your installation media, e.g. \\myfileserver01\software\sql server\ sql-server-2016\.
Once you have located the media, click the setup.exe to begin the installation wizard.
When the installation wizard opens, on the left choose Installation and then choose to New SQL Server stand-alone installation or add features to an existing installation.
Choose the instance of SQL Server which you want to install SQL Server Replication on and click next.
From the list of options, choose SQL Server Replication and click Next.
Assuming you don’t tick any other options, it will skip down to "Ready to Install", click Install.
And that’s it, you installed the features! Now, the burning question on every production DBAs mind will be "do I need to restart SQL Server?" And the answer is, No!
If you attempt the same commands or process that generated this error in the first place, they should now go through just fine.
I hope this tip helped, in the next tips we will configure a distributor and a publisher ready for setting up replication in a future tip.
- Check out these related articles
Last Updated: 2019-12-06
About the author
View all my tips