Mssql-docker: Where did /opt/mssql/bin/sqlpackage go?

[spinningarrow]

I just pulled the latest microsoft/mssql-server-linux image and it doesn't appear to have the sqlpackage binary any more. It used to be at /opt/mssql/bin. Was it moved somewhere else?

[twright-msft]

It has been removed temporarily. We are going to be updating DacFx to work on top of dotnet core and then sqlpackage will run on top of the new DacFx. Sqlpackage will also be moved to it's own package or the mssql-tools package. For now you can use sqlpackage on a Windows machine. Sorry for the temporary inconvenience.

[spinningarrow]

I don't have access to a Windows machine. This makes the mssql-docker image unusable for me. Is there a way I can go back to an older version that does have sqlpackage in it?

[twright-msft]

Yes, you can pull/run the mssql-server-linux:ctp2-1 image.

[twright-msft]

Here's a couple of other options until we get this available again. Depending on your scenario, these might work just as well.
1) restore an existing .bak file into the new image using the sqlcmd CLI tool. More info here: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools
2) generate a T-SQL script to create the database and insert data. Then execute the T-SQL script with sqlcmd. The script can be generated with the “Generate Script” wizard in SSMS on Windows or the new Python-based mssql-scripter CLI tool on any OS.

[sanagama]

@spinningarrow, can you share more details about your scenario? How are you using sqlpackage?

[ikemtz]

I'm also using sqlpackage and had to revert back to ctp2-1. Personally, docker is a beautiful thing in that it allows you to downgrade as quickly/simply as you can.

As far as my particular scenario, I use it on my across my team to run our local development environments. My solution uses a mixture mongo and sql and I use docker-compose to stand up all the required infrastructure locally.

The workflow is the following. We use Visual Studio along with sql database projects that produce our dacpacs. If someone on the team needs to make a change to the schema, it's as simple as make the change, build the dacpac, then docker-compose, build and up. It's a really great solution.

We added two additional developers to the team in the last week and I've never seen anything better in my 15+ years of software development.

[ikemtz]

@twright-msft you mentioned that sqlpackage is in progress of an update, do you happen to know the timeline? The reason I ask is because I'm having some issues deploying a rather complicated schema.

[twright-msft]

Thanks for sharing your story @pcalchi. Docker is beautiful. I'd be curious to see your Dockerfiles, entrypoint scripts, or docker-compose files if you wouldnt mind sharing them via pastebin or something.

Yes, Sqlpackage is getting updated to run on top of the new cross-platform DacFX and to be packaged differently. That doesnt necessarily mean that we will be changing things around how a DACPAC is deployed though. If you want to share the details of that with us via StackOverflow or something we could take a look at it.

[sanagama]

@pcalchi, since you're using Visual Studio and Database Projects to produce the dacpac, is running sqlpackage on Windows to deploy the dacpac to SQL running in Docker an option for your scenario?

[apboyle]

@sanagama @twright-msft, I've also had to pin my Dockerfile to a previous CTP so that I can continue to use sqlpackage.
In my case I'm building up an image containing a copy of our database for use during integration testing.

The Dockerfile includes something like:

RUN <script that downloads dacpac artifacts from TeamCity to /scripts folder>

RUN /opt/mssql/bin/sqlserver.sh & sleep 20 \
&& /opt/mssql/bin/sqlpackage /Action:Publish /TargetServerName:localhost /TargetUser:SA /TargetPassword:$SA_PASSWORD /SourceFile:/scripts/dacpac/MyDB.dacpac /Profile:/scripts/dacpac/MyDB.publish.xml /p:BlockOnPossibleDataLoss=false \
&& sleep 20 \
&& pkill sqlservr && sleep 10
&& rm -rf /scripts

The resulting image is used in a docker-compose file along with our application code. We can then quickly and easily stand up a fresh database every time we run through our integration tests.

I hope this is a useful example for you :)

[spinningarrow]

Sorry for the awfully late reply everyone, and thanks for chiming in. Here's my update:

Yes, you can pull/run the mssql-server-linux:ctp2-1 image.

@twright-msft This works perfectly for me at the moment. I was looking for something like this, but being new to Docker, I wasn't able to easily find a list of tags for an image. 🙈

can you share more details about your scenario? How are you using sqlpackage?

@sanagama I was using it to load in a small test database from a bacpac file when the sqlserver container starts, basically in order to have a small database for local development/testing.

[twright-msft]

You probably already figured this out @spinningarrow, but for others, if you go to Docker Hub you can see a list of tags on the Tags tab.
https://hub.docker.com/r/microsoft/mssql-server-linux/tags/
Docker Store has the same thing as do other container catalogs like the Red Hat Container Catalog.
If you dont specify a tag then the image tagged as :latest will be pulled.

[jmcelwain]

Hi @twright-msft, is there a better place to follow progress on the "new cross-platform DacFX"? I'm not super familiar with the MS ecosystem. Can this be tracked in dotnet/core?

We are super excited to be able to use these build tools cross-platform!

[twright-msft]

We havent moved dacfx into the open source domain yet so it's hard to follow the progress. We are working on it though...

[eturan]

Hi @twright-msft

with the latest update (2017-GA and 2017-latest) /opt/mssql/bin/sqlpackage executable is gone again. Is it in the image or removed completely?

Best,
E.T.

[twright-msft]

Removed completely for now. Will put it back in the next few months.

[jordanhenderson]

ctp-2.1 is about to expire. I am still using sqlpackage and rely on it extensively for deployments.
Are there any other alternatives at this stage other than using Windows to prepare scripts?

[twright-msft]

We are working on sqlpackage on Linux right now. I've seen it working. Maybe could provide you a preview drop of it. Shoot me a mail at twright @ reallybigsoftwarecompanyname dot com. How much more time do you have left on ctp2-1?

[jordanhenderson]

Thank you, will do. We have ~11 days left in the evaluation period.

[necrosisbb]

I am in a similar situation we have been deploying using sqlpackage (our whole pipeline builds dacpac files and deploys to docker image with to mssql-docker on linux before running tests after the full env is brought up with docker-compse). We have been doing this for most of the year and are also sweating on the expiry time of ctp2-1, so it would be great if a we could use a preview as well.

[twright-msft]

@necrosisbb - ok please contact me through email and I'll see what we can do

[xkrt]

@twright-msft why not to publish the sqlpackage as docker image to docker hub with some 'beta' version tag?

[borgdylan]

@twright-msft Will the new sqlpackage (once it hits preview/GA) be added to the tools packages? I would not like to have to install sql server on each dev machine. I prefer hosting dev DBs on one machine and only have the tools like sqlcmd installed on all machines.

[twright-msft]

Yes, it will be either in its own package or in the mssql-tools package. We might put it in its own package because it is fairly large and it has a dependency on dotnet core. Input on that decision is welcomed. :)

[weishenaustralia]

@twright-msft , ctp2.1 is going to expire in a few days. Before the new version (including sqlpackage) comes out in a few months, I think it's a good idea to release another evaluation version of ctp2.1 (essentially just to extend expiration), so we can use it without struggling to emailing you to get the preview package, build an image and fix issues.

[nakah]

Any updates on SqlPackage availability in a container ?

[twright-msft]

Still in progress, but if you need an early copy, hit me up.

[borgdylan]

Are early test copies available outside a container, preferably as deb package or binary linux tarballs?

[twright-msft]

Yes, but just in a limited as needed basis for now. Shoot me an email at twright - microsoft and I'll follow up with you via email with the details.

[aleks091]

@twright-msft are you guys planning to add the SqlPackage.exe into microsoft/mssql-server-windows-developer:latest as well ?

I use SqlPackage.exe to generate an update script from the .dacpac in the container.

$SqlPackagePath = 'C:Program Files (x86)Microsoft SQL Server130DACbinSqlPackage.exe'
& $SqlPackagePath /SourceFile:DB_TEST.dacpac
/Action:script /OutputPath:deploy.sql /Properties:CommentOutSetVarDeclarations=true /TargetServerName:. /TargetDatabaseName:DB_TEST /TargetUser:sa /TargetPassword:$sa_password
/Variables:BasePath='C:PostSnapshotScripts' /Variables:DeploymentEnvironment='Dev'

I haven't been able to get SqlPackage.exe in my container using microsoft/mssql-server-windows-developer:latest so I had to downgrade to microsoft/mssql-server-windows-developer:2016-sp1

[twright-msft]

At some point yes, but its probably not imminent. What have you tried to get sqlpackage.exe into your container? Is there some issue with adding it via your own Dockerfile?

[aleks091]

@twright-msft Thanks for your quick response.

I managed to get SqlPackage by installing Microsoft.Data.Tools.Msbuild.
RUN & nuget install Microsoft.Data.Tools.Msbuild -Version 10.0.61026

Now I'm getting the following error when building my own SQLServer image with SQLEngine and Replication features:
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'User ManagerContainerAdministrator'..

[twright-msft]

@aleks091 - Just to make sure - You are talking about a SQL Server in _Windows_ container now?
nm: I see from your comments above that you are working in Windows.
Can you please share your Dockerfile and related resources - entrypoint scripts or whatever?

[aleks091]

@twright-msft

For some reason I get the error: Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'User ManagerContainerAdministrator'.. when using the latest FROM microsoft/windowsservercore

I finally got SqlServer with Replication enabled working on a windows container by using FROM microsoft/windowsservercore:10.0.14393.1480 when building my own dockerfile for SQLServer.

Here is my dockerfile: https://gist.github.com/aleks091/831f1ed01cb7ca9f0d5dbdbe313bbab7/

Also I was getting a permissions issue when trying to execute SqlPackage.exe to create an update.sql file from a .dacpac using a Microsoft.Data.Tools.Msbuild version that was not compatible with SqlServer 2017. This one was a bit tricky to figure out since the error log said that my log in failed. So I tried to use SqlServer 2017 with Microsoft.Data.Tools.Msbuild -Version 10.0.61710.120 to successfully create my update.sql file and create my DB.

After some research and trial and error. I finally got my personal SQL server project running in a windows container using own docker file for SqlServer with replication enabled.

Thank you.

[seanfdnn]

Hi @twright-msft , any forecast on when this will be put back into /opt/mssql/bin? Or better yet, open sourced, or a .net core code sample made available of how to achieve?

[twright-msft]

Pretty soon it will be back in the bin. Just going through testing now. Look for it in the CU5 or CU6 probably.

[seanfdnn]

Where does the 200GB limit on BACPAC size in Blob storage come from? Blob supports into the terabytes? Is there a way around this? (or should I file another issue?)

[twright-msft]

It seems that this used to be the limit in block blobs but since May 31, 2016 it is now. 4.75 TB
https://docs.microsoft.com/en-us/rest/api/storageservices/understanding-block-blobs--append-blobs--and-page-blobs

Are you actually hitting a limit or just referring to the limit that is documented here?
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-export

[seanfdnn]

Just referring to the limit that’s documented there – perhaps the documentation is out of date? That would be fantastic if that’s the case.

Sean

[ihs.com]https://ihsmarkit.com/

Sean Dunn, CD, P.Eng., PMP
Principal Software Engineer | Energy
1331 Macleod Tr SE | Calgary, AB, Canada | T2G 0K3 |
P: [phone]
sean.[email protected][e-mail]

[Facebook]http://facebook.com/Explore.IHS[Twitter]https://twitter.com/IHS[LinkedIn]http://linkedin.com/company/ihs[YouTube]http://youtube.com/ihschannel[IHS Blogs]http://blog.ihs.com/[Google+]https://plus.google.com/+IhsExplore

From: Travis Wright [mailto:[email protected]]
Sent: Thursday, February 8, 2018 9:30 PM
To: Microsoft/mssql-docker [e-mail]
Cc: Dunn, Sean [e-mail]; Comment [e-mail]
Subject: Re: [Microsoft/mssql-docker] Where did /opt/mssql/bin/sqlpackage go? (#135)

It seems that this used to be the limit in block blobs but since May 31, 2016 it is now. 4.75 TB
https://docs.microsoft.com/en-us/rest/api/storageservices/understanding-block-blobs--append-blobs--and-page-blobs

Are you actually hitting a limit or just referring to the limit that is documented here?
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-export

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHubhttps://github.com/Microsoft/mssql-docker/issues/135#issuecomment-364332053, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AQ-D7FEP2ZyiI8brUJUadslFOuHX6zUyks5tS8nbgaJpZM4OjYP9.

[twright-msft]

Yeah, my guess is that it's the docs are out of date. Are you going to give it a try? Let us know how it works out. If it seems to work OK, then we'll just update the docs to 4.75 TB.

[johnconger]

What is the status of this issue? Is there a version of sqlpackage available for linux? I have a fairly complex database project that I would like to build, publish & deploy to SQL server from my ubuntu machine. The rest of the application is running on dotnet core using vs code so I can deploy and publish everything except my database. I do not have access to a windows machine, so using sqlpackage.exe is not an option for me.

From the comments above, it sounds like there is a beta version of sqlpackage that can run on linux. Can someone point me to where that is?

[twright-msft]

Should be in the next couple of months. If you need something more immediately than that we have a beta version I can provide you. Shoot me a mail at twright a t m i c r o s o f t dot c o m.

[brianjorden]

I'm very anxious to see how the dacfx/msbuild/sqlpackage combo could be used to bring an SSDT type experience into VS Code or SQL Operations Studio. My hacked together version is what I've been playing with for the past week or so, but thought I was just missing something on getting it working besides in a Windows environment.

[twright-msft]

Hi all! We finally have a preview ready for sqlpackage that is built on dotnet core and is cross-platform! Below are the links to download from. They are evergreen links, i.e. each day a new build is uploaded. This way any checked in bug fix is available the next day. Included in the .zip file is the preview EULA.
linux
https://go.microsoft.com/fwlink/?linkid=873926
osx
https://go.microsoft.com/fwlink/?linkid=873927
windows
https://go.microsoft.com/fwlink/?linkid=873928
Release notes:

1. The /p:CommandTimeout parameter is hardcoded to 120
2. Build and deployment contributors are not supported
   a. Need to move to .NET Core 2.1 where System.ComponentModel.Composition.dll is supported
   b. Need to handle case-sensitive paths
3. SQL CLR UDT types are not supported.
   a. This includes SQL Server Types SqlGeography, SqlGeometry, & SqlHierarchyId
4. Older .dacpac and .bacpac files that use Json serialization are not supported
5. Referenced .dacpacs (e.g. master.dacpac) may not resolve due to issues with case-sensitive file systems

For lack of a better method, please provide any feedback you have here on this GitHub issue.

Thanks for giving it a try and letting us know how it goes!

[joshbooker]

@twright-msft Thanks for posting this. Is there any cross-plat sqlcmd.exe?

[twright-msft]

Yes!
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools?view=sql-server-linux-2017

There is also a new open source command line tool that's super cool and way, way better than sqlcmd in my opinion - mssql-cli.
https://github.com/dbcli/mssql-cli

Microsoft engineers are the primary committers on that project.

[joshbooker]

thanks again! Which of those has parity with sqlcmd.exe - meaning can directly execute output from:
sqlpackage.exe /a:Script /sf:%SourceDacpac% /tf:%TargetDacpac% /tdn:%Target% /op:%OutFile%

[joshbooker]

@brianjorden here is a test of doing extract, compare, update and scripting in sqlopsstudio command line using legacy sqlpackage & sqlcmd for windows plus cross-plat mssql-scripter:
https://github.com/joshbooker/sqlOS-Scripter-Extract-Compare-Update

I will try with these cross-plat tools soon.
edit: tried - success!

Also please vote for this idea to add SSDT equivalent to sqlopsstudio:
https://github.com/Microsoft/sqlopsstudio/issues/389

[tnsholding]

@twright-msft trying to use the new cross-platform sqlpackage in a docker container. Cannot resolve reference to master.dacpac. I 'm copying master.dacpac together with my primary xxx.dacpac.

Sqlpackage outputs the following:
Warning SQL72025: No file was supplied for reference master.dacpac; deployment might fail. When package was created, the original referenced file was located C:PROGRAM FILES (X86)MICROSOFT VISUAL STUDIO2017ENTERPRISECOMMON7IDEEXTENSIONSMICROSOFTSQLDBEXTENSIONSSQLSERVER130SQLSCHEMASMASTER.DACPAC.

How can I get sqlpackage to correctly resolve master.dacpac?

[apboyle]

@tnsholding, there's a known issue at the moment with case-sensitive file systems. I had to change the case to MASTER.DACPAC before sqlpackage could find it.

[twright-msft]

@tnsholding , @apboyle - Thanks for reporting this issue. We have a fix in the pipeline for this.

[moodmosaic]

Great news! Just wondering why there are different packages for each platform? Isn't the whole point of this to use just one package/set of assemblies across platforms?

Also, I've noticed there's no README in the (Windows) .ZIP file. As a consumer of Microsoft.SqlServer.Dac.dll, do I have to manually copy all these files in the program's output directory?

In general, it'd be easier if you provide a NuGet Package, perhaps in addition to those .ZIP files, so that the users can create local NuGet package feeds and have NuGet take care of any dependencies.

[twright-msft]

@moodmosaic - bit of a long story here... sqlpackage is dependent on a library called DacFX which in turn has historically been dependent on the full .NET framework. Of course that just works on Windows. In order to get sqlpackage to run on Linux we had two options - 1) run sqlpackage on the SQL Platform Abstraction Layer (SQL PAL) which also includes full .NET. If you are not familiar with the SQL PAL, please read more about it here or 2) replatform DacFx on dotnet core. Since #1 was relatively easy we did that at first and that was the original release of sqlpackage, but it's not the ideal user experience. We also wanted to make sure that sqlpackage could run on macOS. So, we decided that #2 was the better longer term solution and started working on replatforming DacFX to dotnet core. We have other tools that depend on DacFx and some people develop solutions directly against DacFX so they will benefit from this replatforming too. Now that the native sqlpackage with DacFX is available and runs on dotnet core the experience is much better and you can run it on Linux, macOS and Windows. The packaging is slightly different for each of the different operating systems to provide a familiar installation experience on each platform.

The fwlinks that I posted above are just to some raw builds of sqlpackage not the official GA ones. They are there as a preview of what is coming and especially for people trying to use sqlpackage in a Linux container to give us feedback while we are finalizing the release.

On Windows, you should be able to download/install the latest official from the download center

Good suggestion on the Nuget package. I'm not sure if we have had many requests for that yet, but if there was enough demand for that we could certainly look into it.

[pensivebrian]

To add on to explanation by @twright-msft, I did want to mention that DacFx does ship via nuget package Microsoft.SqlServer.DacFx.x64. However this package targets the .NET 4.6 framework, and does not yet contain the cross platform DacFx assemblies which target .NET Standard. Updating the nuget package to include the cross platform assemblies is on the road map, but we don't have any specific timelines, so it's probably a few months out.

The DacFx nuget package is meant for users that need to use the DacServices API directly, which is not very common since most folks use sqlpackage for .dacpac deployments. To that end, we will publishing sqlpackage .rpm, .deb, and brew packages in the next month or two to simply the acquisition of sqlpackage for non-windows users.

[johnconger]

Is it possible using the fwlinks above to deploy a dacpac file to MS SQL 2017 running native (not docker) on linux? For example:

dotnet sqlpackage.dll /Action:Publish /SourceFile:"MyDacpac.dacpac" /p:CreateNewDatabase=false /TargetConnectionString:"Data Source=localhost;User ID=sq;Password=pwd123;Database=MyDB;Pooling=False"

When i try to do this i get this error:
*** Could not deploy package. Unable to connect to master or target server 'MyDB'. You must have a user with the same password in master or target server 'MyDB'.

From what i see online this points to a discrepancy between my MS SQL version and the version in the Dacpac.

Is this problem possible to solve or do the versions of DacFX prevent this?

[twright-msft]

Is this a typo issue? You have ID=sq. Maybe you meant ID=sa?

dotnet sqlpackage.dll /Action:Publish /SourceFile:"MyDacpac.dacpac" /p:CreateNewDatabase=false /TargetConnectionString:"Data Source=localhost;User ID=sq;Password=pwd123;Database=MyDB;Pooling=False"

[moodmosaic]

Thanks for the comments, @twright-msft and @pensivebrian. I'm actually using the DacServices API directly so I'll be looking forward to a NuGet package release for .NET Standard.

Meanwhile, I might try to just consume what's inside the nightly builds―if so, I'll let you know how it goes.

[johnconger]

@twright-msft I'm embarrassed to say that the typo was my issue. Thanks! and sorry for wasting your time.

[SamuelMarks]

Thanks @twright-msft https://github.com/Microsoft/mssql-docker/issues/135#issuecomment-389245587

Until an official build is released, I've built an image https://hub.docker.com/r/samuelmarks/mssql-server-fts-sqlpackage-linux:

docker run -d -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' -p 1433:1433 --name sqlfts0 mssql-server-fts-sqlpackage-linux
docker exec -it sqlfts0 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YourStrong!Passw0rd>' -Q 'CREATE DATABASE MyDb0'
docker cp ~/Downloads/foo.bacpac sqlfts0:/opt/downloads/foo.bacpac
docker exec -it sqlfts0 dotnet /opt/sqlpackage/sqlpackage.dll /tsn:localhost /tu:SA /tp:'<YourStrong!Passw0rd>' /A:Import /tdn:MyDb0 /sf:foo.bacpac

BTW: If anyone has pointers on implementing Udt, I'm all ears… getting this error from my .bacpac sqlpackage import:

Type Udt is not supported on this platform.

[pensivebrian]

We now have some proper sqlpackage documentation, along with installation docs. The install docs have link to the latest sqlpackage builds and call out known issue in the release notes. Going forward, all now builds will be noted in the docs.

@SamuelMarks - There a two issues with UDTs at the moment for the cross platform sqlpackage build:
1) We're currently using .NET Core 2.0. We need to move to .NET Core 2.1, which has fixes in SqlClient for handling UDTs. I hope to have a new sqlpackage build out soon with this change.
2) SQL Server CLR UDTs (SqlGeometry, SqlGeography, and SqlHierarchyId) still won't be supported since we don't have a version of the Microsoft.SqlServer.Types.dll that targets .NET Core. I don't have any timelines for a fix. However after fixing 1), you should be able to use your own custom CLR UDTs provided you compile a version for .NET Core.

[twright-msft]

Now that sqlpackage is release for Linux and MacOS, I'm going to close out this issue. Thanks to everybody for your patience as we got this figured out and released!

[edwardaskew]

So is sqlpackage ever actually coming back to the docker image or is it always going to have to be installed on top of it? Neither :latest nor :2019-CTP3.0-ubuntu seem to have it right now.

[ormico]

@pensivebrian Did sqlpackage ever make it into apt-get on Ubuntu? I'm not seeing it but previous comment in thread indicated it was planned to go there.

[johnymachine]

Now that sqlpackage is release for Linux and MacOS, I'm going to close out this issue. Thanks to everybody for your patience as we got this figured out and released!

Where is good place to submit issues for linux version of sqlpackage? This thread is as close as I coul find.