Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #098–Technical Challenges

    tsqltuesdayIt’s the first T-SQL Tuesday of 2018, being brought to you by Arun Sirpal. His invitation this month asks for you to talk about some technical challenge that you conquered in your career. Some type of issue that you had to troubleshoot and discover what the issue was, as well as the item you corrected.

    I’ve written about lots of items in my career, so I decided to reach back to pick one that wasn’t too hard to diagnose, but was hard to solve.

    Inconsistent Errors

    A long, long time ago, in a company far, far, away, I was a DBA. Actually, not that far away. Just down the road in Englewood, CO, and it wasn’t that long ago, but it was prior to the year 2000, which was it’s own adventure (and non-adventure).

    In any case, I was a DBA for a company that had a fairly large and active application in use by hundreds of clients. There were a number of issues I had to solve here, but one of the most memorable came when a customer called and said they were getting an error in our application. We tended to hide most errors and return generic ones to the user, so I had a developer get some debug logs and we discovered a severe error from SQL Server that would drop the connection.

    This was annoying since the VB6 app wouldn’t reconnect by itself and the user had to close it and restart. What was interesting was that the user didn’t get this for all activity, just a few items.

    I tested a few queries on dev systems and they seemed to work from isql/w. We tried the application and that worked. That led me down the path of a data issue. I suspected some data might have had strange characters that the app couldn’t handle.

    As we dug into this on production, it seemed our test accounts and various others we tried would work, even for this customer data. Along the way we were running SQL Trace (this was v6.5) and app logging to debug. A few hours in, I stumbled on a few pieces of data for this customer that caused a broken connection.

    The error led me to believe there was corruption in the database and I immediately opened a call to Microsoft while alerting our management. Since we were a company in the financial area, this was a big deal. Fortunately it was near the end of the day and we could take some emergency downtime on the system. Since we traded mutual funds at the time, we weren’t involved in real time activity outside of NY business hours.

    At this point I’d been working on this a few hours, and in talking with Microsoft, we started some diagnostics, including CHECKDB work. We ran this periodically, but if I recall, this was weekly.

    Eventually we discovered that there was corruption in part of one table. With out indexes and a spread out client load, many queries read around the corrupt section, which explained the behavior we saw. Unfortunately, backups wouldn’t help here, nor could we select out the table to get data. As I was handed off around the world to different customer service centers, my apprehension grew.

    I was told that we’d need to move data out of the table and rebuild it, no easy (or quick) task. Since we didn’t know exactly where the corruption was, I was given quite a few queries to slowly work through sections of the clustered index and find what was readable and what wasn’t. Eventually we boxed in the bad sections and moved good data from other areas into a new table.

    When we thought we had it all, we dropped the table and renamed a new one. However, I wasn’t done. While this was going on, I was also restoring a few other backups to try and find out when the corruption started and hopefully recover other data.

    I worked all night, and into the next day. A couple cat naps while some things ran, but I’d been up close to 40 hours by the time I could leave. I’d recovered most data in the corrupt areas from backups, leaving notes for our service people to try and recreate the rest. We could enable our FKs again, though a few dummy records were needed in places where we weren’t sure what the data should read.

    Most customers never knew about the issue and only a few were upset. We replaced a lot of disks and rechecked server hardware, planning on moving to new metal as soon as we could, though the nature of v6.5 made this a challenge in getting disk systems setup correctly.

    That was one of my more memorable days at the office, though not the only overnight session. I kept a pillow and blanket in my desk, sleeping on the floor 4 or 5 times that year. Eventually I moved on, and was glad to do so.

  • T-SQL Tuesday #098–2018 Goals

    tsqltuesdayA great topic for T-SQL Tuesday this time from @sqlmal. It’s Setting Learning Goals for 2018, and it’s a topic I both loathe and love. I hate making commitments that I can’t keep, and I constantly find that a chaotic life will get in the way of learning over time.

    As an example, I tried reading the Powershell in a Month of Lunches book. I managed to get to Day 19. It’s not that I don’t like PoSh or have given up (I still blog on the topic). It’s that the setup was more complex later in the book (really need a domain) and I became busy at work.

    Goals do have a way of focusing a person on a task, and they’re good in that sense. With a year of less travel coming, this was a timely topic for me and forced me to stop and think a bit about the way I want to drive learning in 2018.

    Goals for 2018

    What do I want to learn next year? Here’s a short (ish) list, in no order:

    • CosmosDB
    • Python
    • Pester
    • Extended Events
    • Entity Framework

    I could try to tackle all of these, but that certainly won’t help me do well. This is too large a list to become strong at all of them, though I could spend time improving my skills a little bit in all these areas.

    Of these items, I could relate many of these to my job, which can make it hard to choose. In addition to these items, I’ve got other things that I know will be a part of my learning next year. Product changes, new integrations between our products and others, plus who knows what will come about.

    Picking Two

    If I have to say that I’ll spend 100 hours on something next year and try to get better, I’ll say that the two areas I’ll focus on are Extended Events and Python. While I may dabble in the others, I think that these two areas will be more important to my career as well as my job over time.

    I need a much better understanding of how XE works apart from trace, and I need a lot more comfort in gathering and analyzing the data.

    I also think that Python, with its addition to SQL Server ML and the popularity of the language in new packages and modules means that this is an area that provides some variety, excitement, and a relation to my job. Plus, it’s in SQL Data Generator, so I should have lots more examples there.

    My goals for these two are to spend 50 hours on each over the year, in a combination of course work (articles, videos, etc.) and practical work (building things and trying to use the technology to solve problems).

    Caveats

    There’s always something that comes up, and while I don’t want to start making excuses, I recognize that there are a few things that might derail my plan and make me change.

    First, GDPR. I have no idea how this will impact Redgate and my, but I might need to spend a bunch of time early in the year on this. We are getting calls, questions, and requests from clients, and as some of our new integrations and products come out, this might eat up time.

    Second, travel. I haven’t planned on much, but I’ve really only set the first 4 months of the year. After that, we’ll see. Who knows what might come up or what demands arise.

    Third, life at home. I never know what will happen here and how things might change, so that could change how I focus. Plus, I have a sabbatical coming, so we’ll see how that affects me.

    Getting Ready

    I’m doing a few things right now. First, I’m setting a monthly reminder to update my goal progress. I’ll try to write a post each month, for better or worse, on where I am.

    2017-12-06 17_22_15-Appointment Recurrence

    Second, I’m setting an appointment for myself to sit down and plan some things out the last week of December. That’s a good time to spend an hour or two and try to come up with a plan. It won’t be perfect, but it will give me a direction on which to start. I can amend this during my monthly review if needed.

    That’s it, thanks to Mala for a great topic, and we’ll see how it goes.

  • In a basement, a long, long time ago… #tsql2sday

    It’s T-SQL Tuesday time and this is a good one. Read the invitation and proceed.

    Who Influenced Me?

    I’ve been working SQL Server for a long time. Since 1991, in fact, and had held 4 jobs as a DBA or developer by 1999. I was comfortable with the platform, and had actually published some articles in early 1999 for a small UK journal. My career was going well and I felt very comfortable with the platform (v6.5 in my company) at that time.

    As a developer, I knew T-SQL well, and was still learning things from reading articles, but there weren’t many SQL problems I couldn’t come up with some solution for. As a DBA, I knew the tricks to keeping a large, busy instance running, including the DR tricks to ensure my restores worked well on new hardware.

    I was lucky enough to get my company to fund a trip to the initial PASS Summit. I went, along with my wife and infant son, to visit Chicago. We went to Comiskey Park on the last ,day of the baseball season, braving chilly weather along with about 1,500 people to watch the White Sox. I’m not sure my wife has been to a game since…

    In any case, my main memory of that event was being the basement of some hotel in downtown Chicago. There were a few rooms in use, and we packed into see sessions on various topics. One was delivered by Kalen Delaney, @sqlqueen, who was perhaps the person I saw as an amazing speaker and teacher. I’d read her Inside SQL Server 7.0 book, anticipating an upgrade. I was delighted to see her speak, and perhaps more thrilled to shake her hand and get an answer to a question.

    That was quite a highlight in my career.

    Since then I’ve seen Kalen speak many time, and we’ve become friends. I’m always glad to get a hug and a few words with her, but each time, I remember that early, semi-awestruck time when I met her.

    She’s inspired me, both as a data professional and a teacher.

    If you ever get the chance to meet Kalen, take a moment or two with her. You won’t regret making the effort to introduce yourself and ask a question. I know I haven’t.

  • Automate Migrations–T-SQL Tuesday #94

    tsqltuesdayIt’s T-SQL Tuesday time again, and I’m slightly pressed for time, which is sad. This is a great topic.

    Rob Sewell hosts this month with his question about what we are going to automate? He’s a PowerShell advocate, so I’m not surprised here. As much as I enjoy working in various languages, PoSh becomes more and more handy to me when I need to work outside of the SQL Server platform. I’ve been trying to play with it, and I enjoy it more and more.

    If you want to participate, check out the rules:

    1. Write a post on the topic below
    2. Schedule the post to go live on Tuesday, September 12th (between zero am and midnight, UTC)
    3. Include the TSQL Tuesday logo in the top of your post
    4. Link the post back to this one (it’s easier if you comment on this post and link it)
    5. Optional: Tweet a link to your post using the #tsql2sday hash tag on Twitter

    Automate Things Between Instances

    The first time I saw the dbatools project in action was at SQL Saturday Cambridge, where Chrissy Lemaire gave a session with Rob. I was surprised at the power and ease of the project. I was impressed, and decided to help learn more as well as promote the project. I’ve tried to blog regularly about their cmdlets as I get a chance to play with them and I’m pretty much always impressed.

    The next time you need to move some object, setting, job, etc. from one instance to another, you should try this:

    1. install the dbatools module
    2. look through the command index
    3. try migrating your object(s) with PoSh.

    That’s it.

    Maybe you need to copy a database or login. Maybe you want to copy jobs to a new server. The dbatools module makes all of these things easy.

    So give it a try. There are some great tools for migrations as well as wonderful items for common DBA tasks.