Starting OTRS version 3.1, OTRS uses the NVARCHAR data type rather than VARCHAR or TEXT, to store textual data. This is because the NVARCHAR type has full support for Unicode, whereas the old data types store data in UCS-2 format, which is a sub-set of Unicode. Also, the TEXT data type is deprecated since SQL Server 2005. Due to this, starting with OTRS version 3.1, the minimal SQL Server version required for operation with OTRS is now Microsoft SQL Server 2005.
Because dropping and re-creating these indexes is a time-consuming operation, especially on large databases, please plan enough time for performing the upgrade. We would recommend that you perform the upgrade on a copy of the database prior to doing the actual conversion to test the upgrade procedure and to time how much time will be needed on your specific environment.
Please make sure that, before you start, there is enough space available on the database server. Make sure the free space on your database server is at least 2.5x the current size of the database.
This upgrade procedure will upgrade all fields of the mentioned data types to the new types. This procedure first removes any indexes and constraints in which these fields are referenced, upgrades the fields, and then adds the indexes and constraints back. It will do so on all tables found in the SQL Server database that OTRS uses. If you would have stored non-OTRS tables in the OTRS database, and these tables contain columns of the data types VARCHAR or TEXT, these will also be updated.
Open a Command Line on the OTRS server.
Change directory to the OTRS root directory. If you're using the default OTRS installer this would be C:\Program Files\OTRS\OTRS.
Run the following command:
shell> perl scripts/DUpdate-to-3.1.mssql-datatypes.pl
This will generate three scripts in the specified directory scripts\database\update. Run these scripts on the SQL Server database, via SQL Server Management Studio or isql.