Routing to Different SQL Server Instances Running through Docker on Default Port
sql server docker production
docker sql server cluster
sql server docker performance
docker-compose sql server
sql server docker licensing
enable sql server agent docker
connect to sql server from docker container
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
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\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
Distributed transactions (MSDTC) with SQL Server on Docker, To enable MSDTC transaction in containers for docker, you must set two new to pull and run a single SQL Server 2019 container configured for MSDTC. in distributed transactions with the host and with other external servers. For more information about routing ports, see Configure port routing. Note. With Docker, you can start as many fresh clean instances of SQL Server on a host as you need just by running docker run. When you are finished with them, you can tear them back down again without any messy residuals.
Configure SQL Server settings on Linux, TCP port, Change the port where SQL Server listens for connections. If you choose to navigate to that path instead, run mssql-conf in the context SQL errorlog file is set becomes the default log directory for other logs. The following options configure TLS for an instance of SQL Server running on Linux. For SQL Server outside of a container or for non-root containers, a different ephemeral port, such as 13500, must be used in the container and traffic to port 135 must then be routed to that port. You would also need to configure port routing rules within the container from the container port 135 to the ephemeral port.
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.
SQL Server network configuration, as it includes network traffic routing standards with advanced security protection. By default, the SQL Server instance listens on TCP port 1433, where the the SQL Server instance from using different TCP port on each server restart. processes running on the same machine or on a remote computer in Let’s continue with this blog post series about SQL Server and Docker. A couple of days ago, I was in a customer shop that already implemented SQL Server 2017 on Linux as Docker containers. It was definitely a very interesting day with a lot of customer experience and feedbacks. We discussed with him about lot of architecture scenarios. The interesting point here is I was able to compare
5 Fatal Docker Gotcha's - for new users, Docker host port binding, bypasses typical iptables INPUT rules However, Docker port binding uses iptables PREROUTING level by default. that you were running many more hosts in the cluster than the required containers. servers; Counter-examples (requires persistency) : SQL servers, file servers. Using Docker to run Integration Tests for dbachecks Posted on January 19, 2019 by SQLDBAwithTheBeard My wonderful friend André Kamman wrote a fantastic blog post this week SQL Server Container Instances via Cloudshell about how he uses containers in Azure to test code against different versions of SQL Server.
Docker run reference, In foreground mode (the default when -d is not specified), docker run can start the Publishing ports and linking to other containers only works with the default Your container will use the same DNS servers as the host by default, but you With the network is none a container will not have access to any external routes. Issues with Using default Port Numbers. When you install SQL Server, the default instance is setup to listen on port 1433. This is the default port number and it is well known. Because of this, you commonly see unwanted attacks against port 1433 from hackers.
Container networking, By default, when you create a container, it does not publish any of its ports to the outside so the Docker daemon effectively acts as a DHCP server for each container. However, you can connect a running container to multiple networks using When you connect an existing container to a different network using docker APPLIES TO: SQL Server Azure SQL Database (Managed Instance only) Azure Synapse Analytics (SQL DW) Parallel Data Warehouse Adds a new route to the routing table for the current database. For outgoing messages, Service Broker determines routing by checking the routing table in the local database.
- 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.
- 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.
- 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.
- Responding to only one aspect of your question: There are no named instances in SQL Server for Linux.
- I'm using two different servers because I want to run them as docker containers. So roles and permissions can't help in this case