Voice of the DBA

Author: way0utwest

  • Software Has Bugs

    Every once in awhile I have someone I meet complaining about software quality, usually with regards to some piece of Microsoft software. I’ll invariably hear how there shouldn’t be bugs in SQL Server or Windows, and Microsoft isn’t testing their software. This person will claim that they write software with no bugs. I can’t remotely believe this is true, aside from small, somewhat trivial applications. All software has bugs, but there’s no way to disprove anyone’s assertions unless you can get to their software and examine it.

    I do think that some people think their software is more bulletproof than it really is, perhaps because no one really does test the limits of their work. It’s easy to assume that there aren’t problems when no one has ever probed deeply in the different ways the code might execute. After all, this is one of the reasons that developers turn in code that they think works, but in which others find bugs. They haven’t explored enough different inputs against the software. This is also why I think unit testing needs to be constant and evolutionary, growing and changing as you find new issues and bugs in your code.

    Recently a vulnerability was found in the Samba software, and apparently it has existed for seven years. This means lots of software was vulnerable, and everyone should be patching as soon as possible. I don’t know how many people have port 445 open to the Internet, or even open locally, but plenty of people might now know about the issue, so patching makes sense. Scans of the Internet have turned up hundreds of thousands of devices, some of which are running versions that can’t be patched.

    This isn’t meant to complain about Samba, but point out that this is code that is open source, lots of people have looked at and developed against, and I would guess no shortage of security people have checked this for potential vulnerabilities. And still a bug slips through.

    Security isn’t hopeless, but it’s also hard. Software development, with few boundaries limiting the choices developers can make, allows new interactions among the building blocks of code that have never been seen before. Building code is much, much more complicated than any physical structure because we don’t have any of the inherent limits of the physical environment. This means that we will, and do, struggle to test all of our software.

    I don’t think we’ll ever eliminate all bugs, but we can vastly improve quality (as many companies have), by moving to a more DevOps development style where we seek to constantly improve quality as a part of software development. We also have more and more developers also seeking to improve their skills, learning new and better patterns throughout their careers, and hopefully, building better software.

    Steve Jones

    The Voice of the DBA Podcast

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

  • T-SQL Tuesday #91–Early DevOps

    tsqltuesdayThis month the T-SQL Tuesday invitation is from Grant Fritchey, my colleague at Redgate Software. Surprise, surprise, the topic is DevOps.

    I write and talk about this a lot, but for this month I’ll look back at DevOps for me in 2001.

    If you want to participate in the blog party, you need to publish on the second Tuesday of the month, GMT time. You can see the invitations at tsqltuesday.com.

    2001, A DBA’s Journey

    I started working for a small startup in 2001. I was the DBA (dev and production) with 4-5 developers. Eventually we grew to 10 developers, a couple QA people, and a UX person. Our goal was to release code every Wednesday night, and developers would work from Tues-Mon am on that week’s changes.

    The initial plan when I started was to get together with the lead dev and produce the scripts to upgrade our web application as well as the database. Early on we had a small system, so it was a few hours on Monday afternoon putting together a script for the QA server. Inevitably we’d make changes if QA found issues, and then Wednesday night we’d go into the office and usually spend a few hours deploying and fixing things. Often we had another developer or two with us to ensure that we could get the changes out.

    Over a few months we started to try and smooth our process. We talked about the issues, and started small. First with version control, Visual SourceSafe at that time. We’d get everyone to check in changes, which let us more easily determine what had changed that week.

    We also talked about what made life easier for the lead dev and me. How could we package code to easily ensure it gets deployed. For the database, this meant ensuring all files were named easily and branched to a folder.

    Ultimately, the technical challenges were few. A few scripts that automatically packaged changes for us and deployed them. However, the biggest challenge was cultural. Getting all the developers to think forward about what was needed to get code to production and slightly alter their behavior. We also had to break habits of “fixing” code in QA. We had to learn to reset QA, change our scripts in version control, and then redeploy.

    Sharing information, communicating, and adhering to a consistent process for deployments, ensuring that we practiced our production deployment on QA, helped us move to a release process that consisted of a phone call and a few minutes instead of a late night at the office.

    DevOps works, but you have to work at building a process for your environment.

  • VSTS–Changing the Default Build Queue

    I wrote recently about the Release agent using the hosted queue as the default, which is sometimes a problem. The Build process has the same issue, though the way you change things is different.

    In a build, you see a set of tasks with various menu items across the top, as shown here.

    2017-06-01 19_48_11-Build-CI - Visual Studio Team Services

    If you click on Options, which will then be underlined, on the right side the Agents options appear. The default, as with releases, is the hosted agent. Change this to another pool if you want local builds.

    2017-06-01 19_48_19-Build-CI - Visual Studio Team Services

    Microsoft makes this the default for a couple reasons. One, it’s easier. No need to install an agent, which might be an issue for some people. I think it’s simple, but for some teams, you might not want this.

    The other reason is that while you get 240 minutes per month, if you’re like me and built often, you can blow by this. Also, I have machines available, so I’d rather build locally, and I wish I could make this a default.

    Microsoft wants to make money, and that’s fine. I like the service, and in a commercial environment I’d weight the cost here v purchasing something like TeamCity + add-ons, and then make a decisions. Since I dislike uncertainty, and not knowing the costs, I lean towards local builds always. YMMV.


  • Why Does My Log Grow–SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    This is a great topic, and really, every DBA or admin should blog this and be sure they understand the issue.

    I saw a posting from someone that said this: they kept running low on disk space where the transaction log was kept. They would shrink the log, and they had 3 log backups per day, but the log kept growing, and this was an issue. What can they do?

    Let’s examine what happens: first, assume you have a trans‌‌action log that gets 2 transactions an hour. You have enough log space for 4 transactions in your log file. You back up 3 times a day (every 8 hours). Here’s your log size in transactions:

    1:00am - 2 transactions
2:00am - 4 transactions‌‌‌
3:00am - 6 transactions (log grows)
4:00am - 8 transactions (log grows) 
‌5:00am‌‌ - 10 transactions (log grows)
‌5:00am‌‌ - 12 transactions (log grows)
‌5:00am‌‌ - 14 transactions (log grows)
8:00am‌‌ - log backup with 14 transactions. Log is large enough for 14 transactions
‌9:00am - 2 transactions
10:00am - 4 transactions‌‌‌
11:00am - 6 transactions 
12:00pm - 8 transactions 
1:00pm‌‌ - 10 transactions 
‌2:00pm‌‌ - 12 transactions 
‌3:00pm‌‌ - 14 transactions 
4:00pm‌‌ - log backup with 14 transactions. Log is large enough for 14 transactions
‌4:30 - you shrink the log back to 4 transaction size
5:00pm - 2 transactions
6:00pm - 4 transactions‌‌‌
7:00pm - 6 transactions (log grows)
8:00pm - 8 transactions (log grows) 
9:00pm‌‌ - 10 transactions (log grows)
‌10:00pm‌‌ - 12 transactions (log grows)
11:00pm‌‌ - 14 transactions (log grows)
12:00am‌‌ - log backup with 14 transactions. Log is large enough for 14 transactions
‌

    Repeat this every day.

    Now, how does this change if we run log backups more often? Let’s say we decide to run log backups every hour. Now I get:

    1:00am - 2 transactions‌‌‌, log backup runs
2:00am - 2 transactions‌‌‌, log backup runs
3:00am -2 transactions‌‌‌, log backup runs
4:00am - 2 transactions‌‌‌, log backup runs
‌5:00am‌‌ - 2 transactions‌‌‌, log backup runs
‌5:00am‌‌ - 2 transactions‌‌‌, log backup runs
‌5:00am‌‌ - 2 transactions‌‌‌, log backup runs
8:00am‌‌ - 2 transactions‌‌‌, log backup runs
‌9:00am - 2 transactions, log backup runs
10:00am - 2 transactions‌‌‌, log backup runs
11:00am - 2 transactions‌‌‌, log backup runs
12:00pm - 2 transactions‌‌‌, log backup runs
1:00pm‌‌ - 2 transactions‌‌‌, log backup runs
‌2:00pm‌‌ - 2 transactions‌‌‌, log backup runs
‌3:00pm‌‌ - 2 transactions‌‌‌, log backup runs
4:00pm‌‌ - 2 transactions‌‌‌, log backup runs
5:00pm - 2 transactions‌‌‌, log backup runs
6:00pm - 2 transactions‌‌‌, log backup runs
7:00pm - 2 transactions‌‌‌, log backup runs
8:00pm - 2 transactions‌‌‌, log backup runs
9:00pm‌‌ - 2 transactions‌‌‌, log backup runs
‌10:00pm‌‌ - 2 transactions‌‌‌, log backup runs
11:00pm‌‌ - 2 transactions‌‌‌, log backup runs
12:00am‌‌ - 2 transactions‌‌‌, log backup runs

    In both scenarios, the total log transaction load across the day is the same. The total log backup size is the same across the day. However, a log backup allows me to reuse the log, so I never run out of space and get growths in the second scenario.

    If you aren’t sure how things work, or want to write your own blog, I would also recommend you read this:  http://www.sqlservercentral.com/articles/Administration/64582/

     

    ‌‌