Troubleshooting SQL Server Replication Installation


By:   |   Updated: 2019-12-06   |   Comments (1)   |   Related: More > Replication


Problem

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:

Replication components are not installed on this server 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:

Replication components are not installed on this server error
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.
Solution

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.

sql server setup.exe location

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.

sql server installation center

Choose the instance of SQL Server which you want to install SQL Server Replication on and click next.

sql server installation screen

From the list of options, choose SQL Server Replication and click Next.

select features for sql server installation

Assuming you don’t tick any other options, it will skip down to "Ready to Install", click Install.

sql server installation complete

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.

Next Steps


Last Updated: 2019-12-06


get scripts

next tip button



About the author
MSSQLTips author Gareth Newman Gareth Newman is a SQL Database Administrator and Microsoft Certified Professional from the UK. He has experience in HADR, Clustering, Performance Tuning, Replication, Migrations, Upgrades, Distributed Always On Availability Groups and more.

View all my tips
Related Resources



Comments For This Article




Saturday, January 16, 2021 - 11:40:56 PM - Abhishek Back To Top (88056)
Thanks!!


download





Recommended Reading

Change Not For Replication Value for SQL Server Identity Columns

Steps to clean up orphaned replication settings in SQL Server

Troubleshooting transactional replication latency issues in SQL Server

Add new article to existing publication for SQL Server Transactional Replication

SQL Server Replication Overview














get free sql tips
agree to terms