Microsoft SQL Server Engine?

[brianmckeen]

Do you have any plans to add an engine for Microsoft SQL Server?

[theory]

I do not, but some others have expressed an interest in doing so. Please post to the mail list if you'd like to lend a hand.

[drmuey]

For anyone interested in this issue, there is already a thread (and efforts underway) for this.

https://groups.google.com/forum/#!topic/sqitch-users/RILseUAHRPY

Thanks @theory and @brianmckeen

[theory]

@brianmckeen's fork is here.

[drmuey]

thanks! @theory

[theory]

Hey, what's the status on this, guys?

[drmuey]

I've been on a different project (using sqitch but w/ sqlite) but will revisit soon and update this issue, thanks @theory!

[theory]

Awesome, I look forward to it, @drmuey.

[drmuey]

Quick update for you @theory:

• I just did a pull request on @brianmckeen’s fork to rename the engine to mssql.
• After that I'll start on the driver stuff (I have a question for you on that at in the group discussion)

[theory]

Awesome, thanks!

[drmuey]

Quick update for you @theory:

• @brianmckeen merged the rename-to-mssql pull request, thanks @brianmckeen!
• I just submitted a pull request to @brianmckeen that will add the mssql DBD driver logic.

[theory]

Thanks! I left a comment.

[sudhirvkumar]

@theory @drmuey Any update on supporting mssql engine?

I see that the latest one seems to be https://github.com/drmuey/sqitch and latest commit is in Jul 2016.

We are using sqitch and would love to be able to use it in our project where we will be using MS SQL Server.

@theory thanks for sqitch, it has made our life better!

[drmuey]

@sudhirvkumar The only thing left is adding tests which I stopped working on because of of VPN/VM issues I couldn't resolve (i.e. my tests could not connect to the MSSQL servers I had access to).

The status and punch list at https://github.com/drmuey/sqitch/wiki/MSSQL-TODO

If you're in a position to work out the tests I'd be happy to take a pull request! If we can get it complete then it will be in shape for a proper upstream pull request. I just updated my fork with @theory’s so it should be ready to go!

[sudhirvkumar]

@drmuey I will check the code and let you know if I am able to do a PR. Thanks for your reply.

[jugatsu]

@sudhirvkumar @drmuey guys, any updates on this issue?

[GiorgioG]

+1

[GiorgioG]

If there's anything I can do to help, let me know.

[theory]

I think someone needs to just pick this back up, rebase it, and get it too completion.

[drmuey]

@jugatsu @GiorgioG

@theory is correct, details of where it is and what needs done are outlined in this comment: https://github.com/theory/sqitch/issues/149#issuecomment-276668457

[matthewblott]

+1 for this :-)

[dynajoe]

What’s the current state of mssql? 5 years in the making!

[theory]

Just takes someone committing to doing the work.

[sbraverman]

I have been able to get MsSQL to work using ODBC Driver. https://github.com/sbraverman/sqitch/tree/mssql-odbc

It builds off all the work that @drmuey has done.

It does not add any tests. I am not a Perl-ista (this was my first stab at Perl), nor am I sure that these changes break the way that the ADO driver was using it.

my sqitch.conf:

[core]
    engine = mssql

[engine.mssql]
    driver = {ODBC Driver 17 for SQL Server}

The way I run sqitch commands:

sqitch verify -t db:mssql://<user>:<password>@<server>:<port>/<database>
sqitch status -t db:mssql://<user>:<password>@<server>:<port>/<database>
sqitch deploy -t db:mssql://<user>:<password>@<server>:<port>/<database>
sqitch revert -t db:mssql://<user>:<password>@<server>:<port>/<database>

[theory]

NIce. Anyone know of a way to set up automated testing for this on Travis or GH workflow actions?

[CoreyKaylor]

You might be able to use containers for this and use https://docs.github.com/en/actions/reference/workflow-syntax-for-github-actions#jobsjob_idcontainer

https://hub.docker.com/_/microsoft-mssql-server

[lastlink]

You might be able to use containers for this and use https://docs.github.com/en/actions/reference/workflow-syntax-for-github-actions#jobsjob_idcontainer

https://hub.docker.com/_/microsoft-mssql-server

I've got several db servers working using service containers see https://github.com/lastlink/dotnet-ci-pipelines/blob/master/.github/workflows/dotnetcore.yml

[theory]

Does that docker image include a sqlcmd client?

[lastlink]

you can use your own docker base image, this is a separate service container docker image that you can access the services of along side the base one you are running which you can put whatever you want on.

on this note you spin up a separate service container w/ mssql on it. then on your base image should have the dependencies necessary to connect to it, install as needed and run sqitch as an integrated test to the db. I usually don't have the database on the same machine b/c of all the dependencies needed for each one. you can create your own custom docker images, but I try to use the officially supported ones for base and service containers.

[TroySchmidt]

So the last step is to get the testing in place using containers and the ODBC connection for SQL Server?

[theory]

It has been a few years, so I'm frankly not sure what all needs doing, but it's likely it will need quite a bit of work to sync it with changes since 2017.

[lastlink]

I can help with the pipelines, GitHub actions make it easy to run database container with the pipeline.

[matthewblott]

MSSQL has been running on Linux for years now, is there a reason it's not supported?

[lastlink]

Then again the sql database projects in visual studio work so well (only for sql server) that there probably isn't a need for sqitch to handle it.

[matthewblott]

That requires using Windows which I haven't done since about 2015. A lot less people using MS tech work on Windows these days.

[TroySchmidt]

MSSQL has been running on Linux for years now, is there a reason it's not supported?

From above, the work was done to make it work via ODBC but the unit testing for this project was not put in place.

As for the use case, having one tool to manage database version deployments regardless of engine is nice to have. Especially for small companies. While DB projects in Visual Studio does work their dacpac deployment is something of a black box. It also doesn't have features like being able to tell version or the changelog of applied changes like Sqitch does.

[arialdomartini]

Any update on this? Having Sqitch supporting MS SQL would be really great.

[theory]

I agree! It just needs someone with the interest, motivation, and time to implement. Do you know of someone who could pick it up?

[mika76]

I guess the biggest issue is it's in perl - I'd help if it was in c# or javascript or something 😄

[theory]

Well most of the work would need to be in SQL, but I hear ya.

[mika76]

@theory I could help with the SQL part if someone else handles the perl part

[theory]

I think if you wanted to start with the registry schema, that's be amazing. Should be named lib/App/Sqitch/Engine/mssql.sql and be similar to the other SQL files in that directory. Then maybe the proper arguments to pass to the SQL Server command line client (there is one, yes?) would help to get the basics going.

[mika76]

Sure, I'll make a fork and work on that - and yes there's a command line client sqlcmd which can be used. How do you handle different db versions, and how far back do you go? There were some pretty big changes to mssql over the years, although Microsoft only really supports sql 2012+ currently.

I assume I should fork the develop branch?