External Database

The installer allows you to configure the Mail Express Server to use a pre-existing external SQL Server 2008 R2 or 2012 instance or Oracle 11g instance. You can instruct the installer to either create a new database and user on an existing external instance or to use an existing database and database user. In this case, the installer will simple create the necessary database object and default data within the existing database. This option is provided for users that prefer more manual control over the database creation.

Required Settings

For proper operation with a SQL Server database, the Mail Express Server requires the following attributes of the instance, database, and database user account used by the Mail Express Server:

The TCP/IP protocol must be enabled for connections to the SQL Server instance
The database must use a case sensitive collation
The database "ALLOW_SNAPSHOT_ISOLATION" setting must be ON
The database "READ_COMMITTED_SNAPSHOT" setting must be ON
The database user’s default schema should be set to "dbo"
By default the database user’s "Database role membership" will be set to "dbowner"; however, because the Mail Express Server does not modify the database DDL during the course of operation, the "Database role membership" may be optionally locked down to "dbdatareader" and "dbdatawriter" after initial installation. Note, however, that during subsequent upgrades, the database user used during the upgrade must have permission to modify the database DDL. As such, provision is provided in the upgrade sequence of the installer to specify an alternate user to use for performing the actual upgrade. Alternatively, you may temporarily grant the normal Mail Express Server database user the right to modify the database DDL for the course of the upgrade.

Database Creation Example

The following sample SQL statements will create the database for SQL Server. The collation name should be adjusted depending on the desired database language. (Case-sensitive collation is required. Case-sensitive collations typically include "_BIN" (for Binary) or "_CS" (for Case Sensitive) in the collation identifier. You should avoid using collations whose identifier contains "_CI" (for Case Insensitive).)

CREATE DATABASE "MAILEXPRESS" COLLATE SQL_Latin1_General_CP1_CS_AS

ALTER DATABASE "MAILEXPRESS" SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE "MAILEXPRESS" SET READ_COMMITTED_SNAPSHOT ON

There are no equivalent statements for Oracle. Oracle does not have an entity between the instance and the schema like SQL Server; also, Oracle is implicitly case-sensitive and the default transaction isolation is sufficient.

Database User Creation Example

The following sample statements will create the database user:

CREATE LOGIN "medatabase" WITH PASSWORD='Hv2rKQ8n2NVM', DEFAULT_DATABASE="MAILEXPRESS", CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE "MAILEXPRESS"; CREATE USER "medatabase" FOR LOGIN "medatabase" WITH DEFAULT_SCHEMA=dbo

USE "MAILEXPRESS"; EXEC sp_addrolemember 'db_owner', "medatabase"

These are the corresponding statements for Oracle:

CREATE USER "MEDATABASE" IDENTIFIED BY "Hv2rKQ8n2NVM"

GRANT CONNECT, RESOURCE to "MEDATABASE"