Installation and Upgrade > Upgrade Reference > Changing SQL Server Database Collation
  
Changing SQL Server Database Collation
Upgrading a SQL Server database to version 2012 or later requires a change of the existing database collation. Use these instructions to change the SQL Server database collation for the following databases:
• Changing Windchill SQL Server Database Collation
• Changing Windchill Business Reporting (WBR) SQL Server Database Collation (only upgrades to SQL Server 2012 is supported)
Changing Windchill SQL Server Database Collation
If you are planning to upgrade a Windchill SQL Server database to version 2012 or later, you must change the existing database collation from "SQL_Latin1_General_CP1_CS_AS" to the new, supported collation "Latin1_General_100_CS_AS_SC". This new collation has support for UTF-16 encoding and supplemental characters. If both Windchill and the database are upgraded at the same time, then PTC recommends that you perform the collation conversion steps before upgrading Windchill. Perform the following steps to change the database collation to Latin1_General_100_CS_AS_SC:
1. Stop the Windchill server and related services.
2. Back up Windchill and related databases.
3. Upgrade the SQL Server instance to SQL Server version 2012 or later.
4. Open a command prompt from the SQL Server 2012 (or later) database server, and change directory to SQL Server Collation Conversion Utility installation folder (For example, E:\ptc\Windchill\SQLServer\collation_conversion).
5. Optional, but highly recommended: Create a read-only database snapshot for the upgraded Windchill database. Having a database snapshot will save a lot of time and effort in case there is a failure while executing collation conversion scripts and you need to revert the database to its original state. The following batch files in the collation_converstion folder can be used to create and restore the database snapshot:
createDBSnapshot.cmd
restoreDBSnapshot.cmd
Although the snapshots are very useful, there is also a drawback, the snapshot will require the same amount of disk space as the original database on the database server, but the disk space can be recovered by deleting the database snapshot once the collation conversion is complete.
More information about SQL Server database snapshots can be found at the following URLs:
◦ Creating a database snapshot: http://msdn.microsoft.com/en-us/library/ms175876.aspx
◦ Reverting a database to a database snapshot: http://msdn.microsoft.com/en-us/library/ms189281.aspx
◦ Dropping a database snapshot: http://msdn.microsoft.com/en-us/library/ms190220.aspx
6. Execute the AlterDatabaseCollation.cmd batch file and provide the upgraded 2012 (or later) database information when prompted. Enter target Application Name Windchill, when prompted. Verify no error message is reported.
7. Start SQL Server Management Studio and log in to the 2012 (or later) server as the Windchill database user. Execute the following queries to verify that collation was changed successfully:
8. On the Windchill Server, open a Windchill shell and execute the following commands to change the Windchill database connection properties to the destination SQL Server 2012 (or later) database server:
xconfmanager -s wt.pom.dbUser=<Windchill_DB_USERNAME> -t "db/db.properties"
xconfmanager -s wt.pom.dbPassword=<Windchill_DB_USER_PASSWORD> -t "db/db.properties"
xconfmanager -s wt.pomjdbc.database=<Windchill_DB_Name> -t "db/db.properties"
xconfmanager -s wt.pom.jdbc.host=<SQL2102 or later_Database_HostName> -t "db/db.properties"
xconfmanager -s wt.pom.jdbc.port=<SQL2102 or later_Database_Post_Numnber> -t "db/db.properties"
xconfmanager -s wt.pom.jdbc.service=<SQL2102 or later_Database_Instance_Name> -t "db/db.properties"
[not required for default instance]
xconfmanager -p
9. Start Windchill and verify that it starts successfully without any exceptions.
Changing Windchill Business Reporting (WBR) SQL Server Database Collation
If you are planning to upgrade a Windchill Business Reporting (WBR) SQL Server database to version 2012 (or later), you must change the existing database collation from "SQL_Latin1_General_CP1_CI_AS" to the supported collation "Latin1_General_100_CI_AS". If both Windchill and the database are upgraded at the same time, then PTC recommends that you perform the collation conversion steps before upgrading Windchill. Perform the following steps to change the database collation to Latin1_General_100_CI_AS:
1. Stop Windchill Business Reporting (WBR).
2. Back up the Windchill Business Reporting SQL Server database.
3. Only if the Windchill database and Windchill Business Reporting are on different servers, upgrade the Windchill Business Reporting SQL Server instance to SQL Server 2012 (or later).
4. Open a command prompt from the SQL Server 2012 (or later) database server, and change directory to SQL Server Collation Conversion Utility installation folder (For example, E:\ptc\Windchill\SQLServer\collation_conversion).
5. Optional, but highly recommended: Create a read-only database snapshot for the upgraded Windchill Business Reporting database. Having a database snapshot will save a lot of time and effort in case there is a failure while executing collation conversion scripts and you need to revert the database to its original state. The following batch files in the collation_converstion folder can be used to create and restore the database snapshot:
createDBSnapshot.cmd
restoreDBSnapshot.cmd
Although the snapshots are very useful, there is also a drawback: the snapshot will require the same amount of disk space as the original database on the database server, but the disk space can be recovered by deleting the database snapshot once the collation conversion is complete.
More information about SQL Server database snapshots can be found at the following URLs:
◦ Creating a database snapshot: http://msdn.microsoft.com/en-us/library/ms175876.aspx
◦ Reverting a database to a database snapshot: http://msdn.microsoft.com/en-us/library/ms189281.aspx
◦ Dropping a database snapshot: http://msdn.microsoft.com/en-us/library/ms190220.aspx
6. Execute the AlterDatabaseCollation.cmd batch file and provide the upgraded 2012 database information when prompted. Enter Application Name WBR, when prompted for the target application. Verify no error message is reported.
7. Start SQL Server Management Studio and log in to the 2012 (or later) database server as the Windchill Business Reporting database user. Execute the following queries to verify that collation was changed successfully:
8. Using Cognos Configuration, update the value of the following configurations, if applicable:
◦ Cognos Username and Password
◦ SQL Server Database Name
◦ SQL Server Host Name and Port, or SQL Server database instance
9. Start Windchill Business Reporting and verify that it starts successfully without any exceptions.