Voice of the DBA

Author: way0utwest

  • Five Minute Refactoring

    I’ve listened to, and been a part of, no shortage of arguments about the technical merits of a particular programming solution. I’ve seen two (or more) developers argue about the way to solve a particular problem for hours, even days at times. The Internet is full of discussions and debates in forums on the “best” way to write code for all sorts of specific issues.

    These arguments are sometimes referred to as religious wars for the devotion and belief that each proponent has in their solution. I tend to try and avoid becoming embroiled in any sort of zealotry as I am always cognizant of the fact that I need to be effective. I need to get things done and can’t spend a lot of time arguing about a solution, whether for or against it. i need to get work completed.

    When I was managing people I tried to limit the devolution of my staff into a never ending argument, but I ran across a better solution recently. Al Shalloway wrote a blog about limiting discussions to five minutes. He notes that two level-headed people ought to be able to come to a consensus in five minutes. Beyond that he says it’s likely that one of them is arguing about covering possibilities rather than current realities.

    That’s interesting, and like many rules, I’m sure there are exceptions. A manager can make a decision to continue a discussion if they really think that it is warranted. However as a default, the idea that a team should choose the easiest solution to implement if they can’t decide makes sense. It’s the least amount of work that might be wasted, and we want to avoid wasting time and work when we can.

    We won’t always agree, and despite our best intentions, we will make mistakes and we will make poor decisions. Don’t let indecision continue indefinitely and don’t rashly make decisions, but don’t let the fear of mistakes prevent you from choosing a path. Be decisive and move forward, adjusting as you learn more that helps you refine your solution.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Challenges of Space

    It wasn’t that long ago in my career when a survey showed the vast majority of SQL Server databases were < 10GB in size. These days I’m sure the average has risen as there are some very, very large SQL Server databases. I know a few people managing PB sized systems, which certainly will move the average upa bit. It’s likely, however, that the majority of databases are still very small, powering smaller applications with < 100GB.

    However almost every databases continues to grow, and as systems grow, new challenges come into play. While we can still store a 100GB backup file on a thumb drive or SD card, it’s still a significant amount of data to write, which can take more time than our clients would like. Archivals, backups, restore, all tend to come from non-flash media, so larger systems certainly mean longer RTOs if we have issues, even if we’re just restoring a copy of a database to extract some deleted information.

    However there can be more fundamental problems. I ran across a post from the CSS Engineers at Microsoft that talks about exhausting NTFS resources as more and more writes occur on a filesystem. I’d never heard of this, or expected it to be an issue, but apparently it’s occurring more often. I suspect even the NTFS developers hadn’t considered the scales to which we probably stress the file system daily. What is interesting is that this can occur with DBCC operations, database snapshots, and even bcp operations. There are workarounds, and certainly I’m sure that Microsoft has people working hard to increase the capacities of ReFS, which I expect will take over from NTFS, just as NTFS has largely displaced FAT.

    As we grow to larger and larger scales of data sizes, with multi-terabytes on laptops and petabytes on servers, I think we’ll need to adapt a number of our techniques and operations in the future. We might end up needing different types of backups (anyone considering lookup table backups or daily backups of a set of rows, like orders?), as well as the possibility of continuing to spread the load of our server across multiple drives. I just hope that Microsoft recognizes this and looks to ensure mount points work smoothly across all of their technologies and features. At some point, 26 drive letters won’t be enough for the majority of us.

    Steve Jones

  • The Rename Game

    As I’ve been talking about database development, Continuous Integration, and Continuous Deployment, I keep hearing complaints and concerns about the problems that occur when we make database changes. In particular, I often hear about breaking changes made to databases, that require some manual scripting work.

    It seems that the most common breaking change is adding a not null column to a table with existing data, but certainly there are other problematic issues. The other common change is renaming objects. In the past, I’ve rarely renamed anything once I’ve deployed tables to production, but I’ve run into quite a few developers that note they regularly change names. This week I’m wondering how common this is for most of the data professionals out there.

    How often do you rename objects?

    It could be tables, views, columns, constraints, whatever. If you rename anything in your database, I’m curious if it’s a regular event. Is this an event that causes you issues with your software development process, let us know. I’d also be curious with how you manage tracking and deploying the code from development through to production when you are making these types of breaking changes. Are things still scripted and able to be automated or do you force manual steps.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Using the DAC with SSMS

    I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

    While troubleshooting another issue, I needed to connect to SQL Server with the DAC. I couldn’t remember the syntax, so I looked it up quickly and ran into this link: Diagnostic Connection for Database Administrators. I added the ADMIN: to my connection in SSMS for a query window and clicked Connect.

    dac_g

    This took entirely too long and then I got this:

    dac_a

    That error resulted in a rabbit trail for me to debug this, but it worked out and I’ve learned a few things. The main one is to be sure that I’ve read the documentation and errors correctly.

    As I tried a few things, including SQLCMD, I realized I had issues. I searched and found that the DAC gets set to a specific port. I checked the error log to get the number.

    dac_b

    With that, I added it to my connection dialog, sure that this would work, but of course, it didn’t.

    dac_c

    Eventually I stumbled on the post about errors and realized I should have checked the SQL Browser earlier. I thought about it, but discarded that thought because I could connect in other ways. Mistake. Check networking first, and networking is where the browser comes in. Sure enough, it was stopped.

    dac_d

    The properties were set to DISABLED, so I had to change that before I could start it. I assume you can do that, if not, poke around the properties until you find that setting. With that changed, I started the service.

    dac_e

    Then I could connect. Here’s the SQLCMD version.

    dac_f

    With this working, the first command dialog at the top of this piece worked from an SSMS query window.

    SQLNewBlogger

    This was longer. What I thought was a quick lookup turned into a troubleshooting exercise that lasted about 20 minutes as I searched, read, experimented, etc. At least I made a bunch of screenshots as I was experimenting, so I had lots of data and didn’t duplicate anything.

    The actual writing was only about 10 minutes, most of that looking through screenshots and trying to organize my thoughts.

    References