Lessons Learned While Working with Databases in Docker

Docker commands for a SQL Server container

Basic command to start SQL Server container

docker run -d -p 1433:1433 --name sqlserver_persondb -e sa_password=<YOUR_PASSWORD> -e ACCEPT_EULA=Y -v c:/data/:c:/data/ microsoft/mssql-server-windows-developer

-p 1433:1433 Publish a containers port(s) to the host
--name sqlserver_persondb Assign a name to the container
-e sa_password= Set the system administrator (userid = ‘sa’) password used to connect to SQL Server once the container is running. The password must meet the password complexity requirements.
-e ACCEPT_EULA=Y Confirms acceptance of the end user licensing agreement found
-v c:/data/:c:/data/ Map a volume from the container to host

Here is the Docker command to start a MS SQL Server container with attached database(s) and setting the auto-restart policy so that the container will restart automatically if the host system reboots.

docker run --restart=always -d -p 1433:1433 --name sqlserver_persondb -e sa_password=<YOUR_PASSWORD> -e ACCEPT_EULA=Y -v c:/data/:c:/data/ -e attach_dbs="[{'dbName':'Person','dbFiles':['C:\\data\\Person.mdf','C:\\data\\Person_log.ldf']}]" microsoft/mssql-server-windows-developer

This next command adds a specified IP address to the container. By default a random IP address is assigned to the container each time it starts. This means that every time you restart your container all resources which connect to that container will need to be updated with the new IP address. By specifying an IP address we don’t have to go changing the config file in our application each time we reboot our computer.

docker run --restart=always -d --ip 172.23.132.28 -p 1433:1433 --name sqlserver_persondb -e sa_password=<YOUR_PASSWORD> -e ACCEPT_EULA=Y -v c:/data/:c:/data/ -e attach_dbs="[{'dbName':'Person','dbFiles':['C:\\data\\Person.mdf','C:\\data\\Person_log.ldf']}]" microsoft/mssql-server-windows-developer

Docker command to execute a SQL script file in a given container.

docker exec -it sqlserver_persondb sqlcmd -S. -Usa -P <YOUR_PASSWORD> -i "c:\data\MapPersonUser.sql"

Docker command to get the IP address of the last container created. This is useful if you have not specified the IP address in the docker command and need to connect to it.

docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' $(docker ps -q)

SQL to list tables in specific database.

SELECT TABLE_NAME FROM PERSON.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

SQL to remap SQL Server login user with database user.

USE Person
GO
sp_change_users_login 'Auto_Fix', 'PersonUser', NULL, '<YOUR_PASSWORD>'

Docker commands for an Oracle Container

Basic command to start Oracle container

docker run --restart=always -d -p 8080:8080 -p 1521:1521 --name oracle_firecall -v c:/data/oracle/:/u01/app/oracle sath89/oracle-12c

Wait several minutes while the database starts up.

Connect to the database using the following information:
Id: system
Password: oracle
Host: localhost
Port: 1521
SID: xe
Service Name: xe.oracle.docker

SQLPlus command to connect to the database:

sqlplus system/oracle@//172.17.0.2:1521/xe.oracle.docker

Once you are connected to your Oracle database you can begin creating your tablespaces, users, and schemas. These two commands create the tablespace defining the data file and temporary file.

CREATE TABLESPACE tbs_perm_01
  DATAFILE 'tbs_perm_01.dat' 
    SIZE 10M
    REUSE
    AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

CREATE TEMPORARY TABLESPACE tbs_temp_01
  TEMPFILE 'tbs_temp_01.dbf'
    SIZE 5M
    AUTOEXTEND ON;

Next we need to create a user and grant the user access to the tablespace to create all the schema elements.

CREATE USER REPLACE_USERNAME
  IDENTIFIED BY REPLACE_PASSWORD
  DEFAULT TABLESPACE tbs_perm_01
  TEMPORARY TABLESPACE tbs_temp_01
  QUOTA 20M on tbs_perm_01;

GRANT create session TO REPLACE_USERNAME;
GRANT create table TO REPLACE_USERNAME;
GRANT create view TO REPLACE_USERNAME;
GRANT create any trigger TO REPLACE_USERNAME;
GRANT create any procedure TO REPLACE_USERNAME;
GRANT create sequence TO REPLACE_USERNAME;
GRANT create synonym TO REPLACE_USERNAME;

Now we have a user with the proper permissions and can begin creating our schema.

Getting Test/Production Schema or Data into Your Local Container

Often I am working with an existing system which already has an existing database. What I like to do is copy that schema and/or data to my local development instance. There are a of tools for doing this. Here are two tools that I have looked at.

Devart has Schema Compare and Data Compare
Red Gate has SQL Compare and Data Compare

I have only used the Devart tools at this point. What I typically do is run Devart Schema Compare to synchronize the schemas. Then I run Devart Data Compare to synchronize the data. Now I have local instance of my applications test or production database which I can begin developing against.

Docker command to create a new network

Sometimes you may want to create your own subnet which to host your containers in. This command will create a new network named joshuanet with a specified subnet.

docker network create --subnet=172.18.0.0/16 joshuanet

Shared Drive Credentials

The Docker shared drives require a user account to run under with the proper permissions to read/edit the directories. If you change the password of the account you must update the stored credentials in Docker also. You can do this by opening the Docker settings window. Clicking the Shared Drives tab and clicking Reset Credentials as shown in the image below.

Joshua Chini