Expert Note:  Sqlserver to Sqlserver, Refresh operation causes Primary Key constraint violations

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #23349
    Donna Zehl
    Keymaster

    Expert Note:  Sqlserver to Sqlserver, Refresh operation causes Primary Key constraint violations

     

    Scenario

    With a Sqlserver to Sqlserver replication configuration, the Refresh operation causes Primary Key constraint violations on the target when source by default sets the collation to case insensitive.

    When you create a SQL Server database, by default, it sets the collation to case insensitive.  This poses a problem if the Source database is set to Case Sensitive and has primary key values that essentially match outside of the upper and lower case values.  When you run a Refresh operation, you get PK constraint violations on the target.

     

    Scenario 1:  Both Sqlserver source & target database were set to SQL_Latin1_General_CP1_CI_AS (collation, Column Insensitive), but on the source, it is very common to have primary key columns set to SQL_Latin1_General_CP1_CS_AS  (collation, Column Sensitive).  This poses a problem during Refresh.   All primary key columns in the database were set to case sensitive (CS).  One workaround was to change the collation at the DB level.

    Question,  Is there a way for us to create these columns exactly as they are on the Source even though the DB is set to CS.?

     

    Example/Workaround:

    Changing the HVR database setting to Case Sensitive resolves this issue currently.

    A JIRA has been opened to enhance the product so the workaround may not be needed in the future

     

    Reference for understanding SqlServer collation:

    https://www.mssqltips.com/sqlservertip/4395/understanding-the-collate-databasedefault-clause-in-sql-server/

     

Viewing 1 post (of 1 total)
  • The forum ‘Expert Notes’ is closed to new topics and replies.

© 2019 HVR

Try now Contact us