How to setup Docker container SQL Server 2017 for Liferay Development Environment
When we start a new project using Liferay and the project involves the development of new entities, I suggest to set up your own local development environment as closely as possible to the customer’s development environment, particularly as regards the database.
In this article I will explain how to use Docker to setup the Microsoft SQL Server 2017 container for use with Liferay. I also recommend reading the article How to build a Docker Liferay 7.2 image with the Oracle Database support.
These instructions comprise the bare-minimum setup required for initialization of a Liferay instance. The instructions are targeted for an audience of experienced developers with little to medium experience of Docker and Liferay. The deployment described results in a per-developer SQL Server setup on your local development host that you can use for Liferay.
1. What are the requirements
Before proceeding with the Docker container setup, let’s see what the requirements are:
- You need an account on Docker, which you can create from Docker – Signup;
- You need to install or update Docker on your PC. Refer to this article for how to install Docker on Windows.
The docker versions on which I have successfully run the SQL Server Docker container are: 17.x, 18.x and 19.x. The version of Docker Engine (Community Edition) that I will refer to in this article is 19.03.1 (you can check version with command docker version). For more information regards SQL Server requirements you can read Quick Start: Run SQL Server container images with Docker.
What is shown in this article can be performed on Windows, Unix-like operating systems. In my case I use macOS. However, always refer to the documentation of Docker and SQL Server for what concerns the minimal requirements that the target system must have.
2. SQL Server Database Docker Image
The latest version available is SQL Server 2017, this release is the version supported by Liferay (you can see Liferay Digital Experience Platform 7.2 Compatibility Matrix). Official images for Microsoft SQL Server on Linux for Docker Engine available on Docker Hub.
The version of our interest is the latest version: SQL Server 2017. We then perform the pull with the following command.
docker pull mcr.microsoft.com/mssql/server:2017-latest
Once the pull is done we could get more information on the image using the command docker images and docker inspect mcr.microsoft.com/mssql/server:2017-latest
3. How to starting SQL Server Database Server instance
To get a working SQL Server instance for use with Liferay, we need to:
- Creating a Docker container for SQL Server
- Setting up a database and login user specifically for Liferay
docker run -d -p 1433:1433 \ -h sqlserver.local --name sqlserver.local \ -e 'ACCEPT_EULA=Y' \ -e 'SA_PASSWORD=yourStrong(!)Password' \ -v mssql-data:/var/opt/mssql \ -d mcr.microsoft.com/mssql/server:2017-latest
We start by deploying the Microsoft-supplied SQL Server image to a local developers host: It will take one or two minutes to initialize the database.
Docker manages several kinds of objects the two we are interested in here are the so-called docker image (a minimal linux operating system with Microsoft's SQL Server installed); and a docker container, which is a virtual machine running an instance of that image.
The above command accomplishes both of these functions, fetching the image from Microsoft's Docker Hub account to your local computer and starting that image it in a container.
The argument -p host-external-port:container-internal-port makes the container's sql server port (which must be 1433) available on the specified host computer's port.
Port 1433 is the default port for MS SQL Server. I explicitly specify the defaults in the commands below for those cases where you might want to expose several SQL Servers, each in their own container, each exposing a unique port on the host computer.
The -d flag causes docker to run the container in the background as a daemon process.
The --name sqlserver.local argument provides an optional name for the container; it is arbitrary but must be unique across your containers. It is merely for convenience.
The -h sqlserver.local argument provides an optional hostname for the container; it is arbitrary but must be unique across your containers. It is merely for convenience.
The argument -v mssql-data:/var/opt/mssql creates an independent docker volume named mssql-data which associates the /var/opt/mssql filesystem on the container to an external independent volume - think of it as a share that will be mounted. The Microsoft-supplied SQL Server image has been specifically configured to keep all its data in that filesystem.
Docker images have a default startup command built-in; the -e variable=value arguments set environment variables and are a way to indicate to the start-up program that we are in the process of setting up the Microsoft SQL Server instance. They need only be run once when initially setting up a container.
The SA_PASSWORD environment variable set the system administrator password. In this case the password set to: yourStrong(!)Password
Note that by default the hosts 1433 port is available on both on the external interface (its TCP/IP address) as well as the loop-back port (localhost). You can get information on our setup with the docker ps, docker volumes and docker container inspect commands.
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 41b28847c78c mcr.microsoft.com/mssql/server:2017-latest "/opt/mssql/bin/sqls…" 14 minutes ago Up 14 minutes 0.0.0.0:1433->1433/tcp sqlserver.local
[ { "CreatedAt": "2019-10-02T21:04:14Z", "Driver": "local", "Labels": null, "Mountpoint": "/var/lib/docker/volumes/mssql-data/_data", "Name": "mssql-data", "Options": null, "Scope": "local" } ]
[ { "Id": "41b28847c78cf689f7e3016a98a8e9fdb7dcd0f9ce68e1efa013ecd4653cff13", "Created": "2019-10-02T21:30:50.3077299Z", "Path": "/opt/mssql/bin/sqlservr", "Args": [], "State": { "Status": "running", "Running": true, "Paused": false, "Restarting": false, "OOMKilled": false, "Dead": false, "Pid": 47844, "ExitCode": 0, "Error": "", "StartedAt": "2019-10-02T21:30:51.0129593Z", "FinishedAt": "0001-01-01T00:00:00Z" }, "Image": "sha256:d9b9b96627b758fa300f8392065a150f138bab08cb7b3fa885cd5528459bec59", "ResolvConfPath": "/var/lib/docker/containers/41b28847c78cf689f7e3016a98a8e9fdb7dcd0f9ce68e1efa013ecd4653cff13/resolv.conf", "HostnamePath": "/var/lib/docker/containers/41b28847c78cf689f7e3016a98a8e9fdb7dcd0f9ce68e1efa013ecd4653cff13/hostname", "HostsPath": "/var/lib/docker/containers/41b28847c78cf689f7e3016a98a8e9fdb7dcd0f9ce68e1efa013ecd4653cff13/hosts", "LogPath": "/var/lib/docker/containers/41b28847c78cf689f7e3016a98a8e9fdb7dcd0f9ce68e1efa013ecd4653cff13/41b28847c78cf689f7e3016a98a8e9fdb7dcd0f9ce68e1efa013ecd4653cff13-json.log", "Name": "/sqlserver.local", "RestartCount": 0, "Driver": "overlay2", "Platform": "linux", "MountLabel": "", "ProcessLabel": "", "AppArmorProfile": "", "ExecIDs": null, "HostConfig": { "Binds": [ "mssql-data:/var/opt/mssql" ], "ContainerIDFile": "", "LogConfig": { "Type": "json-file", "Config": {} }, "NetworkMode": "default", "PortBindings": { "1433/tcp": [ { "HostIp": "", "HostPort": "1433" } ] }, "RestartPolicy": { "Name": "no", "MaximumRetryCount": 0 }, "AutoRemove": false, "VolumeDriver": "", "VolumesFrom": null, "CapAdd": null, "CapDrop": null, "Capabilities": null, "Dns": [], "DnsOptions": [], "DnsSearch": [], "ExtraHosts": null, "GroupAdd": null, "IpcMode": "private", "Cgroup": "", "Links": null, "OomScoreAdj": 0, "PidMode": "", "Privileged": false, "PublishAllPorts": false, "ReadonlyRootfs": false, "SecurityOpt": null, "UTSMode": "", "UsernsMode": "", "ShmSize": 67108864, "Runtime": "runc", "ConsoleSize": [ 0, 0 ], "Isolation": "", "CpuShares": 0, "Memory": 0, "NanoCpus": 0, "CgroupParent": "", "BlkioWeight": 0, "BlkioWeightDevice": [], "BlkioDeviceReadBps": null, "BlkioDeviceWriteBps": null, "BlkioDeviceReadIOps": null, "BlkioDeviceWriteIOps": null, "CpuPeriod": 0, "CpuQuota": 0, "CpuRealtimePeriod": 0, "CpuRealtimeRuntime": 0, "CpusetCpus": "", "CpusetMems": "", "Devices": [], "DeviceCgroupRules": null, "DeviceRequests": null, "KernelMemory": 0, "KernelMemoryTCP": 0, "MemoryReservation": 0, "MemorySwap": 0, "MemorySwappiness": null, "OomKillDisable": false, "PidsLimit": null, "Ulimits": null, "CpuCount": 0, "CpuPercent": 0, "IOMaximumIOps": 0, "IOMaximumBandwidth": 0, "MaskedPaths": [ "/proc/asound", "/proc/acpi", "/proc/kcore", "/proc/keys", "/proc/latency_stats", "/proc/timer_list", "/proc/timer_stats", "/proc/sched_debug", "/proc/scsi", "/sys/firmware" ], "ReadonlyPaths": [ "/proc/bus", "/proc/fs", "/proc/irq", "/proc/sys", "/proc/sysrq-trigger" ] }, "GraphDriver": { "Data": { "LowerDir": "/var/lib/docker/overlay2/b69e33de59299005dc89d3d583321b5d52c8859ee5195a85a4369e753b6466a7-init/diff:/var/lib/docker/overlay2/b1c9478d0c72e7206ca529376d2426de28be50b4df0026cccc716e58dfd3febe/diff:/var/lib/docker/overlay2/6af2770e759c88593b0780bbccc2fea3e8cb86aefe40f2783deb7468130f48e0/diff:/var/lib/docker/overlay2/f4aa2ce0efb7dbab242407dfcb10820b49f863ee45de4fe3bfb3069d84d094a2/diff:/var/lib/docker/overlay2/fad7c1152f054c8637a489df05971117aa6960a7acc7ac45ab31755ba4fd8349/diff:/var/lib/docker/overlay2/4a7d0dddd251d90a364f84e9a8ad04cc25f89430cb64b3e57d752506f91692e1/diff:/var/lib/docker/overlay2/f7b31fabfd43e1a8898644c34bcbfbdc220c526e0172a1854582d7cc20363621/diff:/var/lib/docker/overlay2/1407e02d43ccaa2d00de404a542be107f2150b16009325020896ba586564e942/diff:/var/lib/docker/overlay2/88b0c4e78b75386aa570f552b7089676d776c6f5678be540fbc138cbce2cff81/diff:/var/lib/docker/overlay2/d2cbe30634c06fb0098b0d631d5d704c3e7dc43c74c4dfbcb54bebdfdd6023b4/diff", "MergedDir": "/var/lib/docker/overlay2/b69e33de59299005dc89d3d583321b5d52c8859ee5195a85a4369e753b6466a7/merged", "UpperDir": "/var/lib/docker/overlay2/b69e33de59299005dc89d3d583321b5d52c8859ee5195a85a4369e753b6466a7/diff", "WorkDir": "/var/lib/docker/overlay2/b69e33de59299005dc89d3d583321b5d52c8859ee5195a85a4369e753b6466a7/work" }, "Name": "overlay2" }, "Mounts": [ { "Type": "volume", "Name": "mssql-data", "Source": "/var/lib/docker/volumes/mssql-data/_data", "Destination": "/var/opt/mssql", "Driver": "local", "Mode": "z", "RW": true, "Propagation": "" } ], "Config": { "Hostname": "sqlserver.local", "Domainname": "", "User": "", "AttachStdin": false, "AttachStdout": false, "AttachStderr": false, "ExposedPorts": { "1433/tcp": {} }, "Tty": false, "OpenStdin": false, "StdinOnce": false, "Env": [ "ACCEPT_EULA=Y", "SA_PASSWORD=yourStrong(!)Password", "PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin" ], "Cmd": [ "/opt/mssql/bin/sqlservr" ], "Image": "mcr.microsoft.com/mssql/server:2017-latest", "Volumes": null, "WorkingDir": "", "Entrypoint": null, "OnBuild": null, "Labels": { "com.microsoft.product": "Microsoft SQL Server", "com.microsoft.version": "14.0.3223.3", "vendor": "Microsoft" } }, "NetworkSettings": { "Bridge": "", "SandboxID": "e93b62c6c1ebbc9e4c9e7feaa429b1c894a704bea86eebf98941c9fe76249a00", "HairpinMode": false, "LinkLocalIPv6Address": "", "LinkLocalIPv6PrefixLen": 0, "Ports": { "1433/tcp": [ { "HostIp": "0.0.0.0", "HostPort": "1433" } ] }, "SandboxKey": "/var/run/docker/netns/e93b62c6c1eb", "SecondaryIPAddresses": null, "SecondaryIPv6Addresses": null, "EndpointID": "da6a6d19df60437c199f3980cd667d98e799773a91510a3f81766966e1661710", "Gateway": "172.17.0.1", "GlobalIPv6Address": "", "GlobalIPv6PrefixLen": 0, "IPAddress": "172.17.0.3", "IPPrefixLen": 16, "IPv6Gateway": "", "MacAddress": "02:42:ac:11:00:03", "Networks": { "bridge": { "IPAMConfig": null, "Links": null, "Aliases": null, "NetworkID": "b0dc647384156d4bbd920ea297a330f3436e5b757972faff96e9aa5abd043a03", "EndpointID": "da6a6d19df60437c199f3980cd667d98e799773a91510a3f81766966e1661710", "Gateway": "172.17.0.1", "IPAddress": "172.17.0.3", "IPPrefixLen": 16, "IPv6Gateway": "", "GlobalIPv6Address": "", "GlobalIPv6PrefixLen": 0, "MacAddress": "02:42:ac:11:00:03", "DriverOpts": null } } } } ]
Great 😉 We are almost there. At this point we have to create the database, the login, the user and assign the correct permissions for the user who must access the Liferay database. The SQL script shown below performs the above description.
CREATE DATABASE lportal_dev GO USE lportal_dev CREATE LOGIN liferay WITH PASSWORD = "lportal@DEV@72", DEFAULT_DATABASE = lportal_dev GO CREATE USER liferay FOR LOGIN liferay GO ALTER ROLE db_ddladmin ADD MEMBER liferay ALTER ROLE db_datareader ADD MEMBER liferay ALTER ROLE db_datawriter ADD MEMBER liferay GO
To execute the script we can use the following command docker exec.
docker exec -it sqlserver.local /opt/mssql-tools/bin/sqlcmd \ -S localhost -U SA -P 'yourStrong(!)Password' \ -Q 'CREATE DATABASE lportal_dev GO USE lportal_dev CREATE LOGIN liferay WITH PASSWORD = "lportal@DEV@72", DEFAULT_DATABASE = lportal_dev GO CREATE USER liferay FOR LOGIN liferay GO ALTER ROLE db_ddladmin ADD MEMBER liferay ALTER ROLE db_datareader ADD MEMBER liferay ALTER ROLE db_datawriter ADD MEMBER liferay GO'
Now you can connect, from the host computer, to the containers' SQL Server via sqlcmd as the liferay user as so:
$ docker exec -it sqlserver.local bash # /opt/mssql-tools/bin/sqlcmd -S localhost,1433 -d lportal_dev -U liferay -P 'lportal@DEV@72' -Q 'SELECT @@VERSION'
Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64) Jul 12 2019 17:43:08 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS) (1 rows affected)
Executing the query SELECT @@VERSION produces the output shown above. The query returns detail information about the SQL Server version. The images below show the setup of the connection to the Liferay database that we have just created. The tool used is Azure Data Studio.
4. The next step
In the next article (which will come soon), we will see How to create a Docker Liferay 7.2 image with support for the SQL Server 2017 database.