Changing default collation for database, which is used in replication. It is recommend that all replication database have the same default collation.

Microsoft.SQLServer.2000.Changing_default_collation_for_database_which_is_used_in_replication__It_is_recommend_that_all_replication_database_have_the_same_default_collation (Rule)

Knowledge Base article:

Causes

You have issued the ALTER DATABASE statement with the COLLATE clause to change the default collation on an existing database. The database is configured as a Publisher (sp_replicationdboption was used to set the "merge publish" and/or "publish" option to true), or the database is configured as a distribution database.

Microsoft recommends that all databases in a replication topology have the same collation to avoid potential case sensitivity, sorting, and translation issues.

The following is not a comprehensive list of all potential problems; it is a subset of the high-level types of issues you might encounter:

For example, consider the scenario where the publishing database has a case-sensitive collation, but the subscriber has a case-insensitive collation. On the publisher you have a row with the primary key equal to "Test" and a row with the primary key equal to "TEST". But if you try to replicate that data to a case-insensitive Subscriber, replication will fail with a duplicate key error. The same scenario could occur with a difference in accent sensitivity, kanatype sensitivity, or width sensitivity.
Even with the same case-sensitivity and with all unicode data types, you could have different results on servers with different collations. For example, an application that returns the top 10 rows could return completely different data sets on a dictionary order collation compared with a binary order collation. Result set differences could also occur if the difference is between a sort order with uppercase preference and one without uppercase preference.

Resolutions

It is recommended that you use the same collation for all databases in your replication topology. If you must have different default collations for databases within the same replication topology, thoroughly test your specific replication scenario with realistic data, and adjust your configuration to account for any anomalies that could occur.

External

For more information about collations, see "SQL Server Collation Fundamentals" and "Copying Data Between Different Collations" in Books Online and Microsoft Knowledge Base article 325335.

Element properties:

Target

Microsoft.SQLServer.2000.DBEngine

Category

EventCollection

Enabled

True

Event Source

$Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ServiceName$

Alert Generate

True

Alert Severity

Warning

Alert Priority

Normal

Remotable

True

Alert Message

Changing default collation for database, which is used in replication. It is recommend that all replication database have the same default collation.

{0}

Event Log

Application

Comment

Mom2005ID='{52E4067B-E411-405F-BC18-2E02AAEEADC4}'

Member Modules:

ID	Module Type	TypeId	RunAs
EventDS	DataSource	Microsoft.Windows.EventProvider	Default
GenerateAlert	WriteAction	System.Health.GenerateAlert	Default

Source Code:

<Rule ID="Microsoft.SQLServer.2000.Changing_default_collation_for_database_which_is_used_in_replication__It_is_recommend_that_all_replication_database_have_the_same_default_collation" Target="SQL2000Core!Microsoft.SQLServer.2000.DBEngine" Enabled="true" Comment="Mom2005ID='{52E4067B-E411-405F-BC18-2E02AAEEADC4}'" Remotable="true">

  <Category>EventCollection</Category>

  <DataSources>

    <DataSource ID="EventDS" TypeID="Windows!Microsoft.Windows.EventProvider">

      <ComputerName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>

      <LogName>Application</LogName>

      <Expression>

        <And>

          <Expression>

            <SimpleExpression>

              <ValueExpression>

                <XPathQuery>PublisherName</XPathQuery>

              </ValueExpression>

              <Operator>Equal</Operator>

              <ValueExpression>

                <Value>$Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ServiceName$</Value>

              </ValueExpression>

            </SimpleExpression>

          </Expression>

          <Expression>

            <RegExExpression>

              <ValueExpression>

                <XPathQuery>EventDisplayNumber</XPathQuery>

              </ValueExpression>

              <Operator>MatchesMOM2005RegularExpression</Operator>

              <Pattern>^(17052|17055)$</Pattern>

            </RegExExpression>

          </Expression>

          <Expression>

            <RegExExpression>

              <ValueExpression>

                <XPathQuery>EventDescription</XPathQuery>

              </ValueExpression>

              <Operator>MatchesMOM2005BooleanRegularExpression</Operator>

              <Pattern>((^.*5076 and (not ^.*\n.*5076)) or (^.*5076.*\n.*5076)) AND (NOT ^.*[0-9]+.*5076) AND (NOT ^.*5076[0-9]+)</Pattern>

            </RegExExpression>

          </Expression>

        </And>

      </Expression>

    </DataSource>

  </DataSources>

  <WriteActions>

    <WriteAction ID="GenerateAlert" TypeID="SystemHealth!System.Health.GenerateAlert">

      <Priority>1</Priority>

      <Severity>1</Severity>

      <AlertOwner>$Data/PublisherName$</AlertOwner>

      <AlertMessageId>$MPElement[Name="Microsoft.SQLServer.2000.Changing_default_collation_for_database_which_is_used_in_replication__It_is_recommend_that_all_replication_database_have_the_same_default_collation.AlertMessage"]$</AlertMessageId>

      <AlertParameters>

        <AlertParameter1>$Data/EventDescription$</AlertParameter1>

      </AlertParameters>

      <Suppression>

        <SuppressionValue/>

      </Suppression>

    </WriteAction>

  </WriteActions>

</Rule>