locked
When / Where / If to change Collation RRS feed

  • Question

  • Greetings :)

    I've been through the pain of upgrading sql 2000 databases to sql 2012 (via 2008r2) although the new box (sql2012) is not live yet.

    I notice the COLLATION of the migrated user databases is SQL_Latin1_General_CP1_CI_AS and the server/system collation is Latin1_General_CI_AS.

    QUESTIONS:

    1) does it matter? they're almost the same

    2) how would I have changed an individual databases (and contents) collation during the upgrade? (IE: backup / restore onto 2008r2 (autoupgrade), backup / restore onto 2012 (autoupgrade)). I'm happy to go through the pain again if best.

    3) wouldn't it now be easier to just change the server and system databases to match the upgraded user databases (SQL_Latin1_General_CP1_CI_AS)?

    4) and wouldn't 3) above ensure continued smooth operations with the rest of the environment? (EG: Reporting Server)

    Richard







    Thursday, July 31, 2014 2:34 PM

Answers

  • Hi Richard,

    Based  on your description, SQL Server lets us store data in different collations, and we can set this at both the server level and at the individual database level. But when we join between different databases, or when we create a temp table and then compare the temp table data to the data of a user database in a different collation, conflict may occurs.

    When you restore a database, RESTORE uses the collation of the source database that is recorded in the backup file. The restored database has the same collation as the original database that is backed up. During the process of restoring database, you cannot  change the collation of the database. However, you can change the collation of the database(and contents) in SQL Server 2012 after restoring. You can create a script to change the collation of every object, and create a stored procedure to script all the objects (primary keys, foreign keys, constraints, indexes), then run these scripts in SQL Server 2012. For more details, you can follow the steps in this blog: Easy way to change collation of all database objects in SQL Server. Please make sure that you have backed up the database before changing the collation.

    The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server, and also any newly created user databases. The server collation is specified during SQL Server installation. Actually, it is a complex operation to change the default collation for an instance of SQL Server, I recommend you not to change the server collation. For more details, please review this article: Set or Change the Server Collation.

    If you change  the server collation to a new collation,  the system databases (contains master, model, msdb and tempdb)  will be rebuilt using the new collation setting. Any user modifications to these databases will be lost, so it is important to backup any of this information you wish to retain.



    Thanks,
    Lydia Zhang


    Friday, August 1, 2014 6:04 AM
  • Thanks Lydia

    I think I'm going to backup the upgraded databases, remove and reinstall SQL 2012 specifying the collation 'SQL_Latin1_General_CP1_CI_AS'. Then restore the backups.

    Richard


    Friday, August 1, 2014 9:20 AM

All replies

  • Hi Richard,

    Based  on your description, SQL Server lets us store data in different collations, and we can set this at both the server level and at the individual database level. But when we join between different databases, or when we create a temp table and then compare the temp table data to the data of a user database in a different collation, conflict may occurs.

    When you restore a database, RESTORE uses the collation of the source database that is recorded in the backup file. The restored database has the same collation as the original database that is backed up. During the process of restoring database, you cannot  change the collation of the database. However, you can change the collation of the database(and contents) in SQL Server 2012 after restoring. You can create a script to change the collation of every object, and create a stored procedure to script all the objects (primary keys, foreign keys, constraints, indexes), then run these scripts in SQL Server 2012. For more details, you can follow the steps in this blog: Easy way to change collation of all database objects in SQL Server. Please make sure that you have backed up the database before changing the collation.

    The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server, and also any newly created user databases. The server collation is specified during SQL Server installation. Actually, it is a complex operation to change the default collation for an instance of SQL Server, I recommend you not to change the server collation. For more details, please review this article: Set or Change the Server Collation.

    If you change  the server collation to a new collation,  the system databases (contains master, model, msdb and tempdb)  will be rebuilt using the new collation setting. Any user modifications to these databases will be lost, so it is important to backup any of this information you wish to retain.



    Thanks,
    Lydia Zhang


    Friday, August 1, 2014 6:04 AM
  • Thanks Lydia

    I think I'm going to backup the upgraded databases, remove and reinstall SQL 2012 specifying the collation 'SQL_Latin1_General_CP1_CI_AS'. Then restore the backups.

    Richard


    Friday, August 1, 2014 9:20 AM