Voice of the DBA

Tag: software development

  • What Metrics Do You Collect?

    One of the hot terms in software these days is observability. There are a few definitions (Splunk, RadixWeb), but essentially this is the insight into how your software runs and performs using metrics, logs, traces, etc. In DevOps, we do this with an eye toward improving performance and identifying the root cause of issues. The focus is slightly different from monitoring, where we often focus more on resources and health. We need both, but often in trying to improve software and the behavior for users, developers need observability. Infrastructure people responding to acute issues and looking to ensure we have the capacity, availability, and other x-bilities, that need monitoring.

    Today I’m wondering if you collect a variety of types of metrics for your software that might tell you how your system is running. What things are important to you in order to better serve your clients? If you’re a DBA/sysadmin, what is important to you? If you are a developer, are there different types of data you want?

    Certainly, you might collect various resource measures (CPU, IO, reads, etc.), but there are many more things. There are logs, which could include the SQL Server error log, but I’d hope that you had a more in-depth way of measuring the activity on your system. Do you have custom xEvent traces running? Can you collect application logs easily when you’re looking at issues? Do you spend time trying to solve chronic issues? Do you look for potential future problems?

    Most software applications should include some sort of basic logging of major functions, but I would hope that there are various levels available. If problems are reported or noticed, can you increase the detail of logging? Can you correlate this with logs from different systems, such as the database? Can I get execution data that matches calls and trace down the potential issues that users are experiencing?

    I know that we have problems in applications. Some (many?) of these are data-related, which might be easy or hard to trace down. That often depends if the data changes too quickly or is static enough for someone to investigate a report. Some errors are logical code errors, which might indicate a lack of testing early in the software process, but we ought to be able to determine this quickly from logs. If there are performance issues, and we have a lot of these, how easily can we verify a problem?

    Let us know what types of metrics help you solve issues. I certainly think that you should have some sort of monitoring and observability system in place that helps you dive deep into the database, especially concerning execution plans at the time of the issue. The situation can change quickly inside a database, so capturing data regularly is important. If there is something you wish you had, let us know as well. Maybe someone else will have a neat solution for you.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • Polyglot Persistence

    I’ve seen the term polyglot persistence floating around Redgate a bit recently in the marketing department. I haven’t really seen this term anywhere, and I wonder if you have. If you have, drop a comment.

    The definition is based on the polyglot programming, where you write an app using multiple programming languages. I don’t know a lot of people who do that, and if you do, let me know. I guess some do, since I’ll see things like HTML+C#, or maybe something like Blazor + C#.

    In any case, the idea seems to be that you use multiple databases to satisfy your requirements. A few examples might be:

    • SQL Server for most data, Redis to cache shopping carts
    • Oracle for most data, ElasticSearch for full-text searching
    • PostgreSQL for profiles and leaderboards, MongoDB for real-time game actions

    I’ve tended to showcase NoSQL + Relational here, but I have seen a few people using two relational stores or two NoSQL stores because it suits their needs. The really common additions (for me) are some type of NoSQL store for performance with more stable, long term data in relational.

    I don’t think this covers the relational for OLTP and something else for OLAP/warehousing, as that seems to be an ETL/ELT/transfer of data over time for a different purpose rather than for the same application, but I guess it could cover that.

    I think Polyglot Persistence is a good idea, especially when you have complex requirements that aren’t easily solved with one database, even when you have in-memory or search/graph features in your database. Often those don’t perform as well as specialized systems, though there is a level of complexity and the challenge of ELT/ETL’ing the data to/from the second system.

    Evaluate carefully, PoC, and test at scale before you decide this is worth the trade.

  • T-SQL Tuesday #170–Abandoned Projects

    It’s the first T-SQL Tuesday of the new year. As we move forward, this month’s invitation is neat in that it’s looking back to learn how to move forward. I think while many people do look back, they don’t always use that information to move forward. This month’s host, Reitse Eskens, asks us about abandoned projects.

    As always, if you’re interested in hosting, blog, and then send me a note about hosting. I still have space in 2024.

    Abandoning a Project

    I’m sure that many of my employers started something and then abandoned it, and there are no shortage of projects I’ve abandoned myself, but nothing big springs to mind. Mostly I find my employers muddle through very poorly written, poorly performing projects and are very hesitant to abandon them.

    However, I will say that I have a couple things on my mind personally. This past December I completed the Advent of Cyber, which I wrote a bit about.  I actually got a certificate (of sorts).

    2023-12-28 16_08_10-Window

    That wasn’t abandoned, and I was glad I went through this, learning about a few tools.

    However.

    In the past, I’ve worked on the Advent of Code, and I’ve never finished it. I even built a repo, where I separated out puzzles by year. I invariably get a week in and then don’t have the time, and I struggle with coding out the puzzles. They get hard, and since this is a spare time thing, I run out of energy and interest.

    I do think, however, I learn some things. A few years I’ve tried to solve puzzles in PowerShell, Python, and SQL, which makes me translate logic, and think about the issues. SQL is hard, as a number of the puzzles are really iterative, and SQL isn’t great at iteration.

    I do learn.

    Even when I abandon projects, I get better at writing code and solving problems. It requires me to think, and it also teaches me things about how to look at a requirement and then write code. I also love the skill of building in tests to verify my code from the samples, which is a good skill.

    It’s an abandoned projects, and even as I write this, I feel like I should work on the 2023 puzzles a bit. I probably won’t because of time, but I do think starting and abandoning this project teaches you something.

  • Does Management Care About the Database?

    Many of the services that businesses use are taken for granted. In an office, management expects electricity, water, and heat just work. If they don’t, then often a crisis is declared and funds are spent to rectify the situation. I’ve been in businesses where any of those systems fail and usually, it’s very disruptive. I think these days a computer network is often seen as the same type of utility service where we take it for granted, but if it doesn’t work, it’s a crisis.

    Is the database seen as a service or is it more important than that? I think many software applications are different, and often seen as critical to business success, but I sometimes see both developers and management treating the database as a utility service. They just expect it to work, view it as a filing cabinet, and want to ignore it whenever possible.

    I think this is one reason that developers are often searching out new platforms, either cloud or NoSQL, because they want to keep the storage aspect of data simple and not spend time or effort managing data. Or they want to experiment with something they think is easier to use. Many developers like the idea of a schema-on-read, and sometimes use that to argue against another relational database and instead choose another platform.

    I think this is short-sighted, however, as the app now has the technical debt of supporting multiple schemas, and many of the databases using this format then suffer from one of two constraints. Either they store duplicate data for performance reasons, essentially de-normalizing their data storage, or they require some sort of ETL to ensure the data can be queried for analytics. Often schema-on-read isn’t efficient when querying across documents/keys/nodes/etc.

    There might be good news for many of us database professionals as an article talks about the boardroom starting to think about the database. To be fair, this is a sponsored article from Redgate, but it is based on a lot of research and feedback from customers that show how management is starting to treat the database as a strategic asset and not a common utility. Many executives are starting to realize that their data is full of value if it can be analyzed, and that the performance of these systems is important. We both need well-designed databases and well-written code, and we need to ensure that database development keeps up with today’s application changes.

    More and more of the customers I work with are finding their executives see more value in ensuring the database gets the resources to ensure it performs well, including better training for developers and better processes. It’s good to see more and more companies embracing modern DevOps development in both application software and database software.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.