search

ErikEJ / erikej.github.io

ErikEJ blog
3 stars 1 forks source link

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

Open ErikEJ opened 4 years ago

ErikEJ commented 4 years ago

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

mymatrixpixel commented 4 years ago

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 commented 4 years ago

What takes time - the upload or the restore?

Sitanshu71 commented 3 years ago

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

ErikEJ commented 3 years ago

@Sitanshu71 No.

ErikEJ commented 3 years ago

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

vyrotek commented 3 years ago

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 commented 3 years ago

@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 commented 3 years ago

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

hastiAu commented 3 years ago

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 commented 3 years ago

@hastiAu Use sqlpackage for publishing (as always)

hastiAu commented 3 years ago

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

is this for publish too ? or not

ErikEJ commented 3 years ago

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

hastiAu commented 3 years ago

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 commented 3 years ago

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

hastiAu commented 3 years ago

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 commented 3 years ago

My initial reply remains: https://github.com/ErikEJ/erikej.github.io/issues/8#issuecomment-753945341

hastiAu commented 3 years ago

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 commented 3 years ago

@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 commented 3 years ago

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 commented 3 years ago

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

hastiAu commented 3 years ago

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 commented 3 years ago

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

hastiAu commented 3 years ago

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 commented 3 years ago

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

hastiAu commented 3 years ago

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

ErikEJ commented 3 years ago

@hastiAu Should = recommended

hastiAu commented 3 years ago

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 commented 3 years ago

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

hastiAu commented 3 years ago

@ErikEJ Thank so much

hastiAu commented 3 years ago

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 commented 3 years ago

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

ErikEJ commented 3 years ago

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

iSeiryu commented 3 years ago

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 commented 3 years ago

@iSeiryu This is discussed and solutions provided here: https://github.com/rr-wfm/MSBuild.Sdk.SqlProj/issues/64

dustrat commented 3 years ago

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 commented 3 years ago

@dustrat post build event or similar.

Or linked item and then build action none.

dustrat commented 3 years ago

@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.

SQLBurro commented 2 years ago

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 commented 2 years ago

@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 commented 2 years ago

@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 commented 2 years ago

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

auraboldizsar commented 2 years ago

@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 commented 2 years ago

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

auraboldizsar commented 2 years ago

@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 commented 2 years ago

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

Ryanman commented 2 years ago

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!