I'm building an ASP.NET Core 2.0 Web API application that is hosted in an Ubuntu environment. So far, I've had great success getting things building and running (for the .NET Core app) in Ubuntu.
For the database, I have a SqlProj included in my solution. The project includes typical things such as tables, SPs, and pre/post deployment scripts. I'm using the following command (on my Windows-based dev machine) to build and deploy this project:
msbuild .\MyProject.DB.sqlproj /t:Build /t:Publish /P:SqlPublishProfilePath="./PublishProfiles/MyProject.DB.publish.xml"
When I take this approach, everything builds and deploys properly; however, since I will be taking advantage of the .NET Core CLI commands + CI/CD that targets an Ubuntu environment, I'd like to do something more like:
dotnet msbuild .\MyProject.DB.sqlproj /t:Build /t:Publish /P:SqlPublishProfilePath="./PublishProfiles/MyProject.DB.publish.xml"
In Windows, I immediately get the error:
error MSB4019: The imported project "C:\Program Files\dotnet\sdk\2.1.4\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the path in the <Import> declaration is correct, and that the file exists on disk.
Basically, what I'm asking is how to successfully build and deploy a SqlProj project in an Ubuntu environment. I've tried Googling, but I have had zero luck thus far. All of the similar issues that I've found were for individuals who were editing their .proj file to target their VS folder's SSDT. All of these individuals were fixing the issue in Windows. This approach will not work in Ubuntu, since the targets file uses Windows registry keys.
EDIT: I'm aware that SSDT is needed in order to perform such a deployment using MSBuild. I've found no evidence that installing/using SSDT is even possible in Ubuntu. If it is not, perhaps there is an alternative solution?
FYI, I'm aware that using a code-first approach with EF Core is possible. I'm attempting to take the raw SP approach (along with leveraging indexes) and keep track of all of my code using SqlProj instead. This will all be stored and CI/CDed from a Git repo.
7条答案
按热度按时间kiz8lqtg1#
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
eufgjt7s2#
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://learn.microsoft.com/en-us/azure/devops/pipelines/apps/aspnet/build-aspnet-dacpac?view=azure-devops
sqougxex3#
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:
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
or using a profile generated by visual studio :
or
dm7nw8vv4#
Azure Data Studio now has an extension that lets you build database projects (sqlproj) using the dotnet tool. The brains behind building the project lies in the SQL Server Tools package, which is where the extension gets the required "BuildDirectory" DLL and targets dependencies.
Though not documented, if you want to set this up completely headless outside of Azure Data Studio, you can follow their CLI guide, https://learn.microsoft.com/en-us/sql/azure-data-studio/extensions/sql-database-project-extension-build-from-command-line?view=sql-server-ver15 , but instead extract the necessary files from the RHEL release in https://github.com/microsoft/sqltoolsservice/releases and then follow the rest of the extension's documentation. Here is a working Dockerfile that demonstrates the approach:
The commented command at the end shows what you could run inside the container in the directory with your database project.
This can also then be combined with a container utilizing sqlpackage to implement a full dacpac build and publish automation toolset.
b5lpy0ml5#
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.
6bc51xsx6#
As mentioned, the easiest way to build DacPac file on a linux agent is done via MSBuild.Sdk.SqlProj Go to your database project directory in parallel to .sqlproj file create a directory like
DB.Build
under it createDB.Build.csproj
copy.pase the content as belowAfter run you will see dacpac file appears under
DB.Build/bin/Release/netstandard2.0/DB.Build.dacpac
Here's my build agent output (Ubuntu agent on Azure devops)
Note: Make sure to restore you NuGet packages in step prior to build
enyaitl37#
The package often suggested by others, R&R WFM's MSBuild.Sdk.SqlProj does not come from Microsoft, and it doesn't really do a "real SSDT build". It does not use SSDT build tools, it is a separate 3rd party tool that can generate limited DACPAC packages with reduced features. This info does not come from my experience with that tool. I read it somewhere when I tried to research what options I have left as I'm stuck with DACPACs and Linux. While I can't confirm that info in any way, it looks true, repo doesn't have 'Microsoft' logos anywhere, and I read such warnings on multiple unrelated sites, posted by supposedly different people.
That being said, it's an awesome tool that certainly required huge amount of workand knowledge.
But there's another one as well.
Recently Microsoft started developing their own cross-platform SSDT build tool, Microsoft.Build.Sql coming from DacFx . At the time of writing this, it's still in
preview
and the current version is0.1.10
. You'll often see0.1.3
or0.1.7
if you search for articles about how to use that.Microsoft provided a nice guide how to convert an existing
.sqlproj
generated by VisualStudio+SSDT. It's concise, a bit too concise, and requires minor obvious fixes. The article also points to a minimalistic new-style .sqlproj file, the link is broken, but the file resides in a nearby folder .The article mentions that new-style projects support globbing (so Include="folder/**/*.sql" instead of listing all files), and that including all SQL files is already the default behavior, so an 'empty' project file like the sample one will grab all SQL files - but be careful, and read the final paragraph about compatibility with VisualStudio.
Using this info, I was able to convert my .sqlproj file. I did the steps explained there, and also I didn't remove any now-not-needed file inclusions, so VS is happy.
My .sqlproj file looks now like that:
That 'xxx' parts is just anonymization of course.
I was able to successfully build this project and get a DACPAC file by simple
or
I did not have to add
/p:NetCoreBuild=true
- the tooling in0.1.10
detects that it is ran fromdotnet
command and sets this automatically.This worked both when the build was done at my local dev machine (Win10, VS2022,
dotnet --version
= 7.0.302), and when ran on CI agent (Amazon Linux 2).The CI system was AWS CodeBuild and script used was:
Not much happening really. The relevant output parts were:
So it actually took 6.0, different than on my local. A bit odd, but it still built the dacpac.
Two things worth noting - while
dotnet build
is able to build a DACPAC, the tooling is "preview" and if I remember correcly, the docs at GitHub mention that not all features are supported. I have no idea how the feature gaps compare to the gaps in rr-wfm's nuget, but the tool is still developed, so there's hope :)The second thing to note is that while
dotnet build
is able to build the DACPAC, thedotnet publish
is NOT able to run it. Trying toat best currently ends up with following error
I get this error BOTH when I try to run the
dotnet publish
on my local dev machine (where everything worked), and obviously also when I try that at the CI agent.This is because the
dotnet publish
command isn't currently supported by the0.1.10
tooling. It probably will not be supported at all, for some probably important, probably internal reason.Instead of
dotnet publish
, a separate tool is provided by DacFx, called SqlPackage . It's advertised as the cross-platform tool for running DACPACs.It can be installed with
dotnet
:and it can
publish
the DACPAC, although with a bit different commandline syntax (see the previous link above for details):I have not tried installing and running this SqlPackage tool, but looks very promising.