Objective: Ensure existing Unicode (UTF-16LE) data is displayed the when moved to MySQL (UTF-8).
Background: I'm migrating 1TB from SQL Server to MySQL. The current Collation in SS is "Latin1_General_CPI_CI_AS". Given MySQL supports Latin1, I am assuming there shouldn't be a problem with the collation.
My main concern is the Character Set conversion. In SS, all numerical data-types are set to Character Set = NULL. The remaining are set to "UNICODE". Since SS Unicode is UTF-16LE and it needs to be UTF-8 in MySQL, I fear I'm stuck verifying each character in all non-numerical columns. That would be very long and intensive given ~4k tables.
Potential solutions: 1. Using SqlDataReader, validate each record value, change if necessary, and then save updated results to an export file. 2. BCP data out of SS and save to .txt file. Reopen each .txt file and re-save with the UTF-8 encoding.
Problems encountered: When converting the character set from UTF-16LE to UTF-8 using a sqldatareader, it works great except when the source value to be converted is already in UTF-8. Ideally, the solution would only run if the data needed to be converted.
Does anyone know of an easy way to ensure data is correctly set and verified from database to another? Am I over thinking this? Is this even an issue?
Thanks in advance!