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.


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.

SQL Scripts Manager

We have Down Tools Week multiple times per year at Red Gate. This allows various employees to spend a week on a project, working to build something new and interesting that we rarely have time to spend our limited resources on. Past projects have resulted in SQL Search, a mobile view of Simple Talk, and a number of internal tools that have proven very beneficial.

Last year one of our developers embarked on a project for SQLServerCentral to provide a plug-in for Management Studio to allow access to the scripts on our site. This wasn’t quite complete enough to release, but when Down Tools week occurred recently, a few developers collaborated to complete this project.

We’re releasing this Management Studio plug-in today, SQL Scripts Manager, along with our framework to build your own plugins.  You can download the tool from this link, and a link will also be placed at the top of our Scripts section at SQLServerCentral. Once you have this plugin installed, you can search for and download scripts inside SSMS, and access your briefcase without ever switching applications.

I think this is really amazing, and I’m hoping that many of you find it useful. It’s a great way to easily access your collection of scripts from anywhere, getting the latest versions of the code from SQLServerCentral that you find really useful.

If you have comments, suggestions, bugs, etc., please feel free to post them in this thread.

We’ve worked hard at Red Gate to not only build useful tools that help you build software, but also teach you how to better create your own applications. We’ll have more information and more details on the framework coming soon as well, and we look forward to seeing what types of enhancements you’ll create for SSMS. If you create something really useful, send us a note and we’ll feature your tool on the site.

2013 Tribal Awards

We’re the #sqlfamily on Twitter, and often in person where we often get together for a #sqlrun in the morning and a bit of #sqlkaraoke at night. In some sense, we are a tribe within the larger technology community, focused on our common work with SQL Server. I thought the title for  was fantastic, bringing together new authors to share their knowledge, and raise money for a charity.

At SQLServerCentral and Simple Talk, with the sponsorship of Red Gate Software, we decided to create some end of year awards in various categories, based on the community, not companies or products. We kick off our nominations today in a number of categories, including Advice that saved my bacon in 2013, Best Speaker, Best Outfit, and more. You can read more about the categories in today’s announcement.

The idea is that the community can recognize others in the community with a nomination, and some supporting material that encourages others to nominate the same individual, and vote for the winner. The top five with the most nominations will get voted on over the holidays with awards presented in January.

I hope you have fun with this, and think back to those people and events that stand out in your mind in 2013.  Nominate them, give a few reasons, and celebrate the help that each of us gives each other as a part of the SQL Server tribe.

PS: we had a great #sqlski last year for SQL Saturday in Albuquerque and we’re doing it again this year. Feel free to join us at Taos on Friday, January 24th, right before SQL Saturday #271 in Albuquerque on Jan 25, 2014.

Steve Jones

Video and Audio versions

Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

Follow Steve Jones on Twitter to find links and database related items and announcements.
Steve Jones Windows Media Video ( 15.2MB) feedMP4 iPod Video ( 18.5MB) feed

MP3 Audio ( 3.7MB) feed

Feeds are available at iTunes and Mevio

To submit an article, rant or editorial,
log in to the Contribution Center

Filestream needs MAX

I wrote today’s Question of the Day for SQLServerCentral on Filestream. I asked which data type was needed for Filestream data, and gave a few choices. Someone pointed out that I wasn’t complete in my choices, and that was by design.

Filestream is an attribute placed on a column, not a datatype. Why it couldn’t be a datatype, I’m not sure, but that would have saved some confusion. In any case, there’s only one data type that can take the Filestream attribute, and that’s varbinary(max).  Not varchar(max), not varchar, and not variant. There isn’t, of course, a FILESTREAM type.

However I didn’t want to include MAX since I thought it might sway people towards varbinary, and I was really hoping to teach people, not have them guess. I supposed I could have included MAX with all types, but to me, the (MAX) is also an attribute for the type.

I think it’s a good question, but if you disagree, let me know. Writing better questions is something I want to do to help the community learn.

The Colocated Dangers

Titanic Poster
It’s not “if”, but “when” you will have a disaster.

We went down.

It was quite a surprise for me, and something that hasn’t happened in a long time. It wasn’t for long, but a month ago, there was a period of time when SQLServerCentral was down. It’s also one of the very few times it’s happened since I was in charge of the servers. The exact reason was something to do with out hardware, and it was fixed relatively quickly by our hosting provider.

Early on in the site’s history we had a few hiccups. I was the one that drove out to a co-location facility to replace hardware or fix something that was broken. There were a few times where all that was required was a reboot, but we didn’t have the “remote hands” service available at a few of our hosting facilities. Fortunately for me I had an understanding boss that would allow me to flex and extend my lunch time during these emergencies.

Since Red Gate purchased SQLServerCentral, we’ve had excellent uptime. A few outages for upgrades of the site, a minor DNS issue, and a few hiccups from maintenance by our hosting facility, but all very short. This hour-or-so long outage was the first unplanned outage in years. However it reminded me of a somethings: it’s not if a disaster will occur, but when it will occur?

Putting all your data (and the servers it lives on) in one place is the fact of life that most of us have to live with, but we ought to have some idea of a DR plan if our data center goes down, or our hosting company has an issue. It might not be a thoroughly documented plan, but it ought to be a thoroughly thought out one.

At least if expect your enterprise to continue to function during the disaster and your employment to continue after.

Steve Jones

The Voice of the DBA Podcasts

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

Closing Out Replies

This editorial was originally published on Oct 17, 2006. It is being republished as Steve is at SQLBits 9 today.

I’ve been posting on the Internet for nearly 17 years, from back in the days of Usenet, GOPHER, ARCHIE and FTP when everything was text. I’ve asked questions and answered them on various topics and as the World Wide Web grew, I grew with it, moving to BrainBench and various other sites where I could gett and provide answers. Recently some one sent me this definition for Warnock’s Dilemma, which was timely considering the today’s article on posting manners, which I received about the same time. I thought it was interesting wanted to bring it to people’s attention.

Early on when I’d post a question, I got an answer, but then I wondered if it was good information. I could usually test it myself, but since I wasn’t sure of how to solve it on my own (hence the post), I wondered if there was a better way to solve it. Ideally I’d get 2-3 responses, maybe even some improving or commenting on others, to help me sort things out.

If I didn’t get a response, then I wondered if I hadn’t phrased things correctly, or no one else knew, or even that no one bothered to read my post. All of these are valid interpretations and the new definition from Wikipedia pointing out that a lack of response doesn’t necessarily mean anything. There are 5 possible interpretations of this.

When we started SQLServerCentral.com, our goal was to answer every single post. The three of us (and three others at the time) would research questions and make it a point to answer in some way, some type of post. As we grew, we encountered areas, such as MDX, where none of us had any expertise and couldn’t even understand the research sometimes, much less the question. To facilitate getting responses, you have probably noticed that we add an automatic post to some threads if they haven’t seen a response in a day or two. This is to bring them back to the top of the list and hopefully someone else’s attention.

The other thing we debated on with answered posts was whether one answer was sufficient. Often one of us would come on a thread that someone else had answered and we liked the solution, but there was no closing “thanks” or “that worked”. So we decided that adding another post that agrees with the solution, a “me too” post, was a good idea. That way we hoped that we’d build some confidence in the solutions we believed in and give the newbies out there a reason to follow the advice.

I’m really not sure what the best way to handle posts on the Internet. We’ve been asked to allow people to “rate” solutions as well as “close” threads. We’ve resisted because we see various issues with all of those solutions, just as with the thread within a thread capabilities of many forums. We’ve opted for a simpler solution that we think works well and we hope you agree.

Know when to hold ’em

The SQLServerCentral party is always a great time

It’s time for the annual SQLServerCental party at the PASS conference in Seattle again. It’s become an event that’s a lot of fun, and a great way for the SQL Server community to have some fun and get to know each other in a relaxed atmosphere. This year we’ve once again combined the party with the Exceptional DBA awards, and I’m honored to be there to celebrate noted SQLServerCentral author Jeff Moden as the winner.

We have once again contracted for a casino themed party with various game tables around. We’ll call them the card game where you try to get twenty one, the competitive card game with a flop, river and turn, the roll-the-ball-around-the-circle-and-guess-the-number game, and probably that dice game as well. Have to be careful with the spam filters these days. ;)

This year we’re selling tickets in advance for $20, or you can bring $30 to the door. We’ll have lots of prizes again, with me going through Best Buy and Think Geek to get a large variety of things to give away. I am happy to take suggestions for those items that you think would be fun gifts to take home.

No skill is needed at the games as we’ll give prizes away randomly to people playing at the tables. Even if you have no idea what you’re doing, stick a few chips down,  introduce yourself to the player next to you and get some advice. You might learn something, and more importantly, you might make a friend.

Come join us on Tuesday night for a great time. More details are in the party announcement.

Steve Jones

The Voice of the DBA Podcasts

The Exceptional DBA for 2011

Once again we’re running a contest to find the Exceptional DBA around the globe. I’ve been a judge the last three years, and it’s been a hard choice trying to pick from some great applications. This year the judges are Brad McGehee, Brent Ozar, Rodney Landrum and me.

Nominations are now open, and you can nominate yourself, or a colleague for the change to be named the Exceptional DBA of 2011.

Best of luck to everyone, and a few hints.

  • More writing is better – We don’t want to read a chapter from War and Peace, but take a few paragraphs to describe your environment with some details.
  • Be specific on the highlights. – Let us know what you did, why it’s important or impressive to your organization.
  • Be well rounded – We are looking for people that do more than their job. They teach others, they are proactive, and they make the DBA profession better for everyone.
  • Proofread – Have a friend or even your boss review the nomination form.

The nominations are open until June 30, so take a week or so and think about how best to present yourself.

SQL Source Control Webinar

The 8th SQLServerCentral webinar, featuring SQL Source Control, is coming in a couple weeks. I’ll be hosting again, and this time my good friend, Andy Leonard, will talk about lifecycle management and source control as a part of that.

You can register here.

I believe in source control, mostly as a way of organizing the work of many different people and providing some insurance. It’s not often that I’ve rolled things back, mostly because I’m loathe to check things in that don’t work, but I have had to track down changes from older versions when a deployment didn’t go well, or someone quit and we weren’t sure what they had changed in code.

SQL Compare would help somewhat here, but it has been more convenient for me to use Source Control systems, primarily Visual SourceSafe in the past.

I haven’t seen this presentation from Andy, but I like hearing him talk, and I’ve been impressed with other talks he’s given in the past.

Don’t forget to register here for the webinar on Mar 24, 2011. It’s at 5pm GMT, which is a nice, not-too-early 10:00am MST for me.

iPads for Christmas

Recently I mentioned that having a smartphone has made me much more productive. I’m on the move fairly often, and the ability to respond to emails and keep in touch with people, take notes, and check on SQLServerCentral from almost anywhere in the world has become very valuable to me. It certainly is a double edged sword as it is also hard to get away from work. Setting those boundaries is important, and smartphone or not, I would encourage you to make sure you have some boundaries with your employer.

The iPad is a new device that changes the game in terms of what we can do with mobile devices. It handles remote connectivity in a way unlike smartphones, but is less cumbersome than a laptop and works in many situations where you might not want to carry, or power up, a laptop.

Back in October, I got an email from my employer, Red Gate Software. Wibke, in the Marketing department, asked for people with iPads to take pictures of themselves using the new SQL Monitor software to remotely monitor servers. It seemed fun to my wife, who got me on a horse for the picture you see here.

It was contrived, after all, I couldn’t really check on the SQLServerCentral servers from a horse. The ranch is too large, and my wireless signal won’t reach out to the far side of the barn. That doesn’t mean I wouldn’t monitor my servers from outside if I had wireless connectivity. You, however, can see how the SQLServerCentral database cluster is running by connecting to the remote demo instance of SQL Monitor and see in real-time how our servers are performing.

The idea of using an iPad to monitor your servers, and even get work done, however, isn’t contrived. I used my iPad to show off SQL Monitor at SQL Saturday #59in New York City, and I have a friend that manages many of his Windows servers using iTap, a remote RDP program for the iPad. At SQL Connections this year, I was surprised to see quite a few people checking their email in Outlook using RDP technology from an iPad. I asked a few people what they thought and while it was more limited than using a laptop, most of these geeks thought the iPad was more convenient in a remote environment.

But I Don’t Have an iPad?

Not many people have iPads, and most of my geek friends find them to be an expensive toy. I mostly agree, but I have found you can definitely use these devices for work. So I’m going to give you a chance to have your own iPad and let me know if you agree.

Red Gate has decided to sponsor three contests, all of them related to SQL Monitor, and all designed to get you an iPad in time for Christmas. I have 10 iPads to give away, along with 10 licenses of SQL Monitor to install on a server and let you begin using your iPad for work right away. That’s decimal ten, not binary 10, so there will be quite a few winners.

Actually there will be 30 winners, since Brent Ozar and Grant Fritchey are running their own SQL Monitor/iPad contests and each giving away ten bundles of their own. You can read about Brent’s contest on BrentOzar.com, and Grant’s contest at ScaryDBA.com. Brent is asking where you might go and use an iPad for monitoring while Grant is asking about how this might help you find a better work/life balance.

My contest is asking a little different question. I’d like you to think a little bit about how you could create new synergies with the iPad. How could you do your job better, a little quicker, or in a new way if you had a small, lightweight tablet with a large screen that gives you the connectivity to reach other computing resources. I’m looking for some creativity and a thoughtful way of freeing yourself from a desk and at the same time bringing some additional value to your employer.

Think about it a bit and write me an interesting paragraph in the discussion for this editorial. I’ll judge the entries submitted before Friday , Dec 17, 2010 and ship off some iPads to the ten best over the weekend.

Good luck and send me an interesting note. You can only enter one of the three contests, so think about which one make the most sense for you:

The fine print rules:

  1. The contest is open to professionals with SQL Server monitoring responsibility. Entrants must be 18 years old or over.
  2. Entries must be received by Friday, December 17, 2010. The contest organizers accept no responsibility for corrupted or delayed entries.
  3. Employees of Red Gate, the contest organizers and their family members are not eligible to participate in the contest.
  4. Entries are limited to one per person across the three simultaneous contests hosted on www.sqlservercentral.com,www.brentozar.com, and www.scarydba.com.
  5. The organizers reserve the right, within their sole discretion, to disqualify nominations.
  6. The organizers’ decisions are final.
  7. Red Gate Software and those involved in the organization, promotion, and operation of the contest and in the awarding of prizes explicitly make no representations or warranties whatsoever as to the quality, suitability, merchantability, or fitness for a particular purpose of the prizes awarded and they hereby disclaim all liability for any loss or damage of any kind, including personal injury, suffered while participating in the contest or utilizing any prizes awarded.

Important dates

  1. Deadline for entries: Friday, December 17, 2010.
  2. Winner announced in week commencing Monday, December 20, 2010.

Good Luck to you all and leave me a comment in the discussion for your chance to win. (hint, I talk a touch more in the podcast for what I’m looking for)

Steve Jones