Voice of the DBA

Category: Editorial

  • Provisioning

    This editorial was originally published on May 24, 2013. It is being re-run as Steve is on holiday.

    At a talk recently, the presenter talked about the time lag for an on-premises server to be installed and configured to be on the order of months. Not that many, but it could easily be six months.

    That seems crazy, but in many companies I’ve worked in, that wasn’t an unusual time frame. From the time someone decided that a purchase was allowed, it could be days for the accounting department to approve the funds. A day or two to place an order, assuming that IT had already provided the specifications. Vendors respond quickly, but it could still take a week or two, possibly even more, for a server to arrive at our company.

    One of the advantages of cloud computing services is that new virtual machines can be bought and enabled in minutes. That might be true, but I wanted to ask many of you if you know how longs it actually takes for you to begin working on a new SQL Server.

    How long does it take to provision a new server?

    Imagine that you were to ask for a database server today, and you management agreed to grant the approval, think about how long would it be before you could sign into a SQL Server. I’m sure some of you have virtual infrastructures available, and that should reduce the time it takes, but let us know if that’s your setup and how long it takes.

    The ultimate service would be a cloud like application inside your organization that allowed you to select a SQL Server template, and send you a server name and login with minutes after an automated build took place. I don’t know many companies that have that yet, but I think many of us would appreciate that capability. Especially if we could choose the hardware we required.

    Steve Jones

     

  • The Future of Auditing

    This editorial was originally published on Dec 5, 2013. Is it being re-run as Steve is on holiday.

    I was reading Captain’s Share the other day and enjoying a quiet afternoon at home. It’s a science fiction book about one man’s journey in the future as a captain of a space freighter. It’s an interesting series from Nathan Lowell that I’ve enjoyed and recommended to other science fiction fans. In the book, there’s a scene where the main character is leaving his old ship as first mate and moving to a new ship as the captain. However he notes that the formal process is to deactivate his records on the old ship and ensuring they will be read only forever. The book notes they can’t be deleted because they are a part of the ship’s records, log entries, etc.

    That seems to be a far cry from the way auditing takes place in current computer systems. Auditing of systems is under the control of the sysadmins (who are sometimes hackers) and can be altered, changed, etc. We, as software designers, haven’t done a good job of ensuring the integrity and longevity of log records. In some sense, it seems to be a fundamental flaw in OS and software design to not have separated out the auditing and recording of actions from the administration and rights of the rest of the system.

    I’d hope that we would recognize that auditing actions and preserving this data is something that ought to be tightly linked to, but separate from, the rest of system operation. I’d like to think that fundamental changes and actions taken on the system should be written separately to an area that is easily marked as readable by non-sysadmins that are designated to review the information. I know we have the challenges of managing the space and the problems of spurious actions being generated to fill (or rollover) logs, but I’d think after 50+ years of computing we would have considered some sort of event log that isn’t under the control of the people whose actions it is recording.

    SQL Server has improved its auditing features and capabilities, but far too much is still linked invariably to the sysadmin, often the same person the auditing should be watching. This is certainly one area that I hope matures in future versions as the need grows to track and review actions taken by privileged accounts.

  • Test Coverage

    This editorial was originally published on Nov 12, 2013. It is being re-run as Steve is on holiday.

    I’ve never had to work full time in a QA group, but I have had to perform software testing of an application and it wasn’t fun. Even as I worked through the various features, looking for edge cases, common mistakes, etc., I realized that running tests was something that really required more discipline and process than I was giving it at the time. Too often I ‘d realize that my regressions weren’t 100% duplicates of previous executions because I’d allowed too much “human effort” into my process.

    These days most testing of software is automated. I know QA groups still exist, and they need to handle some of the manual checks that are very hard to automate. However more and more testing is being pushed back onto developers to handle, with frameworks like NUnit and JUnit. In the database world, we haven’t done a great job of including testing into the code we write, but there’s a great testing framework we can use.

    TSQLT is a framework written by Sebastian Meine and Dennis Lloyd and it’s free. It’s been developed to help you write tests that can exercise your T-SQL code to determine if it’s doing what you expect. I’ve used it a little, and while I see the potential, I also realize that this will take some practice to learn how to more easily write tests that can cover the various potential places where bugs can be introduced.

    However, if you use version control, and you should, then you can easily spread the load of writing tests to all of your developers. Since the tests are stored procedures, they can be included in your VCS project and shared by all your developers. With a little effort from each member of your team, you might be surprised at the code coverage you can achieve with this framework.

    Testing is important, as we see over and over again as software is released and doesn’t work as expected. I think much of that is our fault, as software developers, for not improving our testing skills and discipline.

    Steve Jones

     

  • Better Coding, More Savings

    This editorial was originally published on May 13, 2013. It is being re-run as Steve is on holiday.

    I’m sure most of us would like to think that we write fairly efficient code. However the reality for many of us might be that we don’t actually know. Many of us use the same patterns and practices that we’ve been using for a long time, rarely changing. When we learn a new technique or find a different way of coding that works better, we tend to then use that method over, and over, and over, and over again.

    I would guess that if many of us profiled our code, and examined the CPU and network bandwidth we consume, we might be surprised at what we find. CPU and network usage isn’t something we are often concerned about. We assume that we’ve bought a machine and we should be able to use as much of it as we can at any point in time. That’s not the best approach, but since we often have more hardware than we need for many processes, it works. It also explains why so many applications struggle as the load increases. They’re not coded efficiently.

    If you’re going to work in the cloud, you better learn to code more efficiently, mostly because it costs money. If you think about your design, you can reduce the amount of resources you use. In the cloud this translates to less cost. In the on-premises world, this means better performance and higher scale. It also means less complaints and phone calls.

    Scaling up an application can be hard, but much of the struggle comes from poorly coding your application in the beginning. Most of us have heard the saying that it takes less time to do it right the first time. That’s true in many situations, and it’s true for your application development. Learn to write more efficient code and use patterns that conserve resources. You’ll find your applications will run better, no matter what type of environment hosts them. If you’re not sure what patterns and practices work well, read an article or ask a question and find out what efficient techniques others use.

    Steve Jones