The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication.

As you may know SQL Replication can replicate data between different versions of SQL. It can replicated data from SQL 2012 to SQL 2016 etc. It can also replicate data from higher version to a lower version of SQL. However if you are trying to setup replication to a subscriber that is on higher version than your publisher you may get following error:

“The selected Subscriber does not satisfy the minimum version compatibility level”

Now the problem occurs only when you are adding a new subscriber using GUI and it is related only to version of SSMS you are using during setup.

Lets assume you are configuring subscriber that is on 2016 but your publisher and distributor is on 2012. You most likely connected to a Publisher and trying to add new subscription stream.

You have two solution for this:

  1. Use T-sql. When setting up by using T-SQL, the version of subscriber is not being checked as you are not even trying to connect to a subscriber. Only Distribution agent will connect to it later after the setup.
  2. As T-SQL setup could be bit difficult you have a second solution, which is to use tools that are on the same version as subscriber (in our example it should be SSMS 2016). You can do it either from Subscriber or other server. NOTE: The tools/SSMS should be located on workstation/server that have access to Publisher/Distributor and Subscriber. you need to have access to all 3 components of replication.

If you have other solution for this issue let me know in comments:)

How to setup Transactional Replication? Part 2 (Publications and articles)

In Part 1 we covered configuring Publisher and Distributor server. Now you are ready to create first publications and add articles. We will focus on doing this by using SSMS.

Publication

First step is to create Publication on a database that you want to publish. Since you already setup Publisher and Distributor you should be able to extend Replication tree and Right Click on Publication, choose New Publication:

NewPublication

This will open new Publication Wizard:

NewPubwizard

On the Next window you will need to choose which database you want to publish. This will enable given database for Publishing:

Choosingdatabase

In next step you need to choose type of replication. We focusing only on Transactional Replication and we choosing that one:

ReplicationType

Now you need to choose articles. You can choose Tables, Procedures, Functions etc. In my DB I have only Tables:

ChoosingArticles

You can extend each Table to get column list and you can “filter” it vertically by removing some columns from replication. You don’t need to replicate entire table schema.

On the right side you have Article Properties button. You either set Properties for highlighted article or for ALL articles in this Publications. The list of the settings is quite impressive.

Articles Properties

You can decide if you want to copy triggers, all indexes, if table will be dropped on subscriber, choose if you want to send deletes or not etc. There are plenty of settings that you can modify. Choose wisely as some of these settings can invalidate snapshot and you will need to re-snapshot publication. As there are so many of these properties I will go describe it more in separate post.

In next step you can “filter” articles horizontally by adding WHERE condition. For Example you want to replicate only new data then you can set condition on date column etc.

FilteringRows

As you can see amount of properties and configurations that you can use are making replication very flexible:)

In next step you need to decide when Initial Snapshot will be run. If you don’t want to choose anything, you can always run it manually from replication Monitor. You can either schedule Snapshot or choose to generate snapshot straight after publication will be created:

Snapshot

Next step is to configure Security settings for LogReader and Snapshot agent:

SecuritySettings

I already explained (Understanding Replication Agents Part 1 , Understanding Replication Agents Part 2 ) How Replication Agents work. In that place you configuring Processing Account (under what account jobs and exec will be running) and what account will be used in order to connect to Publisher. You can setup different account for LogReader and Snapshot. You also need to keep in mind that account that will be used to connect to Publisher need to have relevant permissions (sysadmin) on Publisher. That account will be generating Snapshot and reading transaction log on Published database.

SecuritySettings

For this example I choose for both SQL Service account. NOTE: I needed to grant permission for Distributor (if remote one) SQL service account on Publisher. If you choosing different processing account for Snapshot than SQL service account, you need to make sure that this account have permissions do file system on Distributor. Snapshot agent will be creating folders and files on disk. SQL Service will have that permissions but if you use different account you may have issues when generating snapshot.

In next step you can choose that either Publication will be created or just generate the script for the Publication you just configuring (or both):

Createorscript

It could be useful to save T-Sql script as it may me useful in future. It is also nice to see what exactly happen when you configuring Publication (as there are many procs executed and configuration changed during that process that you do not see when using SSMS).

Finally we can put name of our Publication and Finish configuration:

Naming Publication

If all went well you will see new Publication in your SSMS:

Completed

DonePublication

This is pretty much entire process of creating basic publication for Transactional Replication. Easy?? Yeah it is easy we will go bit “deeper” in next post.

In next posts will describe some of the articles settings that you can modify, creating subscriptions and configuring Publications/Subscriptions by  using T-sql.

Configuring distributor and publisher with T-SQL

I already described how to configure distributor and publisher by using GUI.

How to setup Transactional Replication? Part 1

However if you have to setup more than one publisher  to setup and also multiple distributors (or distribution dbs) it is getting quite hard to do it quickly. I often you one remote distributor server and multiple distribution dbs on that one server. That below script helped me a lot.  By default I set retention to 48 hours and 24 hours for history. You can change it in the script or later by using GUI. Script will also generate code that you need to run on Publisher server in order to enable Replication and configure distributor.

Note: It will try to create a folder, if parameter @create_folder set to 1, otherwise it will create db in default or provided path. Still if you want to create folder for new distribution database xp_cmdshell need to be enabled on the server.

If you have any improvements or comments to below script let me know.

/* NOTE: 
1.You need to run the script for EACH distribution db u want to create
2.Model db cant be in use during the installation (It need exclusive lock on model in order to create distribution db)
*/
/****** Installing the server as a Distributor.  ******/
use master

declare @distribution_Db nvarchar(300),
@path nvarchar(300),
@SQL nvarchar(max),
@publisher nvarchar(300),
@password nvarchar(300),
@distributor nvarchar(200),
@create_folder bit

-------------- setting variables ------
set @distributor=@@servername
set @distribution_db=''
set @password=''
set @publisher=''
set @path =NULL -- leave null if u want to create distribution db in this forlder: 
--select substring(physical_name,1,patindex('%tempdb%',physical_name)-1) from sys.master_files where database_id in (select database_id from sys.databases where name='tempdb') and file_id =1
set @create_folder =1 --xp_cmdshell need to be enabled otherwise distribution db will be created in default folder. Set to 0 if you created folder manually.
-------------- end of variables --------

--Configuring Distributor
if not exists (select 1 from sys.servers where is_distributor=1 and name ='repl_distributor' and data_source=@distributor)
BEGIN
exec sp_adddistributor @distributor = @distributor, @password = @password
END

if @create_folder =1 and @path is NULL

BEGIN
		select @path=substring(physical_name,1,patindex('%tempdb%',physical_name)-1) from sys.master_files where database_id in (select database_id from sys.databases where name='tempdb') and file_id =1
END

if @create_folder =1 and @path is not NULL
	BEGIN
		set @path = @path + @distribution_Db
set @sql = 'exec xp_cmdshell ''mkdir '  +@path +''''
print @sql
if @create_folder =1
	BEGIN
	 if exists (select 1 from sys.configurations where name ='xp_cmdshell' and value_in_use =1)
	 BEGIN
	 
	print 'Creating Folder  ' + @path
	exec sp_executesql @sql
	END
	ELSE
	BEGIN
	print 'xp_cmdshell not enabled. Db will be created in default folder'
	set @path =NULL
	END
	END
END




-- Adding the distribution databases
if @path is not NULL
set @path = @path + '\'

if @path is NULL
print 'Distribution db will be created in default folder'


if not exists (select 1 from sys.databases where name =@distribution_db)
BEGIN
exec sp_adddistributiondb @database = @distribution_db, 
@data_folder = @path, 
@log_folder = @path,  
@min_distretention = 48, 
@max_distretention = 48, 
@history_retention = 24, 
@security_mode = 1
END


-- Adding the distribution publishers
exec sp_adddistpublisher @publisher =@publisher,
 @distribution_db = @distribution_Db, 
 @security_mode = 1, 
  @publisher_type = N'MSSQLSERVER'

/****** End: Script to be run at Distributor ******/



select 'Execute on publisher ' + @publisher
select 'use master 
exec sp_adddistributor @distributor = ''' + @distributor + ''' , @password = '''+ @password +''''