SQL Server Collation

dgsmith80

Hey Fellow Thwacksters,

With many people in the process of upgrading to SQL Server 2016, I have come across a problem that I wasn't sure how to answer so I thought where better to place the challenge than Thwack!

So the recommended SQL Collation according to SolarWinds are:

SQL_Latin1_General_CP1_CI_AS

SQL_Latin1_General_CP1_CS_AS

But, I have come across many customers who are running old databases from several versions ago which are running in Latin1_General_CI_AS

This leaves them with a decision to make, do they provision the new SQL server with SQL_Latin1_General_CP1_CI_AS and end up with different server/database collations - OR - do we make sure the new server matches the existing collation of the database we migrate.

I asked SolarWinds support, and they sent me some links on how to change server and database collation, but pretty much told me I was on my own as it's not something they support! I'm not a DBA and would feel nervous about updating tables in my SQL Database.

What do other people do? Do you just make sure the collations match and leave it at that? Do you go to the trouble of upgrading the tables in the DB to match the newly supported collation (This seems like the right thing to do, but how!) or is there another approach I'm missing?

I look forward to any thoughts and suggestions.

David

dpad

Hello David,

I'm on the same predicament, we'll be upgrading and migrating environment to a new one this year. The current one has a different collation since it runs on SQL Server 2012. I called support and informed me that they would recommend just starting out a new DB instance, although this relates not only to SQL collation but other data that are messed up.

I've heard some ways before to change collation but I've never tried it.  I saw this post and it seems a manual way didn't work:

Alter SQL Collation of Solarwinds Database

If anyone already experienced this then please let us know the pros and cons.

Thanks!