Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Replication Error: cannot create publication because the server is too old


By:   |   Read Comments   |   Related Tips: More > Replication

Attend this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more


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.

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”.

object explorer

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.

  1. I check to make sure the replication components were installed correctly on the SQL Server 2016 instance. I did not see any issues.
  2. 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.

generate scripts

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.

new publication wizard

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


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Nisarg Upadhyay Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional with more than 5 years of experience.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools