Voice of the DBA

Tag: Database Weekly

  • 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

  • Serverless Gets Faster

    When the Azure SQL Database serverless option was introduced, I was a bit disappointed that I couldn’t get the database to pause any sooner than 1 hour. That meant I needed to ensure clients didn’t access the system for an hour, but also, that I burned an hour of compute after the last access.

    Recently I saw an announcement that this time frame has come down to 15 minutes. While this might seem like a very simple change from a technical standpoint (just alter a timer option), I’m sure there was more work needed. I’m also sure there was a lot of debate on the sales/marketing side to decide if this would lose a lot of revenue.

    I’m sure this costs Azure some compute revenue in the short term, but it might also create opportunities from customers who consider using this in new situations since it can shut down quickly. I certainly think this makes the use of an Azure SQL database for QA/staging type work more attractive. This might also get more people to take a look at serverless and realize the auto-scale benefits are pretty cool.

    My request would be to drop this down to 5 minutes and increase the range of auto-scale as well. Maybe allow me to go from 2-16 vCores if needed with corresponding memory jumps. I don’t know I need this by the minute, but I would like to have things shut down fairly quickly if we stop a workload and aren’t using the system.

    I’d also like a better retry on startup other than trapping an error on the client and re-sending my request to connect. It’s just embarrassing that we still have that happening for a cloud PaaS service.

    Steve Jones

  • Analyzing Breached Data

    A few of you out there might be data scientists who profile data regularly. Probably a fair number of you do import/export work and learn to check data values, perhaps with counts, distincts, or other aggregates. I don’t know if the performance tuners out there look at the skew of data or the details of what is in a query that needs improvement. However, all of you are likely familiar with data and trying to query it for some type of meaning.

    One of the largest data breaches occurred with National Public Data. Troy Hunt analyzed the breach as a part of his work with haveIbeenpwned. The piece is an interesting analysis of the data, trying to determine both it’s legitimacy as well as what is actually included in the breach. It’s a fascinating read and I encourage you to look at it not just from the data analysis side, but also to be aware of what data about you is being aggregated and sold by companies.

    The read is interesting as it is a bit of a detective story, digging through data in a folder, which is something I’ve had to do. I’ve had people in previous jobs just dump a bunch of data on me and ask me to load it into a database. Or a table. Often without them knowing what type of data it is, what formats, do files relate to each other? Are there multiple tables worth of data in a file? All questions I’ve had to ask myself (and answer), and similar to what Troy did to analyze the breach.

    Data is very important to many of us, in different ways, but I’m often amazed at how few people actually understand how to organize data and ensure others can track the metadata about their data (what their data represents). I’m guessing this is why every person that gets an extract of data to load into Excel formats it in different ways.

    In many cases, people want the ability to query data, but they prefer to just focus on one table that contains a lot of information. They don’t want to know how to “join” data together. I think this might be the reason we see so many views in databases, and why we have views built on views. Each new client of the database needs their own view structure.

    The world of data is a mess, even inside an organization. Once we start moving data between organizations, it’s truly a mess. We might bemoan all the inefficiencies and work we do to move, change, and re-load data as custom, human ETL machines, but there is one great thing about this tangled web. It provides for steady, secure jobs for many of us with no end of work in sight.

    Steve Jones

  • Protecting Schools

    One very common targets for hackers are schools and universities. The latter may have plenty of resources for staff, but often school systems don’t have that same type of budget. In many ways, it’s sad that hackers would target schools that struggle to educate and help others. The staff often deals with low pay and high stress already, and losing access to computer systems adds to an already tough job.

    There was an interview this week with the director of technology at one district. Johnathan Kim works for the Woodland Hills school district and is a former staffer at the Navy Cyber Defense Operations Command. That’s the type of training that I think few school district employees have. I’ve known a few people who manage technology inside schools, and while they are often smart, capable people, they aren’t security professionals.

    The interview talks about a few of the changes that Mr. Kim has made, such as enabling two-factor authentication (2FA) and removing local admin rights for many teachers. I know these are the types of rules that frustrate many workers who use computers. In fact, I ran into someone who rarely upgrades software on their development machine because so many applications require administrative rights and they don’t want to bother opening tickets more than a few times a year.

    Two-factor authentication can be a pain, and I know I get confused sometimes as I have both 2FA and MFA with different processes for different systems. It’s good in that a few times in the last year I’ve caught a hacking attempt, but it’s also a pain to deal with when I’m doing something simple. I can see why people don’t like it when they don’t understand the challenges of securing systems. Every time I find myself frustrated, I stop and remember the problems others have had, especially those that have dealt with ransomware in their organizations.

    A good point in the interview is that education can help smooth the way for security practices that feel unnecessary or disruptive. It’s good to remind ourselves why we want the least privileges needed assigned to others, and why those of us with privileged access need a second account for that access. We also ought to come up with a good story to educate others when they complain, perhaps using a story of a breach or loss to help remind others that our systems are constantly under attack.

    Steve Jones