Voice of the DBA

Author: way0utwest

  • Failover

    I saw a note from someone that had a database set up in a High Availability (HA) configuration for production. This person had received an 823 error on the primary server, but a failover hadn’t occurred. This wasn’t a critical error, but one that noted some anomalies in a few pages, which potentially could be fixed by the automatic page repair in SQL Server.

    In this case, the individual would have liked to have had the system fail over, just in case there were a chance this would impact production. To make this happen, an alert on the error would be needed, which then forced a failover. This wasn’t part of the native SQL Server configuration, and this individual was concerned. However, there are certainly cases where a failover might not be warranted when there is some sort of reaction such as Automatic Page Repair.

    If you are running an HA system, I assume you want control over when and why a failover occurs. If know there is a delay for client connections after a failover, or potentially less resources on the secondary node, or some other impact when you move to a secondary note, perhaps you want to be more careful and when a failover occurs. Wouldn’t you want configurable rules, even those that might require manual setup from a DBA? What about if you have a system that isn’t really designed to handle the full, normal workload; it’s just for emergencies. Do you want to fail to a secondary node if the primary node could still be used?

    There is a whole spectrum of situations where we might want or not want automated failover for our systems. In fact, if you have something like Mirroring or Log Shipping, it can be a complex process to fail back. In those cases, you really want to be sure something has broken enough that a failover is warranted. I’m sure there are plenty of cases where you might not even want to script a failover because you’d rather take a short outage than fail to a secondary machine only to need a fail back in a short period of time.

    Most of us worry a failover won’t happen when the primary system goes down. That’s the main concern we have, and certainly we want to test and be sure this works as we expect in an emergency. I’d also suggest that it might be worth taking a few minutes to think about what happens if your system fails over when you don’t want it? Those can be more problematic, especially if they occur too often and users are dealing with an unreliable system that seems to disappear or pause as it moves from node to node on a regular basis. That might be worse for your reputation than a system that doesn’t fail over in an emergency.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Quick Scalar Tables–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    This actually comes from Itzik Ben-Gan, by way of Kevin Feasel.  Kevin’s post is about CROSS APPLY, which is something I need to play with more because I hadn’t thought about this.

    However, the one thing I did see as interesting is the use of VALUES to get a quick table. Here’s an example. Suppose I want to get a list of the last four years in a table for some reason, I can do this:

    WITH    cteYears ( Yr )

          AS ( SELECT   2015

               UNION
               SELECT   2014
               UNION
               SELECT   2013
               UNION
               SELECT   2012
             )
    SELECT  Yr
    FROM    cteYears;

    Or I could be more compact and do this:

    WITH    cteYears ( Yr )
          AS ( SELECT  Yr = y
               FROM ( VALUES (2016), (2015), (2014), (2013) ) a(y)
             )
    SELECT  Yr
    FROM    cteYears;

    Is one arguably easier? Certainly some might like the UNION, but it’s a lot more typing, and I think it can be easier to make some mistake in editing. The VALUES clause can easily simulate a table, and you can quickly see groups of rows as well.

    WITH    cteYears ( Yr, champ )
          AS ( SELECT   y
                      , c
               FROM     ( VALUES ( 2016, 'Broncos')
                           , ( 2015, 'Patriots')
                           , ( 2014, 'Seahawks')
                           , ( 2013, 'Ravens') ) a ( y, c )
             )
    SELECT  Yr, Champ
    FROM    cteYears;

    SQLNewBlogger

    This is a quick item I noted, and one I’ve started to use more and more to build quick tables. It seems much easier for me to visualize and create the virtual table. I have started to use this to mock data, or run quick tests.

  • Too Much Information for Sports

    Many of us deal with lots of data and information, sometimes conflating the two when they are really separate things. Our jobs usually revolve around somehow  helping others to extract useful value from the data we store in some way. This could be with reports, or with transfers to another system, or even a simple explanation of what data in a column means from a business perspective.

    Like many industries, pro sports have been using more and more data to help them better manage their business. This was popularized with Moneyball, a book (and movie) about baseball. Since this time, plenty of other teams across many sports have started think about data analytics. The NBA gathers lots of data for each team, even making some available publicly (including spatial shot data). The NFL uses lots of sensors, and more sports, especially Olympic sports, are gathering data to help athletes perform better and learn more about the impact of their decisions.

    However, more data isn’t necessarily what makes decisions on the field of play. There was a great quote from Doug Baldwin, an American Football player for the Seattle Seahawks. In looking at all the data collected, he noted this:  “Yes, the data and information is useful, and give it all to me. But at the end of the day, the user has to use it the right way.”

    That’s a great quote, especially considering the owner of his team is Paul Allen, founder of Microsoft. More data doesn’t necessarily solve a problem, tell you what to do, or how to do it. Instead, data analytics and analysis is still a bit of an art with some science behind it. Data matters, but when we reduce a problem to a simple set of statistics, we can be missing the subtleties of the actual situation.

    I wouldn’t think most industries could do without data gathering and analysis, but people still need to be involved and work with the bits and bytes. Whether you’re in sports or business or some other area, it’s important to use data, but don’t discount the human factor in applying experience and understanding to extracting information from that data.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Learning to Search

    I’ve had my own domain for about 17 years now, and about 6 or 7 years I decided to move my email from GoDaddy to Google. There were a variety of reasons, but the bottom line was I got more accounts, more space, for less money. I’ve been happy there, and I even set up accounts for my kids to have their own email.

    About a year after I gave my son an email, I went to ask him if he’d seen a particular note from our Scout troop. He opened email and I saw that he had quite a full inbox. He had various newsletters and kid related emails (most unread) as well as a bunch of different messages. As he looked for the email, I asked him why he didn’t delete the old messages that he was ignoring or had already read. His reply surprised me.

    He pointed to the left side of the screen, below the list of folders, at a small graph. “See that,” he said, pointing at the percentage of space he’d used. “When that goes above 10%, I’ll worry about deleting things.” It was at 1% at that time, after a year of email.

    Not long after that, I read a note from Mark Cuban on email, where he said he didn’t bother to delete emails or move them into folders or anything else. It’s inefficient to worry about this. Instead, he buys space as needed and uses search. I started doing that as well, rarely deleting emails, and counting on search to help me find things. I’m at 5% of my Google allocation after 6 years and if something isn’t on the first page of email, I always use search to find it.

    I’ve started to do that elsewhere. Even in databases. I was reminded a few months ago that Redgate Software (my employer) makes SQL Search. Ike Ellis made a short video that shows how he uses SQL Search to find objects in databases rather than wandering through the Object Explorer. Ike is a consultant and runs into lots of databases and can’t remember where every object is in all of them.

    I like that, and I find myself starting to use SQL Search because it’s quicker than opening a database, then opening Programmability, then opening Stored Procedures, and scrolling. Search is a couple of clicks and keystrokes, and way faster.

    I use search more and more, on my local disks, inside particular websites (you should all know about site: searches on search engines), in Books Online, and in code. Most IDEs make this easy, and trust me, once you start to do this, you’ll never go scrolling around again.

    At least not if you want to be efficient.

    Steve Jones

    The Voice of the DBA Podcast

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