14

I can use Traefik for web sites since they use headers when they are connecting. But I want to have multiple different instances of SQL Server running through docker which will be externally available (outside the docker host, potentially outside the local network)

So, is there anything which allows connecting to different sql server instances running on the same docker instance WITHOUT having to give them different ports or external ip addresses such that someone could access

sql01.docker.local,1433 AND sql02.docker.local,1433 from SQL Tools.

Start Additional Question

Since there has been no replies perhaps there is a way to have different instances like: sql.docker.local\instance1 and sql.docker.local\instance2 though I imagine that may also not be possible

End Additional Question

This is an example of the docker-compose file I was trying to use (before I realised that queries to sql server don't send through a host header - or am I wrong about that?)

version: '2.1'
services:
  traefik:
    container_name: traefik
    image: stefanscherer/traefik-windows
    command: --docker.endpoint=tcp://172.28.80.1:2375 --logLevel=DEBUG
    ports:
      - "8080:8080"
      - "80:80"
      - "1433:1433"
    volumes:
      - ./runtest:C:/etc/traefik
      - C:/Users/mvukomanovic.admin/.docker:C:/etc/ssl
    networks:
      - default
    restart: unless-stopped
    labels:
      - "traefik.enable=false"

  whoami:
    image: stefanscherer/whoami
    labels:
      - "traefik.backend=whoami"
      - "traefik.frontend.entryPoints=http"
      - "traefik.port=8080"
      - "traefik.frontend.rule=Host:whoami.docker.local"
    networks:
      - default
    restart: unless-stopped


  sql01:
    image: microsoft/mssql-server-windows-developer
    environment:
      - ACCEPT_EULA=Y
    hostname: sql01
    domainname: sql01.local
    networks:
      - default
    restart: unless-stopped
    labels:
      - "traefik.frontend.rule=Host:sql01.docker.local,sql01,sql01.local"
      - "traefik.frontend.entryPoints=mssql"
      - "traefik.port=1433"
      - "traefik.frontend.port=1433"
    networks:
      - default
    restart: unless-stopped    
  sql02:
    image: microsoft/mssql-server-windows-developer
    environment:
      - ACCEPT_EULA=Y
    hostname: sql02
    domainname: sql02.local
    networks:
      - default
    restart: unless-stopped
    labels:
      - "traefik.frontend.rule=Host:sql02.docker.local,sql02,sql02.local"
      - "traefik.frontend.entryPoints=mssql"
      - "traefik.port=1433"
      - "traefik.frontend.port=1433"
    networks:
      - default
    restart: unless-stopped    

networks:
  default:
    external:
      name: nat
Matt Vukomanovic
  • 1,392
  • 1
  • 15
  • 23
  • Communicating with SQL Server is done through the TDS protocol, which is a binary protocol that's very much not like HTTP. (For starters, TDS connections are typically persistent, while HTTP connections typically are not.) At the very least, you'd need a generic TCP proxy. SQL Server also does not care about its hostname -- you cannot have two instances running on the same port. On the same "machine" (whether virtual or real) instances must use different ports. Instance names only serve as a mechanism to resolve those names to ports. – Jeroen Mostert Aug 07 '18 at 14:46
  • It *is* possible to proxy TDS connections based on the server name the client specifies when connecting -- I know because I built a proof of concept. But I don't know of a (production worthy) TDS proxy. Ultimately, such issues are better solved by assigning different host names to the same machine, and having these resolve to separate network interfaces, so the servers have distinct IP addresses even though they're running on the same node. No proxy is required to make this work. – Jeroen Mostert Aug 07 '18 at 14:48
  • In docker they are already on different interfaces, however when it comes to accessing that from outside the docker server it's a single network interface, so I cant do it that way. Obviously if it is possible I'm going to have to look into this further. – Matt Vukomanovic Aug 09 '18 at 08:17
  • Responding to only one aspect of your question: [**There are no named instances in SQL Server for Linux**](https://stackoverflow.com/a/49858586/864696). – Ross Presser Sep 30 '19 at 17:59

3 Answers3

1

As mentionned earlier traefik is not the right solution since it's a HTTP only LoadBalancer.

I can think right now in 3 different ways to achieve what you want to do :

  • Use a TCP Load Balancer like HAproxy
  • Setup you server in Docker Swarm Mode (https://docs.docker.com/engine/swarm/), that will allow to bind the same port with a transparent routing between them
  • Use a service discovery service like consul and SRV records that can abstracts ports number (this might be overkill for your needs and complex to setup)
webofmars
  • 1,439
  • 1
  • 18
  • 25
0

you can't use traefik, because it's a HTTP reverse proxy.

You're sql server listen and communicate via TCP.

I don't understand what's you're final goal. Why are you using 2 differents sql-server ?

It depends on what's you want but you may have two solutions:

  • Can you use a simpler solution ? different databases, roles and permissions for separation.
  • You can search into the documentation of SQL Server Always On, but it doesn't seems easy to route queries to specific sever.
MoiioM
  • 1,914
  • 1
  • 10
  • 16
0

There is no "virtual" access to databases like for HTTP servers. So - no additional hostnames pointing to same IP can help you.

If you insist on port 1433 for all of your instances, then I see no way for you except to use two different external IPs.

If you were on a Linux box you may try some iptables magic, but it not elegant and would allow access to only one of your instances at any single moment. Windows may have iptables equivalent (I never heard of it) but still only-one-at-a-time you cannot escape.

My advice - use more than one port to expose your servers.

Jomu
  • 349
  • 2
  • 5