SSDT-style Import from database and sqlpackage.exe integration (extract, compare, update, publish, etc.)

[joshbooker]

It would be great if the Project-Oriented Offline Database Development features of SSDT were incorporated into SQLOps.
Namely:
Import from Database or dacpac: (would create project structure with .sql scripts for all db objects.)
SQLPackage.exe integration (to extract, compare and generate update scripts)
SQLCMD mode option for executing sqlcmd scripts such as those generated by sqlpackage.exe /a:script cmd

Thanks for listening.

[joshbooker]

related issue #265

[joshbooker]

related test project:
https://github.com/joshbooker/sqlOS-Scripter-Extract-Compare-Update

[erickangMSFT]

@joshbooker related test project https://github.com/erickangMSFT/generator-sqlproj

[joshbooker]

@erickangMSFT thanks for this but does the msbuild do compare and update?
It's seems we need a multiplatform command line equivalent of sqlpackage.exe.
https://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx

[xandhen]

It would be very beneficial to support this on macOS and Linux. We are currently moving to the .NET Core stack and one main reason is broader platform support. As we are starting to experiment with a more CI based approach to the SQL layer as well, this would be a necessity as limiting SQL development to Windows is not an option. It would be a real shame if this was limited to Windows long term, especially now when we can even run SQL Server in a docker container in macOS.

[joshbooker]

Looks like there is now a cross-platform sqlpackage.exe:

microsoft/mssql-docker#135 (comment)

Now all we need is cross plat sqlcmd.exe

[joshbooker]

There is also cross-plat sqlcmd.exe so now all dependencies are available. Lets add this to SQL Ops Studio.

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

Thanks for listening.

[joshbooker]

@kburtram, It appears now there are indeed cross-plat versions of all tools needed to enable Extract, Script, Compare, Update fucntionality via command-line. Any chance these could get wired up to context menus for simple 'SSDT > Schema Compare' equivalent?

[yualan]

@joshbooker We are currently working on this. Would you be interested in seeing wireframes and provide feedback on the design?

[joshbooker]

@yualan I would love to see and provide feedback. Can you share what dependencies you are considering? For example, are you thinking of using cross-plat sqlpackage.exe, sqlcmd.exe and mssql-scripted? Like: https://github.com/joshbooker/sqlOS-Scripter-Extract-Compare-Update/

[carlowahlstedt]

@yualan I'll help test in any way possible as well.

[offbeatful]

@yualan Interested as well.
Additional use cases:

1. Maintain source controlled scripts as a single source of truth.
2. Build SQL Server docker image out of raw files
3. Support cross-platform development

[yualan]

#3171 Seeking feedback through this issue.

All our command line tools now have a cross-platform story: https://docs.microsoft.com/en-us/sql/tools/overview-sql-tools?view=sql-server-2017

[joshbooker]

Looking forward to providing feedback to improve the cmdline experience for Extract, Compare, Update.
This is what we're doing now and we'd love to see this become a wizard experience in ADS.

SCRIPTER

EXTRACT, COMPARE, UPDATE

ALTER, COMPARE, UPDATE

[offbeatful]

@joshbooker Thanks for referencing this. I was trying to explore more deeply but I was not able to find how can implement the following use case:

1. I have a snapshot of my database in source control and I can build a dacpac using VisualStudio on Windows (sqlproj).
2. How can I do the same on linux/mac? I suspect something like sqlpackage /Action:Script /Source:/path-to-folder-with-script. I can't find any documentation on that?

In our development workflow we try to use source control as a single source of truth.
Developer builds dacpac file from source and use it for his development environment. Then developer works with his local database and make changes to it. Once functionality is completed he dumps the changes from database to source control, commits it and pushes. CI builds docker image. Then we use that for automated tests.

Our CI and Development heavily relies on Docker. It will be nice if we can eliminate dependency on VS and be able to do everything using command line tools inside container.

[joshbooker]

@offbeatful I don’t Linux or docker but believe your use case is indeed doable with the new cross-plat sqlpackage.
Docs here: https://docs.microsoft.com/en-us/sql/tools/sqlpackage?view=sql-server-2017
And yes script action compares schema of two dacpac files and outputs a delta DDL script which when executed on the target database will make it the same as source db. Here i’m Using it like so:
sqlpackage.exe /a:Script /sf:%SourceDacpac% /tf:%TargetDacpac% /tdn:%Target% /op:%OutFile%

[carlowahlstedt]

@yualan so, it appears the SSDT tools aren't yet x-plat? I was trying to take a local SSDT project and apply it to a database.

[joshbooker]

@carlowahlstedt All the SSDT command line tools are indeed now cross-plat. See Here: https://docs.microsoft.com/en-us/sql/tools/overview-sql-tools?view=sql-server-2017#command-line-tools-to-manage-databases
If you extract your project to a .dacpac, you can then compare and publish to a database using sqlpackage.exe https://docs.microsoft.com/en-us/sql/tools/sqlpackage?view=sql-server-2017

[offbeatful]

So the missing functionality that I am experienced so far is the ability to build dacpac file from source (from raw sql files). I use Visual Studio DB Project to hold all the source files and MsBuild works just fine generating dacpac file (only on Windows).
My suggestion is to build something that can add all the sql files in a directory to dacpac. This utility should be cross platform and should not depend on Visual Studio DB Project.

[carlowahlstedt]

@joshbooker ah, so the difference here is that if you know all of the command line tools then you can make it work, but the "easy developer experience" of VS and F5 or Azure DevOps Pipeline and build the project isn't there (x-plat). Which is what I really meant by SSDT tools, an "easy" developer experience.

[xandhen]

@yualan Would building a DACPAC file from .sql sources be within the scope of this issue or should a new one be filed if one doesn't exist already? Like @offbeatful we are also seeking to have raw sql source files as the primary source in our cross-plattform (macOS/Windows). It's very important that a CLI version is available though for easy script-based provisioning and CI.

@joshbooker I've converted parts of your windows scripts to bash and it seems to be working very well on macOS with the cross-plattform versions.

[carlowahlstedt]

Completely agree with @xandhen after looking at this in more depth. It seems the creation of the DACPAC from sql source is what's missing and what the SSDT project in Visual Studio does today. If this could be accomplished, then we'd have the bare tools needed to be able to commit to source and put it in a pipeline.

[offbeatful]

What I ended up doing (while waiting for this nice feature to generate DACPAC from SQL sources) - I configured Azure DevOps CI process to create dacpac on Windows Agent, then I added this dacpac to Docker image with SQL tools and deploy to ACS (multi agent pipeline or something). After I can use this docker image on any machine to apply db changes.

My docker file:

FROM ubuntu:16.04

ARG DEBIAN_FRONTEND=noninteractive

RUN apt-get update && \
    apt-get install -qq curl apt-transport-https unzip locales
RUN curl -q https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl -q https://packages.microsoft.com/config/ubuntu/16.04/prod.list | tee /etc/apt/sources.list.d/msprod.list

RUN apt-get update && \
    ACCEPT_EULA=Y apt-get install -qq mssql-tools unixodbc-dev libunwind8 libicu55

RUN curl -Lq https://go.microsoft.com/fwlink/?linkid=873926 -o ~/sqlpackage.zip && \
    unzip ~/sqlpackage.zip -d /opt/sqlpackage && \
    rm ~/sqlpackage.zip && \
    chmod +x /opt/sqlpackage/sqlpackage

RUN locale-gen en_US.UTF-8 && \
  printf 'LANGUAGE=en_US.UTF-8\nLC_ALL=en_US.UTF-8\n' >> /etc/default/locale

ENV PATH=$PATH:/opt/mssql-tools/bin:/opt/sqlpackage

ADD tools /opt/db-tools
ADD bin/Debug/db.dacpac /opt/db-tools
WORKDIR /opt/db-tools

tools folder contains setup.sh

export FILE_NAME=script.sql
sqlpackage /a:Script /sf:${DACPAC_FILE} /tsn:${SERVER_NAME} /tdn:${DATABASE_NAME} /tu:${DATABASE_USER} /tp:${DATABASE_PASSWORD} /OutputPath:${FILE_NAME}
sqlcmd -i ${FILE_NAME} -S ${SERVER_NAME} -d master -U ${DATABASE_USER} -P ${DATABASE_PASSWORD}

Then my development docker-compose file :

version: "3.4"

services:
  db-tools: &db-tools
    image: <your acs>.azurecr.io/<db-tools-image_name>
    container_name: db_tools
    build: 
      context: .
      dockerfile: ./docker/tools.Dockerfile

  db:
    container_name: db
    image: microsoft/mssql-server-linux:latest
    restart: unless-stopped
    environment: 
      - ACCEPT_EULA=Y
      - SA_PASSWORD=<your sa password>
    ports:
      - 1433:1433

  db-updater:
    <<: *db-tools    
    environment:      
      - DACPAC_FILE=/opt/db-tools/db.dacpac
      - SERVER_NAME=db
      - DATABASE_NAME=<you db>
      - DATABASE_USER=sa
      - DATABASE_PASSWORD=<your sa password>
    entrypoint: sh /opt/db-tools/setup.sh    
    depends_on:
      - db

You may extend this and put your own tools (sh files in tools directory).

[stevef51]

We are in the process of moving our monolithic .NET Framework + SQL (read .sqlproj, with folders full of .sql files) to .NET Core and dockerizing.

Impressed to see SQL running on OSX (via Docker ofcourse) and all its command line tools :)

However we have the same problem - developing in VS we modify .sql files in the .sqlproj and when ready right-click "Snapshot" which spits out a .dacpac we can then deploy this to databases via sqlpackage.exe.

This appears to be the "missing link" in the x-platform tooling, the ability to take a folder full of .sql files and pack them into a .dacpac, seems rather odd since the x-platform tooling supports generating .sql files from a .dacpac but not the other way round which I am sure for developers wanting to keep their .sql in source control (ie everyone surely) is preventing them from using the tools from dev all the way to prod, at the moment when we need to modify .sql files we need to fire up a Windows machine (VM) and hit VS to do the job of packing .sql files into .dacpac.

Any update on the progress of this feature ?

[joshbooker]

@stevef51 You're correct, .sql files to .dacpac is the missing link. Plus script database to .sql files per object.

I'm not aware of that being in-progress for ADS at this time but I believe progress is being made in that direction. They have recently implemented the Schema Compare Extension which basically adds most of the functions of sqlpackage.exe to ADS, but as you note that tool doesn't understand .sqlproj or .dbschema files used to manage database projects in Visual Studio. As far as I can determine, that's all VS specific stuff and and not a part of any x-plat command line tools at this time.

There is mssql-scripter which does Database to .sql extraction, but nothing outside of VS that does the .sql files to .dacpac, unfortunately.

As far as ADS, I hope they move forward with enabling the script from database sooner than later considering there is a nice utility for that. As for the 'database project' stuff someone would have to recreate or OSS the VSDBCMD.EXE utility for x-plat first.

We should make a new issue to track the remaining bits for full SSDT parity which are:

1. SQL CMD MODE
2. Script database to .sql files (create sqlproj)
3. Import .sql files (sqlproj) into Dacpac

Only then can we use SQL Compare to comapre a projects, database and dacpac like we can in VS.

We welcome thoughts from the ADS team.

[stevef51]

@joshbooker thanks for your detailed reply - given this though, what is the suggested workflow from development through to production deployment for SQL based projects outside of VS?

[joshbooker]

Good question. Absent the above mentioned bits I’m not sure there is a 1st party workflow outside of VS.

[joshbooker]

SQLCMD Mode is done.
Woot! Thanks ADS Team!

That leaves only two bits remaining for SSDT parity in ADS.
Namely:

1. Script database to .sql files (create sqlproj)
2. Import .sql files (sqlproj) into Dacpac

Here is an old issue that could be used to track that:
#45

[stevef51]

@joshbooker is any work being done on #2 "Import.sql files (sqlproj) into Dacpac" ?? :)

[rrmistry]

@joshbooker , based on workflow in these gifs: #389 (comment) any chance we can prioritize #10370 ?

[GrahamTheCoder]

@joshbooker Yeah it'd be great to get to get official support for these workflows outside VS
My use case is to auto-pull-request database drift. i.e. Compare deployed version to the db, script any changes to files, commit, PR.

There are some promising looking workarounds here - though I haven't tested them yet:

1. https://github.com/DarylSmith/DacpacToSqlCmdlet/blob/master/DacpacSqlConverter.cs#L47-L89
2. https://github.com/DarylSmith/DacpacToSqlCmdlet/blob/master/SqlDacpacConverter.cs#L33-L52

[dzsquared]

The combination of SQL Database Projects, Schema Compare, and Data-Tier Application Wizard extensions provides for a cross-platform database development experience in Azure Data Studio. The functionality draws from existing SSDT experiences and incorporates support for new features, such as Azure SQL Edge. SqlPackage.exe and the .NET Core SDK are the foundational technologies behind the interfaces.
While this issue is being closed since the feature asks in this item have been completed, our work on developer experiences continues. Work on Tasks (#10370) is underway and other items can be found under the "Area - SQL Project" label.

[GrahamTheCoder]

Good to hear of progress.
I know sql proj is a bit neglected (e.g.old project format). But off the top of your head, do any of the things you mentioned have a command line for extracting a live database to create a sql proj (or updating an existing one?).
If not, and if you aren't phasing it out, I can create a new issue if you'd like since this had a lot in it?

[dzsquared]

@GrahamTheCoder The DB proj extension has a UI but not a command line option for importing a current/live DB to a project. Go ahead and drop us a new issue and we'll check it out, thanks!