patternsqlMinor
Distribution database compatibility level
Viewed 0 times
databasedistributionlevelcompatibility
Problem
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?
Solution
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.
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.
Context
StackExchange Database Administrators Q#16083, answer score: 2
Revisions (0)
No revisions yet.