How to Solve SQL Server 2019 UTF-8 Replication Problem

This blog article explains how to fix a problem with SQL Server 2019 replication when using UTF-8 collation for text data.

SQL Server 2019 introduced support for UTF-8 encoding, which is a widely used standard for text data on the web and other applications. UTF-8 encoding allows storing and processing text data in various languages using fewer bytes than other encodings. However, some users have encountered a problem when replicating data between SQL Server 2019 instances that use UTF-8 collation. This article will describe the problem and provide a solution with references and screenshots.

Problem

The problem occurs when replicating data from a publisher database that uses UTF-8 collation to a subscriber database that also uses UTF-8 collation. The data is transferred correctly, but the text data is displayed incorrectly on the subscriber database. For example, a text value that says “St. Joseph’s Medical Center awarded $4M to expand NICU, pediatric unit” on the publisher database appears as “St. Joseph’s Medical Center awarded $4M to expand NICU, pediatric unit” on the subscriber database. This is because the replication process converts the text data to 1252 code page literally, instead of preserving the UTF-8 encoding.

Solution

The solution is to change the replication agent profile parameters for the snapshot agent and the distribution agent. These parameters control how the replication agents handle text data during replication. By default, these parameters are set to use the ODBC driver default settings, which may not be compatible with UTF-8 encoding. To fix the problem, we need to set these parameters to use UTF-8 encoding explicitly.

The following steps show how to change the replication agent profile parameters using SQL Server Management Studio (SSMS):

1. Connect to the publisher server and expand the Replication folder.
2. Right-click on Local Publications and select Agent Profiles.
3. In the Agent Profiles dialog box, select Snapshot Agent from the Agent type drop-down list.
4. Select Default Agent Profile from the Profile name drop-down list and click Edit.
5. In the Edit Agent Profile dialog box, scroll down to find the parameter Output.
6. Change the value of this parameter from -OutputVerboseLevel 1 to -OutputVerboseLevel 1 -OutputEncoding UTF-8.
7. Click OK to save the changes and close the dialog box.
8. Repeat steps 3 to 7 for the Distribution Agent, changing the value of the Output parameter to -OutputVerboseLevel 2 -OutputEncoding UTF-8.
9. Click OK to close the Agent Profiles dialog box.

After changing these parameters, you need to reinitialize the subscription and generate a new snapshot for the publication. This will ensure that the text data is replicated correctly using UTF-8 encoding.

Frequently Asked Questions (FAQs)

Question: What is UTF-8 encoding?

Answer: UTF-8 encoding is a variable-length character encoding standard that can encode all Unicode characters using one to four bytes per character. It is compatible with ASCII and widely used on the web and other applications.

Question: What is collation in SQL Server?

Answer: Collation in SQL Server defines how text data is stored, sorted, and compared in a database or a column. It affects the character set, case sensitivity, accent sensitivity, and other aspects of text data.

Question: How do I check or change the collation of a database or a column in SQL Server?

Answer: You can check or change the collation of a database or a column using SSMS or T-SQL commands. For more details, see Set or Change the Database Collation and Set or Change the Column Collation.

Summary

In this article, we learned how to solve a common issue with SQL Server 2019 replication when using UTF-8 collation for text data. We explained the problem and provided a solution with references and screenshots. We also answered some frequently asked questions about UTF-8 encoding and collation in SQL Server. We hope this article helps you to replicate your text data correctly using UTF-8 encoding.

Disclaimer: This article is for informational purposes only and does not constitute professional advice. The author and publisher are not responsible for any errors or omissions in this article, or for any damages arising from its use. The reader should always consult a qualified professional before implementing any of the solutions presented in this article.