Voice of the DBA

Tag: administration

  • Kill a SPID

    This editorial was originally published on Sept 5, 2007. It is being re-run as Steve is traveling.

    I know some people get a kick out of running the KILL command. Heck, I’m sure all of us enjoy stopping a runaway process from an annoying user at times. But not everyone seems to understand exactly how databases work. It’s not necessarily a knock on the original poster, after all, most of us had to learn about the ACID properties at some point. Perhaps even after someone dropped a database in our lap without warning.

    If you kill a spid, I saw some confusion about data loss in a recent thread. You won’t lose data, but you could still have a problem. It’s not a technical problem and the storage engine inside SQL Server ensures that the transaction conforms to the ACID properties to ensure data integrity.

    The users, however, might or might not realize that their “work” was not done. It depends on the error handling of your application, what message (if any) that is presented to the user, and if they’re even still around. I’ve seen people start processes they expected to run long and then leave their workstation. If there wasn’t a message on it later, they might assume the transaction had gone through.

    Even if you tell them you’re killing the process, they might still think that some amount of work is completed. There are all sorts of users, with all different kinds of expectations out there, so you should be sure that they understand exactly what is happening.

    And be sure that they know their “work” is lost. From the point of view of someone doing data entry, if the transaction rolls back and the application can’t handle it, they will have to enter information again. So their work is essentially lost.

    Those of us in technology sometimes forget the impact of our systems in the real world. Even when things work well or as designed, they may still be a problem for real people that have real tasks to get done.

  • The Danger of Custom Software

    The Movie Vanishes
    My kids enjoyed this DR tale from Pixar.

    There’s been a great little movie short making the rounds of the Internet from Pixar. It’s called “The Movie Vanishes” and it’s worth a few minutes of your time. Toy Story 2 was almost lost because of a mistake and some bad luck at Pixar.  This was at a time when the company was successful, and certainly should have been able to better prepare for a disaster. If you want a touch more background, there’s a few other notes at Quora.

    A lot of the software that Pixar uses was written in house. That’s a double edged sword because there isn’t anyone that can stand behind the software, other than the people that wrote it. There might not be adequate testing and there are certainly bugs in the software that may lie dormant for years. I have no idea of any of the bugs inside Pixar’s software caused this disaster, and I’m not implying it did.

    The positive side of building your own software is that you know how it works. You have the source code, and if you have a developer that can understand it, you can fix problems, patch issues, and customize it to suit your needs. As long as you have the time and resources to do so.

    I saw someone write recently that building their own monitoring solution for a set of SQL Servers was easy, but that was the smallest part of the job. Maintaining and enhancing it over time were much larger jobs than setting up monitoring. This person said they’d rather buy a package in the future than build their own again.

    If you have a system set up, it probably makes sense to use it, but as you look to develop new software, whether for monitoring servers or handling sales, it might be worth spending a bit of time trying to determine if there is something out there you can buy, which might be well tested, vouched for by other customers, and be easier to integrate than your own system.

    Steve Jones

    SQL Monitor from Red Gate SoftwareIf you don’t have monitoring set up, you should. SQL Monitor from Red Gate software is an easy way to get notified when something in your environment needs to be looked at further.

    If you want to get monitoring setup without minimal effort and immediately, think about downloading a trialof SQL Monitor and testing it with your servers.

    If you’d like to see SQL Monitor working on the live SQLServerCentral database server, go over to monitor.red-gate.com.

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • T-SQL Tuesday #31 – Logging

    TSQL2sDay150x150It’s T-SQL Tuesday time again, and this month Aaron Nelson (blog | @sqlvariant) is hosting. The topic is logging, and if you’re like to participate, read Aaron’s post and learn the rules. We do this on the second Tuesday of every month.

    If you’d like to host, contact Adam Machanic. It’s easy to do. Get on the schedule, pick a topic, and then write a post.

    A list of previous posts is here,

    Logging

    I’ve found documentation of events to be one of the most important things I can do in my career. Finding out what happened, what changed, or what I did has been important many times, and often helped me come through difficult situations.

    Logging is the automated version of documentation. All kinds of applications, including SQL Server, produce logs of the various activity on the system. In SQL Server, we are moving to an eventing system, and if you haven’t looked at Extended Events, you should.

    One of the times when I found logging to be lacking was in a startup I worked at a decade ago. We had a number of developers that were working on various development servers. They had full rights, and they were allowed to build their own objects. That was a little concern to a controlling DBA like me, but I allowed it since they often wanted new objects quickly, and if I allowed them to write their own, they’d use stored procedures.

    A good compromise, if you ask me.

    However in the hectic pace of development, I found that the developers didn’t often keep good notes about what was being built for which features and functions. Since we had to produce a build script fairly quickly every Monday in order to update our QA systems, we would find that developers invariably would forget objects and we would not have a well tested QA script on Monday afternoon.

    I decided that we needed to better log the changes on our development server. I didn’t care about every change, especially intermediate changes to objects, but I did care about the gross changes made each day.

    This was in the SQL Server 2000 days, with limited tracking of changes outside of SQL Trace. Since I had no desire to move through lots of trace files, even in an automated fashion, I decided on a much simpler method.

    In sysobjects (now sys.objects), there was a crdate field, which tells you when the object was created. However that doesn’t change if an ALTER TABLE is run (or any other ALTER). That stumped me briefly, but I decided to search further.

    I found that there was a schema_ver field, which is incremented every time the object is changed. Since the majority of our developer changes were ALTERs, I could track the version number and then compare this each day. I tested this out, and it worked well.

    The outline of the solution is that I grabbed a copy of the sysobjects table every day and stored it in a temporary table. I then used a left join to compare this with the previous values stored in a table I’d created to store the data. When I found differences, I logged them in a table, along with the date, and sent myself an email. I would then overwrite the stored version of the objects with the version from the temp table, giving me a baseline for the next execution.

    At the end of the week, I’d have an aggregate list of all objects changed, which I could then compare against our build script.

    At the time we were in an agile environment, releasing new code every Wednesday, and operating on very short timelines. The logging I did cut down on mistakes and allowed us to have a smooth release process that functioned for over 18 months, with code releases nearly every Wednesday outside of holidays.

  • The Platform as Code

    data center
    Trying to manage all your servers individually is like tracing cables in this kind of setup.

    SQL Server has grown more and more complicated over the two decades that I have worked with the product. At the same time anyone can install it and set up a database with almost no training. There are more and more features and functions to learn, yet it’s become a much easier product to use. It seems as though one person may view the platform as very difficult to use while the next sees is as requiring almost no management. Both of these views can be true, but as we say often about SQL Server: it depends.

    When I started working with SQL Server, it was a cumbersome platform, the network configuration could be tricky and it wasn’t easy for developers to master the named pipe connections that were required. Since then it has become a very easy to set up and use platform, with many system administrators managing servers that almost manage themselves. “Almost” being the key word there.

    Just as with other Microsoft platforms, the days of having a set of procedures that handle your daily tasks and lead you through the solution are gone. The platform handles the simple stuff, the routine issues, but it is almost a requirement that a good system administrator be able to write T-SQL queries and string together segments of code.

    I was reading an interview with Jesse Robbins, the founder of OpsCode, a company focused on infrastructure automation. In the interview, he had a few quotes that struck me as very true. “What has happened over a period of time is that sysadmin skill set has been expanding more and more to include more and more basic infrastructure software development.” The second quote deals with managing more and more servers: ” That is not being built on your back every day, when you’re logging into the same 50 boxes and typing in the same 50 commands. ”

    Those quotes were similar to some of the presentations I saw on System Center 2012 at DevConnections. There is a focus on automation, on scripting, on working with groups of machines at scale, not as individual systems. The administrators, whether on Windows, Exchange, SQL Server, or some other platform need to learn how to better administer their systems with code, not with the GUI.Steve Jones

    SQL Server ConnectionsIf you want to learn more about Systems Center and SQL Server, come to DevConnections in the fall. It’s the best conference to learn about all parts of the Microsoft technology stack. Grant Fritchey and Steve Jones will be there speaking along with lots of other great Microsoft technology specialists.

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.