Voice of the DBA

Category: Editorial

  • Securing Code Early

    Last year I started to get alerts from Microsoft Repos that someone had put a piece of security information in their code that pertained to one of my Azure services. At first I was worried, but then I realized this was the public version of AdventureWorks we maintain in Azure. We’ve published the login so people can test code against this if they want, and I started ignoring the warnings. Well, not ignoring. I still glance over them to verify the issue, but I’m less concerned.
    That doesn’t mean that you shouldn’t be concerned about sensitive information in repos. I saw this quote: ” Bots are crawling all over GitHub seeking secret keys, a developer served with a $2,375 Bitcoin mining bill found.” This follows a sentence that says “It once caused Uber to leak the contact details of 75m users“. These are from an interesting look at a way to secure code that might leak API keys. The idea is that you secure code on local commits and prevent secrets from being stored in your VCS.
    That’s a great idea. Can we prevent passwords in SQL code or ASP.NET config files? Can we actually start to teach developers to use secrets and other run-time mechanisms and prevent them from hard coding anything into a VCS? Perhaps, but we have a lot way to go and certainly more tool chains need to be updated to prevent what is a simple, but common, mistake.
    We need to get better at security, and we are. I see more Static Code Analysis tools being used in all sorts of companies, and I’m glad when they start to impact developers. While I get that changing your favorite method of writing a query or procedure is a pain, often we can reduce potential problems by enforcing some standards and avoiding poor coding practices. We do need to have exceptions since a rule for code might really be a general guideline 99% of the time with a few edge cases.
    Catching issues early in development is one of the goals. Having things like inline SCA in SQL Prompt (live demo), or continuous testing in Visual Studio, scanning with Sonarqube, and other tools are improving our code, while allowing us to build applications faster. There is plenty of other work to be done, especially for database code, but we are improving as an industry.
    If you haven’t used any of these tools and you write code, start learning a bit about them. You’ll likely appreciate their benefits once you get over the learning curve. You’ll also start writing better code.
    Steve Jones

  • Capturing the Application Time

    I’ve always thought that any times we recorded in a row were related to the actual data itself. Almost all of the time I’ve built software, we’ve assumed that the time a row is written or updated is the time we need to record as a timestamp. This works great for many applications and it certainly simplifies programming when the database can capture the time. System versioned tables are a great example of where we use time in the database as the time of record.
    Over the years we’ve started to build more distributed applications that reach a widely dispersed audience. With the Internet today, it’s entirely possible that a row inserted by one user takes place in time zones far away from the next row inserted by a different user. What may be more confusing is that I may insert a row in GMT/UTC, but then update that row a day later in the Mountain Standard Time time zone. In fact, that’s something I’ve done.
    The complexity of time in an application means that we can’t assume we know how best to capture or store time. While I think system versioned tables are a great addition to SQL Server, they are an incomplete one. We need the ability to use these tables with some sort of application time marker, not just the database server time.
    This week I’m wondering how many of you might feel the same way. How many of you need to capture application time separately from the system time? Do you do that now, with some bespoke system? Perhaps you wish you could and this is a feature your system is missing.
    There is a feedback request for Microsoft that asks if application time can be captured as a part of the system versioned timestamps. Regardless of whether or not you have the need, this is an important enhancement that would greatly improve the system versioned table feature. Cast a vote today, and let us know if you would use application time in addition to the time in your database server.
    Steve Jones

  • Building a Database Engine

    I never wrote a database engine, unless you count writing code to read, write, and update flat files. I remember doing that early in my life with a friend as we tried to build our fantasy baseball game system. We needed a way to handle data, and use flat files, setting tokens to denote various “rows” and columns of data. I’m not sure that’s much of a database, but that’s the most I’ve done in building an engine.
    Someone decided to tackle building a SQLite clone in C and wrote a series of articles on the process. They are an interesting read as the author works his way through adding new functionality for the engine. While I wouldn’t want to actually recreate a database engine that I needed, the exercise is interesting. My C knowledge is a little rusty, but I can follow along enough to appreciate the way that the application takes shape.
    Writing software is often a challenge when we are creating a new system from scratch. It is often easier when we are trying to copy something that already exists, but there is still an effort to recreate all the functionality that already exists. However, it’s a good exercise and one that often helps software developers build stronger skills and practice their craft.
    If you were going to practice writing some code, database or otherwise, what would you like to write? Would you attempt a database engine? Many of us know how quite a bit about how SQL Server works, but I don’t know if we’d actually want to recreate the code for some part of the system. When I learned how the memory-optimized tables were structured, I had fond memories of building similar linked lists in university, though at a far simpler (and less efficient) level.
    I find myself tackling some problems to help others, or teach them a technique, but I haven’t had to build a full set of software in years. Maybe I’ll find a project at some point that I really want to tackle and actually build something larger. Finding the spare time to tackle a project is hard, but I’m ever hopeful that I’ll make the effort at some point.
    Steve Jones
    Listen to the podcast at Libsyn.

  • The End of XP

    This week we have the end of Windows XP. At least, we have the end of support, which means the lifespan of the OS was over 17 years. That’s a long time. It’s longer than I’ve run any system without an upgrade. I have some Windows 7 systems I use at times, but that’s a mere 10 years old. I know there are still a few SQL 2000 systems out there, which is older than XP, and likely a few of them are running on Windows 2000 in a VM somewhere.
    I have to admit that I liked Windows XP and thought it was a nice upgrade from previous versions. I ran it for a long time, skipping Vista, before moving to Windows 7 when it was released. Most of the early days of SQLServerCentral were run from an XP workstation, with SQL Server personal and lots of text editors helping me manage the site. I’m somewhat sad that it’s gone, though I think Win7, and now Win10 were very nice improvements.
    XP isn’t likely gone, as some companies will continue to run it and I expect some ATMs, some kiosk displays, and other embedded applications will show that XP start screen on occasion. That’s not unlike the way that some of us might connect to an SQL instance and be surprised to see a single digit number in the major version space. In fact, a lot of you might still see a “9”, for SQL Server 2008. I was seeing that until last year when we moved SQLServerCentral to SQL Sever 2017. I expect the more and more organizations will be slowly moving on, perhaps reluctant to upgrade those old versions that work and don’t cost much to maintain.
    Or do they? Is there a big cost on older systems that run and don’t see regular development work? My thought has been that for many internal systems, I need a database platform, and likely an OS, to run for 10 years or more. I know some of the larger enterprises have this view as well. Picking a platform is a major decision and it can be very hard to change directions. Porting takes a lot of resources, as do upgrades, so ensuring a system can handle a load for 10 years makes sense.
    I know many of us would like to regularly switch versions, and one of the great things about my job is that I get to do so. I’ve held onto SQL 2014 and 2016, but I’m slowly moving all my work to SQL Server 2017 and 2019, with the idea that apart from some repro situations, I won’t try to run older versions anymore.
    It seems many people are town about upgrades. Some people prefer the system they know and are comfortable with, since they know what workarounds are needed. They may bemoan the need to learn new skills and change old habits. Others prefer the latest and greatest, quickly adopting new platforms and agonizing over the spend on old systems. No matter which way you feel, it’s likely that you’ll be forced to do a bit of both as the pace of change for SQL Server means many of us will adopt new versions for new work and end up supporting 4 or 5 versions at any one time.
    Steve Jones