Voice of the DBA

Tag: SQLServerCentral

  • Should We Move to Azure?

    This editorial was originally published on May 23, 2014. It is being re-published as Steve is out of the office, with a few minor editorial changes.

    Update: We made a number of changes last year, upgraded the forums, and have continued to implement bug fixes. We are still considering this idea.

    At SQLServerCentral we have been debating some architectural changes to the site. We’d like to improve the way a few of our features work, add new ones, and replace a few items that are buggy and hard to maintain. The site has worked well over the years, but we’ve made relatively few improvements, mainly due to the fact that like many companies, this website isn’t our business, and work has a lower priority than some other areas in the company.

    Recently I had a suggestion presented to me and I wasn’t sure how I felt. There were certainly pros and cons to both taking advantage of the opportunity as well as passing it up. While we are still talking about the process, I wanted to ask the community what they might think this week.

    Should we move SQLServerCentral to Azure?

    I really like the idea of PaaS. There are issues, and certainly I think Microsoft could do a better job of creating competition as well as securing and even deploying their platform. However I think the idea of just consuming services as opposed to installing and managing pieces of software, is a great direction in which to move. SQLServerCentral would seem to be a perfect type of application that fits in the cloud.

    We run a generic ASP.NET site, using fairly common and simple database features in SQL Server. We don’t store or manage much in the way of PII, financial, or medical data outside of email addresses, and we don’t have the need to scale to high levels, either in data storage or computational resources. The one problematic area is our email requirements. I’m not sure what the cost is to send emails from Azure, but we send millions per week, and CAN-SPAM requirements can be tricky to meet in the cloud.

    However I’m just wondering what the community thinks. If we successfully moved to the cloud, and disclosed details about the process, would that give you more confidence in the cloud? Would it be interesting? Or is SQLServerCentral just a service and you don’t care how it works, as long as it does. Let us know this week.

    Steve Jones

  • Changes are Coming to the SQLServerCentral Forums

    The codebase for SQLServerCentral is old. Not as old as it looks, since our developers tried to keep the look and feel of the original site when they upgraded the underlying framework in 2009. Since then we’ve made a few changes, but not a lot of development. Like many of you working in organizations, I struggle for development resources when there are higher priority projects inside the company. I think I’m a bit of a victim of my own success here as the site has remained popular despite the aging design.

    Around September 2016, we did get more resources and a project approved to make changes. The first of which is an upgrade to our forum software. We’ve been running an old version of InstantForum, which was customized to integrate into the rest of the site. After evaluating quite a few options, we decided the newest version of this software was the best fit and have had developers working on integration as well as sending feedback to the vendor about some bugs we’ve discovered (and fixed). They’ve taken some pull requests from us, and since we’ve abstracted some of our customizations away from their codebase, we should be able to take upgrades and improvements over time.

    This is a large change, with a database upgrade, data movement, and large deployment of changes. After many practice runs and a beta test period with a number of users, we’re ready to deploy the new software. Our plan is to deploy the changes on the early morning (UK Time) of January 12th, and the site will have a short outage during this time.

    While much of the appearance of the main site will be similar, the underlying forum code and styling has been revamped with some new features and integration with SPAM detection engines. Hopefully that will cut down on some of the annoying posts we’ve seen across the last year. My fingers are crossed for that this happens.

    This should be a smooth upgrade, and I have confidence in the technology team that’s been working on this project. At the core, however, the mission of SQLServerCentral is to educate you, and that doesn’t depend on technology. We seek to publish information that helps you learn more about SQL Server and allow to ask questions and get answers to the problems you face in your work. When we come back up on the 12th Jan, we’ll get back to the business of helping you get better at your job.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Slack Integration

    About a year ago, my group at Redgate Software adopted Slack as a way of communicating with each other. Slack has become very popular with development teams, and is used extensively in many companies, including my own. At first I liked this tool, which allows some real time communications, but also some tagging and notifications if someone messages you. However, over time, I find it less useful. Perhaps the seven hour time difference accounts for some of that.

    However plenty of people have found various ways to integrate Slack into their development process. Everything from build failures to successful deployments to mentions on Twitter. I’m not sure of the value of these myself, but I can understand that grabbing messages and centralizing them in a channel can provide value. Personally I still prefer to work through email, which gives me a nice, asynchronous, disconnected, comfortable tool.

    The other day someone asked me if we would think about integrating parts of SQLServerCentral with Slack. I wasn’t sure there was value, but then I thought, perhaps some people would like a channel with interesting or hot threads in the forums, or mentions of articles. Certainly many people like different mediums of communication, so why not Slack?

    It wouldn’t be for me, but I do wonder how many of you are using Slack and would want to to add in a new channel. Or a few channels. Maybe you’d like one with the Question of the Day and interesting notes from the discussion. Since Slack can work across companies and accounts, we certainly could open things up to you.

    Let us know today what you think of Slack and would you like any integration with the tool. How would you use it?

    Steve Jones

    The Voice of the DBA Podcast

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

  • 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.