Building SQL Server Database Project In Ubuntu

As an alternative, it is possible to achieve this with dotnet cli and sqlpackage as explained here using an MSBuild Sdk.

You basically have a database project. Let's call it "DatabaseProject".

You create a new project which is a .NET standard c# library that you can call "DatabaseProject.Build".

Then you can configure you DatabaseProject.Build.csproj as such:

<Project Sdk="MSBuild.Sdk.SqlProj/1.11.4">
   <PropertyGroup>
       <TargetFramework>netstandard2.0</TargetFramework>
       <Configurations>Debug;Release</Configurations>
   </PropertyGroup>
   <ItemGroup>
       <Content Include="..\DatabaseProject\**\*.sql" />
       <Content Remove="..\DatabaseProject\bin\*.sql" />
       <Content Remove="..\DatabaseProject\**\*.PostDeployment.sql" />
       <PostDeploy Include="..\DatabaseProject\**\*.PostDeployment.sql" />
   </ItemGroup>
</Project>

Be Aware The version used V1.11.4 is the one that supports the current .NET SDK shipped with visual studio at the time of the edit of this post. Check out the github repo to get the latest nuget version for your projet.

Using dotnet build will generate a dacpac that you will be able to use with either dotnet publish or sqlpackage.

You can then publish to you SqlServer instance. If you're like me using a linux runner in your CI, you'll probably need SqlServer authentification method and then run either

sqlpackage /Action:Publish \    
/SourceFile:\"DatabaseProject.Build/bin/Debug/netstandard2.0/DatabaseProject.Build.dacpac\" \
/TargetServerName:MyDatabaseServerName \
/TargetDatabaseName:MyDatabaseName \
/TargetUser:Username\
/TargetPassword:Password

or using a profile generated by visual studio :

sqlpackage /Action:Publish /Profile:\"DatabaseProject/PublishProfile/MyProfile.publish.xml\" /SourceFile:\"DatabaseProject.Build/bin/Debug/netstandard2.0/DatabaseProject.Build.dacpac\"

or

dotnet publish /p:TargetServerName=MyServerName /p:TargetDatabaseName=MyDatabseName /p:TargetUser=<username> /p:TargetPassword=<password>

You can use this NuGet package to deploy without installing SSDT https://www.nuget.org/packages/Microsoft.Data.Tools.Msbuild I don't know if it will run on Ubuntu or integrate at all with the dotnet cli

My 2020 Solution

I would like to revisit this in 2020 with an updated answer to my original question.

I have taken a different approach to building an deploying SQL Server projects. My current approach is to build a pipeline that uses a vs2017-win2016 agent and use this to build a .dacpac. From there, you build a deployment pipeline to deploy the dacpac (from your artifact drop) out to the SQL Server instance.

This approach better accommodates DevOps methodologies and removes the manual process associated with my previous solution.

You can read more about this here:

https://docs.microsoft.com/en-us/azure/devops/pipelines/apps/aspnet/build-aspnet-dacpac?view=azure-devops

I can't speak to whether or not this will work on Ubuntu, but we recently got through this on a Windows build machine that does not have SSDT installed, using the NuGet package mentioned above. The breakthrough came from piecing together the details in the article below, specifically that using the SDK with MSBuild needed to have environment variables set in order to work.

https://blogs.msdn.microsoft.com/ssdt/2016/08/22/part-5-use-your-own-build-and-deployment-agent/

With that added info, we installed the NuGet package in the root of the solution folder and then wrote a build script in PowerShell. The script sets the environment variables first and then calls MSBuild on the SqlProj file with the appropriate output directory. We don't specifically publish at that point, but instead publish the artifact to Octopus Deploy in our workflow which does the actual deployment.

Again, not sure it will help on Ubuntu, but thought the additional detail might be useful.