Magento 2 Split Database Solution (Enterprise)

Magento 2 Community Edition introduces a plethora of new features compared to 1.x, while Enterprise Edition offers even more advantages, including the Magento 2 split database solution. This significant improvement provides the ability to utilize 3 master databases simultaneously, increasing the performance of your ecommerce website. The Magento 2 split database feature incorporates such sections of the platform as orders, checkout, and product data. Each one can possess its own master database which supports replication, so you can easily scale load from Magento 2 checkouts, browsing, or order management and merchandising activities.

The platform incorporates the unified MySQL database connection available in the ResourceConnections class. In their turn master database queries rely on the CQRS database pattern which utilizes its routing queries logic to appropriate databases. It is also necessary to mention that there are no independent read and write connections, and as a developer you don’t have to know what configurations are used.

If you are not familiar with the CQRS, it is command query responsibility segregation – a principle when a method can be a command performing an action or a query returning data, but never combine both characteristics simultaneously. Thus, the key principle upon which CQRS is based requires querying to be separated from executing commands.

As for the database replication, it provides such features as data backup, scalability, and data analysis which does not affect the master database. Since the MySQL database replicates asynchronously, slaves don’t have to be permanently connected for receiving master updates. The following image illustrates the Magento 2 split database solution.

As you can see, Magento 2 Community Edition incorporates only a single master database, while Magento 2 Enterprise Edition utilizes three master databases simultaneously supplemented by a customizable number of slave databases responsible for replication. Furthermore, there is a universal interface for all database connections which helps to improve both performance and scalability.

Magento 2 Split Database Prerequisites

The Magento 2 split database feature requires 3 MySQL master databases (you already have one in your MAgento 2 installation, so it is necessary to add two more databases) to be set up on any host. You can use the Magento server server for all of the, set up each one on a separate server, etc. One master database is used for checkout tables, another one is utilized for  OMS tables, while the last one is incorporated in the maintenance of the remainder which is used in the Magento 2 application tables. In this mechanizm, slave databases act as backups and load balancers.

magento_checkout, magento_oms, and magento are all the master databases described below. You can use other names for your particular databases.

Magento 2 Split Database Implementation

You can enable the split databases feature at whatever time after installing Magento 2 EE. Please note that it is possible to add two new databases, since Magento 2 EE is installed with one database, to the system with order and checkout data.

To set up additional checkout and OMS master databases you should perform the following actions:

• You should be logged in to the database server. Any user is possible.
• Run a command prompt via this command:

• You will be prompted to enter a MySQL root password. Do it.
• Create new database instances. By entering the following commands, you will gate new databases dubbed magento_checkout and magento_oms. Please note that they have the same passwords and usernames:

•  To quit the command prompt, enter

• The last step requires verifying new database instances. It is important to verify one database at a time:

For the checkout database, utilize the following command:

In case of the order management database, use this one

If everything is ok, the MySQL monitor is displayed. Otherwise, you get an error, so it is necessary to repeat the aforementioned commands.

Magento 2 Split Database Configuration

Now when you have three master databases, it is necessary to configure the Magento 2 system to be able to leverage them. On your Magento 2 server be logged as a user with the permission to write to the file system. If you are looking for a convenient way to run commands from any directory, always add <your Magento install dir>/bin to the existing Magento system PATH. You should also pay attention to the variety of syntaxes, so it is extremely important to examine such sources as stackexchange before going any further.

To configure the checkout master database, use the following command syntax:

To configure the OMS master database, utilize this one:

If everything is ok, the system will inform you that the migration procedure has been finished successfully. Now, you should verify the Magento 2 split database implementation.

Magento 2 Split Database Verification

The new master databases implement the following configuration: 250 tables for main database; 10 – for checkout database; 54 – for OMS. To verify they working correctly, do the following:

• Add several items to a cart. You will verify your checkout database is working properly. And don’t forget to check if rows have been added to such tables as quote, quote_item, and quote_address.
• Complete your order to verify if your OMS database is working as expected. New rows should be added to such tables as sales_order_address, sales_order_payment, and sales_order_item.

After your Magento 2 split databases are verified and everything works as expected, you can set up optional database replication.

Optional Database Replication

The database replication procedure is covered in the following guides: Replication (MySQL documentation) and Master Slave Replication by DigitalOcean. Besides, there is a sample MySQL configurations provided by Magento with the ResourceConnections class README.md. More advanced configuration can be found here.

Final Words

That’s how the Magento 2 split database solution acts. Now, you can easily streamline all features related to this innovation, making your ecommerce website faster and user experience better. For further information, check the following links.

Useful Links And Sources

• Split database performance solution
• Command–query separation
• Introducing the Command Query Responsibility Segregation Pattern
• Command Query Responsibility Segregation (CQRS)
• What is Command Query Responsibility Segregation (CQRS)?