Database,

How to Build a Free MS SQL Server Spatial Database Using Docker

by August 27, 2018no comment
1
Shares
connect to sql server

Hello everyone, welcome to my GIS Tutorial. This would be a pretty long article but I think it is worth it to share with you. I was able to build a free MS SQL Server Spatial Database for my projects. I am using Docker technology which is currently rising its popularity. Microsoft releases the free version of their popular DMBS, Microsoft SQL Server container. It can be run on any operating systems including Linux. On this article, I will show you how to build a free SQL Server container on top of a Linux operating system. And then, we can connect our favorite GIS software to the SQL Server.

This article will cover the following:

  • Install Docker
  • Install Microsoft SQL Server
  • Create New Database
  • Connect to Microsoft SQL Server from some GIS software

Lesson 1. Install Docker

Actually, you can install Microsoft SQL Server on top of Windows OS. You don’t need to install Docker. But personally, I prefer to use Docker to run my SQL Server rather than direct install to my Windows.

Install Docker on Windows 10

To install Docker on Windows 10, follow these steps. For more information and troubleshoot, please visit the official Docker documentation for Windows.

  1. Download Docker App for Windows
  2. Double click the file Docker for Windows Installer.exe
  3. Follow on-screen installation wizard.
    Please ensure that you have a working internet connection during the installation process. The default installation settings should work well in most cases.
    When finished, you will need to log out and re-login to Windows 10.
  4. Start Docker service. If Docker does not start automatically, you can start Docker by opening Docker App from Windows start menu or shortcut in your Desktop. You should see the Docker icon on the taskbar. Right-click on the icon to see the available menus.
  5. You will also need to login using your Docker ID. You can create a new Docker Hub account for free.

Install Kitematic

After installing Docker, we recommend you to install Kitematic. It is a graphical interface to manage Docker features such as manage images, containers, networking and some more. To install Kitematic, right-click the Docker icon on the taskbar and click Kitematic. It will download the installation package from the internet. And then you can extract the package and double-click Kitematic to start using it. Login using your Docker Hub account.

Now enjoy Kitematic

OK so now, we have Docker installed on Windows 10. Let’s jump to the next lesson.

Lesson 2. Install MS SQL Server Container

On this section, we are going to install Microsoft SQL Server Container. To do this, open the Docker CLI. You can click the Docker CLI icon from Kitematic (bottom left).

Pull SQL Server docker image.

On the PowerShell window (Docker CLI), paste the following command to download or pull the SQL Server image from Docker Hub

docker pull microsoft/mssql-server-linux:2017-latest

The file size is about 1.5 GB. So, make sure you have a good internet connection.

Now let’s deploy a new SQL Server container

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<YourStrong!Passw0rd>" `
-p 1433:1433 --name sql1 `
-d microsoft/mssql-server-linux:2017-latest

Don’t forget to change <YourStrong!Passw0rd> with your own password. Make sure the password meets the requirement as shown here.  You can also change the default port. For example to port number 14330.

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<YourStrong!Passw0rd>" `
-p 14330:1433 --name sql1 `
-d microsoft/mssql-server-linux:2017-latest

Now check the Docker container with command

docker ps

Output:

Done. At this point, we have a working SQL Server container. We are ready to connect to the server and create a new database.

Lesson 3. Connect to SQL Server container using SQL Server Management Studio

Download and Install SSMS from here. And then open it and go to File | Connect Object Explorer

Enter the server credentials such as server IP address and port number (separted with comma). Login and password as we specified on the previous steps. Click Connect.

Create a New Database

To create a new SQL Server database, right click the database on SSMS window. Next, type the database name and press OK.

Now we have a new database. We are going to use this database to store our spatial datasets.

Lesson 4. Connect to SQL Server from GIS Software

Before we can connect to SQL Server Container from GIS software, we need to create a new ODBC connection. Please read our previous post to create a new ODBC Connection on Windows 10.

Connect from Mapinfo Professional (Mapinfo Discover Pro)

Open Mapinfo Professional and then go to File | Open DBMS Connection. Click New and then select the new ODBC Connected we created earlier.

Please navigate to the second part of this tutorial to continue reading How to Build a Free MS SQL Server Spatial Database Using Docker part 2.

Leave a Reply