Update deprecated data types for MS SQL Server

Suggest new features
#1

The TEXT, IMAGE, and NTEXT data types have now been marked as deprecated for several versions of MS SQL Server. SonarQube still uses them for a few tables and columns, which may currently cause performance issues and will make it incompatible with future versions of SQL Server.

Here are the affected columns, based on a DMA 2019 scan of a SonarQube 8.9.3 LTS database on SQL Server 2017:

Object dbo.file_sources.binary_data uses deprecated data type TEXT, IMAGE or NTEXT which will be discontinued for future versions of SQL Server. For more details, please see: Line 9, Column 5.
Object dbo.issues.locations uses deprecated data type TEXT, IMAGE or NTEXT which will be discontinued for future versions of SQL Server. For more details, please see: Line 26, Column 5.
Object dbo.notifications.data uses deprecated data type TEXT, IMAGE or NTEXT which will be discontinued for future versions of SQL Server. For more details, please see: Line 2, Column 5.
Object dbo.project_measures.measure_data uses deprecated data type TEXT, IMAGE or NTEXT which will be discontinued for future versions of SQL Server. For more details, please see: Line 8, Column 5.

These should be updated to varchar(max), nvarchar(max), or varbinary (max) as detailed in https://go.microsoft.com/fwlink/?LinkId=798558 .

Thanks!

#4

Hello @cba ,

I did start a fresh copy of SonarQube 8.9.3 with MSSQL 2017 and all tables you mention have appropriate data type.

Could you tell me since which version you are using SQ? Maybe it would help me narrow down to some specific version and reproduce the issue.

#5

Hi Jacek,

Thanks for looking into this. We started with SQ version 6.4, and have carried the DB forward through updates since then. the update path we took between 6.4 and 7.9.2 is a bit murky since it was mostly a manual process back then. Since then, we’ve one through these updates, mostly following the LTS path:
7.9.2
7.9.3
7.9.4
8.9
8.9.1
8.9.3

Cheers,
Chris

#6

Hi @jacek.poreda,

Any update on this? If I compare our 8.9.3 database to a fresh install, and then create some Alter Table scripts to bring the deprecated data types in line with the current setup, would that work? Or do you see any risk in that?

Thanks,
Chris

#7

Thanks for that information, I will double check on my side - sorry for the delay.

I will also verify if manually executing SQL script won’t break anything.

#8

Thanks for your patience @cba .

I was able to reproduce the issue, by running migrations from: 4.5.7 -> 5.6.7 -> 6.7.7 -> 7.9.6 and I can confirm that problem can occur for old instances. I’ve created a ticket to track this: SONAR-15780

I think that manually updating types of those column shouldn’t hurt, though I would advise you to wait for a release with bug fix.

2 Likes
#9

Sounds good, thanks!

I’ll wait for the next LTS release.

1 Like
#10

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.