IceWarp Server Databases

Database Types

• Accounts is used for accounts information. It is critical for an administrator to be able to log into a console.

• Directory cache keeps records of the folder structures of the mail storage.

• GroupWare is used to store all events, tasks, notes, contacts, and other user data except mails.

• ActiveSync (EAS) is used by EAS server as metadata storage. It is essential for synchronization to work. If it is lost, all devices will go for a full re-synchronization.

• Antispam is used to store users' blacklists and whitelist.

• Antispam reports is used by challenge script which generates qurantine/antispam reports for users.

• WebClient serves as cache for WebClient. It keeps records of folders and mails (metadata) together with flags (colored). If it is lost, colored flags are gone (IMAP flag will remain). Server will have to generate new cache for each user, can be very resource demanding at early stages after loss.

What Happens when DB Is Unreachable (or Lost)

• Accounts. Server will not recognize accounts so neither mail acceptance nor login will work. Domains/accounts will not be present in the administration console. This is a serious problem on Linux machines where remoteconfig has to be used. In such a case, administrators have to enable remote login using the tool.sh or wizard.

  Example on tool.sh

  [root@durotar icewarp]# sh tool.sh create account iwadmin@durotar.icewarp.in

  Account iwadmin@durotar.icewarp.in created.

  [root@durotar icewarp]# sh tool.sh set account iwadmin@durotar.icewarp.in u_password Secret1 u_admin 1

  Account iwadmin@durotar.icewarp.in updated.

  Account-save problem most probably indicates that password does not meet password policy set on server. Mail storage is unaffected by loss of this DB, therefore if account with same primary alias is created mails are again available to that account.

• Directory cache. When it is lost, server performance will be dramatically lowered, which will cause terrible user experience. The same will happen when mail storage path (u_mailboxpath) is set outside of the scope of default mail storage path defined in storage section of admin console (this must be fixed using thge externaldirs.dat file). When lost, re-index should be scheduled immediately, however admin should be aware that the process can take a lot of time to complete (depends on # of accounts and directories on storage). When the cache is not available, IMAP will not return all directories (in IMAP terminology mailboxes) when the xlist command is issued.

• GroupWare. When it is lost, GW data will not be present - no calendar events, notes, tasks, etc. (mails are not affected). GroupWare service indicates this state in logs.

• ActiveSync. As it was stated before, this DB holds synchronization metadata as well as devices and their settings, ABQ mechanism uses this DB as well. When it is lost, all EAS setting will be doomed, all clients (mobile devices mostly) will go for full resynchronization as record of synchronization keys will not match. Full synchronization can take hours (especially for users who have a lot of GW data and/or emails, for example, managers).

• AntiSpam. When it is lost, user Black List/White List will not be accessible/applied.

• AntiSpam reports. Challenge script will not work therefore reports will not be sent, this can be easily discovered in logs.

• WebClient The server will generate a new DB on user login to WebClient. If there are hundreds of accounts logging at same time (e. g. when users start to work), server storage performance can be significantly decreased. There are two types of flags: IMAP flags (red flags) and WC flags (colored ones). When this DB is lost, colored WC flags are lost that can effect the workflow. IMAP flags are handled by IMAP so unaffected by loss of this DB.

Best Practices

• Oracle MySQL / MariaDB / Percona MySQL offer the best functionality.
• Use SQLite only for testing purposes.
• It is better to keep databases split. Do not use one for everything. Accounts are essential especially on Linux with remote config so better keep them separate. GroupWare and WebClient should be separate too. As for dircache + EAS, AntiSpam + reports, they can go together.

Migration

• There can be issues when migrating from SQLite to any other DB engine. SQLite does not respect column type definitions (it can store more characters than allowed, null is empty string).
• Most issues can be overcome by altering table definition in calendar.xml.
• Sometimes few records have to be located using query and updated in the same way. IceWarp Server's SQL manager can help with this.

Migration of WebClient DB

• Migration should be made without client sessions opened. In order to force that, stop control service, delete session data from php/temp, disable access to webmail (you can use firewall to block access temporarily) and then start the migration.
• When migrating WebClient DB, loss of entries with colored flags can occur. This is not a problem on servers where these are not used as missing entries in cache will be automatically added by webmail when users log into WebClient.
• Cache size and number of colored flags used can be measured by attached script. Compare results before migration and after it. Extract the cache_meter.zip file under the html/ folder and access it via a web browser. There is no need to configure anything as the script reads DB settings in similar way as WebClient does. There is more information in the readme.txt file.

WebClient Tips and Tricks

• Keeping the IceWarp WebClient cache in database requires a server with sufficient resources (especially RAM) to hold the database. External database server is recommended. Be aware that cache is growing steadily. Also, the DB engine will need a proper configuration.
• Moving to DB server can lower load put on mail server's storage or can free a considerable space on storage.
• If you use a single DB for all users, deleted account data will persist in cache after deletion. This problem can be solved by running a following queries:
  • DELETE FROM item WHERE folder_id IN (SELECT folder_id FROM folder WHERE account_id LIKE :accountID;
  • DELETE FROM folder WHERE account_id LIKE :accountID;
  • Where :accountID is a placeholder for full account email which you must know.