Mail Archiving System provides a way to archive emails sent/received by EmailArchitect Email Server to a database server such as MySQL and Microsoft SQL Server, and let administrator and user to query and recover email from database. Once this feature is enabled, all archived emails will be stored in database.
System Requirements
For MySQL Server, version 4.1 or higher WITH MySQL Connector/ODBC 3.51.25 Driver installed. The driver can be downloaded from http://dev.mysql.com/downloads/connector/odbc/3.51.html
Important Startup Variables in MySQL
By default, MySQL server limits the data size sent from client. To archive large email, max_allowed_packet variable in my.ini must be changed. You can find my.ini in the MySQL installation path. Open it with notepad and add this line:
[mysqld] //find the mysqld section and add max_allowed_packet = 120MMake sure max_allowed_packet is double of the maximum message size limited in EmailArchitect SMTP Service. Otherwise, large email will block the Archiving System.
For Microsoft SQL Server, SQL 2000 or higher is required. SQL Express is not recommended due to performance issue.
Data Connection String
A Data Connection String must be specified for the Archiving System to function. This connection string is used by the Archiving System to connect database server and execute SQL statement. After the connection string is defined, use "Test Connection" function to verify the database connection.
Connection String Reference
Warning: incorrect data connection string will degrade the performance of EmailArchitect Server.
For other connection strings, please contact our support team.
Using desktop database engine, such as MS Access, is not recommended. They are too slow and in turn degrade the server performance.
Database Server Driver
In Data Connection String, you should specify a database driver to connect database. You can open "Control Panel" -> "Administrative Tools" - > "ODBC Data Sources" - "Drivers" to check current installed database drivers.
Common SQL Driver Download
If SQL Server is installed on a remote server, and you don't have SQL driver installed on local machine, then you need to download and install corresponding driver on local machine.
MS Access Database Driver Download
Microsoft Access Database Engine 2010 Redistributable
Test DB Connection
After "test connection" is performed, two tables "eas_archive" and "eas_archive_owner" will be created automatically in database. Now, if you have "Mail Archive is active" checked and click "OK", the Archiving System will start to work. You can send a test email, and click "Archive Query" to view your email archive. For MySQL database, we suggest you to send a large email (larger than 5MB) to test if the "max_allowed_packet" is set up correctly.
Options
There is an option "User can query mail archive". If it is checked, user can query and recover the archive from Web Mail -> Options -> Mail Archive. Nevertheless, except for "system" user, domain administrator and normal user don't have permission to delete email from the Archiving System.
History Query Connection String
The Archive System will take up a lot of storage after prolong use. If your database is large enough, you can consider to set up another database for archiving new email. It is easy to change the archive database, the only thing you need to do is to change the data connection string. You can still query the archive from the old database server by inputting the database connection string here.
The History Query Connection String syntax: [name]:[data connection string] Each line presents one connection string.
For example:
2007 Archive:DRIVER={SQL Native Client};SERVER=localhost;DATABASE=test;uid=myuser;Password=mypassword;
2006 Archive:DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;user=myuser;Password=;OPTION=3;
When you query the archive, you can choose "Default"/"2007 Archive"/"2006 Archive" to query emails. You can specify any easily understood name to each connection string.
Automatical deletion of old emails
You can set up "Delete the archive from database after specified days" based on the domain name. For example:
emailarchitect.com:5 adminsystem.com:365Above rules mean the Archive System will delete the email belong to "emailarchitect.com" 5 days after the email stored in database, and Archive System will delete the email belong to "adminsystem.com" 365 days after the email stored in database. If you don't specify the rule, the archive will never be deleted. Alternatively, you can delete the archive from database directly with SQL query statement. For example: the following SQL statement will delete all emails received before 2005-01-01
DELETE FROM eas_archive WHERE mail_datatime < '2005-01-01 00:00:00' DELETE FROM eas_archive_owner WHERE mail_datatime < '2005-01-01 00:00:00'
Stat Email Traffic by SQL Query Statement
Another benifit of the Archive System is that you can stat the email traffic by querying the eas_archive table. See the following samples:
Stat the number of emails sent by authenticated user since 2008-05-01 00:00:00
SELECT count(mail_size), auth_user FROM eas_archive WHERE auth_user <> '' AND mail_datetime
> '2008-05-01 00:00:00' Group by auth_user ORDER BY count(mail_size) DESC
Stat the total size of emails sent by authenticated user since 2008-05-01 00:00:00
SELECT SUM(mail_size), auth_user FROM eas_archive WHERE auth_user <> '' AND mail_datetime
> '2008-05-01 00:00:00' Group by auth_user ORDER BY SUM(mail_size) DESC
Stat the number of emails received by IP address(anonymous user) since 2008-05-01 00:00:00
SELECT count(mail_size), source_ip FROM eas_archive WHERE auth_user = '' AND mail_datetime
> '2008-05-01 00:00:00' Group by source_ip ORDER BY count(mail_size) DESC
Stat the total size of email received by IP address(anonymous user) since 2008-05-01 00:00:00
SELECT SUM(mail_size), source_ip FROM eas_archive WHERE auth_user = '' AND mail_datetime
> '2008-05-01 00:00:00' Group by source_ip ORDER BY SUM(mail_size) DESC
Administrator can adjust the Traffic Control based on the statistic result.
See Also
Quick Tutorial User Permissions Domain Administration User Administration Services Administration SMTP Service POP3 Service IMAP4 Service Remote Object Call Service Webmail Service SSL Configuration Realtime Black List Anti-Spam Anti-Virus List Administration Traffic Control DBConnector Mail Archive DomainKeys and DKIM signature Storage and User Mailbox Incoming/outgoing Filters Advanced Functions in Filter Templates
EmailArchitect Server
Website
EmailArchitect Server Community
MS Access:
Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;
Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\\mydatabase.accdb;Uid=;Pwd=;
ORACLE:
Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=Username;Pwd=asdasd;
MySQL:
DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=myDatabase;USER=myUsername;PASSWORD=myPassword;OPTION=3;
Driver={MySQL ODBC 5.1 Driver};Server=myServerAddress;Database=myDatabase;User=myUsername;Password=myPassword;Option=3;
MS SQL Server 2005 Native provider:
Provider=SQLNCLI;Server=serveraddress;Database=database;Uid=user;Pwd=password;
MS SQL Server 2008:
Driver={SQL Server Native Client 10.0};Server=serveraddress;Database=database;Uid=myUsername;Pwd=myPassword;
MS SQL Server 2008 Native provider:
Provider=SQLNCLI10;Server=serveraddress;Database=database;Uid=user;Pwd=password;
MS SQL Server 2012:
Driver={SQL Server Native Client 11.0};Server=serveraddress;Database=database;Uid=myUsername;Pwd=myPassword;
MS SQL Server 2012 Native provider
Provider=SQLNCLI11;Server=serveraddress;Database=database;Uid=user;Pwd=password;