How to Make Changes to Multiple Sharded Databases and Tables with One SQL Statement?

Databases have evolved to a point where sharding is no longer a novel concept. Traditional single-node database architectures encounter performance bottlenecks and scalability issues when the volume of data and frequency of access reach a certain scale. Sharding technology distributes data across multiple database instances to share the load, thereby enhancing the overall performance and stability of the system.

Of course, this article is not about how to implement sharding, nor is it to praise how great sharding technology is. Instead, it aims to specifically discuss the data change challenges that many enterprises face in sharding scenarios.

Many of you must have a deep understanding that in the practical application of sharding, since a database's data is stored in different databases, a DDL statement often needs to be executed in multiple database instances, which brings a series of headaches.

Challenge one: Changes need to be executed in every sharded table. If a company has 1024 sharded tables, then it needs to be executed 1024 times, which is extremely time-consuming and prone to errors.

Challenge two: If you write a script to execute in batches, the technical threshold is high, and you also need to pay attention to all kinds of details:

• Connecting to data sources one by one: If there are too many databases that need to be changed, you have to write as many connection scripts as there are databases, which is a burden for developers.

• Error handling: SQL statements are not 100% successful, and there may be various factors that cause execution failure. Therefore, the script must be able to capture various errors that may occur during the execution and handle them properly after the error occurs to ensure that the change statement is successfully executed in all tables after the script is executed.

• High script maintenance cost: Due to the diversity of change scenarios, after spending a lot of effort to write the script, it can only be used for the current change. Next time there is a new change requirement, it may not be directly applicable, and the script needs to be optimized again.

At this point, some of you may have resonated and are welcome to identify with it, because most of the enterprise's database changes are actually done this way. If you want to get rid of these pain points, there must be a way. Is it possible not to do sharding? Of course not, the importance of sharding for enterprises is self-evident. We need to provide solutions while ensuring sharding.

Let's imagine the following scenario: divide all the databases that need to be changed uniformly into a database group, and then only need to submit a DDL change to this database group, then the DDL statement will automatically execute in all databases under this database group.

Doesn't it look very convenient? Let's take a look at how to implement the above process.

Implementing Batch Sharding Changes through NineData's Database Grouping Feature

In NineData's database DevOps professional version and above, database grouping is supported, which allows you to add databases from different data sources to the database group for unified changes and queries.

Let's briefly introduce the configuration process:

Based on the above process, let's demonstrate the configuration method.

Step One: Enter Data Sources

Enter all the data sources where the sharded databases are located into the NineData platform.

Step Two: Establish Database Groups

Add all the sharded databases in different data sources to the database group. In the example below, a database group named Poc_Shard_DB is created, which includes 4 sharded databases poc_test01, poc_test02, poc_test03, poc_test04 from the data sources MySQL-POC1 and MySQL-POC2.

Step Three: Perform Table Structure Changes on Database Groups

Through NineData's SQL Task feature, initiate a change request for the database group. After double approval by the system (standard pre-audit) and the approver, the DDL statement will be executed in all sharded databases in the database group.

1. Submit an SQL task, select the database group, and enter the DDL statement. This example adds an age column to the Poc_Shard_DB database group.

   

2. The system automatically plans the target database for DDL execution (see table below), and then evaluates the DDL statement based on the standard, which can be submitted for manual approval after passing.

   

3. Submit for approval, select the approver, and then click OK.

   

4. After approval, it can be executed. During the execution process, if the DDL is not executed successfully in a certain sharded database, it will be displayed in the task list, which is very convenient.

   

5. After execution, you can check these groups and find that the corresponding age field has been added to the target tables in all sharded databases.

   

Summary

The entire change process in this article is centered around database groups. Database groups can be freely added according to the actual business scenarios of the enterprise, which is highly versatile and very convenient to operate, so it can be applied to various scenarios of database changes.

As for the several change difficulties mentioned above, NineData's database group solution can easily resolve them. From now on, database changes only need to click a few times to complete, and you don't have to rack your brains to write scripts.

Finally, add a newcomer gift package. If the number of enterprise data sources does not exceed 10, you can use the above functions for free permanently. Not only that, all advanced features of the professional version can also be used for free permanently. Without further ado, try it directly.