Oct 312012
 

SQL Saturday 172This is going to be a crazy week!

I’m very happy to be speaking at SQL Saturday #172 in Portland, Oregon on November 3, 2012. I’ll be giving my presentation entitled The Skinny on Data Compression which covers the different types of data compression available in SQL Server and some of the details behind how they work. This session will also cover the pros and cons of compressing data and how to determine which flavor of compression is most appropriate for an object. This presentation was very popular in both Kansas City and Madison, so I’m expecting more of the same in the Pacific Northwest!

Registration is still open as of when this post went live so if you’d like to attend please sign up here. If you’re on twitter, follow the #sqlsat172 hashtag for the latest information.

But that’s not all…

Folks in Oregon had the good sense to plan their SQL Saturday for the weekend before the PASS Summit, so out-of-towners can stop in Portland before heading to Seattle. It sounded like a great idea to me, so that’s exactly what I’m doing.

After SQL Saturday, I’m heading to Seattle for my first-ever PASS Summit! That’s right folks, I really am a first-timer. Several people have told me they can’t believe I haven’t been to Summit before, but it’s the truth. I’m very grateful to have an employer that supports my attending such a conference. I’m not quite sure what to expect, and I’m already overwhelmed by the choices of what there is to do. Fortunately the twitterverse has been incredibly supportive so I’m sure it will be a most excellent time!

Oct 182012
 

Back at the first Chicago Free-Con event in 2011, Brent Ozar was talking about how wonderful the SQL Saturday movement is and prophesized that in the near future we’ll be seeing similar free training events put on by individual vendors instead of community volunteers. Eighteen months later I found myself at Red Gate’s SQL in the City, a multi-city tour which fit very well into Brent’s prediction. The Chicago event took place on Friday, October 5.

SQL in the CityThe Event

The tagline for SQL in the City was “Free SQL Server Training the Red Gate way.” I’m happy to say that the Red Gate way was a great improvement over what I’m used to. I’ve been to plenty of dog and pony shows put on by vendors where they demonstrate how you can create an entire website, database, and private cloud with zero keystrokes and only 4 mouse clicks. You are then sent home, usually with more questions than you came with. Here there was plenty of time for meaningful questions and conversation, which made a significant difference.

As with many events, the day was divided into sessions and tracks covering a wide variety of SQL Server topics. Many sessions centered around Red Gate tools and problems that could be solved by using them. I thought all the presentations I attended were very fair in that they discussed not only how Red Gate tools could help in certain situations, but also covered cases where they might not be as helpful and you may be better suited by utilizing native SQL Server functionality. I really appreciate it when a vendor isn’t ashamed to talk about the limitations or tradeoffs associated with using their tools.

I was also very impressed with the number of UK-based staff that Red Gate brought on this tour. There were developers, project managers and experts for all their products available to answer questions and do demos. Several of these “Red Gaters” were presenters throughout the day. Some local experts were also invited to present, including Allen White (blog | @SQLRunr), Jes Schultz Borland (blog | @grrl_geek), and Brent Ozar (blog | @BrentO).

Community

Red Gate is a supporter of the SQL Server community in many ways, including sponsorship of SQL Saturdays, sending swag to user groups, and running SQLServerCentral.com. And while putting on events like these is an excellent way to bring people together to learn from presenters and each other, they went one step further and included breaks in the schedule for even more socializing. Attendees were encouraged to grab a snack, a drink, and network amongst each other and the Red Gaters. At the end of the day there was a reception with more drinks, snacks, and prize drawings. Everyone also got a goodie bag and their choice of a book from Red Gate’s published collection – there were tons to choose from!

Prettier than the average auditorium

Venue

Like most people who have lived near Chicago their entire life, I rarely visit any of the tourist attractions in the city. I was happy that the Field Museum was chosen as the venue since I’ve only been there a handful of times, mostly on school field trips long ago. I was surprised to find that the museum had a beautiful auditorium and smaller lecture halls available for hosting an event like this. There was plenty of space for everyone as well as award-winning restrooms.

Thanks

A huge thank you to Red Gate for taking the time to put on this excellent event for the community. To plan this tour, give away lots of free stuff, and bring their employees over from Cambridge is clearly a costly undertaking, but it’s also a tremendous demonstration of Red Gate’s commitment to community. I got a ton of knowledge out of it and learned a bunch more about their products, including some I didn’t know existed. I was able to meet lots of new people and see some familiar faces as well.

I’d also like to thank Annabel Bradford, event manager for the SQL in the City tour. While all the Red Gaters were clearly working very hard to ensure this was a success, it was evident that Annabel was in charge as she spent most of the day being in multiple places at once and keeping everything running smoothly. Well done!

I’d also like to thank my employer for allowing me to attend. I talked to a disturbingly high number of people who had to take personal or vacation time so they could come. One attendee told me management wrote it off as “just another vendor event”, while someone else said their boss told them “if you want free training, pick a book off my shelf”. I also spoke with some people who were unable to attend because work decided they’ve been to “too much training” this year. I find that incredibly upsetting – if someone has no meetings to attend and is otherwise caught up with their work I see no reason why they shouldn’t be able to go attend a free local event that will probably show them at least one way they can get their job done better/cheaper/faster. I’m very grateful to work in an environment where training is supported and encouraged.

It’s Not Over Yet

If SQL in the City sounds like something you’d like to attend, there’s still 1 more event left in Seattle on November 5, right before the PASS Summit. As of this post going live, registration is still open. Sign up – you’ll have a great time!

Oct 092012
 

T-SQL Tuesday!This year, time seems to be flying by faster than ever. It’s October already, and we’re up to our 35th installment of T-SQL Tuesday. This time we’re hosted by Nick Haslam (blog | @nhaslam). Nick’s asking “what is your most horrifying discovery from your work with SQL Server?”

The story that scares me the most is one I’ve actually told a few times lately. It’s all about the condition of database backups at my current job right after I started there. As soon as I found out what was going on, I knew resolving this issue would be my top priority.

When I started at my current job, our main production database was 22TB in size. I was of course aware of this going in, and had done plenty of reading about best practices for dealing with very large databases. A commonly covered topic is backups: there are many articles out there mentioning how native SQL Server backups aren’t always the best choice when dealing with VLDBs and that SAN snapshots may be more appropriate in many environments. While a native backup will read the entire database and write its contents out to a backup file and can take many hours, a SAN snapshot will only take a few seconds to create a read-only image of all database files at a particular point in time. This snapshot can then be mounted on a different server and backed up from there so that the main server doesn’t incur any of the processing or I/O load associated with the backup.

While going over all of our systems to get a general lay of the land, I found that backups of the production database were being created not with snapshots but clones instead. For those who might not be familiar, a snapshot is basically a “virtual” copy of the data that can be created in seconds and consume very little disk space (generally only changes to data since the snapshot was created are actually written to disk) while a clone is a full copy of the data that can take a very long time depending on the amount of data being copied. Clones aren’t always bad, and in some cases can be an excellent solution to a problem. In this case, however, they were a disaster waiting to happen because the clone was taking about 5 days to complete. On top of that, there was only enough disk space to store one clone at a time. So the backup process worked pretty much like this:

Sunday – Start creating clone
Thursday – Clone is finished
Sunday – Delete previous clone and start creating new one

So there was a chance of everything being restorable on Fridays and Saturdays, but if a failure occurred during the other 5 days of the week when the clone was being created, we would be up a creek with no backup of any type. As someone who’s obsessed with backups and recovery, this was quite horrifying! Needless to say, improving this process was at the top of my to-do list.

To make a long story of experimentation and optimization short, I ended up leveraging several SQL Server technologies including data compression, backup compression, and backup striping across multiple drives to bring the time to create a full backup down to a little over 3 hours. Differential backups are now possible and complete in about 20 minutes. I’m happy to report that backups are now fast enough to be useful, small enough that we have sufficient space to store many of them at a time, and are easily testable.

So that’s my horror story – I’m glad to say it has a happy ending! Thanks to Nick for the great topic!