Sql-server – Compatibility level for published database and transactional replication

replicationsql serversql-server-2008-r2transactional-replication

I am running a database on compatibility level 80 because the application using it does not yet support a higher level.

It is my understanding that transactional replication on SQL Server 2005 and up has some new features like schema changes replication and improved schema locking.

Can I benefit from these new features if my database is on a lower compatibility level?

In other words, do these features depend on the compatibility level of the published database or does this rely only on the version of the server? I know merge replication has a publication compatibility level (independent from database compatibility level) but I can't find a clear answer about transactional replication.

Best Answer

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 :