Voice of the DBA

Category: Editorial

  • Google Scale

    I heard someone talking the other day about how Google needs to optimize their code. They really pay attention to low level operation of their software, actually having people spend time writing compilers and ensuring they generate efficient code. This is on top of the idea that they want to ensure data structures efficiently use space, there is no unnecessary network traffic, etc. The reasoning is that at “Google-scale” (Gs) if something takes 10% longer, that’s 10% more electricity they have to supply, 10% more machines, and 10% of the Google IT infrastructure budget is a big, big number.

    I’ve never worked at Gs. In fact, I’ve never worked at 0.001Gs. Most of my work in software development has been to support dozens, or maybe hundreds of users. SQLServerCentral, which was a joint programming project, was my largest piece of software , and that was built on other pieces of software, with little contributions from me and lots of Andy Warren’s keyboard sweat in the early years. Despite working on small projects, I still have always learned to consider the little things.

    When you grow up with compilers that take minutes, as in 10 minutes to compile a few hundred lines of code, you pay more attention to your algorithms. When you grow up with kb of memory, you use it wisely. When you work across a 300 baud modem, you don’t mess around with lots of data transfer. Even though many of those constraints don’t apply, I still try to be careful and watch my data types, watch my round trips, and be aware that there are actually bits being whipped across the ether or in and out of transistors in a not-so-little package on my computer system.

    The argument about “just buying more hardware” has some validity, but it’s not an excuse for not improving your skill and becoming better at your craft. As my friend Jeff Moden often quotes, it doesn’t take any more time to do it right the first time. That’s true, but you need to know how to do it right, we at least well. For all of us, that should mean some regular effort to improve your skills and become better at your craft, just like Google looks to constantly optimize their own systems.

    Steve Jones

  • Proving Your Identity

    I caught this article on Dark Reading that talks about the problems of multi-factor authentication. It’s interesting to me as I had written an editorial on passwords and mentioned that I used the fingerprint reader on my laptop. Someone pointed out that my fingerprints are likely all over the machine and could easily be lifted and used to gain access to the machine. That’s true, and it’s something I hadn’t thought of. To me a fingerprint reader is a convenience, but I might need to disable it for travel.

    As database professionals, we often rely on some other system to prove a person’s identity. For SQL Server, we typically rely on two common choices: a simple name and password, or some security token from the operating system. Those two have worked well, and since SQL Server 2005, we have also had additional encryption options that can be used to protect data, including the ability to use certificates to protect the keys that encrypt data. SQL Server 2008 also allowed Extensible Key Management, so that third party products could be used to secure data.

    As we store more and more data, and this data becomes valuable, it is more and more likely that individuals will try to steal data. While we can’t protect the data from insiders that need legitimate access, we do need to ensure that rights are properly granted and that our security systems have some way to verify the identity of the person or application that connects to SQL Server.

    It still feels like that the security mechanisms for SQL Server as a little immature, and the costs of implementing things like EKM are too high. I am hoping that it becomes more practical to implement better security over time, and we get more tools in SQL Server that help administrators manage permissions.

    Steve Jones

  • More Triggers

    In the old days of T-SQL, back when we wrote “CREATE TRIGGER …. FOR INSERT” we could only have one insert/update/delete trigger for each table. Eventually SQL Server allowed us to have multiple triggers, and even have some control over in what order the triggers fired.

    Triggers are often hidden objects that confound DBAs who aren’t aware they exist. It’s not easy to tell when a table has a trigger on it, and since we don’t often use triggers, it’s not the first place people look when something strange happens.

    However triggers are useful, and it seems that there are many people using them. For this Friday, I wanted to ask how people implement triggers in their applications.

    Do you prefer one trigger for each table action or multiple triggers?

    I’m curious what’s the 80 in your 0/20 rule for triggers. Should all update actions be handled in one trigger? Or should there be one trigger for business logic  and a separate one for auditing? I’m not sure it matters a lot for performance, but I can see that it might be easier to manage and track fewer triggers. The flip side is that something like auditing can be handled with one trigger, and business logic with another: a clean separation.

    Triggers aren’t usually my first solution to a problem, but I do think there is value in using them. However I don’t see a lot of guidance about how to best implement them, so I’m hoping your answers today will help.

    Steve Jones

  • Live Monitoring

    My employer, Red Gate Software, recently released a new product called SQL Monitor. This is the 2.0 version of SQL Response, enhanced and renamed after a lot of feedback and input from various DBAs around the globe. It’s very cool and in preparation for the release, they asked if we could install the software on the SQLServerCentral servers. I was happy to have it on the servers for additional monitoring. Even though I don’t have to manage the databases, I wanted to make sure whoever is managing them has some tools.

    Then my boss asked if we could make the data public. He mentioned that the early look at the console showed a lot of alerts, potentially some less than optimal configurations, and more. I said we should definitely show the site, and at The Future of Monitoring, the try it online link shows the live SQLServerCentral database cluster.

    That’s live, as in a few seconds delay to process the data, but if you look at instance 1, the graphs are changing in real time. Keep in mind that it might appear slow with the huge SQLServerCentral community checking the servers, but you probably don’t have a million wannabe administrators checking out your servers.

    We haven’t changed much at all in the configuration since we made the data public, but over the next few months we are going to slowly tune and configure the server live, informing you of the changes, and showing the before/after data. If you’re following along, you can track the performance differences yourself as well. This is a real world performance because just like most of you, we have resource constraints while managing and tuning servers. We can’t optimally design, build, and manage our servers as that’s not our primary job, but we’re trying to get better by using SQL Monitor to help us keep better track of the servers and work more efficiently.

    One of the coolest things I’ve seen is that our IT staff actually has iPads to watch over servers, and they love it. There’s a slide show on the front page of the monitoring site showing lots of people running SQL Monitor with tablets. Maybe I should take on some DBA chores and see if they’ll get me one. Maybe you can convince your boss to get an iPad with a copy of SQL Monitor as well. 😉