Comprehensive Static Analysis of database deployment

Comprehensive Static Analysis of database deployment

I spent most of my tech career in startups. I like fast-paced opportunities to learn new things and the sense of accomplishment of bringing a new product into the market. My career started with QA. In startups, the ratio of developers to testers cannot be as low as that of large enterprises. As a QA Engineer at a startup, there are always more emails in the inbox than in the inbox. You are the last level of the new version release, so you are always under the microscope. In the early stages of start-up, you may also belong to the "Customer Support" team, so when there is a problem with the product, you will become the busiest one.

Like other people engaged in the same job, I am always very concerned about finding the right tools to relieve my burden, but I cannot sacrifice my personal position on the quality of work. So I met FindBugs 10 years ago. When I used this tool for the first time, I shared the results to the development engineers of the team. They felt that this tool had generated more false positives than real bugs or it was a "nitpicking" method. However, as we constantly adjust and expand the inspection performed according to our own needs, and associate the data obtained by FindBugs with the actual number of bugs found in testing and production, findBugs finally becomes an integral part of overnight build versions and On-Demand build versions. These reports are a good clue to potential problems in the early stage, and allow developers to correct errors in advance to avoid occupying test time or operation failure time. In addition, developers in my team receive system building reminders every day, and these reminders will help them get rid of their bad habits and write code that is safer, more efficient, and more stable, therefore, they have fewer and fewer defects. The release cycle is shortened, the product quality is improved, and the customer satisfaction is also improved, all of which prove that prevention is indeed more important than treatment.

As enterprise IT continues to embrace agile development practices and adopt the DevOps model to launch better products to the market faster, DBAs are actually beginning to feel the pressure. The previous introduction of QA engineers for start-up software companies also applies to DBA. As versions are published more frequently, DBAs receive more and more SQL scripts to be written, checked, modified, or optimized. DBA is the last line of defense for data quality, data security, and data platform performance. Therefore, DBA is the first person to respond to problems.

Among the Fortune 500 DBAs that work with us, the most time-consuming task in their daily work is to check SQL. Some DBAs spend 70% of their time manually checking SQL scripts. They will check SQL like FindBugs and other tools to check Java code: encoding patterns, security vulnerabilities, performance issues that indicate logical problems, and compliance issues that violate internal best practices or external regulations.

Obviously, DBA needs the same tool as FindBugs a decade ago. SQL static analysis is not a new technology, but currently only available products can achieve static analysis. Generally, they evaluate SQL statements without considering the context. This limitation will affect the final productivity and quality, because most of the time you manage the database lifecycle, you know who is performing what operation at and where. For example, an organization may be allowed to assign permissions to a test environment and execute INSERT statements, but they are never allowed to be executed in automated tasks in the production environment. Environment parameters must be considered for any SQL static analysis tool.

What makes the problem more complicated is the database "version change ". Although applications of various versions are packaged, set, and fully replaced, the data modes that support applications are constantly evolving. In addition, external compliance standards and internal audit requirements usually require strict control of database incremental modifications, and use a strictly defined process to track changes. This means that DBAs must also manually process and check SQL comments) to identify the cause of the change and track the execution results of the change in each environment.

DaticalDB Rules Engine specifically considers the special challenges posed by SQL checks and static analysis in its design and implementation. The following are some reasons why Datical DB improves efficiency safely and reliably through static analysis:

• Model used to perform a strong evaluation-- Datical DB abstracts the application pattern into an object model that is strictly defined and verified. The authorization process for these powerful rules is fast and simple. Once written, they will take effect when each database in the lifecycle executes Forecast or Deploy.
• Verify the modification of the perceived Environment-- This model contains information about the client environment and various database instances in the application lifecycle. You can write rules to maximize the flexibility of the environment in the early deployment phase and to maximize the security of the sensitive environment.
• Confirm internal and external audit requirements as soon as possible-- In Datical DB, to comply with external and internal audit requirements, you only need to closely bind the changes to the data model. Automated checks executed at each or automated framework) Forecast or Deploy replace manual checks to validate the reviewability of changes.
• The most important aspect of Automatic Verification for you-- Provides the custom analysis function to cover your internal best practices, such as naming conventions, allowed and not allowed SQL and object dependency management.
• Automated repetitive tasks-- Similar to many static analysis tools for source code analysis, Datical DB can be integrated into the building or deployment system with some simple mouse operations. Now, every time you build or deploy an application, it executes verification rules and generates a report for the entire organization. Since DBA significantly reduces the screen time, they can focus on more important projects and problems.
• Higher code quality means fewer bugs-- DBA makes rules and shares them with developers. Developers then obtain a knowledge base for encoding that is accepted and not accepted by their organization. Reducing the number of bugs during development will greatly increase the node time and money.
• Allows O & M personnel to participate in Database Development-- This rule engine is highly integrated with Datical DB Forecast. This feature allows you to simulate database modification without modifying the target database. When the DBA shares their rules with the O & M personnel, the O & M personnel can execute Forecast on the STAGE or PROD environment every night, make sure that the modifications made to the DEV or TEST environment comply with the strict verification performed by the downstream, which can also be found in the early stage of the lifecycle. Therefore, the code to fix the problem is lower and more difficult.