Apology
First off, apologies for length - this is a weird issue so I'm trying to give enough detail to make it reproducible.
tl;dr
So I've think found an issue while exporting a *.bacpac Data Tier Application between two MS SQL Servers to migrate an instance of an Indeo ProGet database which creates an inconsistent bacpac. Trying to import this bacpac results in an error, and I'd appreciate a second pairs of eyes on the problem to make sure I'm not doing anything daft...
I think the root cause is a possible bug in the "Export data-tier application" process when there are multiple schema columns using the same User Defined Type, and that UDT is bound to a validation Rule - the result seems to be a corrupt *.bacpac file that can't be imported without doing some manual tweaking first.
My question is:
- Part 1: Am I doing something wrong, or is this a known (or new) bug?
- Part 2: If it's a bug, do you have any idea where I can report it?
Schema
The following re-creates a small part of the schema from the Inedo ProGet database that demonstrates the issue. (FWIW, I'm currently using SQL Server 2022 v16.0.1105.1, but I'm pretty sure it occurs in other versions as well).
It basically does this:
- Creates a User Defined Type
YNINDICATOR - Binds it to a rule that restricts the values to
YandN - Creates two tables that each have a column of type
YNINDICATOR(see[dbo].[CustomLanguage].[Active_Indicator]and[dbo].[ClusterNodes].[Primary_Indicator])
CREATE TYPE [dbo].[YNINDICATOR] FROM [char](1) NULL
GO
CREATE RULE [dbo].[YNINDICATOR_Domain]
AS
@Ind COLLATE Latin1_General_BIN IN ('Y', 'N');
EXEC sp_bindrule 'YNINDICATOR_Domain', 'YNINDICATOR'
GO
CREATE TABLE [dbo].[CustomLanguages](
[CustomLanguage_Id] [int] IDENTITY(1,1) NOT NULL,
[Culture_Name] [varchar](50) NOT NULL,
[Language_Name] [nvarchar](100) NOT NULL,
[Active_Indicator] [dbo].[YNINDICATOR] NOT NULL,
[CustomLanguage_Xml] [xml] NOT NULL,
CONSTRAINT [PK__CustomLanguages] PRIMARY KEY CLUSTERED
(
[CustomLanguage_Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[ClusterNodes](
[Server_Name] [nvarchar](64) NOT NULL,
[NodeType_Code] [char](1) NOT NULL,
[LastUpdated_Date] [datetime] NOT NULL,
[Primary_Indicator] [dbo].[YNINDICATOR] NOT NULL,
[Node_Configuration] [xml] NOT NULL,
CONSTRAINT [PK__ClusterNodes] PRIMARY KEY CLUSTERED
(
[Server_Name] ASC,
[NodeType_Code] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[ClusterNodes] WITH CHECK ADD CONSTRAINT [CK__ClusterNodes__NodeType_Code] CHECK (([NodeType_Code]='S' OR [NodeType_Code]='W'))
GO
ALTER TABLE [dbo].[ClusterNodes] CHECK CONSTRAINT [CK__ClusterNodes__NodeType_Code]
GO
Error
Exporting the above schema into a Data Tier Application bacpac file completes without any errors, but attempting to re-import it as a new database gives the following error:
Microsoft SQL Server Management Studio
Could not import package.
Error SQL72014: Framework Microsoft SqlDataClient Data Provider: MSG 2714, Level 16, State 3, Procedure YNINDICATOR_Domain, Line 1 There is already an object named 'YNINDICATOR_Domain' in the database.
Error SQL72045: Script execution error. The executed script:
CREATE RULE [dbo].[YINDICATOR_Domain]
AS @Ind COLLATE Latin1_General_BIN IN ('Y', 'N')
(Microsoft.SqlServer.Dac)
Investigation
I've unzipped the bacpac and found that when there is more than one column defined using the YNINDICATOR UDT, the model.xml file inside the bacpac contains something like this, with two SqlRule elements defined for [dbo].[YNINDICATOR_Domain] - the first contains the column references, and the second contains the UDT reference:
Broken bacpac - two column references and a UDT reference -> two SqlRule elements
... snip ...
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
<Property Name="ExpressionScript">
<Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
</Property>
<Relationship Name="BoundTargets">
<Entry>
<References Name="[dbo].[CustomLanguages].[Active_Indicator]" />
</Entry>
<Entry>
<References Name="[dbo].[ClusterNodes].[Primary_Indicator]" />
</Entry>
</Relationship>
<Relationship Name="Schema">
<Entry>
<References ExternalSource="BuiltIns" Name="[dbo]" />
</Entry>
</Relationship>
</Element>
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
<Property Name="ExpressionScript">
<Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
</Property>
<Relationship Name="BoundTargets">
<Entry>
<References Name="[dbo].[YNINDICATOR]" />
</Entry>
</Relationship>
<Relationship Name="Schema">
<Entry>
<References ExternalSource="BuiltIns" Name="[dbo]" />
</Entry>
</Relationship>
</Element>
... snip ...
This is presumably the cause of the already an object named 'YINDICATOR_Domain' error because it tries to create the SqlRule twice - once for each Element.
If I DROP either of the columns using the YNINDICATOR UDT and re-export a Data Tier Application, the bacpac contains a single Element Type="SqlRule" node which contains both the column and UDT References:
Working bacpac - one column reference and a UDT reference -> one SqlRule element
... snip ...
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
<Property Name="ExpressionScript">
<Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
</Property>
<Relationship Name="BoundTargets">
<Entry>
<References Name="[dbo].[CustomLanguages].[Active_Indicator]" />
</Entry>
<Entry>
<References Name="[dbo].[YNINDICATOR]" />
</Entry>
</Relationship>
<Relationship Name="Schema">
<Entry>
<References ExternalSource="BuiltIns" Name="[dbo]" />
</Entry>
</Relationship>
</Element>
... snip ...
and if I use sp_unbindrule to remove the binding the bacpac contains just a single Element - it no longer creates a second one for the UDT binding as it obviously doesn't exist any more:
Working bacpac - two column references and no UDT reference -> one SqlRule elements
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
<Property Name="ExpressionScript">
<Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
</Property>
<Relationship Name="BoundTargets">
<Entry>
<References Name="[dbo].[CustomLanguages].[Active_Indicator]" />
</Entry>
<Entry>
<References Name="[dbo].[ClusterNodes].[Primary_Indicator]" />
</Entry>
</Relationship>
<Relationship Name="Schema">
<Entry>
<References ExternalSource="BuiltIns" Name="[dbo]" />
</Entry>
</Relationship>
</Element>
I can't really use either of these options though as it doesn't match the actual database schema for the ProGet application, but it's interesting to note how the column and UDT binding get serialised depending on whether there's one or more-than-one column using the UDT.
Workaround
If I manually hack around with the model.xml in the original bacpac file and merge the two SqlRule Elements so there's only one that contains the columns and Rule binding I can import it fine and it creates all the schema objects without error:
This isn't very useful though as I don't want to have to keep doing this each time I migrate the database (I won't be doing it many times, but enough for this to be a painful workaround).
Working bacpac - two column references and a UDT reference hacked into one SqlRule element
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
<Property Name="ExpressionScript">
<Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
</Property>
<Relationship Name="BoundTargets">
<Entry>
<References Name="[dbo].[CustomLanguages].[Active_Indicator]" />
</Entry>
<Entry>
<References Name="[dbo].[ClusterNodes].[Primary_Indicator]" />
</Entry>
<Entry>
<References Name="[dbo].[YNINDICATOR]" />
</Entry>
</Relationship>
<Relationship Name="Schema">
<Entry>
<References ExternalSource="BuiltIns" Name="[dbo]" />
</Entry>
</Relationship>
</Element>
Side note: if I then re-export this database it reverts back to the original form of 2 SqlRule elements - one that contains the columns and one that contains the rule binding.
Update
The problem also happens with the Az CLI:
PS> az version
{
"azure-cli": "2.56.0",
"azure-cli-core": "2.56.0",
"azure-cli-telemetry": "1.1.0",
"extensions": {
"storage-preview": "1.0.0b1"
}
}
PS> az sql db export `
--subscription "My Subscription" `
--resource-group "my-resource-group" `
--server "my-sql-server" `
--name "ProGet" `
--admin-user "my-admin-user" `
--admin-password "my-admin-password" `
--storage-uri "https://mystorageaccount.blob.core.windows.net/proget-db-backups/proget.bacpac" ``
--storage-key-type "StorageAccessKey" `
--storage-key "my storage key"
Downloading the blob and unpacking it shows the problem with multiple SqlRule Elements.
Recap
If you've got this far then thanks for reading, and just to recap, my question in the "tl;dr" at the top was:
- Part 1: Am I doing something wrong, or is this a known (or new) bug?
- Part 2: If it's a bug, do you have any idea where I can report it?
Any help greatly appreciated.
RULEobjects were a backwards-compatibility feature back in SQL Server 2000 and deprecated for about 20 years. I suspect a bug because since it's unlikely they've been well-tested in all the SQL tools. You can submit a bug here.