SQL 2014 collation settings

(0) Share

Report

Posted on by Community Member

Hello!

We are upgrading from Dynamics SL 2011 to SL 2015 and at the same time upgrading SQL server to v 2014, as the new version of Dynamics SL will not work on SQL 2008.

SQL 2008 has a collation set to Latin1_General_CI_AS whilst the SQL 2014 has the collation set to SQL_Latin1_CP1_CI_AS.

Our integrator is flagging this as an issue, seems they have to be the same.

Question then is will this collation difference be an issue or not?

TIA

Peter

*This post is locked for comments

I have the same question (0)

All responses (3)

Answers (0)

Erich Strelow F 16 on at

Like (0)

Report
As far as I know, the collation is a database level setting. This means that each database within a server has its own collation setting. There shouldn't be a problem to migrate the SL databases to the new server and re-create them with a Latin1_General_CI_AS collation.

There is indeed a server-level collation definition, but it reaches only the following:

Set the default collation for new database creation. But it's just the default, you can specify your own collation in the database creation process.

Sets the collation for the model, msdb, master, tempdb databases.

This is the kind of things that appears in the "Before you begin..." section. This is so beause setting collates is as easy as I said in the moment the database gets created. If you try to correct it later you are in trouble. So, you should have a clear data migration procedure to sort this out.

Having said that, the problems that might arise are:

Worst case scenario: failed JOINS. This should be very rare. Different collation means different indexing and some JOIN operations may fail, leaving positively joined rows out of your favorite query. This indeed would leave your system useless, but I don't really think is beyond repair. You could always re-migrate your data or rebuild the indexes.

Almost worst case: major migration errors. This means duplicate keys errors in the target database wich are valid data on the source database. Technically the migration can't happen. Since the "CI_AS" parts of your collations are the same, and given the kind of table keys SL enforces, this shouldn't be the case.

Minor migration errors. This causes some data glitches that may not be abvious at first. The source "piña-colada song" may become the target "pi¥a-colada song".

Was this reply helpful? Yes No
NameBrands 52 on at

Like (0)

Report

www.olcot.co.uk/.../revised-difference-between-collation-sql_latin1_general_cp1_ci_as-and-latin1_general_ci_as

If you don't have any international/foreign language data, it doesn't matter really. The above web site will explain what can happen if you do have foreign language data.

The code page, language code identifier and comparison style are all identical on these 2 collations.

We are using SL 2015 with SQL 2012 with collation SQL_Latin1_General_CP1_CI_AS without any issues for 4 months now.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Erich,

Thanks for your reply. Appreciate the insight.

Was this reply helpful? Yes No