Voice of the DBA

Category: Editorial

  • The Brittleness of Replication

    I’ve used replication for years in different positions. I first discovered it in SQL Server 6.5, using it to simulate a queuing system to keep two disparate applications in sync without requiring transactions between the applications and causing errors. As handy as replication was, it was also brittle. Small things would sometimes break replication and the quickest way to repair the system was to drop replication and re-initialize it. Fortunately my data was small in size.

    As I’ve watched replication grow through the years, I’ve been happy to see enhancements, like the ability to replicate schema changes to objects contained in articles. However I’ve been disappointed that the tooling has barely improved and the brittle nature of the process has not been addressed.

    It seems that as SQL Server grows, we find more and more features and subsystems that are being managed like replication. They get built and then languish with few resources devoted to improving their tooling and robustness. Service Broker, spatial data, and more are examples of features that were introduced, but have received little attention in newer versions.

    I have to believe there are developers at Microsoft that would love to make the product better. I understand the need for new features, and continuing sales, but devoting a percentage of time to improving existing features, regardless of the Connect votes or marketing wishes, would help make the platform that much better for everyone.

    Steve Jones

    The Voice of the DBA Podcast

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

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

  • Data is Not Always Enough

    As data professionals, our jobs deal with ensuring that there is data available, accurate, and relevant to the organizations in which we work. However we often go beyond the simple maintenance, gathering, and manipulation of data to help our clients and customers understand the information contained in our systems. In many of my positions, whether as developer or DBA, I’ve been tasked with working closely with business people to make decisions based on data.

    I’ve always been of the opinion that more data is better, though I certainly understand the problems of Big Data and the potential to reveal false patterns or overwhelm systems with conflicting information. The better I understand the domain, and the better educated the business analysts are, the better we can work to extract information from databases.

    However that’s not always enough to help an organization act. I read an interesting piece this week on data in sports, which talks about one of the NHL team’s managers using data to make decisions on how to restructure his team. It’s interesting to see that additional data gives the general manager more insight, and leads him towards a decision, but the rest of the organization can’t follow through. In this case it’s a matter of money, but resources constrain our efforts in many decisions. I think this is a place where more flexible, and perhaps more in-depth, BI-type analysis of what-if scenarios can be more helpful.

    The other piece that caught my eye had to do with real estate, and housing prices, with the author bemoaning the lack of data in determining the value of a house. It’s interesting, but to me, it’s flawed. More data won’t help because houses aren’t like many other commodities. One house is not fungible with another one, and the market is both fast moving, and inherently full of friction. We rarely buy a house without visiting it, a task that consumes time, and slows the movement of information through the system. I’m as frustrated as the author, but I don’t know that more data would help in this case.

    Data is important, and it should be a part of our decisions, but we should remember that data isn’t necessarily going to make the decision for us. We need to be ready to incorporate our own knowledge and judgment into a data set to help us decide on a course of action.

    Steve Jones

     

  • Monitoring for Non Existent Events

    I was catching up on work recently, reading the third installment of The 5 Worst Days in a DBA’s life, starring The DBA Team. Someone had asked me if I enjoyed having Paul Randal (b | t) of SQLskills join them team. The piece had been edited and published while I was gone, and I hadn’t had a chance to immerse myself in the adventure. I was anxious too read how Paul helped save the day.

    It was a fun read, but one quote in the piece struck me. “A job that runs long or doesn’t run at all can sting just as bad as one that fails.” That’s a quote from my character showcasing a situation that few people actually think about. However jobs that don’t run or don’t finish are situations that DBAs should be monitoring for.

    So many of us adopt a set-it-and-forget-it mentality with our jobs. We assume that things will work, or fail, as we set them up. However it’s easy to forget that there are other states we might find ourselves or our systems in that can cause issues.

    Monitoring is critical to any well run system, but monitoring needs to be set up well. If we require that certain jobs run, we need to not only check for success or failure, but if the job has actually run and completed. It’s easy to accidentally disable the wrong job and not notice. It’s also entirely possible that a job gets stuck and doesn’t complete.

    If you’re not watching for those other states, you might find yourself in a situation where you don’t have backups and your job is on the line. However you probably won’t have The DBA Team to call on.

    Steve Jones

    The Voice of the DBA Podcast

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

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

  • Elevation of Privileges

    At SQL Bits this year I attended a security presentation from Andreas Wolter. The session examined some attack methodologies, showing the flow that an attacker might go through to gain information about your database instance with SQL Injection. It’s a scary and eye-opening talk, and one that I might recommend to all DBAs and developers so that they can understand the dangers involved with poorly coded applications.

    One of the most scary attacks was the elevation of privileges from a web user to a sysadmin on an instance, mainly because of the Trustworthy setting being enabled. I had never imagined this as an attack vector, but it was disconcerting to say the least. However it got me wondering about instances I’ve managed.

    Would I detect if a new sysadmin were added? Or an existing user added to the role? I’m not sure I would, though that’s certainly something I plan on setting up with some sort of monitoring to detect. I would guess that most DBAs, whether professional or accidental, might not catch this either, at least until some audit was performed. At that time it might be too late to protect your data, and certainly too late to protect your reputation.

    Security is a tough topic, and it’s an ongoing process to protect your systems. I hope to see more presentations like this at future events, and I’d encourage you to request them for any events you plan on attending. You can certainly do this for all SQL Saturday events (there’s a suggest a session on the schedule page).

    Security requires vigilance and vigilance requires monitoring. Both of those also need knowledge, so be sure that you don’t neglect the security of your SQL Servers and continue to educate yourself over time as well as implementing technical solutions.

    Steve Jones

    The Voice of the DBA Podcast

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

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.