Tag: SQL

When AI Gets It Wrong: The Insecure Defaults Lurking in Your Code
The arrival of generative AI in the software development lifecycle (SDLC) is arguably the biggest shift in coding in decades. For development teams, tools like GitHub, Copilot, and other AI assistants act as a massive force multiplier, automating boilerplate, suggesting complex logic, and significantly accelerating time-to-commit.

But as organizations rush to equip their teams, a quiet crisis is forming in the codebase. While AI helps developers write code faster, it also helps them write insecure code faster.

The problem isn’t that the AI is malicious. It is that today’s models do not understand security context or intent. Generative AI models are trained in vast repositories of public code, billions of lines written over decades. This training data, while containing brilliance, also includes millions of deprecated patterns, insecure configurations, and “quick-fix” tutorials that were never meant for production. Because generative AI is probabilistic, it doesn’t understand security. It only understands patterns. If the training data relies heavily on insecure database connections, the AI will simply default to suggesting those flawed patterns to developersx.

The Most Common AI-Generated Vulnerabilities

At Checkmarx, we observe distinct, persistent categories of flaws that AI assistants are prone to generating. These are fundamental security errors that can easily bypass standard peer reviews because they “look” syntactically correct.

1. SQL Injection. It’s Back!

SQL Injection (SQLi) should be a relic, yet Checkmarx researchers consistently observe it as one of the most frequent flaws surfacing in AI-generated code. Why? Because the internet is full of older tutorials that use simple string concatenation to build queries. When a developer asks an AI to “write a query to find a user by ID,” the model may default to older, insecure examples due to prevalence, ignoring modern parameterization. The result is a functional query that opens the door to database manipulation the moment it hits production.

2. Weak Cryptography and Authentication

When asked to handle authentication or hashing, AI models often retrieve “classic” examples from their training data. This leads to suggestions using algorithms like MD5 or SHA-1, standards that have been broken for years, rather than modern, secure alternatives. The code compiles, the tests pass, and teams unknowingly bake a critical flaw into the application’s identity layer.

3. Secrets in Plain Sight

AI models trained on public repositories have “seen” thousands of API keys, tokens, and credentials accidentally committed by other developers. We frequently see AI assistants suggesting code blocks that include placeholder credentials. They sometimes suggest hardcoding keys for “testing simplicity,” a dangerous habit that often bleeds into production.

4. The Supply Chain Trap

AI assistants are eager to help developers import packages to solve problems, but they rarely vet the health of those packages. An AI might suggest a library that is popular but unmaintained, or one that was recently compromised. This introduces a supply chain risk: a single “quick import” can bring in a web of exploitable transitive dependencies.

Four AI-Generated Code Smells Every Developer Should Catch Early

As engineering teams lean more heavily on AI, developers need to develop a new “muscle memory” for reviewing suggestions. Security cannot just be a tool; it has to be a critical step in the coding process. Here are four specific “AI code smells” examples that should trigger immediate skepticism.

1. Hardcoded Secrets and Credentials
- The Smell: The AI suggests variable names like apiKey or admin:admin defaults.
- The Risk: The AI is mimicking bad practices from training data. This causes an instant breach if the code is promoted to production.
2. Hallucinated Logic and Unsafe Defaults
- The Smell: The AI invents functions that “look right” but do not map to real APIs. It suggests permissive configurations or disabled TLS verification.
- The Risk: The AI prioritizes functionality over security rules. This introduces overbroad regex sanitization or opens security holes that are difficult to spot.
3. Vulnerable or Outdated Package Recommendations
- The Smell: The AI suggests a quick install for a package without providing context on its version history or CVE record.
- The Risk: Developers import a library with known vulnerabilities. This compromises the supply chain with exploitable transitive dependencies.
4. High Blast-Radius Refactors
- The Smell: A developer asks the AI to refactor a single function. The suggestion ripples across multiple files and modules.
- The Risk: The AI lacks application-wide context. These subtle changes break security checks, invalidate unit tests, or downgrade dependency safety. Without Safe Refactor capabilities, the resulting rework can be massive.
Moving from “Scan Later” to “Fix Now”

The only way to capture the velocity AI promises without inheriting the hidden technical debt it leaves behind is to bring security context directly into the AI workflow.

Security as a Real-Time Partner

This is a core philosophy behind Checkmarx Developer Assist. Instead of waiting for a nightly scan, Developer Assist runs inside the IDE, validating AI completions in real time.

Because flagging the problem is only half the battle, development teams need solutions, not just alerts, to truly maintain velocity. This is where Safe Refactor comes in. If an AI suggests a block of code with SQL injection vulnerability, Developer Assist catches it before the developer hits “save” and generates a secure, parameterized version of that code, offering to swap it instantly. Developer Assist also maps the Package Blast Radius before a developer accepts a recommendation, analyzing dependencies and vulnerabilities to show the potential impact that a simple import could have on the entire application’s security posture.

AI acts as a powerful engine for speed, but it lacks judgment for security. It requires a partner to distinguish “working code” from “secure code.”

Ready to see safe coding in action? Watch the Checkmarx Developer Assist demo to see how we catch and fix AI-generated vulnerabilities in the IDE.
March 4, 2026
All Your Monitoring Solutions Are Just Wrong
There are many monitoring solutions available in the market. Some of them are ubiquitous and very popular. However, all of them are simply wrong and put the burden of monitoring on the users. They still use the approach that worked well when we didn’t have multi-tenant applications and hundreds of microservices but is not good enough today. In this article, we’re going to see what these solutions lack and what modern monitoring should look like.

What is Wrong?

There are many aspects of why our current monitoring solutions are not enough. Let’s go through them one by one. We’re going to see examples from today’s biggest players in monitoring like Datadog, New Relic, AppDynamics, or Dynatrace. We’re going to see where they’re wrong and misleading.

Generic Metrics

Monitoring systems focus on raw and generic metrics because they are easy to obtain. These metrics are anything that can be generalized across systems and applications. For instance, we can always measure the CPU usage of the application or how much memory it consumes. Doesn’t matter if the application is CPU-intensive or memory-intensive, we can always get these metrics. The same for networking, the number of files opened, the number of CPUs used, or the running time.

Presenting these metrics is also easy. We can just plot charts showing how metrics change over time. For instance, below is an example of a Datadog dashboard that shows generic metrics.

The problem with these metrics is that they are general and do not tell us much. Okay, maybe the CPU spiked. So what? Or maybe the application uses a lot of memory. Is that a problem? We can’t answer that easily without understanding the application. Let’s see why.

Memory consumption is a great example of a metric that we can always capture but can’t easily understand. There are many types of memory. There is native memory, there can be managed memory (in JVM or .NET, or any other managed runtime), there can be resources (like graphics or videos) and data structures, the application may use eager garbage collection or a lazy one, there can be many different GC algorithms, and so on. We typically don’t focus on the memory usage in our applications and we let automated GC to deal with it.

Let’s now take a situation in which the server has tons of free memory and we didn’t need to run the garbage collection. The memory consumption metric may be steadily increasing. Is this a problem? That depends. If the memory can be released (and will be released when we call the GC), then it’s not a problem at all. However, if we can’t release the memory for some reason, then it becomes a problem. Another aspect is what type of memory we consume. If we’re running a Java application and we see an increase in the managed memory, then it’s perfectly fine. However, if we see the native memory going up and the managed memory staying flat, then it may be an issue.

It’s easy to capture metrics. It’s hard to understand if these metrics are useful.

Recommended reading: Database Monitoring Metrics: Key Indicators for Performance Analysis

Too Many Metrics

Another aspect is how many metrics to collect and how to group them. It’s not enough to “just track the CPU usage”. We need to group the metrics based on the node type, application, country, or any other dimension.

However, this is where the problems begin. If we aggregate metrics under one umbrella “CPU”, then we won’t see severe issues affecting only a subset of the metric sources. Let’s say that you have 100 hosts and only one of them hogs the CPU. You won’t be able to see that when you aggregate the metrics. Yes, you can come up with p99 or tm99 metrics instead of an average, but that doesn’t help either. What if each single host gets a CPU spike one after another? Your metrics won’t be able to find that out. See this Datadog example:

Once we realize that, we may try capturing more and more dimensions, building more and more dashboards for different dimension subsets and then setting thresholds and alarms separately. This is when we have just too many metrics. For instance, this is how you can end up in Datadog browsing through metrics:

It’s easy to capture metrics. It’s hard to make metrics capture issues.

Manual Understanding

The next issue around monitoring solutions is that they don’t provide answers. They can easily show there are errors and issues based on some generic heuristics or thresholds, but they don’t give the full story. For instance, here is a dashboard from New Relic:

The dashboard looks clean and tidy. We can immediately see there are alerts. But what’s the reason for that? What’s causing memory drops, CPU hogs, or other metric anomalies?

New Relic requires users to manually understand what’s wrong and where the issues are. They make it straightforward in their documentation: Because you spent time upfront narrowing down the service closest to the failure and choosing the likely error group, you have time to read your logs.

While this sounds easy, this is not what we should be doing. Similarly, AppDynamics measures business transaction health across relevant microservices and containers but doesn’t explain how the unhealthy nodes affect each other.

These tools make us spend our time working on debugging the problems instead of adding more features to our applications. They do it because they can’t analyze problems automatically but they only give us “everything we need” to do it on our own. That’s simply a waste of our time.

Recommended reading: What Is Database Monitoring & Why You Need It

Metrics Instead Of Understanding

Yet another issue is overfocusing on metrics instead of the problems. Monitoring systems can be even quite smart around making “metric anomalies” the main problem we need to focus on. For instance, Dynatrace’s Davis uses AI to quickly find and explore time series with similar behavior to the one you’re investigating.

The problem is that it’s not metrics that we’re interested in. We’re interested in our application, business cases and the actual reason for the issues. Sure, having metrics around can be helpful but it’s not what we’re after.

What Should I Have Instead?

We explored some of the problems the biggest players face today. Let’s see solutions and how Metis stays ahead.

Automated Solutions

We need to have automated solutions explaining what happened, why and how to fix that. Solutions that focus on the actual root causes and not on the metrics.

Metis integrates pieces of information from many sources, including CI/CD pipelines, local developer environments, pre-production stages and production databases. Metis understands both infrastructure and database signals. Finally, Metis can project behavior from one environment onto another to understand whether things will work well everywhere. This can be used in many ways:
- You run a query in your local database. Since you have only 100 rows locally, your query is fast. Metis can project this query onto the production database (without executing it there) and understand that the query will be too slow in production as there are millions of rows there and the query doesn’t use any index
- You drop an index as part of schema migration. Metis can identify that this index has been used in the last days in some of the replicas of the database and can warn you that dropping the index is dangerous
- You configure an index to improve query performance. Metis can verify this index against other databases and recognize that the index won’t be used as the database engine doesn’t consider it helpful. You don’t even need to run load tests to verify the performance as Metis can guide you immediately
Those are only some of the examples of what we want. These analyses must be fully automated and executed across all our systems. We can’t spend time browsing through metrics manually or waiting for load tests to complete. We need to push these analyses early to the left to have short feedback loops and actionable insights.

Recommended reading: Observability vs Monitoring: Key Differences & How They Pair

Understanding

We need our systems to provide an understanding of what happened and why. Currently, systems provide facts and we are supposed to reason and build a coherent story. However, this must be changed.

A fact is something that happened. CPU spike is a fact. CPU spike is not understanding. Memory usage is a fact. Memory usage is not understanding. Monitoring solutions are great at capturing and presenting facts in many different ways. However, they don’t build understanding.

We need coherent stories explaining what happened. Not something like “your CPU spiked” but something like “you deployed the changes three days ago; today is prime time in one of the countries; we received many more requests around this particular data; the index is not used; CPU spiked because the database needs to work harder to handle requests; add this index to fix the issue”. This is what we need from our tools.

Metis does that by analyzing data from various sources, projecting them on production and testing various hypotheses. Metis can check various solutions automatically and see if they actually help. Then, Metis can suggest how to apply changes to fix the issues right away.

Recommended reading: How To Prevent Developers From Breaking Production with Metis

Automated Learning

Last but not least, the tools should learn automatically. We need solutions that can reason about the thresholds and alarms automatically based on what they see. We shouldn’t set thresholds manually and spend time figuring out how to tune our alarms for particular countries or metric dimensions.

Metis solves that by running anomaly detection on all the signals. Once Metis captures the data, it can then look for patterns and typical changes in the metrics. It can then understand whether observed changes are acceptable or if they are outliers.

Next, Metis can also understand how metrics should change. Since Metis tracks everything in CI/CD and understands what changes are deployed to the system, Metis can immediately detect drifts. This way we can focus on running our business and Metis can take care of keeping it in shape.

Summary

Modern monitoring solutions are wrong. They overfocus on metrics, capture too many generic signals and don’t provide any understanding. They put the burden of monitoring on the user and only help with presenting the signals in a nice way. This is not how we should move forward.

Metis fixes this by automating reasoning and providing actual solutions. Metis understands moving parts from all the areas of the software development lifecycle. Metis captures CI/CD, schema migrations, infrastructure metrics, database-focused signals and manual changes performed against databases. Metis then can provide solutions and build coherent explanations. Finally, Metis can learn automatically and verify hypotheses without user intervention. That’s exactly what we need to focus on the business and make our applications shine.
January 9, 2025
Let’s Finally Build Continuous Database Reliability! We Deserve It
Over the past few decades, we have undergone significant transformations, enhancing our ability to improve software delivery and creating new methodologies and frameworks to improve collaboration among teams. Despite these advancements, the existing software development lifecycle (SDLC) is still far from flawless. Teams invest a considerable amount of time in the handover of artifacts, and early pipeline checks are inefficient or lacking.

At the same time, we want to achieve a reliable and robust SDLC. We want our deployments to not get blocked, our applications to not fail and our databases to not slow down. We want continuous reliability around deployments, applications and databases. While we worked hard to make sure our CI/CD pipelines are fast and learned how to deploy and test applications reliably, we didn’t advance our databases world. It’s time to get continuous reliability around databases as well.

To do that, developers need to own their databases. Once developers take over the ownership, they will be ready to optimize the pipelines, thereby achieving continuous reliability for databases. This shift of ownership needs to be consciously driven by technical leaders. The potential for platform engineers to revolutionize the industry by implementing proactive measures to safeguard databases is evident. However, having the right tools and processes is essential. Let’s see how to do it and what we need.

The World Moved on But Databases Stayed Behind

Two decades ago, cloud environments were non-existent, and the majority of software operated on on-premise servers. Applications were confined to a few blocks, typically comprising one database, a couple of web servers and file storage. In those times, troubleshooting was relatively straightforward as logs and traces were easily accessible when bugs arose.

However, our software delivery capabilities were hindered by inefficient processes. Developers worked on changes that were later encapsulated in changesets and handed over to system engineers for deployment. This segregation meant that developers were not actively involved in the deployment and maintenance phases. When bugs occurred, system engineers had to step in, leading to prolonged remediation processes due to communication barriers.

Recognizing the inefficiency of excluding developers from deployment and maintenance, the concept of DevOps was introduced, emphasizing the collaboration between developers and system engineers. However, it became evident that collaboration alone did not suffice. DevOps engineers emerged, aiming to merge competencies for smoother development and deployment. These engineers can now develop business code, deploy it and manage cloud infrastructure using infrastructure as code (IaC) solutions equipped with tools and processes for efficient operations.

The landscape has evolved significantly since then, with the adoption of microservices, independent databases for each small application and increased inter-service communication complexity. Bug identification has become challenging, given the distributed nature of systems and scattered signals throughout the ecosystem. Although component deployment has accelerated, managing this complexity remains a struggle. Effective solutions to prevent production issues, streamlined debugging processes and scalable teams are still elusive.

In the era of single-database applications, a team of database administrators sufficed for managing tasks such as database configuration, replication, maintenance, partitioning and optimizations. However, with the current deployment frequency of numerous applications daily, identifying performance issues and scaling teams of database administrators present challenges. The absence of adequate tools and processes exacerbates the situation.

Platform engineers now face the task of addressing these challenges. Just as we learned to bring developers and system engineers together in the realm of DevOps, the next step is to empower developers with ownership of all components, including databases. While developers already manage deployment, CI/CD pipelines and IaC, they lack control over databases due to monitoring challenges, insufficient troubleshooting tools and a lack of alignment with CI/CD pipelines to prevent faulty changes in production. Platform engineers are poised to address these gaps. The subsequent section explores how they can affect this change.

How to Build Database Reliability

Following are the three parts that platform engineers need to cover to build reliability in the database domain:
- Tools and processes that work across the pipeline
- Observability and semantic monitoring of the databases
- Automated troubleshooting.
Let’s go through each of these areas to understand what we need.

Tools and Processes

Various issues concerning databases can occur without developers noticing. These include the N+1 queries problem, an inadequate or excessive number of indexes, challenges related to eager loading versus lazy loading in ORMs, schema migrations and impedance mismatch — just to highlight a few.

It is crucial to recognize that developers are unable to proactively prevent these problems. They lack effective tools and processes to identify performance issues during the development of their applications. Testing databases is often insufficient, as discussed in our article on how to test databases. This inadequacy is due to the limitations of current CI/CD solutions and the testing pyramid, which struggle to detect these issues. Unit and integration tests primarily focus on data correctness and do not address concerns such as the N+1 queries problem, the use of indexes or the impact on performance when utilizing common table expression (CTE). While load tests may offer some insights, they are conducted late in the pipeline, near the end of the deployment process, providing little help to developers in terms of time efficiency.

We need robust database guardrails to enable developers to identify these issues early in the development process and shift checks to the left as much as possible. These guardrails can identify issues such as unused indexes, incorrect configurations, performance concerns and improper settings in object-relational mapping (ORM) systems precisely when developers are writing their code. By implementing these measures before committing any code changes, the turnaround time is significantly reduced. This approach empowers developers to take ownership of their databases’ performance, providing them with the necessary tools without hindering their productivity. Since the ownership is kept within one team, the turnaround increases significantly, which leads to higher reliability.

Observability for Databases

Another dimension that we must address to build reliability is monitoring. Present monitoring solutions fall short of perfection, inundating users with raw data, aggregating signals, obscuring problems within specific user cohorts, or hindering easy debugging to pinpoint issues.

Enabling developers to assume control of their databases requires the development of tools attuned to database-related activities and developers’ workflows. Database monitoring tools should comprehend schema migrations, maintenance tasks, diverse hosting methods, multi-tenancy applications, database extensions, configurations and numerous other facets. Demanding developers to take ownership becomes impractical if monitoring tools overwhelm them with raw data devoid of explanations about the system’s actual workings.

Nonetheless, platform engineers can transition from mere telemetry and monitoring to achieving comprehension and observability, as elucidated in our article on observability. By integrating database-aware tools, platform engineers can empower developers to utilize them. Once implemented, developers can effectively monitor their databases and gain insights into their evolution over time.

Automated Troubleshooting

Developers cannot assume ownership of their databases if burdened with labor-intensive and manual tasks. Activities such as setting thresholds, configuring alarms, reviewing dashboards or correlating queries with REST commands can all be automated. Instead of relying on monitoring systems to report generic issues like ‘high CPU usage,’ we need comprehensive narratives like ‘we deployed these changes to production, altering data distribution, leading to the application’s failure to use an index due to an outdated execution plan when executing the query in this particular part of the code’. This detailed account is what we require.

Platform engineers must furnish developers with tools that narrate the entire story rather than merely elucidating symptoms. This approach enables developers to address issues more expeditiously, avoiding the laborious troubleshooting process, including collecting logs from various sources and using grep to search for correlation IDs. Automation based on our knowledge of databases is essential. Various strategies to enhance database performance, as outlined in our earlier discussions, should be automated within the system. Once these three areas are fortified with database guardrails, developers can once again take charge of their databases. Let’s explore the benefits this approach can yield.

Benefits of the Shift in Ownership

The primary advantage of implementing database guardrails and empowering developers to take ownership of their databases is scalability. This approach eliminates team constraints, unlocking their complete potential and enabling them to operate at their optimal speed. By removing the need to collaborate with other teams that lack comprehensive context, developers can work more swiftly, reducing communication overhead. Just as we recognized that streamlining communication between developers and system engineers was the initial step, leading to the evolution into DevOps engineers, the objective here is to eliminate dependence on other teams. Developers are no longer reliant on system engineers or database administrators; they can independently manage and maintain their databases.

This results in a significantly accelerated evolution process. With each database now under the ownership of the respective microservice owner, any database issues are promptly addressed and resolved by the owner. There is no need for centralized performance management or maintaining teams of database administrators capable of optimization but unable to keep pace with the speed of development.

Another noteworthy aspect is the reduction of the bus factor. As the knowledge of the database becomes concentrated within a single database administrators’ team, concerns about staff turnover or extended vacations are alleviated. Database task handovers can be managed like regular development workstreams, aligning with the principles of agile methodology. Database-related tasks seamlessly integrate into the scrum methodology.

Ultimately, developers taking ownership of their databases minimizes the time required to identify and address database issues. Developers are freed from the burden of slow and mundane tasks. Thanks to semantic monitoring, they promptly identify issues, automated troubleshooting provides a comprehensive understanding of the problem, and they can independently rectify the issues. This eliminates the need for war rooms or call bridges to decipher the situation.

What is Ahead of Us

Continuous reliability is a must regardless of the company size. The shift of ownership provides a way to achieve it. Database guardrails mark the inception of a new era for developers and databases, but this is just the starting point. With the integration of machine learning (ML), automated troubleshooting can evolve into automated code changes. Similar to static code analysis that identifies common issues in programming languages, tools can generate automated pull requests to address typical problems, leveraging production database data captured automatically. Rather than initiating a ticket for ORM configuration changes, database guardrails can autonomously modify the code, seeking approval as a formality.

As developers take charge of their databases, they can employ CI/CD best practices to enhance the database’s state. The testing pyramid will expand beyond checking business requirements to encompass ‘how to do that,’ ensuring not only correct actions but also correct implementation.

Ultimately, this approach will reduce communication bottlenecks between teams and roles, transitioning from DevOps to DevDbOps. This is the path we must tread to unlock the full potential of developers.

Summary

In recent years, the global landscape has grown significantly more intricate. The proliferation of databases, services, communication channels and dynamic components has added complexity. Similar to the shift toward DevOps and the implementation of CI/CD using infrastructure as code (IaC) for expedited change deployment, integrating database guardrails is essential to empower developers to take ownership of their databases. It falls upon platform engineers to advocate for and implement this innovative approach within their organizations.
July 11, 2024
Oracle Plans to Apply Generative AI to Java and SQL

Oracle today revealed it is developing a generative artificial intelligence assistant that leverages multiple large language models to create Java and SQL code, in a way that is tuned to an organization’s existing code bases and best practices that an organization employs.

Vijay Kumar, vice president of product marketing for application development services and developer relations for Oracle, said Oracle Code Assist will also enable DevOps teams to create code for provisioning IT infrastructure resources hosted on the Oracle Cloud Infrastructure (OCI) service.

Scheduled to become available at an unspecified future date, Oracle Code Assist will also be designed to update, upgrade and refactor code written in most modern programming languages. In addition, upgrading from older versions of programming languages will become more automated.

Pinpointing Bugs

Deployed as a plugin for integrated development environments (IDEs) such as JetBrains, IntelliJ IDEA, or Microsoft Visual Studio Code, Oracle Code Assist will be specifically trained to provide expert, opinionated feedback such as code snippets, dependency analysis, error mitigation alternatives, test cases, annotation, summarization and documentation that will make it simpler to pinpoint the precise location of bugs within an application, noted Kumar.

Benefits will include code suggestions based on learned context and patterns to reduce the time spent writing boilerplate code, summarizations of existing code, increased text coverage and faster code reviews.

Finally, any code generated will not be sourced from examples lacking non-permissive licenses, to minimize potential compliance issues.

In effect, Oracle is committing to training LLMs to generate code using samples it has vetted to eliminate hallucinations that occur when general-purpose LLMs such as ChatGPT are invoked. A general-purpose LLM is trained using samples of code of varying quality, pulled from across the web. As a result, they are more likely to create code that, for example, contains known vulnerabilities.

One immediate benefit of Oracle Code Assist should be an acceleration to a more recent version of Java. Oracle most recently made available Java 22, but many organizations are still running older, slower versions of Java that are less secure. Conversely, however, generative AI is also being used to accelerate the conversion of Java into other programming languages.

Ultimately, DevOps teams will be able to take advantage of Oracle Code Assist to create an automated feedback loop, noted Kumar. Additionally, he added, onboarding developers to existing application development projects will become much easier.

It’s not clear whether senior or junior developers will benefit most from generative AI. In theory, junior developers should be able to take on more complex tasks. However, senior developers may find that many of the manual tasks that today conspire to reduce their productivity are being eliminated.

One way or another, the overall pace at which applications are being developed and deployed is thanks to the rise of generative AI is now being accelerated. The only thing left to determine is how best to ensure the quality of the code being generated meets the requirements of enterprise IT organizations that today depend more on software than ever before.

May 7, 2024
The Benefits of a Streaming Database
Streaming databases offer an innovative approach to data processing that combines the familiarity of traditional models with a powerful data transformation engine. This hybrid system provides DevOps and DataOps teams with extremely fast results for complex SQL queries, aggregations and transformations that are impossible or take hours to process in the traditional batch computation model.

By pre-specifying query results upfront (in materialized views) and then incrementally updating them as new data comes in, streaming databases offer a valuable alternative to traditional databases that require complex ETL pipelines or overnight batch processing.

As the demand for faster results continues to rise, streaming databases are emerging as a fundamental new building block for data-driven organizations.

History

Streaming database concepts originated in capital markets, where fast computation over continuous data is highly valued. The first products were basic event processing frameworks that addressed specific needs within hedge funds and trading desks. However, their creators quickly recognized that SQL works as the declarative language for streaming data just as well as it does for traditional static databases.

Today, modern streaming databases are most often used downstream of primary transactional databases and/or message brokers, similar to how a Redis cache or a data warehouse might be used.

Trade-Offs

Experienced engineers understand that no software stack or tooling is perfect and comes with a series of trade-offs for each specific use case. With that in mind, let’s examine the particular trade-offs inherent to streaming databases to understand better the use cases they align best with.

Unique Features
- Incrementally updated materialized views – Streaming databases build on different dataflow paradigms that shift limitations elsewhere and efficiently handle incremental view maintenance on a broader SQL vocabulary. Other databases like Oracle, SQLServer and Redshift have varying levels of support for incrementally updating a materialized view. They could expand support, but will hit walls on fundamental issues of consistency and throughput.
- True streaming inputs – Because they are built on stream processors, streaming databases are optimized to individually process continuous streams of input data (e.g., messages from Kafka). Scaling streaming inputs involves batching them into larger transactions, slowing down data and losing granularity. In traditional databases (especially OLAP data warehouses), larger, less frequent batch updates are more performant.
- Streaming outputs on queries – Many databases have some form of streaming output (e.g., the Postgres WAL), but what’s missing is output streams involving any kind of data transformation. Streaming databases allow for streaming output of complex joins, aggregations and computations expressed in SQL.
- Subscribe to changes in a query – As a side effect of streaming outputs, streaming databases can efficiently support subscriptions to complex queries: Updates can be pushed to connected clients instead of forcing inefficient polling. This is a key building block for pure event-driven architectures.
Missing Features
- Columnar optimization – OLAP databases have advanced optimization techniques to speed up batch computation across millions of rows of data. Streaming databases have no equivalent because the focus is on fast incremental updates to results prompted by a change to a single row.
- Non-deterministic SQL functions – Non-deterministic functions like “RANDOM()” are common and straightforward in traditional databases. But imagine running a non-deterministic function continuously, resulting in chaotic noise. Therefore, streaming databases don’t support non-deterministic SQL functions like “RANDOM().”
Performance Gained
- Minimized time from input update to output update – The time between when data first arrives in the streaming database (input) and when changes to results reflect the change (output) is sub-second. Additionally, it keeps up as the dataset scales because results are incrementally updated.
- Repeated read query response times – When a query or query pattern is known and pre-computed as a persistent transformation, reads are fast because they require no computation: You’re just doing key-value lookups in memory, similar to a cache like Redis.
- Aggregations – The resources needed to handle persistent transformations are often proportionate to the number of rows in the output, not the scale of the input. This can lead to dramatic improvements in performance in aggregations in a streaming DB versus a traditional DB.
Performance Sacrifices
- Ad-hoc query response times – While running ad-hoc queries in a streaming database is possible, response times will be much slower because the computation plan is optimized for continuously maintaining results, not answering point-in-time results.
- Window functions – A window function performs calculations across table rows that are related to the current row. They are less performant in streaming databases because updating a single input row requires updating every output row. Consider a “RANK()” window function that ranks output by a computation. A single update can force an update to every row in the output.
Factors Impacting Scalability
- Throughput of changes – The changes or updates to input data are what triggers work in the system, so changing data will often require more CPU than data that changes rarely.
- Cardinality of the dataset – The total number of unique keys will slow down read queries in traditional databases. In streaming databases, high cardinality increases the initial “cold-start” time when a persistent SQL transformation is first created and requires more memory on an ongoing basis.
- Complexity of transformations – Unlike the on-request model in a traditional DB, SQL transformations are always running in a streaming database in scale in two ways:
  - Memory required to maintain intermediate state – Imagine how you would incrementally maintain a join between two datasets: You never know what new keys will appear on each side, so you must keep the entirety of each dataset in memory. This means that joins over large datasets can take a significant amount of memory.
  - Quantity and complexity of transformations – When a single change in inputs needs to trigger a change in outputs in many views, or when many layers of views depend on each other, more CPU is required for each update.
Use Cases

Like any software primitive, there are many use cases. Here are some categories of products and services particularly well-suited to streaming databases:
- Real-time analytics – Use the same ANSI SQL from data warehouses to build real-time views that serve internal and customer-facing dashboards, APIs and apps.
- Automation and alerting – Build user-facing notifications, fraud and risk models, and automated services using event-driven SQL primitives in a streaming database.
- Segmentation and personalization – Build engaging experiences with customer data aggregations that are always up-to-date: personalization, recommendations, dynamic pricing and more.
- Machine learning in production – Power online feature stores with continually updated data, monitor and react to changes in ML effectiveness – all in standard SQL.
Conclusion

Streaming databases offer a powerful yet accessible way for data and software teams to leverage stream processing capabilities. By using familiar SQL DB concepts and a stream processor to compute SQL transformations, data and software teams can focus on shipping complex data-driven products quickly, with improved performance and scalability. With a streaming database, organizations have the power to transform data in real-time and build the applications of tomorrow.
April 4, 2023
How Database DevOps Fuels Digital Transformation

In today’s real-time, hyperconnected reality, data is a core driver behind most digital strategies. User data is constantly gathered and shared across various technology backends and platforms—as is system performance data to inform engineers. Data is pivotal for everything, from generating machine learning models, refining user experiences and even supporting new data-centric services. But although data is a clear backbone to many enterprises, there is no clear winner regarding which database to use.

Large organizations often find themselves supporting different database styles, from relational databases to NoSQL and distributed SQL. And as organizations continue to adopt multiple databases, safely managing the processing and storage of data becomes more challenging to enact at scale.

I recently met with Jakub Lamik, CEO, Redgate Software, to understand the role DevOps, specifically database DevOps, is playing in advancing data-driven digital transformations. According to Lamik, applying DevOps practices for database management can improve performance, increase productivity and help organizations stay compliant. Below, we’ll consider the state of today’s data-driven digital transformations and highlight ways to improve how DevOps is applied to database management.

State of Data-Driven Digital Transformations

The 2022 StackOverflow Developer Survey found the most popular databases to be MySQL, PostgreSQL, SQLite, MongoDB and Microsoft SQL Server. But most often than not, businesses deploy more than one of these in production. Redgate’s 2021 State of Database DevOps report found that 70% of businesses had more than one DBMS in use, and 48% had teams working on three or more different databases.

Engineers operate in increasingly hybrid and distributed technology environments. This isn’t for no reason—in fact, 90% of organizations said multi-cloud is helping them achieve their business goals. Lamik also notes a diverse array of software technologies in practice today, from multiple clouds to decoupled microservices architectures and database types. But the desire to work with diverse toolsets causes fragmentation, which could hinder software delivery efforts. For example, organizations want to deploy the same framework and similar methodologies across different databases, said Lamik, and have it be database- and cloud-agnostic.

Tips to Improve Database DevOps Operations

To increase the pace of software delivery, infrastructure automation is often needed. And like other roles, database professionals are increasingly trying to automate operations and free themselves from mundane tasks. So, what are some ways to streamline database DevOps? First off, Lamik encourages adopting a common approach across multi-database environments, using standard toolsets that are flexible and portable. “The organizations that standardize deployments across different database environments can increase the frequency and reliability of deployments,” he said.

Catching and resolving errors quickly is also becoming increasingly essential to meet objectives. And although DevOps brings many advantages, the CI/CD pipeline has gaps. “Observability supported by intelligent automation is key to closing those security gaps,” said Lamik.

Thus, he encouraged additional oversight and tracking of changes to ensure stability throughout the database deployment pipeline. Also helpful is encouraging collaboration with security professionals (also known as DevSecOps) and integrating security principles into the DevOps workflow to ensure the process is secure by design.

Another element of database security involves the proper observability and classification of sensitive data. As Lamik describes, organizations must be careful to distinguish between data used for production purposes and data used for development purposes. Ensuring sensitive data is cataloged correctly is imperative to avoid production data from being breached or accidentally publicly exposed.

Final Thoughts

In summary, applying DevOps ideals to database management could be a boon for development efforts. For those who haven’t yet considered it, a DevOps transformation will pay dividends, says Lamik. Furthermore, a common management layer could help wrangle multiple database types and help adopt new emerging database types, such as Snowflake and Postgres. Yet, when acquiring new database toolsets, Lamik admitted it may not be a quick transition. He recommends a land-and-expand strategy rather than a top-down approach (which typically isn’t as effective in empowering individual developers).

Commenting on the industry’s recent move toward platform engineering, Lamik sees it as the next evolution of DevOps. Whereas DevOps brought helpful toolsets to streamline deployment and digitization, platform engineering is taking these practices and productizing them to be reusable at scale across teams and departments, he explained.

Also, in the new cloud-native stack, containers are designed to be ephemeral, causing engineers to craft workaround solutions to enable stateful storage on platforms like Kubernetes. Regardless of deployment, data must have proper backup and recovery controls as well. In this new cloud-native paradigm, the role of database professionals is changing, explained Lamik, and “APIs are becoming the focal point of communication.”

March 17, 2023

SQL Server MDF to NDF Distribution

Do you have massive MDF files on your database? Have you noticed that your SQL Server disk stall metrics for these data files are higher than the storage latency metrics exhibited on the underlying operating system? It could be that your SQL Server data files are being hammered too hard and you don’t have enough data files to help the SQL Server storage engine distribute the load. We do this for tempdb, right? Let’s do this for our user databases as well. It’s easy for a brand-new database. But existing databases may be out of control with a single data file. Let me show you how to adjust this for existing databases without an outage!

I’ll be taking the SQL Server database generated by the HammerDB benchmark, appropriately named tpcc_test. It’s just a backed-up-and-restored copy of the (appropriately named) database tpcc_single to represent a single MDF file present.

I did a restore onto the F: drive of this server. The MDF size is rather substantial at 420GB. The log size is inconsequential for the purposes of this task.

Here’s the interesting part. Take a look at the current file system size versus the internal fill size. Record the internal fill size, as that’s what we’ll be using to help with some math shortly.

Now, how many files do you need? Here’s my standard DBA response—it depends. For the purposes of this test, I’m going to add seven NDF data files to complement the primary MDF. Your mileage may vary based on your workload.

How large do these files need to be? Take the internal fill size on the MDF, divide by the number of data files you want in total and round up to the nearest megabyte. If the database is active with a lot of data being added, you might want to add a bit more just to have it balance by the time we’re done with this task.

SELECT f.name AS [File Name] , f.physical_name AS [Physical Name],

CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],

CAST(f.size/128.0 – CAST(FILEPROPERTY(f.name, ‘SpaceUsed’) AS int)/128.0 AS DECIMAL(15,2))

AS [Available Space In MB], f.[file_id], fg.name AS [Filegroup Name],

f.is_percent_growth, f.growth, fg.is_default, fg.is_read_only,

fg.is_autogrow_all_files

FROM sys.database_files AS f WITH (NOLOCK)

LEFT OUTER JOIN sys.filegroups AS fg WITH (NOLOCK)

ON f.data_space_id = fg.data_space_id

ORDER BY f.[file_id] OPTION (RECOMPILE);

Now is the time to determine if these should be placed on the same disk or if they should be distributed across more disks. I’m doing that on this server to leverage the disk controller queues to more widely distribute the storage activity and see better overall performance when under pressure. I’ve attached three more virtual disks to this virtual machine, and the four are spread on the four disk controllers on this virtual machine. All disks are formatted with a 64KB NTFS allocation unit size with the large FRS flag enabled. Let’s place two of these files on each disk with the fourth holding the last NDF with the MDF.

Here’s a trick: I set the maximum file size of each of the new NDF files to be the percentage of the current main file space consumed.

Now, we’re going to use a little-known command to empty the MDF file. It’ll drain the file completely, but since we capped the file growth of the NDF files that we attached to limit their growth rate, this command will run until we have filled the NDFs, then fail. It will take longer to perform this operation, but it keeps the database online while it runs. You might have a bit of a performance hit from the CPU and I/O impact of this running process, so it’s important to think about timing when running this operation.

You can watch the process work with the internal used space query listed above; the primary MDF is draining while the other NDF files are slowly filling up.

Again, it takes time for this process to finish. When it finally ‘completes’, the EMPTYFILE command will fail due to maximum file size limit on the NDF files.

FYI—Index optimization routines can cause a circular block with this process that I have encountered twice while running this process, so keep an eye out if you see this occur.

Now, your internal data consumed space rates are almost identical.

Verify the autogrow rates are now the same between the data files. Remove the file growth limit on the NDF files.

Perform a one-time shrink of the primary MDF file to shrink it down to where it is the same size as the others.

It will most likely be slightly out of balance. SQL Server does a pretty good job with the round robin algorithm for internal file fill rates, and it will finish balancing and then round robin filling internally as you continue to add data through normal usage. If you need to increase the size on some of the files so they match, go for it, and SQL Server will fill accordingly so it finally balances.

You might also check if you need to enable autogrow all files at the same rate. This could mean that the database users might need to be punted from the database to enable the change. Do this during a maintenance window.

Sample your SQL Server disk stall rates and see what different you experience once this goes live.

I hope this helps you improve the operational efficiency of your SQL Server database data files, and you get better performance as a result. Let me know how this works for you!

Update: If you encounter an error like the following, just shrink the main MDF file down and repeat the emptyfile operation.

February 25, 2022

Microsoft Expands Azure DevOps Portfolio

Microsoft has made available a bevy of updates to its DevOps portfolio that, collectively, are intended to increase its share of a wave of applications that will be rolled out across the extended enterprise.

Additions to the Microsoft DevOps portfolio were announced at the Microsoft Ignite 2021 conference and include DevOps Workflow Generator, a free tool designed to help DevOps teams visualize their toolchain, and updates to Azure Automanage, a framework for automating the management of virtual machines on Windows Server and, in preview, on Linux.

At the same time, Microsoft has added support for open source OpenTelemetry agent software to Azure Monitor and made available a preview of Azure Chaos Studio, a service for testing application resiliency.

Microsoft is also making available a preview of Windows Server 2022 that adds secure-code capabilities for workloads deployed on the platform. Microsoft also previewed updates to backed databases such as SQL Server 2022, Cosmos and its implementation of the Redis database created by Redis Labs.

Finally, Microsoft has also made available a preview of integration between GitHub Actions for automating software development tasks and Azure Active Directory (Azure AD) using Open ID Connect, an authentication protocol advanced by the OpenID Consortium.

Microsoft CEO Satya Nadella told conference attendees the next 10 years will witness more digitization than the previous four decades as IT shifts from an era defined by mobile and cloud computing to one defined by ubiquitous computing and ambient intelligence.

Applications built in everything from procedural code to the Microsoft Power Fx, a low-code tool that Microsoft created using the programming language it originally built for users of the Microsoft Excel spreadsheet. Microsoft envisions those applications will primarily be built on its cloud and distributed everywhere. On the backend, a range of database and artificial intelligence (AI) services will be invoked primarily via application programming interfaces (APIs).

Of course, every major cloud service provider has similar ambitions. Most organizations today are not standardizing on a single cloud. Rather, they are determining which class of workloads run best on which cloud computing platform regardless of whether that approach increases the total cost of IT. Each additional platform adopted typically requires IT teams to master separate tools. However, Microsoft is now also making a case for a cloud-native framework that can be deployed anywhere to build, deploy and manage applications.

In the meantime, the number of workloads deployed on cloud services continues to grow. The market research firm Canalys reported worldwide spending on cloud infrastructure services reached $49.4 billion in the third quarter of 2021, a 35% increase. Microsoft currently trails Amazon Web Services (AWS) in overall market share but continues to steadily make gains. Microsoft and AWS collectively account for roughly half of all cloud infrastructure service consumption.

DevOps best practices will need to evolve to meet the challenges of multi-cloud computing. In addition to cloud platforms and on-premises IT environments, applications are now starting to be deployed at the edge in greater numbers. DevOps teams need to be able to continuously deploy applications and their associated updates across a wide range of platforms. The challenge, of course, is that all those platforms have unique attributes that make truly automating the entire process on an end-to-end basis an elusive goal.

November 8, 2021
The Power and Potential of Stored Procedures

One way of saving time when writing code is with stored procedures; prepared SQL code that you can save so the code can be reused over and over again. Stored procedures are a powerful vehicle for speeding up your code and giving you gains in performance.

Stored procedures have many benefits—they can be easily modified, are reusable and they possess the ability to automate a task that requires multiple SQL statements. Another advantage of stored procedures is the ability to access or modify data in a database.

Before incorporating stored procedures, it’s important to know exactly what a stored procedure is, how it differentiates itself, how to create a stored procedure and how to use them in embedded analytics.

Performing Tasks With Stored Procedures

A stored procedure is defined as a set of SQL statements and other PL/SQL constructs that are stored in a relational database management system as a group that you can save and reuse. A stored procedure can be comprised of multiple SQL statements like SELECT, INSERT, UPDATE or DELETE. These statements run as a unit and are used to solve a specific problem or perform a set of related tasks.

Stored procedures are among the most frequently used and most popular database subjects. Using stored procedures can help simplify and speed up the execution of SQL queries.

Stored procedures can reduce network traffic between servers and clients, for example. This is a result of the commands being executed as a single batch of code rather than multiple. Rather than every single line of code being sent for execution individually, only the call to execute the stored procedure is sent to the server.

The other benefits of using stored procedures include maintainability, performance and efficiency and complex business logic. And maintaining one stored procedure is easier on the server than maintaining copies on multiple machines. Procedure scripts are stored in one location and they can be freely used in other applications, too. In the event of a definition change, only the stored procedure will be affected—not the application that calls it.

Advantages of Stored Procedures

Once you compile a stored procedure, it can then be used many times. This can save a lot of time writing, especially if you are doing ad-hoc queries. This also gives you a slight performance edge on the first execution of a stored procedure over the first execution of an ad-hoc query.

A stored procedure is ultimately a function call on a database. You may include IF statements, LOOPS, security checks and more to ensure the correct data is being sent back to the call of the stored procedure.

Another advantage of stored procedures is that they are easily modified. Simply change one or more statements in a stored procedure with the ALTER PROCEDURE command and it quickly updates the results sent back to the calling app. Unlike a middle-tier component which includes business logic that would need a potentially costly re-deployment, a quick change to a stored procedure takes seconds.

Another benefit of using stored procedures is the security it provides to its data source. The security of an application can be enhanced when using stored procedures by restricting unauthorized access. You may grant someone access to a procedure that updates the database table without allowing access to the table itself. You may also use stored procedures to “hide” the underlying table names from the end user of the stored procedure. Since the caller of the stored procedure has the name of the stored procedure and not the name of the tables used in the stored procedure, they cannot directly access the tables with your data.

Best Practices For Creating a Stored Procedure

Creating a new stored procedure is fairly straightforward. Here are the three basic steps:

1. Within the SQL Server Object Explorer, right-click on the Stored Procedures node under Programmability.

2. Within the New Stored Procedure Template, modify the CREATE PROCEDURE statement to include the tables, parameters and query options to return the desired data from the stored procedure.

3. Within the Toolbar, click the Execute button to compile and save your stored procedure.

There are multiple tools you can use to create stored procedures. Using the interactive SQL Server Management Studio is the easiest, as it gives you an easy way to start connecting to many different types of database servers, including Azure and SQL Server Analysis Services.

Behind the Scenes in Embedded Analytics

More organizations are using data to predict future outcomes and stay competitive, offering their customers what they want when and where they want.

Embedded analytics business intelligence software holds the key to unlocking the power of data. Stored procedures can be incorporated into embedded predictive models and continuous computational language code for data streaming scenarios.

To create a new visualization on a database that supports stored procedures, first review the list of stored procedures that are available. You will treat the stored procedure you select just like any other data source. The only difference is a parameterized stored procedure, which you can use in a Filter by passing variables to the stored procedure.

Some embedded analytics platforms, for example, offer complete programmatic control over stored procedures and parameters via code. To pass a stored procedure name, as well as any required parameters, use the ChangeVisualizationDataSourceItemAsync function. This is an example of passing a stored procedure named CustOrderHist and passing the CustomerID parameter in the Reveal platform.

The Power of Stored Procedures

Stored procedures are a powerful tool to query a database and return results to a calling app. They are secure, offer performance advantages and are easy to maintain and modify. Stored procedures can be a good avenue for simplifying the way you present data and deal with parameters in your application code versus using error-prone and lengthy ad-hoc queries. As you develop applications, you may want to consider incorporating useful stored procedures in your next project or visualization database.

November 5, 2021
Couchbase Revamps DBaaS Based on Document Database

Couchbase Inc. has unfurled a revamped database-as-a-service (DBaaS) platform dubbed Couchbase Capella hosted on the Amazon Web Services (AWS) cloud.

Scott Anderson, senior vice president for product management for Couchbase, said that as application development continues to evolve, it’s becoming apparent more organizations prefer to have providers of databases manage those platforms on their behalf. That approach frees up database administrators (DBAs) to spend more of their time building and deploying applications, he noted.

Couchbase Capella is based on Couchbase Server 7, which added multi-statement SQL transactions capabilities to the document database, based on a JavaScript Object Notation (JSON) format, to make it possible to also run in microseconds transaction processing applications that previously would have required a relational database to process SQL ACID transactions.

That release of Couchbase Server 7 also added schema and table-like organizing structures, called “scopes and collections,” within the schemaless database to make it possible to add a table while transactions are running.

As a document database Couchbase has gained traction mainly thanks to developers who have opted to employ a database they could download and deploy themselves to build applications instead of requiring a DBA to set up a relational database. However, as the number of instances of a document database proliferates, a need to manage them across fleets of servers and cloud services has emerged.

The challenge then becomes determining whether to hire a dedicated administer or employ a DBaaS managed by a vendor. Many organizations are opting for the DBaaS approach because it eliminates the need to hire a full-time administrator. Once an organization opts for the DBaaS approach, however, it next needs to integrate that service within its DevOps workflow to build those applications.

There is, of course, no shortage of options when it comes to DBaaS platforms. The global cloud database and DBaaS market size is expected to reach $26 billion by 2025, according to a report from Adroit Market Research. Overall, the as-a-service market reached a record $13.4 billion in the third quarter, up 55% from prior year, according to a report published by Information Services Group (ISG).

It’s not clear to what degree organizations will ultimately wind up relying on as-a-service platforms, but since the start of the COVID-19 pandemic, the shift to this approach to consuming IT resources has increased significantly. As a result, the way internal IT teams are organized is shifting as lower-level tasks are either automated or handled by an external IT team that works for a vendor.

Regardless of approach, the amount of data being created and analyzed within organizations is only going to grow. Organizations will need to determine to what degree it makes sense for them to manage that data versus relying on an external services provider. The challenge, of course, when relying on an external service provider isn’t just technical; it introduces a new cultural dynamic that takes some time for many IT teams to appreciate fully.

October 29, 2021

Tag: SQL

The Most Common AI-Generated Vulnerabilities

Four AI-Generated Code Smells Every Developer Should Catch Early

Moving from “Scan Later” to “Fix Now”

What is Wrong?

Generic Metrics

Too Many Metrics

Manual Understanding

Metrics Instead Of Understanding

What Should I Have Instead?

Automated Solutions

Understanding

Automated Learning

Summary

The World Moved on But Databases Stayed Behind

How to Build Database Reliability

Tools and Processes

Observability for Databases

Automated Troubleshooting

Benefits of the Shift in Ownership

What is Ahead of Us

Summary

Pinpointing Bugs

History

Trade-Offs

Unique Features

Missing Features

Performance Gained

Performance Sacrifices

Factors Impacting Scalability

Use Cases

Conclusion

State of Data-Driven Digital Transformations

Tips to Improve Database DevOps Operations

Final Thoughts

Performing Tasks With Stored Procedures

Advantages of Stored Procedures

Best Practices For Creating a Stored Procedure

Behind the Scenes in Embedded Analytics

The Power of Stored Procedures