Voice of the DBA

Author: way0utwest

  • Monday Monitor Tips: Am I Patched?

    One of the things that I think is neat is that Redgate Monitor helps you track patching on your systems. This is something that has been challenging in every position I’ve had, with some systems being forgotten or remaining unpatched for too long.

    This post looks at how you track patches and versions.

    This is part of a series of posts on Redgate Monitor. Click to see the other posts

    The Challenges of An Estate

    There are two aspects to tracking your systems: the version and the patch level. Microsoft releases versions periodically and unless you’re in the cloud with a PaaS service, you may or may not have just one version of your database platform. Azure SQL Database is evergreen and updates every quarter or so.

    If you install SQL Server 2016, unless you upgrade, it stays at 2016.

    The second part is the patch level. SQL Server 2016 has had 3 service packs, multiple CUs in between those, and a few post-SP3 security patches this year (2024). Are you up to date?

    It’s a good question since you might be vulnerable to issues, and you certainly can be out of compliance with auditors if you aren’t patched.

    What Systems Are Behind?

    The estate tab has a versions page, in which we list the installed versions of SQL Server. I hope PostgreSQL is coming soon as well (and others). Here is the overview, where you can see this estate spans SQL Server 2008 R2 to 2022, and includes the cloud.

    2024-11_0251

    This helps me with upgrades, as I can see which systems might be old and in need of an upgrade plan. I can filter at the top for different groups, tags, etc., but I can see what I have, and I get quick links to the current patch.

    Below this, I have details. Here is where I can dive down to individual groups and systems to see if they are patched, how long ago the last patch was released, and the end of support

    2024-11_0252

    This is a good way for me to see at a glance how patched I am. The yellow up arrows mean I need to patch. The green check mark means I am patched.

    This is a good view so you can tell how out of date you are. It’s one thing if there are patches released within the last month and not applied. It’s another thing when you have systems that are months or years out of date.

    Using This Data

    This isn’t something I’d check every day or week, but I would set reminders to have this monitored monthly and have plans in place to get patched. While lots of patches might not affect security, they often to affect support if you need it, and certainly these affect compliance and auditing.

    Even if no one audits you, if you have an issue and you aren’t patched, someone will use that as an excuse to blame you in some way. Get patched, at least within 60 days if not 30.

    BTW, this data is maintained by Redgate and updated as patches are released. Redgate Monitor downloads a file that populates the latest patches for each version. If your Redgate Monitor Base Monitor cannot reach the Internet, you can update this yourself by downloading this file and copying to your system.

    Summary

    This short posted highlighted what data you get about versions and patches, and my recommendation, which is to review this monthly.

    Having Redgate Monitor keep all this for you is nice and helps you keep a healthy, up-to-date estate.

    Redgate Monitor is a world class monitoring solution for your database estate. Download a trial today and see how it can help you manage your estate more efficiently.

  • The Pervasive Nature of Open Source

    This week there was a headline that said “Open Source Software Powers 96% of Modern Applications, New Study Finds” and if you stopped reading there, you might think, hey, it’s not in the apps I work on. Or you might think that because you use OSS software, most of the world also does. Microsoft, Oracle, etc. are headed for disaster.

    If you read a little further, there’s this gem: Open source components are present in 96% of codebases. That’s a far cry from OSS powering most modern applications. I think a better headline might be that OSS helps build most modern software. However, this isn’t an editorial on bad journalism.

    I do think OSS stuff is amazing. Many of us in the Microsoft Data Platform space use sp_whoisactive or the First Responder Kit or Diagnostic Queries or some other OSS in our work. Lots of commercial products are built with OSS libraries or components, or there are free versions. Flyway (from Redgate) has an OSS version. OSS helps us build better software, though commercial packages also help.

    One of the interesting things about OSS is that many very popular projects have just a few people maintaining them. If those 1 or 2 people disappear, then the project might stagnate. Or worse, if there are security issues, no one addresses them. One of the main attractions of OSS is that anyone can provide a fix or enhancement, but the reality is that most people don’t. Most people just use whatever is out there.

    And most people rarely upgrade their OSS. They get something that works and don’t want to change. I get that, as I feel the same way often, especially in the real world. I have shoes, gadgets, tires, etc. that work and I don’t want to change. In the software world, this creates vulnerabilities and security issues, as the report shows with many people still using Python 2. I both understand and don’t understand why this is the case, but I do worry about security.

    The other concern is that few people review changes to OSS packages, which has led to previous supply chain attacks with backdoors or vulnerabilities introduced in packages that many other software developers use. Again, OSS is supposed to be better than closed software at preventing this, but the reality is that most (the vast majority) of us are just too busy to look for issues. Even when vulnerabilities are published, far too few developers see the information. Automated scans in CI/CD systems are great, but again, too few people add these to CI/CD pipelines consistently.

    Software is hard. In some sense, I’m glad databases don’t have external compilers or use anything other than raw code, but plenty of people still write SQL Injection vulnerabilities in their functions and stored procedures, and many don’t have good visibility into the code that is submitted to their databases, often because the code is assembled at runtime. I wish more people just used stored procedures and included more testing and vulnerability scanning, but that’s a dream. For now, I suggest most of you developer patterns your staff can use and stick with them.

    And use version control. At least then we can find all the old, bad code and fix it with some search and replace.

    Steve Jones

  • Big Queries, Big Money

    I had been meaning to post this, so as I finished a piece that referenced this, I decided to post the picture. This was from Small Data SF, where the opening keynote referenced the Google BigQuery demo of a 1PB database.

    Here was the slide shown later in the talk.

    2024-12_0187

    The thing the demo didn’t explain was that query cost $5,580. The conclusion, big data is just too expensive to query often.

  • Distributed Monoliths

    I was watching a video called Microservices are Technical Debt. In it, the person being interviewed said that a lot of people really have a distributed monolith. That caught my eye since I’ve worked with a number of customers who are trying to adopt microservice architectures for their applications. I think this is less a performance/scaling choice than a reworking of their software development teams, and I’m not sure they will end up with a better system.

    What is a distributed monolith? I am not an expert, but this appears to be a place where all the services still depend on each other. For example, I might have a service getting user profile info that an app calls, with another service getting previous orders, and a third service that returns inventory. In a monolith, if any of these are down, the others don’t work. In a distributed monolith, these might be built independently, but perhaps the core app/web page still requires all these to be working to show the user something.

    That’s how my gym app seems to work sometimes. It can’t get my profile and the list of classes and the things I’ve registered for, so it hangs. This is very annoying when all I really need is my ID QR code, which never changes, and is validated by another system.

    In a microservices architecture, if any of these were down, the others would still be working, and the app would continue functioning. If inventory is down, I can still update my profile. If previous orders are working, I can view them even if the current inventory doesn’t work.

    Things are loosely coupled for the end user and for the development teams. Not for just one of those groups.

    I’m not sold on microservices, especially when they include databases. There is a lot of power, and efficiency, from being able to pull together different data sets in a database efficiently rahter than doing so in an application, which might require different API calls, separate db connections, and the delays that come with returning more data than needed to let the client join it together. At the same time, I can see how separate services could potentially scale much higher over time.

    The thing that I tend to see is that most of us never need to really scale as high as the Google/Facebook/Spotify/Netflix/etc. organizations that espouse these microservice architectures. They have different problem domains and different needs than most of us. Even Uber has realized that one size doesn’t fit all and is moving some things from microservices to macro ones. I’m not against microservices, but they need to be used in appropriate situations, not all of them.

    What most of us need is a little more help from Brent or Erik or Paul or someone else in the form of a little consulting that tunes our code to run more efficiently. And if we use them, we should learn a few things from them so that we write better code the next time.

    Steve Jones

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

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