Sql-server – SSDT publish multiple referenced database projects to new instance

sql serversql-server-2012ssdt

I am going through the process of importing our existing SQL Server databases into SQL Server Data Tools in Visual Studio 2015 with the aim of putting them under version control.

All the database projects in my solution build correctly after splitting databases with circular references into composite projects. My problem is now trying to deploy them all to a new instance, the publishing is failing because the databases being referenced don't exist e.g.

SQL72014: .Net SqlClient Data Provider: Msg 208, Level 16, State 1,
Procedure Foo, Line 13 Invalid object name
'DB1.dbo.Bar'.

If I try to deploy DB1 then it's same thing as that also references another database which hasn't been created yet

SQL72014: .Net SqlClient Data Provider: Msg 208, Level 16, State 1,
Procedure Bar, Line 64 Invalid object name
'DB2.dbo.Foo'

How do I resolve this so I can publish all database projects in my solution to a new instance?

Best Answer

I've been able to get all databases in my solution to publish by doing the following:

Setting the correct connection string and publish options under Project Properties > Debug for each project in my solution.
Assigning multiple startup projects in Solution Properties
Debugging the solution will then build/deploy all databases in the correct order

Though this doesn't feel like it is the correct way to achieve this as it now means I need to modify all my projects if I want to publish to a different server.

Related Solutions

Sql-server – Registering a SSDT database project as a data tier application fails

I had this error recently so I'll add my solution for anyone else who comes across this,

Turns out in my publish.xml I had RegisterDataTierApplication set to True. The first time I published the database it worked fine, but then I got the same error, as the database was already registered as a Data Tier application.

By setting to false (or unchecking the checkbox in the gui) it works fine.

 <?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>foo</TargetDatabaseName>
    <DeployScriptFileName>foo.sql</DeployScriptFileName>
    <BlockWhenDriftDetected>False</BlockWhenDriftDetected>
    <RegisterDataTierApplication>False</RegisterDataTierApplication>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <TargetConnectionString>Data Source=foo;Integrated Security=True;Pooling=False</TargetConnectionString>
  </PropertyGroup>
</Project>

As a note about extracting the dacpac - you will always get an error if extracting via ssms,

If you use the SQL Server Object Explorer in VisualStudio, you can set more options, including unchecking the 'Verify Extraction' option, which will then allow you to create the dacpac.

Sql-server – Publishing database changes with SSDT that include altering data types of columns

I've been tempted to bypass that flag also but have come down on the side of your co-workers and now try to deal with these issues "correctly". The (marginally) less clunky route is to use pre and post deployment scripts to do the work with a rename.

Rename the existing table in a pre-deployment script.
With the existing table missing the table in focus will be created as per the new schema definition.
In a post-deployment script copy from the renamed original table to the new version.

Depending on the nature of the target you may of course need to take care of dropping and recreating foreign key constraints.

Best Answer

Related Solutions

Sql-server – Registering a SSDT database project as a data tier application fails

Sql-server – Publishing database changes with SSDT that include altering data types of columns

Related Question