Using docker images for enterprise databases with Alfresco

Docker development setups with commercial databases

With Alfresco Content Services 6.0, docker-based installations are the standard way for setting up Alfresco (development) environments, while installer-based installations are not available anymore. By the moment, Docker compose resources use Postgresql database as default, but in between Alfresco supported stacks, there are also commercial databases widely used in Alfresco installations, such as MS-SQL Server and Oracle. In our development enviroments (also for Alfresco 5.x), we need to test and develope with these commercial databases (for example for an upgrade path procedure), and they traditionally were not as easy to install or to set up in a Linux machine. Nowadays, there are several docker images that allow to work easier with MS-SQLServer or Oracle databases. 

In the following, we consider docker setups with MS-SQLServer 2017 (for Alfresco 6) and Oracle 11 (for Alfresco 5.2.3). We assume that you have docker installed in your machine. In my case:

$ docker -v
Docker version 17.03.2-ce, build f5ec1e2

$ docker pull microsoft/mssql-server-linux:2017-latest 

$ docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=zylk' -p 1433:1433 --name sqlserver2017 -d microsoft/mssql-server-linux:2017-latest

First we can try to check if the default port is available:

$ nc -v localhost 1433
Connection to localhost 1433 port [tcp/ms-sql-s] succeeded!

In the following, we are going to create a database for Alfresco. A possibility is to run sqlcmd utility inside docker machine:

$ docker exec -it sqlserver2017 "bash"
root@de2f9e36fd46:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'zylk'
1> CREATE DATABASE alfresco6
2> SELECT Name from sys.Databases
3> GO                                                               

master                                          
tempdb                                                                                                 
model                                                                                                  
msdb                                                                                                   
alfresco6                                                                                         

1> ALTER DATABASE alfresco6 SET ALLOW_SNAPSHOT_ISOLATION ON;
2> GO
1> QUIT

Another possibility is to use a client program such as DbVisualizer :

Host  : localhost
Port  : 1433
UserID: SA
Passwd: zylk

where we can use a SQL editor in an easier way.

CREATE DATABASE alfresco6;
ALTER DATABASE alfresco6 SET ALLOW_SNAPSHOT_ISOLATION ON;
//ALTER DATABASE alfresco6 SET COMPATIBILITY_LEVEL = 140; 
//130 --> SQLServer 2016
//110 --> SQLServer 2012

Now you are ready to use MS-SQL Server at port 1433. For stopping, starting or removing the docker image use the following commands:

$ docker [stop|start|rm] sqlserver2017

In alfresco-global.properties you should have:

db.name=alfresco6
db.username=SA
db.password=zylk
db.host=localhost
db.port=1433
db.pool.max=275
db.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
db.url=jdbc:sqlserver://${db.host}:${db.port};databaseName=${db.name};lockTimeout=1000;
db.txn.isolation=4096

Don’t forget to put the corresponding jar on <TOMCAT_HOME>/lib (sqljdbc42.jar in this case)

Let’s try now for Oracle 11g in Alfresco 5.2.3.

$ docker pull wnameless/oracle-xe-11g
$ docker run -d -e ORACLE_ALLOW_REMOTE=true -p 1521:1521 --name oracle11g -d wnameless/oracle-xe-11g

Once it is run, let’s check the available port:

$ nc -v localhost 1521
Connection to localhost 1521 port [tcp/*] succeeded!

And then to connect via DbVisualizer just using:

Host: localhost
Port: 1521
SID: XE
Username: system
Password: oracle
# Password for SYS & SYSTEM

Then let’s create the user database:

CREATE USER alfresco523 identified by zylk;
GRANT dba to alfresco523;

Take into consideration that Oracle database must be created with the AL32UTF8 character set. This can be checked with:

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';
//AL32UTF8
SELECT sys_context('userenv','instance_name') FROM dual;
//XE

Then in alfresco-global.properties you should have something like:

db.name=alfresco523
db.username=alfresco523
db.password=zylk
db.host=localhost
db.port=1521
db.pool.max=100
db.driver=oracle.jdbc.OracleDriver
db.url=jdbc:oracle:thin:@${db.host}:${db.port}:XE
db.pool.max=275
db.pool.validate.query=select 1 from dual

The driver in this case is ojdbc7.jar

Finally for stopping/starting/removing:

$ docker [stop|start|rm] oracle11g

External links:

• https://docs.microsoft.com/es-es/sql/linux/quickstart-install-connect-docker?view=sql-server-linux-2017
• https://github.com/wnameless/docker-oracle-xe-11g
• https://docs.alfresco.com/6.0/tasks/sqlserver-config.html
• https://docs.alfresco.com/5.2/tasks/oracledb-config.html
• https://docs.alfresco.com/6.0/concepts/supported-platforms-ACS.html
• https://docs.alfresco.com/5.2/concepts/supported-platforms-ACS.html
• https://www.dbvis.com/