We're updating the issue view to help you get more done. 

SQLServer unlimited length string column should use NVARCHAR(max)

Description

AO still uses NTEXT for unlimited length string columns on SQLServer databases.

But Ii's been deprecated since SQLServer 2005, see https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016#features-not-supported-in-a-future-version-of-sql-server

As o SQLServer 2016 it still exists, but no guarantees for next version.

Suggest removing it now in favour of NVARCHAR(max).

Some considerations to test to ensure that no breaking change, so possible to include in minor releases of products, such as JIRA 7.4.0 instead of having to wait until 8.0.0

  • New unlimited length string columns added as NVARCHAR(max) not NTEXT on SQLServer database.

  • Existing unlimited length string columns that are NTEXT, are not changed. They stay NTEXT on SQLServer database.

  • AO operations function without changes, and without data loss. NB: The jdbc data type remains as CLOB (2005) for both NTEXT and NVARCHAR(max) with JTDS driver but is reported as NVARCHAR wuth Microsoft jdbc driver, so ensure both handled.

  • QueryDSL operations work without changes, as well

  • Data export from Jira Cloud and import into a SQLServer Server instance, works. The tables are all created using NVARCHAR(max) and no data loss

  • Ensure that unlimited length column can not be used as a foreign key constraint, and therefore can not have any problems with NVARCHAR(max) <-> NTEXT. This should be true, but verify.

Note, that this change should not affect any other databases (SQLServer only) and does not affect any limited string length columns.

Update path

If anyone wants to convert all existing tables to NVARCHAR(max), as we don't want AO to do this implicitly, the path will be similar to where suggest to manually run the ALTER COLUMN statements, or perform a data export to XML and then reimport it.

Environment

Testing Notes

Add notes...

Status

Assignee

Matthew McMahon

Reporter

Matthew McMahon

Labels

Fix versions

Priority

Major