By: Nisarg Upadhyay | Updated: 2018-03-05 | Comments (3) | Related: > Replication
Problem
I was creating a new publication on SQL Server using SQL Server Management Studio and I encountered this error “You cannot create a publication from server XXX because the server is too old”.
Solution
I will walk you through the setup that caused this problem and what I found.
Create New Publication
I installed SQL Server 2016 on a machine and I wanted to setup transactional replication. I was using SQL Server Management Studio 2014 to connect to this new instance to create the new publication. I right clicked on Local Publication under Replication and selected New Publication.
When I selected New Publication, it showed me this error "You cannot create a publication from server 'TTI412-VM\SQL2016' because the server is too old”.
This was a brand-new installation and I was perplexed by the error message, so I did a few things to make sure things were setup correctly.
- I check to make sure the replication components were installed correctly on the SQL Server 2016 instance. I did not see any issues.
- To verify that SSMS was not the issue, I created a publication using SSMS 2014 on a SQL Server 2014 instance with the publication wizard and it worked properly.
Creating New Publication Script
As mentioned, I was able to create a publication on the SQL Server 2014 instance, so I generated a publication script. To do that, I right clicked on the publication and selected Generate Scripts as shown in the below image.
I made the required changes to the script, so I could execute it against the SQL Server 2016 instance using SSMS 2014 and the script executed successfully.
-- Enabling the replication database USE master EXEC Sp_replicationdboption @dbname = N'Images', @optname = N'publish', @value = N'true' go EXEC [Images].sys.Sp_addlogreader_agent @job_login = NULL, @job_password = NULL, @publisher_security_mode = 1 go -- Adding the transactional publication USE [Images] EXEC Sp_addpublication @publication = N'Images', @description = N'Transactional publication of database ''Images'' from Publisher ''TTI412-VM\SQL2016''.' , @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false' go EXEC Sp_addpublication_snapshot @publication = N'Images', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = NULL, @job_password = NULL, @publisher_security_mode = 0, @publisher_login = N'sa', @publisher_password = N'Your SA password' EXEC Sp_grant_publication_access @publication = N'Images', @login = N'sa' go EXEC Sp_grant_publication_access @publication = N'Images', @login = N'TTI412-VM\Administrator' go EXEC Sp_grant_publication_access @publication = N'Images', @login = N'NT SERVICE\SQLAgent$SQL2017' go EXEC Sp_grant_publication_access @publication = N'Images', @login = N'NT SERVICE\Winmgmt' go EXEC Sp_grant_publication_access @publication = N'Images', @login = N'NT SERVICE\SQLWriter' go EXEC Sp_grant_publication_access @publication = N'Images', @login = N'distributor_admin' go -- Adding the transactional articles USE [Images] EXEC Sp_addarticle @publication = N'Images', @article = N'FileList', @source_owner = N'dbo', @source_object = N'FileList', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'FileList', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboFileList]', @del_cmd = N'CALL [sp_MSdel_dboFileList]', @upd_cmd = N'SCALL [sp_MSupd_dboFileList]' go USE [Images] EXEC Sp_addarticle @publication = N'Images', @article = N'Images', @source_owner = N'dbo', @source_object = N'Images', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x0000000000000087, @identityrangemanagementoption = N'manual', @destination_table = N'Images', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboImages]', @del_cmd = N'CALL [sp_MSdel_dboImages]', @upd_cmd = N'MCALL [sp_MSupd_dboImages]' go
After creating the publication, I could subscribe to the publication from other servers without any error.
Conclusion
I could create the publication on SQL Server 2016 using scripts, but I did not understand why I was not able to create the publication using the UI.
As you may know, SQL Server 2016 does not provide an option to install SSMS that is why I was using SSMS 2014. In order to get the latest version of SSMS you must download it separately. I downloaded and installed the latest version of SQL Server Management Studio and opened it and connected to the SQL Server 2016 instance. Once again, I tried to create a new publication. I right clicked on Local Publications under Replication and clicked on New Publication and the New Publication Wizard started successfully as shown below.
In short, the error was generated because of an older SSMS version, not because of an old SQL Server version. The message was a little misleading, so I hope this tip helps others that may encounter this problem.
I would like to thank my friend Dolly Gupta who helped me with this article.
Next Steps
- Take a look at these other Replication tips.
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-03-05