Must-share information (formatted with Markdown):
- which versions are you using: SonarQube 9.0 Enterprise Edition
- how is SonarQube deployed: ZIP
- what are you trying to achieve: Migrating our database from the built-in PostgreSQL database to an external MS SQL Server
- what have you tried so far to achieve this: We created the schema in MS SQL Server by editing the sonar.properties to point SonarQube 9.0 to an empty database. From there, we used “SQL Server Import and Export Wizard” to attempt to migrate the data, but we’re running into errors here that appear to be related to data type conversion.
Here are two examples of errors that we’re seeing.
Example 1
TITLE: SQL Server Import and Export Wizard
------------------------------
Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.
"sonarqube"."public"."components" -> [dbo].[components]:
- The data type could not be assigned to the column "created_at" in "SQL Server Native Client 11.0".
"sonarqube"."public"."groups" -> [dbo].[groups]:
- The data type could not be assigned to the column "created_at" in "SQL Server Native Client 11.0".
- The data type could not be assigned to the column "updated_at" in "SQL Server Native Client 11.0".
"sonarqube"."public"."perm_templates_groups" -> [dbo].[perm_templates_groups]:
- The data type could not be assigned to the column "created_at" in "SQL Server Native Client 11.0".
- The data type could not be assigned to the column "updated_at" in "SQL Server Native Client 11.0".
"sonarqube"."public"."perm_templates_users" -> [dbo].[perm_templates_users]:
- The data type could not be assigned to the column "created_at" in "SQL Server Native Client 11.0".
- The data type could not be assigned to the column "updated_at" in "SQL Server Native Client 11.0".
"sonarqube"."public"."permission_templates" -> [dbo].[permission_templates]:
- The data type could not be assigned to the column "created_at" in "SQL Server Native Client 11.0".
- The data type could not be assigned to the column "updated_at" in "SQL Server Native Client 11.0".
"sonarqube"."public"."quality_gate_conditions" -> [dbo].[quality_gate_conditions]:
- The data type could not be assigned to the column "created_at" in "SQL Server Native Client 11.0".
- The data type could not be assigned to the column "updated_at" in "SQL Server Native Client 11.0".
"sonarqube"."public"."quality_gates" -> [dbo].[quality_gates]:
- The data type could not be assigned to the column "created_at" in "SQL Server Native Client 11.0".
- The data type could not be assigned to the column "updated_at" in "SQL Server Native Client 11.0".
"sonarqube"."public"."rules_profiles" -> [dbo].[rules_profiles]:
- The data type could not be assigned to the column "created_at" in "SQL Server Native Client 11.0".
- The data type could not be assigned to the column "updated_at" in "SQL Server Native Client 11.0".
------------------------------
BUTTONS:
OK
------------------------------
Example 2
- Pre-execute (Error)
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
(SQL Server Import and Export Wizard)
Error 0xc002f446: Data Flow Task 1: An error occurred while setting up a binding for the "value" column. The binding status was "DT_NTEXT". The data flow column type is "DBBINDSTATUS_UNSUPPORTEDCONVERSION". The conversion from the OLE DB type of "DBTYPE_IUNKNOWN" to the destination column type of "DBTYPE_WVARCHAR" might not be supported by this provider.
(SQL Server Import and Export Wizard)
Error 0xc0202025: Data Flow Task 1: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task 1: Destination - active_rule_parameters failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)
Information 0x4004300b: Data Flow Task 1: "Destination - active_rule_parameters" wrote 0 rows.
(SQL Server Import and Export Wizard)
Information 0x4004300b: Data Flow Task 1: "Destination 1 - active_rules" wrote 0 rows.
(SQL Server Import and Export Wizard)
Information 0x4004300b: Data Flow Task 1: "Destination 2 - alm_pats" wrote 0 rows.
(SQL Server Import and Export Wizard)
Information 0x4004300b: Data Flow Task 1: "Destination 3 - alm_settings" wrote 0 rows.
(SQL Server Import and Export Wizard)
Information 0x4004300b: Data Flow Task 1: "Destination 4 - analysis_properties" wrote 0 rows.
(SQL Server Import and Export Wizard)
These errors were experienced just from the first table, and even when increasing the max length in certain columns, we’re continuing to hit these errors. We’re concerned that, even if we do manage to work through all 69 tables and manually change data types, that it won’t be 100% perfect, and there could be issues down the line with SonarQube.
We don’t have commercial support, so we can’t use the SonarQube DB Copy Tool.
Has anyone gone through this conversion before and have recommendations for how to work through it?
Thank you.
