Replication: What is the difference between Database Compatibility Mode and Publication Compatibility Mode

  • Article

Database Compatibility Mode:

Compatibility level affects behaviors only for the specified database, not for the entire server. Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around version
differences in the behaviors that are controlled by the relevant compatibility-level setting.

           
In this link, you see some examples of how the database compt mode alters the way SQL “treats” the database, for example:

           
With a DB COMPT LEVEL of 90 --> SET XACT_ABORT OFF is not allowed inside a trigger.

https://msdn.microsoft.com/en-us/library/bb510680.aspx

 

Publication Compatibility Mode:

Merge replication uses the publication compatibility level to determine which features can be used by publications in a given database. The values range from 80RTM (SQL Server 2000 that has no service packs installed) to 100RTM for SQL Server 2008.

           
Examples of how the publication compatibility level affects the publication:

The following features require a compatibility level of 90RTM or higher:

  • Logical
    records
  • Subscriber
    upload options.
  • Nonoverlapping
    partitions
  • Business
    logic handlers
  • Schema
    changes that use ALTER <OBJECT> statements

https://msdn.microsoft.com/en-us/library/ms143241.aspx

So basically:

DB compatibility mode affects the DATABASE (and what you can do using this database)

Publication Compatibility mode affects the PUBLICATION (and what you can do in replication)