Data Management:Streamline table schema deployment

Solution overview

1. Customize the R&D process (optional): The default R&D process is Develop > Production. You can customize the process to meet your business requirements.

2. Set a security rule for a database instance (optional): Associate the custom R&D process with a database instance by using a security rule.

3. Design a database schema: Submit a schema design ticket to ensure schema consistency across multiple databases and successful deployment to the production environment.

Prerequisites

1. Create three ApsaraDB RDS for MySQL database instances, and then create an account and a database in each instance. For more information, see Quickly create an ApsaraDB RDS for MySQL instance.

   In this example, the database instances are named Schema_Design-dev, Schema_Design-test, and Schema_Design-product.

   In this example, the databases are named dev_database, test_database, and product_database.

2. Use an Alibaba Cloud account with administrator or DBA permissions to register the three database instances in Data Management Service (DMS). Set the control mode of all instances to Security Collaboration and set their database environment types to Development (Dev), Test, and Production, respectively. For more information, see Register a cloud database.

   Example of registering an instance

   In the left-side navigation pane of the DMS console, click Database Instances, and then click the + icon in the top toolbar to add an instance. In the Basic Information section, select MySQL as the database type, select China (Hangzhou) as the instance region, and select Instance ID as the registration method. Enter the instance ID (for example, rm-xxx). For Secure Hosting, select Enabled. For the access method, select Log on with Account and Password, and enter the database account and password. To configure more parameters, expand the Advanced Information section. After you complete the configuration, click Test Connection to verify the connection. Then, click Submit to register the instance.

Step 1: Customize the R&D process

Create a security rule and configure the schema design process. The R&D process includes the Develop, Test, and Production nodes.

Step 2: Assign the security rule to an instance

Set the control mode of the Schema_Design-dev instance (Dev environment) to Security Collaboration and select the SD-DT security rule.

1. In the instance list on the left side of the console homepage, right-click the Schema_Design-dev database instance.

2. From the shortcut menu, choose Control Mode > Security Collaboration > SD-DT.

Step 3: Design the database schema

Create a table schema for a database by using a three-node (Develop, Test, Production) schema design project.

Step 3.2: Configure the schema design ticket

1. Create a schema design ticket.

   On the Create Ticket page, set Base Database for Change to the database in the development environment. Configure other parameters as required.

   In this example, dev_database is selected as the base database.

   Other parameters in the form include Project Name, Project Background, Security Rule, Associated Iteration, and Stakeholders. After you fill them out, click Submit.

2. In the Develop node, create a table in the dev_database database.

   1. Click Import SQL Statements, enter the SQL for creating the table, and then click OK.

      Sample SQL for creating a table

      CREATE TABLE `teacher` (
          `teacher_id` varchar(20) NOT NULL COMMENT 'Teacher ID',
          `teacher_name` varchar(20) NULL COMMENT 'Teacher name',
          `phonenumber` varchar(20) NULL COMMENT 'Teacher phone number',
          PRIMARY KEY (`teacher_id`)
      ) DEFAULT CHARSET=utf8 COMMENT='Teacher table';

   2. Click Save. In the Precheck Result dialog box, click Confirm Changes and Submit to Save.

   3. Click Perform Changes to Base Database.

   4. In the panel that appears, select an execution strategy, confirm that the SQL statement is correct, and then click OK.

   5. After the change is manually approved, the system writes the new table schema to the dev_database database.

      On the Change Execution History tab, you can view the execution result for the teacher table. The status is Success, the progress is 100%, and the execution strategy is Execute Now.

   6. Click Go to Next Node.

      Note

      Before proceeding, perform multiple design changes and validations on the dev_database database in this node. The system will eventually generate a single create script to be deployed to the next node.

3. In the Test node, synchronize the table schema created in the previous node to the test database.

   1. Click Perform Changes to Target Database.

   2. Select the test_database database, set the execution strategy to Execute Now, and click OK.

      The Execution Script area displays the SQL statement for creating the teacher table. The statement defines three fields (teacher_id, teacher_name, and phonenumber), sets teacher_id as the primary key, uses the utf8 character set, and includes the comment "Teacher table".

   3. After the change is approved, the system applies the schema to the test_database database.

      Important

      The next node, Production, is an anchor. After you proceed to an anchor node, you cannot roll back to a previous node to perform design or deployment operations.

   4. Click Go to Next Node.

4. In the Production (anchor) node, synchronize the table schema to the production database.

   1. Click Perform Changes to Target Database.

   2. Select the product_database database, set the execution strategy to Execute Now, and click OK.

   3. After you submit the change, it must be approved.

      Once approved, the system applies the schema to the product_database database, after which you can write data to it.

   4. After you click Go to Next Node, the schema design process ends and DMS automatically closes the ticket.

Related operations

• For details about the specific rules in the schema design module of security rules, see Security rules for the schema design module.

• For more information about use cases and features of schema design, see Schema design.

• You can also use an API to get the deployment status of a schema design ticket:

  Get schema design ticket details