0

We have set up Transactional Replication in which Publisher and Subscriber side databases are put under the same SQL Servers. Publisher database collation along with column-level collation is SQL_Scandinavian_Pref_CP850_CI_AS, whereas the subscriber side is Finnish_Swedish_CI_AS. SQL Server collation is also SQL_Scandinavian_Pref_CP850_CI_AS. When I am trying to replicate a varchar column having data with special characters like 'åÅäÄöÖ', then it's getting delivered as '†„Ž”™' on the subscriber side, which is not acceptable.

Can you guys please suggest any solution to get the correct data on the subscriber side? Also, if this bug can't be fixed then please suggest any alternate architecture to deliver the data between databases with different collations? Please suggest.

Murali Dhar Darshan
  • 285
  • 1
  • 17
  • Have the same collation on both publisher and subscriber. Once data has arrived/synced/replicated to the subscriber then use Procs or Views to see data in a different collation. But having different collations on subscribers and publisher, you are opening a can of worms and there will be no end to your problems. – M.Ali Oct 23 '20 at 12:08
  • Actually changing the database level and column level collation is the main purpose and we want to do it in a live database in the production environment. So we thought to create a copy of it and use it as a subscriber database in transactional replication and change the collation on the subscriber side so that the publisher database will still be live and transactions will be delivered to the subscriber database through replication. However, we are getting this issue after setting up replication. – Murali Dhar Darshan Oct 23 '20 at 12:48
  • Once everything will be good, then we will make the subscriber side of the database live and break the replication. Can you suggest any other architectural solution for this, apart from using Transactional Replication? – Murali Dhar Darshan Oct 23 '20 at 12:49

1 Answers1

0

This question is a duplicate of another one, which I posted on stackexchange.com. I have posted the answer there as well. Please find the link below to that post: https://dba.stackexchange.com/questions/278640/changing-collation-of-sql-database-in-production-environment-to-minimize-downtim/278933#278933

Murali Dhar Darshan
  • 285
  • 1
  • 17