Tracking SQL syntax errors live in production environment
Our software is constantly upgraded and adds new capabilities and processes, which means that our database is also being upgraded and added new queries and we are required to perform QA on new developments that include new queries.
Most QA professionals do not even think about the possibility of a code problem in the database, but it is important to understand that the database is functioning by running queries made of SQL code, so these queries may have code errors, therefore they need to be tested like any other software code.
But, is there an ability to simply locate these problems without performing QA on the query itself?
In most cases, problematic queries will be detected during the tests because screens will stop functioning and processes will fall and data will be lost.
Where is the problem?
The problem starts when the code problem occurs in the production environment. Each query execution is a test case because the data the queries work on changes all the time and when there is a code problem in the queries in the production environment we must find it quickly in order to eliminate the possibility of losing important data, but how do we know whether there is a code problem in queries?
What should we do?
1) The complainants complain about a software problem (or not) and the problem is being investigated by the software, QA, DB, and IT professionals.
2) We do not know the problem at first and we lose data.
The process of identifying a code problem in a query is complex and it takes a long time to locate the problem and it requires the following:
- Restoring the error, screen or process that led to the problem, using Profiler to record all traffic in order to detect the problem.
- Investigation of an extended event that contains many records and detailed data in a complex XML format.
As stated this process takes a long time and excessive resources so it can affect the system’s availability.
The conclusion
It is necessary to streamline the process and shorten the detection time in order to continuously investigate the code errors 24/7 in order to prevent system malfunctions and failures immediately and most importantly to prevent loss of data.
Performing a daily process that collects the information and catalogs it constantly and enables analysis of the problem for a long time and alerts you when a code problem is found in real-time and helps the IT personnel (DBA, QA, IT managers and software developers)
