FDO road test: SQL Server 2017 on Linux

You can consider this post as the 2017 edition of this post.

So for some background. There's been several annoyances I've been personally experiencing with the SQL Server FDO provider that have given me sufficient motivation to fix the problem right at the source (code). However, before I can go down that road, I needed to set up a local dev installation of SQL Server as my dev environment is more geared towards MapGuide than individual FDO providers.

But just like my previous adventure with the King Oracle FDO provider, I didn't want to have to actually find/download a SQL Server installer and proceed to pollute my dev environment with a whole assortment of junk and bloat. We now live in the era of docker containers! Spinning up a SQL Server environment should be a docker pull away and when I no longer need the environment, I can cleanly blow it away without leaving lots of junk behind.

And it just so happens that with the latest release of SQL Server 2017, not only is running it inside a docker container a first-class user story, it is also the first release of SQL Server that natively runs on Linux.

So through the exercise of spinning up a SQL Server 2017 linux container we can kill multiple birds with one stone:

• We'll know if MapGuide/FDO in its current form can work with SQL Server 2017
• We'll also know how well it works with the Linux version of SQL Server (given its feature set is not at parity with the equivalent Windows version)
• If MapGuide/FDO works, we'd then have a SQL Server environment ready to go which can be spun up and torn down on demand to then start fixing various problems with the FDO provider.

Spinning up the SQL Server 2017 linux docker container

This was easy because Microsoft provides an official docker image. So it was a case of just pulling down the docker image and adjusting some environment parameters to use a custom SQL Server sa login when we go to docker run the container and also define port mappings so we can connect to this container from the docker host OS.

The FDO Toolbox bootstrapping test

This was an easy way to determine if the SQL Server FDO provider works with SQL Server 2017. FDO Toolbox has the ability to:

1. Create a SQL Server data store
2. Bulk Copy spatial data into it
3. Query/Preview data from it

If we can do all 3 things above in FDO Toolbox against the freshly spun up SQL Server 2017 linux container, that's a very good sign that everything works.

Creating the FDO data store

FDO Toolbox has a specialized UI for creating SQL Server data stores that is accessible by right-clicking the FDO Data Sources node and choosing Create Data Store - Create SQL Server

This gives us the UI to set up a new SQL Server data store

The first real test is to see if the FDO provider can connect to our SQL Server container, which is a case of filling in all the required connection properties and clicking the Test button, which gives us:

So far so good. Now that we know the FDO provider can connect to the container, we can fill out the data store parameters and click OK to create the data store, which gave us another good sign:

Now just to be sure that the FDO provider did actually create the database, I connected to this SQL Server instance through alternative tools (such as the new SQL Operations Studio) and we can see that the database is indeed there.

So now we can bulk copy some spatial data into it, which will be a nice solid verification that the feature and schema manipulation functionality of the FDO provider work in SQL Server 2017.

So I set up a bulk copy using a whole bunch of test SHP files. A few moments later, we got another positive sign:

Again, for verification we can look at this database in a different tool and can see that the FDO provider correctly created the database tables.

And that data was actually being copied in

Just as an aside: SQL Operations Studio doesn't do spatial data previews like its big brother SQL Server Management Studio.

A shame really. Oh well, at least we can do that in FDO Toolbox :)

Which is also confirmation that FDO is getting the geometry data out of our SQL Server 2017 linux container without any problems.

So based on all these findings, I feel comfortable in saying that FDO (and applications using it like MapGuide) works just fine with SQL Server 2017, especially its Linux version.

Now to deal with these actual annoyances in the FDO provider itself ...