Forum Discussion

CadenL's avatar
CadenL
Level 6
8 years ago

Potential SQL mixed collation??

Hi

I'm planning to move EV databases between an SQL2008  server and SQL2012 server to allow for an EV12.x upgrade.

The existing SQL 2008 server has a collation of SQL_Latin1_GENERAL_CP1_CI_AS and the new server has Latin1_General_CI_AS. Would this be viewed by EV as a mixed collation?

My understanding is that the default collation is set dependent on the locale for the server when the OS was installed and a US locale will use  SQL_Latin1_GENERAL_CP1_CI_AS  whereas UK will not have the SQL prefix and display the CP1. But both collations are Latin1 General Accent Sensitive and Case Insensitive - which is all that EV needs to compare?

thanks in advance

  • GertjanA's avatar
    GertjanA
    8 years ago

    Hello Caden,

    DON'T you will get a collation issue. Been there, done that.

    Verify on the current databases the collation setting, and make sure the collation on the new server is identical. If you don't do this you will not be able to upgrade.

    Having the proper collation on the new server will prevent numerous issues down the road.

    I believe (I'm no DBA!) that you can set the SQL Server collation at the time of installing, independently of the OS setting.

  • GertjanA's avatar
    GertjanA
    Moderator
    8 years ago

    Hello Caden,

    DON'T you will get a collation issue. Been there, done that.

    Verify on the current databases the collation setting, and make sure the collation on the new server is identical. If you don't do this you will not be able to upgrade.

    Having the proper collation on the new server will prevent numerous issues down the road.

    I believe (I'm no DBA!) that you can set the SQL Server collation at the time of installing, independently of the OS setting.

    • CadenL's avatar
      CadenL
      Level 6
      8 years ago

      Thanks Gertjan

      I'll look to do that then.

      I'm not 100% certain but I think the only way I'm going to be able to get SQL_Latin1_General_CP1_CI_AS from the drop down list is if I set the OS locale to US before I install SQL. If I leave the server as UK then I only get the option for Latin1_General_CI_AS within the list. Which is what I was saying about it being dependent on the OS locale..... I'm not certain of this and it will be easy to test by running through a mock SQL2012 install to see what my options are for.

      Presumably EV deployment scanner will pick this up anyway.

       

      • GertjanA's avatar
        GertjanA
        Moderator
        8 years ago

        Hi C.

        An easy test would be to install the server with the different collation, coy one of the EV databases (Directory as example) to it, and then run Deployment scanner against the new SQL server. That will show the issue.