SQL Server connection string and slowness

I am running SQL Server Express 2014 under a Docker for Windows container using the following command:

docker run -it -p 1433:1433 -v C:/Databases/:C:/Databases/ --env sa_password=P@ssW0rd --env attach_dbs="[{'dbName':'trunk','dbFiles':['C:\\Databases\\trunk_Log.mdf','C:\\Databases\\trunk.ldf']}]" --name sql microsoft/mssql-server-2014-express-windows

The database successfully starts up, and I can connect to it with SQL Server Management Studio. The name of the server seems to be {First 12 Characters of the Docker Container ID}\SQLEXPRESS

Since the container id changes every time a container is started, having a statically-defined connection string is impossible. It seems you need to use the Docker REST API to get the name of the container, and then use it in your connection string.

public string GetSqlServerDockerContainer(string name)
{
      // tcp://localhost:2375 is the address that exposed the REST endpoints
      DockerClient client = new DockerClientConfiguration(new Uri("tcp://localhost:2375")).CreateClient();
      var containers = client.Containers.ListContainersAsync(new ContainersListParameters() { Limit = 10 }).Result;

      foreach (var container in containers)
      {
          if (container.Names != null && container.Names[0].Equals(name, StringComparison.OrdinalIgnoreCase))
              return container.ID;
          if (container.Image != null && container.Image.IndexOf("mssql", StringComparison.OrdinalIgnoreCase) >= 0)
             return container.ID;
      }

      return null;
}

var containerId = app.GetSqlServerDockerContainer("/sql");
containerId = containerId.Substring(0, 12);   // the "server" name that SQL Server uses is the first 12 characters of the container id

string connectionString = $"Data Source={containerId}\\SQLEXPRESS;Initial Catalog=trunk;Uid=sa;Pwd=P@ssW0rd";

It doesn’t seem that you can connect to the database using {Container Name}\SQLEXPRESS. You need the container ID.

I have a two part question:

  1. Is there any better way to resolve the connection string to a running database?
  2. In my C# app which is running outside of the container, I am creating a SqlConnection and then calling connection.Open(). It seems to take a few seconds to actually connect to the database. Is there any way to get around the slowness in connecting?

Thanks,

Marc

It’s not entirely clear to me what your setup is, but resolving using service name should work fine. See this sample (with this possible caveat).

Also see this: Windows contianer : how to talk to other box in the same network with computer name

I’m interested in the slowness you’re seeing, please provide more details.

Hi Michael,

I am actually in the middle of a three-week tour overseas, so I will pick this up when I come back to the States.

-marc

Hello,

I have been working with a sql server express, to use it have been using docker inspect to get its ip address and modifying the connection string. After some research I found out the you can use the instance name as {containder id}\sqlexpress to connect and it works, but its extremely slow, even when I try to change databases in sql manager.

This is the version
Client:
Version: 17.06.0-ce
API version: 1.30
Go version: go1.8.3
Git commit: 02c1d87
Built: Fri Jun 23 21:30:30 2017
OS/Arch: windows/amd64

Server:
Version: 17.06.0-ce
API version: 1.30 (minimum version 1.24)
Go version: go1.8.3
Git commit: 02c1d87
Built: Fri Jun 23 22:19:00 2017
OS/Arch: windows/amd64
Experimental: true

Thanks