Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog How DMS Can Save the Life of DBAs?

How DMS Can Save the Life of DBAs?

In this article, we'll discuss how Alibaba Cloud DMS saves a lot of unnecessary overhead of a DBA and how enterprises can make use of it in their daily operations.

By Ankit Kapoor, Database Senior Solution Architect

In my previous post, "What is DMS?," I have explained what ABC Data Management Service (DMS) is, what it is mainly used for, the different types of databases it supports (can be OLTP,OLAP and NoSQL be it on-prem or on cloud), and also its common use cases including encryption and data rollback. That post was mainly to provide readers a high level information about DMS so that they can identify how DMS can be useful in their daily work.

But that article was limited in terms of the best practices, which required users to explore DMS entirely on their own. Hence, I decided to write this article so that it can act as a reference of various real-world use cases and thus simplifying tasks for DBA by removing unnecessary tasks.

In this article we are going to discuss how DMS can save a lot of unnecessary overhead for DBAs, and how it makes life easy while providing data security (which is its main advantage). I will also discuss how enterprise businesses can make use of DMS in their daily operations, and share various use cases which a DBA normal encountered with.

DMS has been adopted by various businesses like Food delivery, e-commerce, financial, gaming customers.

Development Phases of the DMS

DMS, also known as Data Management Service, as its name implies helps in managing various databases and it can also help in automating various monotonous task. For example, in a daily office environment, we have to request DBA for various changes in the databases (like adding a column for example), and all these requests are made via various ticketing tool like JIRA on which the DB owner has to take an appropriate action on the change request post, which DBA will act accordingly and then he has to execute the change manually. This entire task is tedious and an unnecessary overhead for the DBA, which can be removed if we automate it and make all the processes to be executed in one place.

DMS usually comes in 3 different modes:

  1. Flexible Management
  2. Stable Change
  3. Security Collaboration

To know the difference and the pricing of 3 modes, you can refer to this document as this article does not focus on the pricing module.

What Should Be Streamlined Prior Schema Designing

Think of the early days of your database setup where DBA receives various requests to design and setup the schema. Now as a DBA, we want to make sure that the specified rules should be followed while creating a table or a database. For example, all tables should have a primary key (since in MySQL it is advantageous to have a primary key. Otherwise, Innodb can create its own hidden primary key of 48 bit if there is no non-null unique key in the table, which will be useless and occupy unnecessary space).

Now it is pretty hard to maintain or to ensure that such things will get implemented for sure since there are many chances of human error too. Automating this task can surely help a DBA to overcome the overhead involved. In the managed services, such rules can easily be prepared, which will help in setting up the environment as per the prescribed rule.

1
How an organization wants the table

In the above rules, whenever a table has been created, one column with the primary key will automatically get added into this table. This is helpful for the business as the entire process is streamlined and no table can be created without a primary key.

There are various situations where the DBA needs to make sure that no tables shall be created with any keyword related to the databases, and such things can be restricted with the help of a DSL. DSL, or Domain Specific Language, is a specialized language that has been used for some specific purpose and is not similar to the general languages. Some of the examples of DSL are SQL, HTML, and CSS. We can also design our own DSL as shown below.

if
    @fac.table_kind == 'new' and
    @fac.table_name in ["add", "all", "alter", "analyze", "and", "as", "asc", "asensitive", "before", "between", "bigint"] 
then
    @act.block_submit "[R & D specification] table name is wrong"
end

2
Table having an unwanted name will be rejected

Similarly, with the help of DSL, rules for primary key can also be set up:

if
    @fun.array_size(@fun.extract_index(@fac.table_index_array, ['Primary'])) < 1
then
    @act.mark_high_risk 'Table has no primary key'
end

You can then configure the development process for the table. For example, you can determine the steps of table structure design and the allowable behavior of each step in different environments, including testing, development, and production.

3
Environmental execution flow

Ideally every change needs the approval of the previous step, and hence the above process can be summarized as:

  1. Set up the table design and its internal default architecture.
  2. Approval for execution.
  3. Once approved execute in the next environment.

Automating these 3 tasks can surely remove the unnecessary overhead of DBA and development can get streamlined. In the image below, we have separate nodes for each execution step i.e. table design and approval node without which process cannot be executed and make ensure that the rules must be followed.

4

So far, we know that if in the designing phase development needs to be streamlined:

  1. Table rules must be designed i.e. table default structure, remarks, unwanted names using DSL (Domain specific language).
  2. Approval Flow i.e. who will approve the request.
  3. Number of environment and their priority number.

One thing I missed mentioning in detail is about the approval flow as who are responsible for approving the data change requests. In the below snapshot, approval orders are mentioned in ascending order and then the owner name.

5

Things That Should Be Streamlined Post Designing

Once the schema has been successfully set up, we often receive various requests from various stakeholders. For DBAs, the first step is to analyze the request to determine why those changes are required, and make sure it does not pose any threat to the database in terms of performance and security. Every data change should be analyzed before approval.

6
Submitting data change request

7
Ticket to be approved

With the help of DSL, one can put the restriction on various operations such as limiting the number of read operations to be executed in a day:

if
    @fac.sql_count > 1000
then
    @act.reject_execute 'The number of SQL statements executed at a time cannot exceed 1,000'
else
    @act.allow_execute
end

Maintenance Operations

One of the main operations we can talk about is online DDL (to make changes in the database structure without holding the locks) where we have several open source tools for example Percona OSC, gh-ost and Facebook OSC, which can perform changes in the production environment without locking your table.

But Percona OSC works on the trigger to track the changes happening while executing the required change. Triggers can also be harmful in the production environment since they work in the same transaction where the table is getting updated and can cause locks, whereas gh-ost is a triggerless approach that reads changes via binary logs. Similarly, this task can be achieved by the managed services like DMS, which will also be followed by the approval request.

Until here I have tried to cover most important components that can be managed. Now before ending let me also give a brief idea as how reporting can also be automated with MPP databases like AnalyticDB for MySQL.

Use MPP Database with DMS to Generate Reports

Massive parallel processing (MPP) databases can be useful when there is a requirement to use heavy joins on the petabytes of data with high concurrency. In some cases, we need to schedule these reports and which can easily be automated via orchestrations. However, I am not going to cover this topic in detail for now.

Best Practices

One of the main use cases I usually encounter is to recover from a change that has been executed by mistake. For example, if I talk about my favorite database MySQL, it maintains a file to keep track of all the changes, which can be used for replication, PTR (point in time recovery), and incremental backup. We call this file as "binary logs," and it resides in the default data directory if not being modified to other location. Using this file, I can fetch the any SQL statement being executed at certain time stamp. If in case I have updated multiple records mistakenly and I need to find the previous data, then I will use the mysqlbinlog command to find the correct statement. This entire process is time consuming as I need to go through all the available binary logs since I might not know which binary log I have to read from. If this process is automated, it can save a lot of manual efforts.

8

In short, managed services like Alibaba Cloud DMS can remove various unnecessary tasks of DBAs and can make their life easier.

For those who are interested to learn more about DMS, I recommend you to check out my previous article on DMS, where I explained step-by-step on how to achieve this using the managed services like DMS.

Database Database Optimization Data Management Service AnalyticDB for MySQL ApsaraDB for MySQL DBA
0 0 0
Share on

Read previous post:

Data Encryption, Semi Masking and Recover Deleted Data Using DMS

Read next post:

Sharding in Polar-MySQL with DMS

Your Friend in a need

4 posts | 0 followers

You may also like

Comments

Your Friend in a need

4 posts | 0 followers

Related Products

More Posts by Your Friend in a need

See All