Distribution database compatibility level

Posted on

Question :

Using SQL Server transactional replication with a distributor on 2008R2, and publishers/subscribers all on SQL 2005. Does the compatibility level of the distribution database matter? Should it be set to 90 to match the other servers, or is 100 fine?

Answer :

It seems that 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. If you aren’t seeing that, it’s probably not a problem for you.

As far as the distribution database goes, there doesn’t seem to be any effect. I would leave the compatibility level of any of the SQL Server system databases (master, msdb, tempdb, the distribution database and even the model database) to whatever MS set it to unless Microsoft or a third-party vendor tells me otherwise, gives me a reason and says that I’ll be unsupported if I don’t change it.

I’d leave it at what Microsoft set it when they created the database.

Leave a Reply

Your email address will not be published. Required fields are marked *