DAC Framework: Data Tier Application (DACPAC) deployments incorrectly report unmodified DEFAULT constraints as conflicts during change detection - by steve-jansen

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


7
0
Sign in
to vote
ID 737191 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 4/16/2012 2:01:05 PM
Access Restriction Public

Description

Overview
--------
The Data-tier Application (DAC) framework incorrectly handles in-line DEFAULT contraints created by a Data-tier application deployment.  This bug affects SQL Server Management Studio 2008 R2 SP1 (SSMS), SQL PowerShell scripts, and other framework implementations such as Visual Studio 2010 SP1.   

Deploying a DACPAC and immediately upgrading the data-tier application using the same DACPAC file should result in no changes being detected by the DAC framework.  However, the DAC framework will report in-line constraints as having been changed externally since the DACPAC deployment.  The DAC framework with create an upgrade plan that DROPs and reCREATEs all in-line DEFAULT constraints.


Impact
------
This is a side-effect of redeploying the same DACPAC file due to Bug 737150 (deploy the DACPAC, then upgrade from the same DACPAC) to execute the DACPAC post-deployment step.  However, this bug somewhat degrades the business value of detecting external changes to a SQL Data-tier application, as it the change detection will always have false positives for any database that uses column DEFAULTs.  


Workarounds
-----------
There are no known workarounds for this bug.


Reference
----------
As reference, SQL Server T-SQL supports three methods for the definition of DEFAULT constraints.  

1) The inline method defines the DEFAULTS for a table within the CREATE TABLE DDL statement.  The inline method does not support explicit names for DEFAULT constraints, which differs from other constraints such as primary and foreign keys.  Inline DEFAULTS are partially supported by the DAC framework.  For example:

CREATE TABLE [MyTable]
( [Id] INT NOT NULL IDENTITY(1,1),
  [Name] NVARCHAR(50) NOT NULL,
  [CreatedOn] DATETIME NOT NULL DEFAULT GETDATE(),
  CONSTRAINT [PK_MyTable] PRIMARY KEY ([Id])
);

Because the inline default constraint is not explicity named, SQL Server assigns a system-generated name, such as [DF__MyTable__539830a48fa0342543b6].


2) SQL Server supports the explicit naming of a default constraint via the ALTER TABLE DDL.  However, ALTER TABLE DDL is not supported by the DAC framework.

ALTER TABLE [MyTable] ADD CONSTRAINT  [DF_customname] DEFAULT GETDATE() FOR [CreatedOn]


3) SQL Server supports creation of non-ANSI DEFAULT objects that can be bound to one or more column objects.  However, CREATE DEFAULT is not supported by the DAC framework.

CREATE DEFAULT [DF_getdate] AS
GETDATE();
GO
EXEC sp_bindefault [DF_getdate], '[MyTable].[CreatedOn]'
GO


Analysis
--------
While the DAC framework does support deployment of ANSI-style COLUMN default values, the DAC framework appears to stumble on the system generated names, such as [DF__MyTable__539830a48fa0342543b6].  The DAC framework appears to consistently apply a system generated name.  Perhaps the logic for determining the generated name should be applied to a DEFAULT constraint before comparing a DACPAC against a database?



Dependencies
------------
As reference, the following components were used to reproduce the problem on a single machine:

Windows 7 SP1 32 bit - v6.1.7601
Microsoft .NET Framework - v4.0.30319.261
SQL Server 2008 R2 Management Studio - v10.50.2500.0
Microsoft SQL Server 2008 R2 (SP1) Express - 10.50.2500.0 (Intel X86)
Microsoft.SqlServer.Management.SqlParser.dll - 10.50.2500.0
Microsoft.SqlServer.Management.Dac.dll - 10.50.2500.0
Microsoft.SqlServer.Management.DacEnum.dll - 10.50.2500.0


Sign in to post a comment.
Posted by Microsoft on 1/16/2013 at 8:55 AM
Steve,

Thanks for reporting this issue. The root of the issue is DACFx via the SSMS Deploy DACPAC path not appropriately handling textual differences that arise from the SQL Server engine's normalization of certain scripts. The current workaround is to use the DAC Verbs features inside SSDT. You can leverage the same Deploy DACPAC functionality through SQL Server Object Explorer in SSDT. You can also avoid this issue by using SqlPackage.exe to deploy the DACPAC.

Please let us know if you have any additional questions in this space or other feedback regarding SSDT/DACFx!

Thanks,
Adam Mahood
Program Manager
SQL Server Database Systems - Service Management