Database Configuration

resplin · ‎6 Jun 2015

The official documentation is at: http://docs.alfresco.com

Configuration Database Installation 3.2 MySQL Oracle DB2 SQL Server PostgreSQL

Database Configuration

Introduction

Alfresco Community can be run against MySQL and PostgreSQL. Alfresco Enterprise supports Oracle, Microsoft SQL Server, DB2 as well MySQL and PostgreSQL. Refer to summary of the supported platforms. In addition, the Enterprise version can also be run against Ingres (see below for links).

This page assumes knowledge of how to extend the repository configuration and override properties: Repository Configuration. This page has been updated for the latest Alfresco releases, including Alfresco Enterprise 3.3. For Alfresco versions prior to v3.2, please refer to Database Configuration Prior To Version 3.2.

Overriding the Database Connection Properties

Follow the instructions in Repository Configuration to obtain alfresco-global.properties.sample and copy it to <extensionRoot>/alfresco-global.properties.

Modify the properties to enable the configuration appropriate to the database installation that the server will be run on.

Basic Properties

The following properties must be set for every installation

db.driver: The fully-qualified name of the JDBC driver class
db.url: The JDBC URL to the database connection
db.username: The name used to authenticate with the database
db.password: The password used to authenticate with the database

Advanced Properties

The following properties are configured with sensible defaults, but for advanced purposes you may want to set your own values. Note that not all of these properties are available in the 3.2 Community edition. The default connection pool bean is called defaultDataSource and is declared in the core-services-context.xml file. If necessary, for advanced purposes, this bean may be overriden with a custom definition. See Overriding Spring Configuration.

db.pool.statements.enable: A Boolean that when true indicates that all precompiled prepared statements used on a connection will be kept open and cached for reuse. This is enabled by default.
db.pool.statements.max: The maximum number of prepared statements to cache for each connection. The Alfresco default is 40. Note that Oracle does not allow more that 50 by default.
db.txn.isolation: The JDBC code number for the transaction isolation level, corresponding to those in the java.sql.Connection class. The default value of -1 indicates that the database's default transaction isolation level should be used and this is the most common setting. For the Microsoft SQL Server JDBC driver, the special value of 4096 should be used to enable snapshot isolation.
db.pool.initial: The number of connections opened when the pool is initialized
db.pool.max: The maximum number of connections in the pool
db.pool.idle: The maximum number of connections that are not in use kept open
db.pool.min: The minimum number of connections in the pool
db.pool.wait.max: The maximum number of milliseconds to wait for a connection to be returned before throwing an exception (when connections are unavailable) or -1 to wait indefinitely
db.pool.validate.query: The SQL query that will be used to validate connections are still alive. Useful if your database closes long-running connections after periods of inactivity. Oracle Example: select 1 from dual MySQL Example: select 1 MS SQL Server Example: select 1

db.pool.validate.borrow: A Boolean that when true indicates that connections will be validated before being borrowed from the pool. The default is true.
db.pool.validate.return: A Boolean that when true indicates that connections will be validated before being returned to the pool. The default is false.
db.pool.evict.interval: The number of milliseconds to sleep between eviction runs, when greater than zero. If zero or less, idle objects will not be evicted in the background, as is the default.
db.pool.evict.idle.min: The minimum number of milliseconds that a connection may sit idle before it is eligible for eviction
db.pool.evict.validate: A Boolean that when true indicates that idle connections will be validated during eviction runs
db.pool.abandoned.detect: A Boolean that when true indicates that a connection is considered abandoned and eligible for removal if it has been idle longer than the db.pool.abandoned.time. The default is false.
db.pool.abandoned.time: The time in seconds before an abandoned connection can be removed

In-built examples (Alfresco 3.3 or higher)

This page has been updated for the latest Alfresco releases. For example, the DB dialect should now be auto-detected. For Alfresco versions prior to v3.2, please refer to Database Configuration Prior To Version 3.2.

MySQL example

The following are example steps only and are subject to change:

Create a new 'alfresco' user and database. Set this user's password to 'alfresco'.
Ensure that the alfresco user has the required privileges to create and modify tables.

Verify/modify the Alfresco data location in the alfresco-global.properties file.

Override following properties in alfresco-global.properties:

db.name=alfresco
db.username=alfresco
db.password=alfresco
db.host=localhost
db.port=3306
db.driver=org.gjt.mm.mysql.Driver
db.url=jdbc:mysql://${db.host}:${db.port}/${db.name}
db.pool.validate.query=select 1

Start the application server (eg. Tomcat) to verify your configuration changes.

PostgreSQL example

The following are example steps only and are subject to change:

Copy the appropriate PostgreSQL driver JAR to /tomcat/lib (TomCat) or /jboss/server/default/lib (JBoss).

Create a new 'alfresco' user and database. Set this user's password to 'alfresco'.
Ensure that the alfresco user has the required privileges to create and modify tables.

Verify/modify the Alfresco data location in the alfresco-global.properties file.

Override following properties in alfresco-global.properties:

db.name=alfresco
db.username=alfresco
db.password=alfresco
db.host=localhost
db.port=5432
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://${db.host}:${db.port}/${db.name}

Ensure that the postgresql.conf file (pg_hba.conf for postgres 8.1.3) (refer to PostgreSQL documentation for more info on this file) contains:

host    all    all    127.0.0.1/32    password

... to allow password-authenticated connections via TCP/IP.

Do not forget to change the ownership of the directory tree specified in custom-data-location.properties to be owned by the user running the alfresco server.

Note: For versions *before* 3.3, download and install the PostgreSQL amp file. You will use the Module Management Tool to do that. Newer versions of Alfresco (eg. 3.3) do not need a separate AMP.

Start the application server (eg. Tomcat) to verify your configuration changes.

Oracle example

Requires Enterprise Edition. The following are example steps only and are subject to change:

Create a new 'alfresco' user and schema in oracle.
Ensure that the alfresco user has the required privileges to create and modify tables. This can be removed once the server has started, but may be required during upgrades.

Override following properties in your alfresco-global.properties:

db.name=alfresco
db.username=alfresco
db.password=alfresco
db.host=localhost
db.port=1521
db.driver=oracle.jdbc.OracleDriver
db.url=jdbc:oracle:thin:@${db.host}:${db.port}:<database sid>
db.pool.validate.query=select 1 from dual

If you have multiple Alfresco instances installed on an Oracle server, you will need to force the database metadata queries to target the schema that each database user is using. Put the following in alfresco-global.properties:

hibernate.default_schema=ALFRESCO

Copy the Oracle JDBC driver JAR (ojdbc14.jar) into \tomcat\lib (on Tomcat 6) or \jboss\server\default\lib (JBoss).

Start the application server (eg. Tomcat) to verify your configuration changes.

Note: if you get JDBC errors ensure the location for the Oracle JDBC drivers are on the system path, or add them to the relevant lib directory of the app server - the Oracle JDBC drivers can be found in the <orainst>/ora<ver>/jdbc/lib directory (e.g. c:\oracle\ora92\jdbc\lib)

Note: for performance reasons it is recommended that you use the 10g JDBC drivers, even if your Oracle server is 9i.

Note: Another URL DB format would be this if the first example do not works:

db.url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = {db.host})(PORT = ${db.port}))
(CONNECT_DATA =(SERVER = DEDICATED)(SID = <database sid>)))

Microsoft SQL Server example

Requires Enterprise Edition. The following are example steps only and are subject to change:

create an alfresco database and corresponding user, with sufficient rights on the database (to create tables etc).

Issue the following command to enable snapshot transaction isolation mode.

ALTER DATABASE alfresco SET ALLOW_SNAPSHOT_ISOLATION ON;

Ensure TCP connectivity is enabled on fixed port 1433

Copy the jTDS v1.2.5 JDBC driver to $TOMCAT_HOME/lib.

Edit alfresco-global.properties and add/update the following entries:

db.name=alfresco
db.username=alfresco
db.password=alfresco
db.host=localhost
db.port=1433
db.driver=net.sourceforge.jtds.jdbc.Driver
db.url=jdbc:jtds:sqlserver://${db.host}:${db.port}/${db.name}
db.txn.isolation=4096
db.pool.validate.query=select 1

Start the application server (eg. Tomcat) to verify your configuration changes.

DB2 example

Requires Enterprise Edition. The following are example steps only and are subject to change:

Requires DB2 v9.7 or later

Create a DB2 database with a larger page size of 32 K and using UTF8. If you do not do this you will see error SQL0286N (sqlCode -286, sqlstate 42727) as the schema is created for tables that do not fit the page size.

Set up the alfresco user and associated schema. Note DB2 only integrates with the OS security. You can not add a DB user with a password as you can say in oracle.

Override following properties in your alfresco-global.properties:

db.name=alfresco
db.username=alfresco
db.password=alfresco
db.host=localhost
db.port=50000
db.driver=com.ibm.db2.jcc.DB2Driver
db.url=jdbc:db2://${db.host}:${db.port}/${db.name}

Start the application server (eg. Tomcat) to verify your configuration changes.

Other Custom Databases

Alfresco can also be ported to run against other custom (unsupported) database dialects.

The general configuration is the same ... assuming the relevant scripts have been ported and tested. Make sure the database connector is on the classpath, set the hibernate dialect in the config (if it cannot be auto-detected), set the properties for the database connection in the config, make sure the appropriate user is defined on the database and that they can login, that the holder db/tablespace/... where this user can create tables exists, and that they have rights to create tables etc.

H2

There is an H2 database configuration available at https://github.com/skuro/alfresco-h2-support , but take note that if you upgrade Alfresco version, then the scripts do no provide the necessary support for automatically upgrading the database.

For H2 support you will need to add the H2 lib to the WEB-INF/lib folder of your war or to an appropriate folder for your application server. An example configuration is:

dir.root=/tmp/alfresco
db.driver=org.h2.Driver
db.url=jdbc:h2:/tmp/alfresco/db
db.username=sa
db.password=
hibernate.dialect=org.hibernate.dialect.H2Dialect

Further detail's can be found in the project's wiki.

Ingres example

Alfresco 3.3 is also available for Ingres 10. Please refer to Ingres.

Back to Server Configuration

Database Configuration

Database Configuration

Table of Contents