source controlsql serversql-server-2016ssdt
Does SQL Server SSDT Visual Studio 2017 Database Projects support Source control data for lookup tables, or do we still have to utilize messy Post-Deployment Merge Scripts for multiple tables?
Redgate has a more finesse, solution. Wondering when MS will catch up.
Microsoft has started work on this and there is a signup page for a preview version. In the meantime you may want to look at DBUp and FiveTran, although I haven't worked with either of these.
Update: As of Aug 2019, it is publicly available in preview. https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-install-visual-studio
You'll want to vote for this thread:
After a fair amount of frustration, finally found the cause, and posting as an answer as it may help others....
Finding the reason for why SSDT thought the scheme was different I think is the first point of call. SQL Scheme compare is your friend here. (In VS under Tools => SQL => New Scheme Comparison...)
Firstly thanks to @jadarnel27, was very helpful so upvote dude cheers, but wasn't the answer I'm afraid. Computed column definitions and constraint definitions were definitely candidates, as was potentially column ordering. Ironically I did have one issue with a computed column but it was not the definition, it was the fact that I didn't add NOT NULL to the end of the column definition (which is the default of course) but SSDT saw that as different everytime.
So I had to change a column from
[ColumnName] AS (CONCAT(Col1,' ',Col2)) PERSISTED
to
[ColumnName] AS (CONCAT(Col1,' ',Col2)) PERSISTED NOT NULL
then it stopped constantly dropping and re-creating the column. This showed up in Scheme Compare, not in the definition of the computed column.
Secondly, because we were using replication, SQL was adding NOT FOR REPLICATION to most of the tables that were being used in replication (reasons for is to do with the works of replication) but in essence was changing a tables DDL from
CREATE TABLE [dbo].[Whatever]
(
[WhateverId] INT IDENTITY(1,1) NOT NULL
...etc more columns
)
to
CREATE TABLE [dbo].[Whatever]
(
[WhateverId] INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
...etc more columns
)
again this showed up on Scheme compare......
and to fix, either add NOT FOR REPLICATION into the DDL source code in VS for all tables required or under the publish settings -> Advance -> Ignore tab and scroll down a bit, tick the box as below:
and then everything was disco
Best Answer
There is no specific built-in support for lookup data in SSDT at this time. As you noted in the "related" links in your question, the currently accepted practice is to script out lookup data in post deploy scripts using
MERGEstatements. This can become unpleasant as the size of lookup data grows.Microsoft actually mentions "reference data" as a primary example of the use of post deployment scripts. See How to: Specify Predeployment or Postdeployment Scripts:
You could potentially go outside of SSDT and introduce SSIS packages with flat file sources. This might be a better experience depending on the preferences and expertise of those on your team.