Voice of the DBA

Author: way0utwest

  • Delaying Patches is Problematic

    I was listening to a DevOps podcast from Josh Corman, of Rugged Software. Rugged Software aims to improve security by asking developers and sysadmins to adhere to their manifesto, which recognizes both the importance of software in the modern world, as well as the problems associated by not properly securing and patching software. These are duties that both developers and administrators should perform in their respective roles.

    The goal of the Rugged Software is to promote best practices and constant vigilence to create and deploy secure code, and ensure that software installations remain that way. This isn’t a security state, but rather a process. A way to approach software development, deployment, administration, and maintenance to improve the security of our computer systems. This maps nicely to a DevOps approach to software development, with a focus on security. The ideas here include not just patching, but fixing underlying issues, learning from mistakes and from other organizations, improving the skills of their developers and administrators. This is a holistic approach to ensuring security.

    This not only includes the software we write, but also the software products, platforms, libraries, etc. that we use. For many of us, this means the Windows host OS, the networking software and connections, http servers, vendor .NET libraries, and more. This also includes database software of all sorts, whether you use an RDBMS like SQL Server, perhaps a search service like ElasticSearch, a caching database like Redis, or some other NoSQL data store.

    The idea is that we not only code securely, but also make sure we patch our software. That’s a problem in many cases. In fact, MongoDB has had a security issue for the past few years that was well publicized but still affects many installations. Far too few people have patched their MongoDB databases, and it’s a problem. Data is exposed to anyone on the Internet, and it’s likely someone is taking advantage of this to access the bits. In fact, many hackers exploit flaws in software for which patches are often available. The updates just haven’t been deployed.

    Patching software is a challenge, and it seems that there is no shortage of ways in which companies deploy updates. Microsoft is pushing automatic patches. Some applications make these optional. Some apply changes in the background, some require downloads. If you use a service, then patches are seamless, but the changes may break functionality or force a new workflow on the user.

    Ultimately I think that we, as an industry, need to mature with the ways in which we patch software. Customers and clients should demand better, and the impact of applying patches should go down. Quality should go up, and while that has happened with some companies, I think we need better patterns and practices that are taught and adopted by more developers. Perhaps more people sharing their techniques, templates, and practices will help others become better at building and managing our software updates.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 5.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Reminder: Extending DevOps Practices to SQL Server Databases with Redgate

    I’m hosting a  new webinar on the DLM tools from Redgate Software and database DevOps. Tomorrow, Tues, Jan 17, at noon EST, Arneh Eskandari, a great sales engineer, will be demoing the tools and techniques to deploy changes.

    You can register here and I’ll talk to you on tomorrow.

  • The Top 20 SQL Toolbelt Tips

    Redgate produces some great software, and I’m glad that I get to use all of them as a developer and DBA. I use many of the tools each week, some daily, but I constantly find that there are plenty of features, tips, and tricks that I don’t know about.

    At SQL in the City last year, I watched Tom Austin present a session where I learned a few things that will make me more productive.

    The one that I need to start incorporating into my work is looking for all the references to an object with SQL Search. A quick check means that I won’t commit changes without considering all of the other objects impacting by development,

    Watch all the tips below.

  • Writing the Correct Query is Important

    There’s a saying in the data world: garbage in, garbage out. We use that when we can’t get good information from our database because the data we’ve stored isn’t as useful as we would like. That’s a problem, and it’s one reason why data professionals want to spend time thinking about the data we need to collect and how to store it. We want to be sure that we’ve at least made an effort to collect useful data that someone will use.

    We sometimes have the data we need, but still struggle to use it effectively. I think this is an area where machine learning and similar technologies may help in the future, but there is a lot of work to be done to allow most of us to take advantage of those tools. In the meantime, many of us make do with basic T-SQL to perform data analysis, generate reports, and provide the answers to questions. When we do so, it’s important that our queries actually work correctly to answer the questions we need.

    I don’t want this to be a political discussion, and I would appreciate that any comments be limited to the technical subject. I ran across a piece about a failure of the US government in determining the status of people being checked for immigration status. The interesting quote in this article was “… officials blamed computer code for the problem.” Leaving aside the implications in this case, the idea that computer code, likely some sort of query code, is not working as expected, querying the correct data, or isn’t being used properly is disturbing.

    I’ve run across quite a few stories like this from various consultants that were called in to help organizations, only to find out the queries that had been used for long periods of time were incorrect. They didn’t filter appropriately, didn’t convert or aggregate data as intended, or didn’t even query the correct data.

    We use databases and queries extensively in today’s world, and the growth is only going to increase. As much as I like the idea of DevOps and more frequent deployments, I also want higher quality for our software. This means that we need to ensure that our queries actually work as intended against databases. Code reviews, independent checks, using known data sets that evolve and include edge cases of data are all ways we can work to ensure we are actually writing the correct queries for our data. Above all, we need to be sure we are using test of some sort, preferably unit tests, that ensure the queries are actually the ones we want.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Libsyn.