Voice of the DBA

Category: Editorial

  • Your Job

    This quote is a great:  “I don’t really care if your code runs locally. If your code just runs locally, then my only option is to sell your computer so that our users can use our software, and that really doesn’t scale.” That quote is from an open letter to engineers and talks about what the job of a software engineer really is. It’s not just to write code, but to make sure our code and the entire system works for users.

    That’s important. It doesn’t matter how well you code if the application only works on your machine. We traditionally haven’t been great at getting code from one system to another and ensuring it works, at least not in a smooth fashion. We haven’t really followed an “engineering” process when we write, and deploy, software.

    The DevOps movement is changing this a big, bringing automation, scripting, and repeatability to both software development and operational deployment. All of these things should help us adhere to a more repeatable engineering practice. The commoditization of powerful hardware has also helped as many client environments might be nearly as powerful as development systems these days. It also means that our test systems can be good substitutes for production systems, at least if we can quickly build up and tear down large virtual environments.

    However we still need to be sure that we always remember that our code needs to work on other systems. Whether it has to scale down to meet the needs mobile devices or slightly older browsers and desktops, or our code needs scale up to handle thousands of times more transactions on a production database server than it processes on our laptops. We have to always consider the needs of the end user environment as we develop software. We need to test it in those situations, and broaden our skills to ensure that our applications satisfy the needs of the clients. And, of course, we should be able to quickly and easily deploy our changes to any other environment.

    Steve Jones

     

    The Voice of the DBA Podcast

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

  • Statistical Analysis

    I ran across a book recently that is written to address some of the statistical issues the author has found in many scientific papers. It’s been released online for free, but if you like it and would like a more portable format, you can buy it for a reasonable price.

    I haven’t dug into the book much, and I certainly don’t consider myself to be a statistician. That wasn’t one of my stronger subjects in school, and it’s a weakness I’d like to correct at some point. However I have had to use some of the statistical functions in past applications, and I wonder if I was using them wrong.

    This week I decided to see how many of you are using more complex math in your systems. I’m hoping you understand how the functions work, but I wanted to ask what you’re using:

    What statistical functions have you implemented in a production system?

    I am thinking of functions beyond basic aggregates like SUM and AVG. Are you using standard deviations, linear regressions or some other complex functions? Have you made use of built-in functions in T-SQL, R, or some other language? Are you implementing custom functions in code or CLR Aggregates?

    I think this is one of the areas where our profession will grow more and more across the next decade. As we deal with lots of data of varying types, and our organizations look to gain some strategic advantage through deeper insight into their information, we will have lots of chances to experiment and learn more about complex data analysis.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Hidden Patterns in Data

    There’s a great TED talk on statistics, and one I’d recommend you watch. However, it’s not so much the topic and data presented, but the way in which it’s presented and broken down that becomes interesting. If we ignore the topic, and just look at the analysis, a few  things stand out that I’d apply to business.

    The first noticeable thing for me was the breaking down of the large bubbles into smaller ones. I think that all too often we try to boil down information to a single, or very limited number, of values. While the overall average or graph of a set of data can be helpful, it can also be distorted by outliers in the underlying data. Seeing the data broken part into quintiles helps to analyze how closely the overall average matches up with the data.

    The other item that I think sometimes get lots of analysis of business data is the change across time. We often look at year over year, or last period’s values, but we don’t dive much deeper into the changes over time. Seeing a series of numbers on a page works well for some people, but the animated visual can be helpful in discerning a pattern or making a decision based on data.

    This is a great look at statistics, and it makes me think many of these techniques can be handy in looking at something like server performance metrics and deciding if we need more (or less) resources, or even if we have good candidates for consolidation. I’m looking forward to trying to adapt these tools for my own uses.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Native Audits

    As our databases contain more and more information, it is becoming increasingly more common to implement some sort of auditing feature to ensure that our data is not being inappropriately accessed or altered. It’s not only the management of our organizations, but also regulatory rules are becoming more numerous, and some may think onerous, which increases the burden on the DBA.

    There are numerous products available to help here, but SQL Server has included it’s own internal features since SQL Server 2008. However, it seems that I encounter many people that are unaware of the SQL Server Audit feature, and indeen, may not be aware of the Extended Events system on which it’s based. This week I wanted to ask if you are using this feature.

    Are you using SQL Server’s native Server Audit and Database Audit features?

    If you can share and reasons or details on how you are using the audits, or how easy it is to administer them, it would be interesting. I think we do need to increase the level of auditing on our systems at least to ensure that we are not experiencing any inappropriate data access. At a minimum, we should be monitoring for privileged level access or account changes.

    Steve Jones

    The Voice of the DBA Podcast

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