Publishing SQL Server database in Docker

To me docker containers have an ethereal, almost unreal quality to them. Do they really exist? Where are they? What do they look like? To convince myself of their reality I want to use SQL Server Management Studio on the host to connect to a SQL instance in a running container. Along the way we shall

– bring up the container
– access container command shell
– find out where it is on the network
– connect to it from the host machine
– compare static and dynamically assigned container IPs

 Environment

I’m on windows 10 and I’m going to be working with windows containers.

docker-compose.yml

For this I don’t need a DockerFile as I’m just going to run a library image directly so I’m going straight to a docker compose file docker-compose.yml

version: '3.2'

services:
  db:
    image: microsoft/mssql-server-windows-developer
    ports:
      - "49401:1433"
    environment:
      - sa_password=Secret12345
      - ACCEPT_EULA=Y      
    container_name: sqlserver_db1

networks:
  default:
    external:
      name: nat


so to break it down

image: microsoft/mssql-server-windows-developer

Create the container from the image microsoft/mssql-server-windows-developer. If it hasn’t been downloaded then it will be when we bring up the container

ports:
- "49401:1433"

we are running on port 1433 internally to the container and the port 49401 will be bound to the host. I’m not running it on 1433:1433 because I’ve already got SQL Server on my host so that port is taken. Attempts to bind the host to a port that is already taken generates odd errors.

environment:
- sa_password=Secret12345
- ACCEPT_EULA=Y

we set the system admin password and accept the licence agreement

container_name: sqlserver_db1

and the container is given a name rather than one assigned by docker. It just keeps the rest of the examples easier. It’s not needed.

We haven’t specified a subnet or IP address so the network section tells docker to use its default network connection

Bringing the container up

To bring it up run

docker-compose up

which runs and brings up the docker container

If we jump onto another cmd window and run

docker container ls

We get the list of running containers thus

so we can see our sql box in it’s container in the platonic realm of container space that it is currently inhabiting. It’s called sqlserver_db1 as specified in our docker-compose.yml file

Getting an interactive shell on our container

The container still has an air on unreality to it. To start to resolve it into the real world I want to be able to run commands on it. To do this we need to bring up an interactive shell. I can bring up a cmd window

docker exec -ti sqlserver_db1 cmd

or a powershell window for more options

docker exec -ti sqlserver_db1 powershell

I’m doing the powershell. Once there I cna start to run whatever commands I see fit

echo 'Hello world'
hostname

and so on.

ping google.commands

is a good one to run to check if the container has network connectivity to the outside world. Mine didn’t and it took a while to work out why.

Where is it on the network

It’s not immediately apparent where the container is on the network. We’ve let docker dynamically assign the IP. To find it we go to our interactive shell and run

ipconfig

which will tell use the IP4 address

or outside of an interactive shell (from the host) we could use

docker inspect -f "{{ .NetworkSettings.Networks.nat.IPAddress }}" sqlserver_db1

which will tell us the IP4 address also.

So right now our container is on 192.168.154.121 and we can ping it there. Going back to docker-compose.yml we also specify ports.

ports:
- "49401:1433"

so 1433 is the one that the docker container uses and we can telnet to it from the host to prove it’s open

telnet 192.168.154.121 1433

which it is. Also we can telnet to the host on the other port

telnet 127.0.0.1 49401

which is the same place but via the port that is bound to the host.

Connecting to the container with SSMS

I like to see things to full accept their existence. To convince my inner self let’s connect to the container with SQL Server Management Studio

We can connect to the host on its IP4

Or on the host IP by specifying the loopback IP and the port as we have bound the host to a port other than the default for SQL Server (1433).

Note that to connect via SSMS to a specific port the IP is separated by a comma i.e.

127.0.0.1,49401

Now we can see our container database from the host. We are convinced. It exists.

Connecting with a static IP

We can also assign the database container a static IP address by specifying a subnet and an IP address for our container in the docker-compose.yml file thus

version: '3.2'

services:
  db:
    image: microsoft/mssql-server-windows-developer
    ports:
      - "49401:1433"
    environment:
      - sa_password=Secret12345
      - ACCEPT_EULA=Y      
    networks:
      vpcbr:
        ipv4_address: 10.5.0.5
    container_name: sqlserver_db1
    
networks:
  vpcbr:
    driver: nat
    ipam:
     config:
       - subnet: 10.5.0.0/16      

The container will now always be on 10.5.0.5 and we can connect with SSMS on 10.5.0.5 without worrying about any of the intervening steps. I guess that’s the easy way.

Demo Code

In case anyone needs it the docker files for static and dynamic IP implementations are at on my github site here.

Useful Links

https://docs.docker.com/compose/compose-file/
Full specification of docker compose

Leave a Reply

Your email address will not be published. Required fields are marked *