Dual DB Schema Setup with Drupal

Anyone that has worked on an enterprise level Drupal application knows that having the transactional consumer data separate from your content will save you time and headaches during a release. With Drupal this is very much possible and it's fairly easy to setup. This can be setup with the DB on a separate server or on the same server where you currently have your Drupal DB residing.

The first thing that you will need to do is edit your "settings.php" file for you drupal installation and add the following array block to configure your second schema:

$databases['transactional_db'] = array('default' => array(
   'driver' => 'mysql',
   'database' => 'Name_of_your_Database',
   'username' => 'Database_username',
   'password' => 'some-password',
   'host' => 'mysql-server-host-name',
   'port' => 3360
));

Next step, is to copy the Drupal Database API functions that you need (like: db_select(), db_insert() and other) into your own custom module and prefex the function names with a prefix of your liking. I would suggest something like transactional_db_select(), where "transactional_" is your prefix. Esentially, all you are doing is copying the original Drupal API functions into a module, renaming the functions accordingly and adding two additional lines to each function that you have copied. In addition, you will need to create a Drupal variable (I called it "secondary_db_schema") where you will store the alias that you gave above in "settings.php" file, in my case I chos "transactional_db" as the alias. After the above step this is what your modified function should look like (the two additional lines of code in the below function, will be the same for the other Drupal DB API functions that you choose to modify):

function transactional_db_select($table, $alias = NULL, array $options = array()) {
   db_set_active(variable_get('secondary_db_schema', 'default')); //Added line of code. Setting the "transactional_db" as active.
   if (empty($options['target'])) {
      $options['target'] = 'default';
   }
   $result = Database::getConnection($options['target'])->select($table, $alias, $options);
   db_set_active('default'); //Added line of code. Setting the "default" to be active again, as soon as you are done with your query.
   return $result;
}

It is important to note that this is not the only way you can achieve this in Drupal, there are probably at least two other methods to get this kind of Database setup. This method might now work for everyone, but it did workout great for me. You might also consider looking at "Master" and "Slave" configuration if this does not fit your needs. Hope this helps someone out.