Why do I Need SQL Change Automation to Build a Database?
Published 23 August 2019
It is simple to build a small, standalone database application and there are open-source tools to help. However, for databases of any size, complexity, and importance, you will encounter the following problems.
Databases can grow to the point where there are thousands of tables and procedures, and you may have to deal with several databases, perhaps even on different instances. The size of the data can be daunting, to the point where even a simple alteration to a column of a table can take hours. Manual processes will work with a database the size of a big spreadsheet, but become impossible with a large database supporting a business.
Speed of change
When most database systems were designed, daily delivery of changes was Science Fiction. Now, businesses expect it. To achieve such delivery rates for new functionality, the automation of every possible routine process is essential, especially given the time a deployment takes.
A database application can quickly evolve into something more complex, where the database serves several applications, as well as down-stream reporting systems. It might have data feeds and ETL links with several other systems. There will be many dependencies. It will need elaborate testing; not only integration and unit testing, but also scalability, resilience and penetration testing. This will involve several teams, possibly with different tools and workflow systems. There must be a common and secure means of passing database artifacts between teams, and ways of ensuring that version numbers tie in exactly with code.
There are a host of regulations with which a business must comply, in their applications. These deal with the ways that accounts are kept, reconciled and checked, with ways of providing audit systems. Even the way in which the application's interface works is heavily regulated for the health and safety of the users. There is growing privacy legislation that requires explicit sign-off for the way that personal information is handled. Security and penetration checks are compulsory. All public-facing applications must be checked on every release by representatives of the organization to ensure that the database application complies with the regulatory framework, and report to the board or trustees that it does so. All this requires a means of inspecting code, and a fast and automated way to deploy the database to run checks.
The full test cycle for a large database application can take a long time, months in the case of some financial and banking systems. There are several different types of test that are required, such as scalability, security and resilience; and in some business sectors they are required in law. This requires rapid and secure deployment of databases in test cells. Automation and team workflow, together with robust 'versioning' of the database, provides the best way of speeding the process sufficiently to allow rapid delivery.
The more complex the database application, the more the need for the structured and automated delivery process that SQL Change Automation provides, the outputs of which can be inspected and verified by the developers, as well as operational staff, security experts and others.