Replace TinyTDS/FreeTDS `defncopy` bin with Cross Platform SqlPackage.exe

[metaskills]

Today, we have a tool called sqlpackage.exe which is a .NET command line app that’s built over a .NET library called DacFX and runs on Windows. This tool supports SQL Server on-premise, SQL Server hosted in a VM and also Azure SQL Database in exactly the same way. More info:

• SqlPackage.exe
• dotnet/corefx
• dotnet/coreclr
• DacFx Public Model Tutorial

Our current thinking was to make sqlpackage.exe (and DacFX) work on coreclr and corefx so they could be used in Ruby on Rails in scenarios such as dumping schema and/or dumping schema + data for a SQL Server database. Cross Platform!

[metaskills]

cc @tigermint as my point of contact on this.

[metaskills]

Linking microsoft/mssql-docker#8

[metaskills]

Some notes on playing with the latest docker image which has /opt/mssql/bin/sqlpackage installed. The below commands will eventually use sqlcmd vs sqsh.

Structure Dump

We need an empt database to Script against so we drop the test DB and use that. In reality we would use the database task here too.

$ sqsh -U sa -P $SA_PASSWORD -S localhost -C "DROP DATABASE [myapp_test];"
$ sqsh -U sa -P $SA_PASSWORD -S localhost -C "CREATE DATABASE [myapp_test];"

Now we run the sqlpackage commands. Unfortunately this requires being able to run from the host machine.

$ sqlpackage /a:Extract /q:True \
  /ssn:tcp:127.0.0.1 /sdn:myapp /su:rails \
  /of:True /tf:/opt/mssql/data/structure.dacpac

$ sqlpackage /a:Script \
  /sf:/opt/mssql/data/structure.dacpac \
  /tsn:tcp:127.0.0.1 /tdn:myapp_test /tu:rails \
  /of:True /op:/opt/mssql/data/structure.sql

From here we would need to search and replace the structure file and replace the test database name with the name of the source extract.

-:setvar DatabaseName "myapp_test"
+:setvar DatabaseName "myapp"

Structure Clone/Load

$ sqsh -U sa -P $SA_PASSWORD -S localhost -C "DROP DATABASE [myapp_test];"
$ sqsh -U sa -P $SA_PASSWORD -S localhost -C "CREATE DATABASE [myapp_test];"

$ sqlpackage /a:Extract /q:True \
  /ssn:tcp:127.0.0.1 /sdn:myapp /su:rails \
  /of:True /tf:/opt/mssql/data/structure.dacpac

$ sqlpackage /a:Publish /q:True \
  /sf:/opt/mssql/data/structure.dacpac \
  /tsn:tcp:127.0.0.1 /tdn:myapp_test /tu:rails

[metaskills]

Mac: https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/03/sql-server-command-line-tools-for-mac-preview-now-available/

Linux: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools

[metaskills]

Use new structure_dump_flags and structure_load_flags seen here: https://github.com/rails/rails/blob/5-1-stable/activerecord/lib/active_record/tasks/database_tasks.rb

[vkalach]

@metaskills what is the status of this feature? do you need any help with it? current defncopy does dump not in a really good way if we talk about syntax