Bacpac file won't restore due to columnstore indexes on columns with specific data types.

Question

Situation:

We are using Serverless Azure SQL Databases as projects. Afterwards we delete these databases but before we do that we create backups of the databases. We are using SqlPackage.exe (version: 162.3.566) to create bacpac files.

Restoring bacpac files back to our Azure subscription works fine, except for following situations:

• When a database contains a clustered columnstore index on a table that contains following datatypes: varchar(max) , nvarchar(max), or varbinary

in this situation we can successful create bacpacs but we are NOT able to restore the bacpacs. It gives following errors:

Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 1919, Level 16, State 1, Line 1 Column 'vcharmax' in table 'dbo.Table_ColumnstoreIndex_01' is of a type that is invalid for use as a key column in an index.
Error SQL72045: Script execution error.  The executed script:
CREATE CLUSTERED INDEX [CCI_Table_ColumnstoreIndex_01]
    ON [dbo].[Table_ColumnstoreIndex_01]([vcharmax]);
CREATE CLUSTERED COLUMNSTORE INDEX [CCI_Table_ColumnstoreIndex_01]
    ON [dbo].[Table_ColumnstoreIndex_01] WITH (DROP_EXISTING = ON);

The same error appears when using nvarchar(max) and varbinary.

I understand that we should prevent making clustered columnstore indexes on these kind of columns, but why is it in the first place allowed to make these indexes that gives us issues in our backup databases.

Anyone experiencing the same issues or know how to fix this problem?

Answer 1

It seems that something goes wrong on the tool side when the BACPAC is created or restored. This statement:

CREATE CLUSTERED INDEX [CCI_Table_ColumnstoreIndex_01]     ON [dbo].[Table_ColumnstoreIndex_01]([vcharmax]);

should not be there. Since the data type varchar(MAX) is not supported for rowstore clustered index, this statement gives an error. But would the data type be a supported one, the next statement to create the clustered columnstore index would fail, since you can only have one clustered index on a table.

I cannot do much more than recommend you to report a bug here: https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0If the issue is more pressing for you, and you need this to be fixed, you should open a support case.

Answer 2

Hi Mark van Eck,

Thanks for reaching out to Microsoft Q&A.

SQL Server allows the creation of clustered columnstore indexes on tables, but there are specific restrictions regarding the data types of the columns that can be used as key columns in these indexes. Specifically, varchar(max), nvarchar(max), and varbinary(max) are not valid for use as key columns in a clustered columnstore index. This is why you are ableto create the bacpac files without issue, but facing error during restoration. The errors you see (SQL72014 & SQL72045) indicate that the column types used in the index are invalid for this purpose.

I understand that we should prevent making clustered columnstore indexes on these kind of columns, but why is it in the first place allowed to make these indexes that gives us issues in our backup databases.

SQLServer engine permits the creation of such indexes despite the restrictions on restoration because it does not enforce these rules until a restoration, or a similar operation is attempted. This design choice can lead to situations where developers may unknowingly creating indexes that are not fully supported, leading to issues later when trying to restore or use those indexes.

Anyone experiencing the same issues or know how to fix this problem

• When designing tables that will use clustered columnstore indexes, ensure that the indexed columns do not use varchar(max), nvarchar(max), and varbinary(max). Instead, use fixed length or smaller var length data types that are supported.
• Implement validation checks in your database design process to prevent the creation of indexes that could lead to restoration issues.
• If you have databases with existing CCIs on these column types, consider modifying the schema before taking the backup. You can drop such indexes or change them to supported configurations.
• In some cases where you cannot modify the schema due to business constraints, make sure you have a error handling and remediation process in place. This might involve manual steps to fix the schema post-restoration or using scripts to adjust the indexes.

hth! let me know if you have further questions.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.