- October 7, 2019 at 11:10 am #23349Donna ZehlKeymaster
Expert Note: Sqlserver to Sqlserver, Refresh operation causes Primary Key constraint violations
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.?
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:
- The forum ‘Expert Notes’ is closed to new topics and replies.