Skip to main content

Run SQL changes across sharded databases

In a sharded database architecture, the same schema or data change often has to run across many databases or tables. NineData database groups help teams submit one SQL task and execute it consistently across the selected sharded databases.

Common Challenges​

Sharded database changes are difficult to manage when teams rely on manual execution or one-off scripts:

  • The same DDL or DML statement may need to run across many shards.
  • Each data source requires connection handling, credentials, and execution tracking.
  • Partial failures need to be captured and resolved without losing visibility.
  • Scripts created for one change are often hard to reuse for the next change.

Solution Overview​

NineData Database DevOps Professional Edition and later support database groups. A database group can include databases from different data sources. After the group is created, users can submit an SQL task against the group, and NineData plans and executes the statement across the member databases.

Database group workflow

Prerequisites​

Before you run a sharded database change, make sure that:

  • All target data sources have been added to NineData.
  • The target databases are ready to be added to a database group.
  • You have permission to create database groups and submit SQL tasks.
  • SQL development policies and approval processes are configured for the target environment.

Configure The Database Group​

1. Add Data Sources​

Add every data source that contains a sharded database to NineData.

Add data sources for sharded databases

2. Create A Database Group​

Add the sharded databases from the relevant data sources to one database group. In this example, the database group Poc_Shard_DB contains four sharded databases: poc_test01, poc_test02, poc_test03, and poc_test04 from the data sources MySQL-POC1 and MySQL-POC2.

Create a database group

Run A SQL Task Against The Group​

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

    Submit an SQL task for a database group

  2. Review the execution plan generated by NineData. The system identifies the target databases and checks the SQL against the configured development standards.

    Review the SQL execution plan

  3. Submit the task for approval and select the approver.

    Submit the task for approval

  4. After approval, execute the task. If execution fails on a shard, the task list shows the failed database so that you can investigate and retry.

    View SQL task execution status

  5. Verify the target tables after execution.

    Verify the schema change on target tables

Result​

The SQL statement is executed across all databases in the selected database group. The task details provide a shared approval record, execution status, and failure visibility for the change.

Next Steps​