Voice of the DBA

Category: Blog

  • A SQLServerCentral DR Event

    We had a disaster at SQLServerCentral this past weekend. It wasn’t a big disaster, but it was an event that required a restore of data.

    An Administrative Error

    On Saturday, I was attending SQL Saturday #331 in Denver. In between my sessions, I was prepping a few demos and finished getting ready earlier than expected. Since I had a few minutes, I checked my email and immediately knew we had an issue on the site.

    We’ve been fighting spam for months, slowly tweaking our posting system in the forums. With the start of the American football season, we’ve been getting hundreds of posts every Friday and Saturday. I’ve tried to ensure these posts are removed before our newsletters are generated so that they aren’t filled with advertisements, but it’s been a chore.

    One of the things I can do in the forums is select a series of posts and mass delete (or open, close, hide, etc) the group. For most of the SPAM posts we receive, the posts all occur in the same few minutes and are grouped together. I’ve gotten in the habit of deleting these batches of posts, watching for a legitimate SQL Server post at the end.

    However on this day, one of the our regular threads was buried in the middle of all the SPAM posts. This was THE Thread, the most active and long lived discussion on the site with 45k posts. I inadvertently deleted the post and went on to give my presentation.

    Afterwards, I got a private message from the site, telling me the discussion had been deleted around 2:00pm MST.

    Quick Reaction

    The first thing I did when realizing what had happened was connect to our production database cluster through VPN. When I opened Management Studio, I ran a few queries to verify the discussion had been deleted, and not just “marked for deletion”. Logical deletes exist in many applications, and if this is the case in your own disaster, the last thing you want to do is initiate a database restore.

    In this case, the data was gone, so I immediately tried to initiate a restore. Since over an hour had passed, I didn’t want to restore over top of the current database. Instead, I wanted to restore a copy as a new database, as of 1:45pm or so.

    I selected the proper options, marking the full backup from overnight and the log backups throughout the day. I didn’t have time to worry about using STOPAT and trying to get close to the actual time of data modification, so I choose the last backup I knew would be good. Verifying the database name was a new name, I clicked OK.

    And nothing happened.

    Actually, that’s not true as I got an error. The backup system in use by Rackspace, our provider, doesn’t keep the files available from SSMS. I don’t have rights to work within the restore system, or even request one from Rackspace, so I opened a ticket with Red Gates support for a restore.

    Had this been a situation where the site was down or users were unable to read articles or post, I would have escalated this for immediate action. However since this was a restore of a single thread, and one that exists for entertainment more than education, I chose not to bother our IT staff on a Saturday night or Sunday.

    The Fix

    When I woke up Monday morning, I had a message that the restore had been completed to my specified new database (SSCForumsOld) as of 1:30pm MST on Saturday. I hadn’t asked anyone to do more than this, so this was the extent of actions taken by Red Gate.

    Again, I could have specified actions in more detail, but rather than try to explain to someone in email which thread, and which posts needed to be restored, I decided to handle this myself. After taking my children to school, I sat down and got to work.

    I’ve known the PK of this particular thread since I’ve had to work with in in the past. Connecting from Management Studio to the production instance, I verified I could see the 45k messages in the SSCForumsOld database. I ran the same query on the SQLServerCentralForums database, and validated the data was still missing. I then built a query that would perform an INSERT..SELECT of the parent posts from the restored database to the production system. This took longer than expected, with the table having a number of locks for about 2 minutes. However the post details had been moved.

    That left me with the need to move the actual words of each post, which are stored in a separate table (for some strange reason). Rather than lock up the forums for minutes, I spent time rewriting my next insert to use batches of 1000, and only insert those messages which had not already been moved. Since I could join on PKs, this went quickly, in a few seconds. I next changed my batch size to 5000, and this completed in about 15s.

    That seemed like enough time to run quickly, but also a good batch of data and manually executed this 9 times to move all the data. A quick check on the site showed THE Thread was back and I posted a few notes to let users know.

    Aftermath

    There are still a few issues with points for users that have posted to this topic being incorrect, but that is a lower priority item and I am letting our developers look at it. There is at least one known bug with points, and it’s possible we have another here.

    My personal lesson learned was that I need to move a little more slowly when removing SPAM. I also don’t want to trust myself to do it regularly, so I also spent part of Sunday morning writing a little code and scheduling a job to delete posts with certain patterns of titles that the spammers use. I tried to limit to those obvious subjects so that no legitimate posts are removed.

    We are also escalating some of the issues with SPAM, and with the return of my manager from sabbatical, I’m hoping we can build a few more filters to limit the disruptions in the forums.

    And of course, everyone that posts to THE Thread has my apologies for the mistake.

  • The Mentoring Experiment – Closing Thoughts

    Andy wrote a post today called The End of the Mentoring Experiment, which is something we’ve talked about for some time. In fitting with the decision, and perhaps justifying it further, we decided to do this a few months back, but as with many things, we haven’t gotten to it until today.

    I remember when we started this, and we were very excited and interested. The first cycle of matches, using 8 people we hand picked, consumed a lot of time, and while it was successful, it was difficult to scale. We continued on a couple other cycles, trying to tweak to process a bit, but never found a way to manage this effectively, given other parts of our lives, and were never quite comfortable with automating too much of the experiment away.

    We also struggled with the idea of accidently making some mistake, breaking someone’s privacy, or causing harm to another’s career or life. Andy wrote a few things about this, and it made sense to us.

    We do believe in mentoring, and I hope that those of you looking to grow your careers take time to look for mentors. If you need advice or want help, ask. There are lots of people that will probably give you a little time. It’s not a sign of weakness to need or want a mentor. It’s a sign of maturity.

    For those of you with some experience, keep an eye out for someone that might ask for help, or maybe just seem to be lost. Tread lightly, and carefully, but offer to be a sounding board if they’d like one. You can really make a difference in someone’s career with a little effort.

  • Grant for President

    Grant Fritchey is running for the PASS board. He’s not running for President, though I’m not sure why he shouldn’t be able to. The board makes decisions as a group and the President isn’t necessarily more or less powerful than other members. However the President can be the face of PASS and present an image that motivates others. Grant would be great here, and I’d like to see the President elected directly at some point in the future, without the nonsensical requirements they serve for multiple terms before then.

    However that’s a separate discussion. In the next week, the election will take place and I’m voting for Grant. I’ve known him a long time, I’ve watched him work with the community, and I think he might be able to create some change in the organization.

    For far too long, I think PASS has been stuck in a bureaucracy that acts out of fear rather than leadership, and I’d like to see that change. I wouldn’t blame any past or present board members as I think many of them are fine community members, but I think the organization has systemic problems that create issues. I’ll cease ranting about that, and no, I’m not going to attempt change myself. I did once, but I have neither the time nor inclination to fight those battles right now.

    I’m voting for Grant. You can make your own decisions, but I am choosing Grant because he’s can create change. I’ll also vote for JRJ for the same reason. I think James has worked to create, and implement change, and I’d like to see him do more, especially in non-US areas.

    I’m not sure who else I’d vote for, though I like both Wendy and Sri. I’m not sure what they’ve done, and I’d certainly like to get more information. That’s one of my complaints from all board members, is that they disclose too little information to the community.

    However you vote, please take a minute and vote. We have an amazing community, and the more you participate, the better it gets.

    Disclosure: Both Grant and I work for Red Gate Software, so take that for what it means to you. We’re also on the DBA Team together 😉

  • Get Testing with tSQLt

    tSQLt is a testing framework that is designed to help you write repeatable, isolated tests against your database code. In this session we will briefly examine the goals of testing, where it can be used in an automated deployment pipeline, and introduce you to the tSQLt framework. We’ll show you how to design and write tests to enforce standards, check calculations and other DML operations as well as check exception handling. You will see how a real world refactoring task can be tested in a repeatable manner. Learn how you can incrementally write tests that help you build higher quality code and minimize the introduction of errors against your existing objects.

    We will examine a variety of tests, including

    • Checking table metadata
    • Checking function calculation
    • Isolating a procedure test from an embedded function
    • Checking for error handling

    PowerPoint slides:

    Code: GetTestingtSQLt.zip

    Presentations: