Build (and publish) a .dacpac (SQL Server database project) with .NET Core - even on Linux or macOS!
Opened this issue ยท 46 comments
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.
What takes time - the upload or the restore?
Is it possible not to publish a project to a SQL Server when using the command "dotnet publish" ?
@Sitanshu71 No.
@Sitanshu71 if you just want to create a .dacpac, just use "dotnet build'
Hi @ErikEJ. How does this approach compare to how the Azure Data Studio extension builds a .sqlproj?
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.
@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.
@vyrotek also notice that this project was launched months before the ADS extension was launched
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
on Azure data studio
I see this option ๐ Data-tier Applications
is this for publish too ? or not
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?
SQL Database projects extension can be used for manual publish - what exactly are you looking for?
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
My initial reply remains: #8 (comment)
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?
@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.
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.
@hastiAu I suggest you ask these general support questions on a Microsoft support forum or StackOverflow.
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)?
@hastiAu Yes, you can use sqlpackage with any version and edition of SQL Server / Azure SQL
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 ?
should means : it s not correct ?
or is better that use automated process?
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 ?
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?
How exactly do I deploy the dacpac to a SQL instance running on Docker on OS X?
@aaronscribner run sqlpackage with a connection string pointing to your SQL instance.
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.
@iSeiryu This is discussed and solutions provided here: rr-wfm/MSBuild.Sdk.SqlProj#64
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.
@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.
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?
@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.
@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.)
@dcorbin-wintellect Can you just not simply place a .csproj with a global *.sql file pattern at the root of the folder structure?
@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.
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.
@auraboldizsar it works if you have access to nuget.org so I cannot see how I can help to be honest.
@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?
I do not know anything about Team City but it looks like something is broken.
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!