Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Build (and publish) a .dacpac (SQL Server database project) with .NET Core - even on Linux or macOS! #8

Open
ErikEJ opened this issue May 8, 2020 · 46 comments
Labels
comment Blog comment

Comments

@ErikEJ
Copy link
Owner

ErikEJ commented May 8, 2020

https://erikej.github.io/efcore/2020/05/11/ssdt-dacpac-netcore.html

@mymatrixpixel
Copy link

Hi Erik,

By using Microsoft.Azure.Management.Sql preview nuget package am trying to take *.bacpac file and uploading directly into azure blob storage. Like wise I want to restore the Azure SQL Database. But I see performance issue. (600MB db is taking 30mins). Can you please say any other best approach.

@ErikEJ
Copy link
Owner Author

ErikEJ commented May 12, 2020

What takes time - the upload or the restore?

@Sitanshu71
Copy link

Is it possible not to publish a project to a SQL Server when using the command "dotnet publish" ?

@ErikEJ
Copy link
Owner Author

ErikEJ commented Dec 9, 2020

@Sitanshu71 No.

@ErikEJ
Copy link
Owner Author

ErikEJ commented Dec 9, 2020

@Sitanshu71 if you just want to create a .dacpac, just use "dotnet build'

@vyrotek
Copy link

vyrotek commented Dec 29, 2020

Hi @ErikEJ. How does this approach compare to how the Azure Data Studio extension builds a .sqlproj?

https://docs.microsoft.com/en-us/sql/azure-data-studio/extensions/sql-database-project-extension-build-from-command-line?view=sql-server-ver15#prerequisites

It appears the extension makes different proj xml changes in order to support 'dotnet build'. If this extension works on Linux then shouldn't it be possible to mimic it on AzureDevOps? It seems the crucial part is somehow providing the same DLLs the extension stores in the BuildDirectory.

@ErikEJ
Copy link
Owner Author

ErikEJ commented Dec 29, 2020

@vyrotek This approach is simpler, as no external copying of files is required, but obviously the functionality overlap.

I assume it should be possible to also run the extension based build in Azure DevOps yes, better go ask in the Azure Data Studio github repo though.

@ErikEJ
Copy link
Owner Author

ErikEJ commented Dec 29, 2020

@vyrotek also notice that this project was launched months before the ADS extension was launched

@hastiAu
Copy link

hastiAu commented Jan 4, 2021

Hi
Is this for publish my data base on server?
I use visual studio and azure data studio on mac

now after finish my application I want to publish it on server
but I confused how can I do it ?
specially publish my data base, because as you know on mac we use with docker

@ErikEJ
Copy link
Owner Author

ErikEJ commented Jan 4, 2021

@hastiAu Use sqlpackage for publishing (as always)

@hastiAu
Copy link

hastiAu commented Jan 4, 2021

on Azure data studio
I see this option 👍 Data-tier Applications

is this for publish too ? or not

@ErikEJ
Copy link
Owner Author

ErikEJ commented Jan 4, 2021

@hastiAu Mainly for manual publish, not for pipeline based (automated) publishing

@hastiAu
Copy link

hastiAu commented Jan 4, 2021

and how about :SQL Database Projects extension?

Sorry I don t understand
You mean both of them are for publish
but which one is manually ? which one automated?

@ErikEJ
Copy link
Owner Author

ErikEJ commented Jan 4, 2021

SQL Database projects extension can be used for manual publish - what exactly are you looking for?

@hastiAu
Copy link

hastiAu commented Jan 4, 2021

I move to mac recently
I know on windows after get back up pf db we can copy on Host.

but I don t understand on mac
because on mac I use sql (On azure data studio ) with docker.
so now how can get my database or what is the way for publish my application(That I develop it with dot.net core ) and its database?

This is my question.
and now on confuse with:
SQL Database projects extension

&

sqlpackage

Best regards for your answer

@ErikEJ
Copy link
Owner Author

ErikEJ commented Jan 4, 2021

My initial reply remains: #8 (comment)

@hastiAu
Copy link

hastiAu commented Jan 4, 2021

Ok I see
on this link yes?
(https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver15

and i follow this command:

$ cd ~
$ mkdir sqlpackage
$ unzip ~/Downloads/sqlpackage-linux-.zip -d ~/sqlpackage
$ echo "export PATH="$PATH:$HOME/sqlpackage"" >> ~/.bashrc
$ chmod a+x ~/sqlpackage/sqlpackage
$ source ~/.bashrc
$ sqlpackage

but with write : sqlpackage

nothings happend.
sorry for my initial question. I m junior ;)

and tell me with this way I can give backup file of my db? or it publish my db?

@ErikEJ
Copy link
Owner Author

ErikEJ commented Jan 4, 2021

@hastiAu Please ask Microsoft for support for sqlpackage. And sqlpackage is for publishing a .dacpac (or a .bacpac) - if you have a backup file, you must use the SQL RESTORE command.

@hastiAu
Copy link

hastiAu commented Jan 4, 2021

I have backup file on azure data studio ( it s on docker )
I cant give back up on this path : /var/opt/mssql/data
but I cant find this path and I think because it s on container.
is it correct ?

and after restore just restore this backup on azure data studio.
I need this back up for publish on server.

@ErikEJ
Copy link
Owner Author

ErikEJ commented Jan 4, 2021

@hastiAu I suggest you ask these general support questions on a Microsoft support forum or StackOverflow.

@hastiAu
Copy link

hastiAu commented Jan 4, 2021

Thanks so much
Please answer my last question
sqlpackage. is not just for sql azure?

and can I use this for my sqlserver ( that I use it with azure data studio on docker)?

@ErikEJ
Copy link
Owner Author

ErikEJ commented Jan 4, 2021

@hastiAu Yes, you can use sqlpackage with any version and edition of SQL Server / Azure SQL

@hastiAu
Copy link

hastiAu commented Jan 4, 2021

Thanks so much
And is it correct ?
If i want to publish manually :
first I should create a dacbac file with : SQL Database Projects extension

then with data-tire application wizard , can publish
right ?

@ErikEJ
Copy link
Owner Author

ErikEJ commented Jan 4, 2021

@hastiAu You should use automated processes for database deployment, not GUI tools

@hastiAu
Copy link

hastiAu commented Jan 4, 2021

should means : it s not correct ?
or is better that use automated process?

@ErikEJ
Copy link
Owner Author

ErikEJ commented Jan 4, 2021

@hastiAu Should = recommended

@hastiAu
Copy link

hastiAu commented Jan 4, 2021

because I am junior I prefer GUI
But I will use after progress.

so with my solution now I have these files:

DatabaseProjectEShopCore_DB.dacpac
DatabaseProjectEShopCore_DB.dll
DatabaseProjectEShopCore_DB.pdb

so I should upload this file to server?( instance of backup on windows)
and change connection string on appsetting file?

is it correct ?

@ErikEJ
Copy link
Owner Author

ErikEJ commented Jan 4, 2021

@hastiAu Please ask your general support questions in a public forum

@hastiAu
Copy link

hastiAu commented Jan 4, 2021

@ErikEJ Thank so much

@hastiAu
Copy link

hastiAu commented Jan 8, 2021

Hi a gain
Data-tire application wizard on azure data studio is instance of sqlpackage.? and with it I can transfer my database on server?

@aaronscribner
Copy link

How exactly do I deploy the dacpac to a SQL instance running on Docker on OS X?

@ErikEJ
Copy link
Owner Author

ErikEJ commented Mar 23, 2021

@aaronscribner run sqlpackage with a connection string pointing to your SQL instance.

@iSeiryu
Copy link

iSeiryu commented Apr 17, 2021

Hi @ErikEJ,

My existing .sqlproj builds no problem (together with the whole solution). My goal is to build it in a docker container. Something like this

FROM mcr.microsoft.com/mssql/server:2019-latest

ENV ACCEPT_EULA=Y
ENV SA_PASSWORD=Passw0rd!

USER root
RUN wget https://packages.microsoft.com/config/ubuntu/20.04/packages-microsoft-prod.deb -O packages-microsoft-prod.deb && \
    dpkg -i packages-microsoft-prod.deb

RUN apt-get update; \
    apt-get install -y apt-transport-https && \
    apt-get update && \
    apt-get install -y dotnet-sdk-2.1

WORKDIR /src
COPY . .
RUN dotnet build

After adding a .csproj with netstandard and building the sln via dotnet build I got a lot of

ModelValidationError error SQL71501: SqlView: [dbo].[my_table_or_view] has an unresolved 
reference to object [sys].[internal_tables]

There are also [sys].[dm_db_partition_stats], [sys].[schemas], and some others.

The original sqlproj contains these lines

<ItemGroup>
    <ArtifactReference Include="$(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\120\SqlSchemas\master.dacpac">
      <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
      <DatabaseVariableLiteralValue>master</DatabaseVariableLiteralValue>
    </ArtifactReference>
    <ArtifactReference Include="$(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\120\SqlSchemas\msdb.dacpac">
      <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
      <DatabaseVariableLiteralValue>msdb</DatabaseVariableLiteralValue>
    </ArtifactReference>
</ItemGroup>

I'm assuming my issues are the result of the new Core project not being able to see these references. Not to mention they do not even exist in the MSSQL image.

I could probably disable validation somehow (did not find a way yet), but it does not sound like a good option.
Any ideas how this could be achieved or at least how to avoid getting these errors?

P.S. I do get exactly the same errors running locally on Win and inside the container created by the Dockerfile above.

@ErikEJ
Copy link
Owner Author

ErikEJ commented Apr 17, 2021

@iSeiryu This is discussed and solutions provided here: rr-wfm/MSBuild.Sdk.SqlProj#64

@dustrat
Copy link

dustrat commented Jun 24, 2021

I have created a project building a dacpac successfully. I have a Test project that needs to reference the dacpac file but if the DB project is referenced by the Test project I will get a build error saying "image is either too small or contains an invalid byte offset or count" (CS0009). Any tips? I could probably solve it by copying the dacpac file in a post build event, but that's what I'm trying to avoid.

@ErikEJ
Copy link
Owner Author

ErikEJ commented Jun 24, 2021

@dustrat post build event or similar.

Or linked item and then build action none.

@dustrat
Copy link

dustrat commented Jun 24, 2021

@dustrat post build event or similar.

Or linked item and then build action none.

Yes, linked item is at least better than a post build event. Seems to work! Thanks.

@ErikEJ ErikEJ changed the title Discussion Build (and publish) a .dacpac (SQL Server database project) with .NET Core - even on Linux or macOS! Jan 26, 2022
Copy link

How would this work where you have a large number of development teams all making changes to the same database on a 24/7 work schedule?

@ErikEJ
Copy link
Owner Author

ErikEJ commented Feb 24, 2022

@SQLBurro as long as you have a good proces where you always deploy your master branch and understand and manage radical changes, I do not see any major issues. Happy to engage with you to help you further.

@davidcorbin-atmosera
Copy link

@erik - can you think of any established way to have just a tree of .sql files (without any type of "project file") and create the dacpac (ideally cross-platform). A team does not currently use visual studio at all and you like (yes, I am well aware of the issue) to be able to just add/edit/delete .sql files in their git repository and then update the target database...

Right no the only thing I have though of is to "Synthesize" a set of project files and then use this tooling...but I am trying to not have to re-invent (and thus support) the wheel.)

@ErikEJ
Copy link
Owner Author

ErikEJ commented Mar 23, 2022

@dcorbin-wintellect Can you just not simply place a .csproj with a global *.sql file pattern at the root of the folder structure?

Copy link

@erik please see below the issue I experience.

error : Unable to find package MSBuild.Sdk.SqlProj. No packages exist with this id in source(s): nuget.org [16:41:50][ Target "_FilterRestoreGraphProjectInputItems"] MSB4236: The SDK 'MSBuild.Sdk.SqlProj/2.0.0' specified could not be found.
[16:41:50][ Target "_FilterRestoreGraphProjectInputItems"] MSB4236: The SDK 'MSBuild.Sdk.SqlProj/2.0.0' specified could not be found.

https://user-images.githubusercontent.com/99972517/173237758-9ab2d22f-43a6-45d5-8d7b-141cae140c0b.png

Definitely not trying to waste your time, but use this SDK and make it work so that I can advise other teams in my company how to properly use it. The people responsible with the CI/CD setup said they've added both MSBuild.SDK.SqlProj and MSBuild.SDK.SqlProj.templates to the internal artifactory and installed the project templates on the TeamCity agent but that did not fix the restore issue.

@ErikEJ
Copy link
Owner Author

ErikEJ commented Jun 12, 2022

@auraboldizsar it works if you have access to nuget.org so I cannot see how I can help to be honest.

@auraboldizsar
Copy link

@ErikEJ after discussing with the team looking after the Team City platform, I found out the process is to download and install the SDKs into an internal artifacts platform and eventually the SDKs will be retrieved from there. Unfortunately at the moment the MSBuild.Sdk.SqlProj SDK doesn't get resolved. Do you see any concerns on the setup this team is having?

@ErikEJ
Copy link
Owner Author

ErikEJ commented Jun 14, 2022

I do not know anything about Team City but it looks like something is broken.

Copy link

Ryanman commented Aug 18, 2022

Came across this blog post a couple years later. I can't suggest enough going to the SDK Repo itself:
https://github.com/rr-wfm/MSBuild.Sdk.SqlProj

And following the initial installation/scaffolding instructions there. They support up to .NET 5.0 at this point I believe as opposed to .NET standard and are a major version ahead of this article. They also have more information about handling common things like pre-and-post scripts.

Also neither here nor there, but the community package was stricter on build than the original .NET build! When implementing this in our project, it caught an incorrectly-architected view.

Having the projects side by side for some of the VS support is what we'll do for the forseeable future, but being able to have a "simple" project compatible with VS Code is huge.

Thanks for the post!

@ErikEJ ErikEJ added the comment Blog comment label Feb 17, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
comment Blog comment
Projects
None yet
Development

No branches or pull requests