SugarCRM using multiple MySQL databases

Let’s assume you currently have a MySQL Setup with one “Master” and multiple “Slaves”, and you do want to make sure your Sugar system uses the “Slave” instances for reading purposes. Alternatively, if you are looking at deploying MySQL Master/Slave instances with a click of a button to test this out, you can achieve the same setup using Amazon AWS RDS, by deploying one MySQL database with additional read replicas.

SugarCRM commercial editions already have a feature that allows to connect Sugar to a slave database for Reports and/or an additional one for module’s ListViews, providing additional performance and scalability to the application. The feature is provided out of the box and it is available by changing a configuration file, for self hosted systems.

More Details

Where is this functionality? Looking at Sugar’s code, there are multiple calls to the class “DBManagerFactory” (inside include/database/DBManagerFactory.php) that retrieve the correct database instance to use (eg: DBManagerFactory::getInstance(‘listviews’); and DBManagerFactory::getInstance(‘reports’);). By default the method falls back to the main database if no additional databases are provided.

Note that the “Reports” database is also used by the Enhanced Enterprise Reporting, available in SugarCRM version Enterprise and Ultimate.

Sugar Configuration Options

Adding a “slave” database for Reports and/or module’s ListViews is a matter of: maintaining additional real time database replicas and additionally of adding on the file “config_override.php” (present on the SugarCRM main folder) few configuration lines as described below:

Reports:

$sugar_config['db']['reports'] = array(
 'db_host_name' => 'my_reports_slave_hostname',
 'db_user_name' => 'my_username',
 'db_password' => 'my_password',
 'db_name' => 'my_database_name',
 'db_type' => 'mysql',
 'db_manager' => 'MysqliManager'
);

ListViews:

$sugar_config['db']['listviews'] = array(
 'db_host_name' => 'my_listviews_slave_hostname',
 'db_user_name' => 'my_username',
 'db_password' => 'my_password',
 'db_name' => 'my_database_name',
 'db_type' => 'mysql',
 'db_manager' => 'MysqliManager'
);

For additional configuration options available on the software, please refer to this SugarCRM 6.x guide.