SQL Server – Difference Between Changed Compatibility Level and New Database

Do these features depend on the compatibility level of the published database or does this rely only on the version of the server?

The short answer is Yes they do depend but to a certain extent only.

Compatibility level can have an affect on the publishing and subscribing databases, with respect to object ownership and the existence (or not) of a particular schema.

Also, have a look at sp_vupgrade_replication

From BOL:

SQL Server 2000 supports common schema changes to an existing publication database. You can add columns to, and drop columns from, a published table without dropping and recreating the publications and subscriptions referencing that table. Schema changes can be replicated during snapshot replication, transactional replication, and merge replication. Column additions and deletions are implemented at the table level and propagated to all Subscribers that receive data from that table.

Important: Schema changes to a published table must be made only through the replication publication properties dialog box in SQL Server Enterprise Manager or through replication stored procedures. Do not make schema changes to published tables using the SQL ALTER TABLE statements in a tool such as SQL Query Analyzer or by using SQL Server Enterprise Manager visual database tools. Changes made to the schema of a published table using these tools will not be propagated to Subscribers.

Refer to :

• Compatibility Levels and their effects.
• Using Multiple Versions of SQL Server in a Replication Topology

The major difference between sql server 2012 and below vs sql server 2014 and up is the new cardinality estimator (CE)

See my answer here as well.

My Story

When we migrated from sql server 2008R2 to 2014, we saw huge query regressions happening on our application. So we decided to enable TF 9481 but still have the latest compatiblity level.

Upgrading from sql server 2014 to 2016, I did not see any query regressions. So no Traceflag or older compatiblity mode required in sql server 2016.

In sql server 2016, there is database scope configuration option to govern the CE and optimizer fixes (equivalent of TF 4199)

Should I expect differences in performance after the compatibility level change?

Its a big change for your application. SO test it properly. You can use Database Experimentation Assistant (New & in preview)

What is the best way to measure the performance before and after?

You can use Query store (new in sql server 2016) to measure performance and find easily regressed queries interms of IO, CPU, etc.

Should I take special care when moving the databases the SQL server 2016?

I have written lenghty answer for migrating from lower version of sql server to higher version of sql server.