Tuesday, November 19, 2013

Simplify your on-call hours with automated response from SQL Sentry

For many DBAs and other IT professionals, on-call hours are part of the job.  Keeping critical servers and applications running 24/7 requires the ability to respond to problems at any time of day.  This usually means that a call or page to the on-call administrator when something does go wrong off-hours.  With SQL Sentry's extensive automated alerting and response capabilities, this isn't always necessary.  Let's take a look at how it works.

Automated response types:

There are four different responses that I'm going to focus on for this post: Execute Job, Execute Process, Execute SQL, and Run QuickTrace.  We will also look at using some notifications alongside them.  Let's start by examining the capabilites of each in detail.

Execute Job

The Execute Job action can be used to execute a SQL Agent job on the current server, or any other watched server in your enterprise. This action effectively enables you to create simple "one-to-one" job chains by associating it with Job Completed, Success, or Failure conditions.  SQL Sentry Event Manager users can also create more advanced job workflows using Event Chaining, but that's a topic for another post.

Execute Job is my favorite response type because I can wrap almost anything in a job, and the job itself is monitored by Event Manager.  This allows me to not only automate a response, but very easily configure a Send Email or Send Page notification in case of a failure in my automated response.

Execute Process

The Execute Process action can be used to execute any executable or batch file against any of your monitored servers.  This option does require xp_cmdshell to be enabled on the server, so this is not always an option.

Execute SQL

The Execute SQL action executes any T-SQL statement(s) against any of your monitored servers.  This option is very flexible, able to respond to pretty much anything you can anticipate and write a script or stored procedure to resolve.  The upside to this versus Execute Job is that you do not have to create an object on the server.  The downside is that it is a little bit more complicated to build a notification to respond to issues with your automated response. 

Run QuickTrace

Sometimes you have a recurring issue that some extra trace data would be helpful to resolve, but it is hard to predict when the issue will happen.  Run QuickTrace is for this kind of scenario, as it allows you to run a short-term trace in response to the event occurring.  This allows you to minimize the amount of time the trace has to run against the server, and the amount of data that needs to be sifted through to analyze the problem.

The Quick Trace runs for a short time, I usually set its duration for 10-30 seconds, and grabs everything running against the server at either the batch or statement event level.  You can also specify a rowcount limit for the trace, limiting the amount of data collected.

The QuickTrace is unfiltered and collects a broad range of data columns.  This is something we would typically think of as being fairly high-impact from a standpoint of overhead on the server,and certainly something we would be very resistant to firing off from Profiler as a client-side trace.  SQL Sentry has a number of different safeguards and optimizations to reduce that overhead.  I won't bore you with the details here, but they're all available in the SQL Sentry User Guide.

Configuring an example in SQL Sentry:

Let's take a look at one possible example using my demo environment. 

First, we need a problem to solve. For our example, I have built an automated response to handle a failure of my nightly full backup job on one of my monitored SQL Server instances.  I created a second backup job for my automated response, and it backs up to an alternate storage location.  This way, if my primary backup location runs out of space, or there is some other issue, we can retry to an alternate location.  In this case, I wouldn't disable the failed job email alert that I have configured globally, because I do want to know that my backup isn't going to be in its usual location.  Let's take a look at how I configured it:

Click to enlarge.  On the left, I've configured the Execute Job action on failure to start the alternate backup job.  On the right, I set a Send Page alert targeting both of my call groups and the help desk so a high-priority notification goes out if the alternate backup should fail as well.

This is just a simple example, but outlines one of the many ways you can have SQL Sentry automate response to events to save aggravation and ensure that problems are resolved quickly. 

Tuesday, October 15, 2013

The Unofficial PASS Summit Sportbike Tour

The closest we came to an action shot of the entire group.

 How it came together:

People that ride motorcycles inevitably talk about them all the time, leading to meeting others who do the same quite regularly.  As a result of my travels with SQL Sentry, I've met quite a few members of the community who are also sportbike riders.  The idea of a pre-Summit ride got started at SQL Bits this year, as I was talking to, and probably having a beer with, André Kamman, Mark Rasmussen, Mark Pratt, and Tim Kent at various times.  

Over the intervening months there was much discussion and banter as we planned the trip.  As the discussion went on, we ended up looping in my friend Mario Faraone, and local SQL guy and former SQL Sentry team member Ryan Brickey.  Tim and Ryan were unable to make the trip, but will hopefully make the next one.  There WILL be a next one!

Once we had what looked like a seven person group, we stopped looking for other riders.  Not because we didn't want company, but because groups larger than that become very difficult to manage on the kinds of roads we planned to ride.  I have some ideas on how we can grow this in the future without issues, so please contact me if you are interested in future rides.  I have a feeling there will be more rides not only in the US, but also in Europe.

Even if sportbikes aren't your thing, this does highlight one of the many benefits of attending community events like SQL Bits, PASS Summit, SQL Rally, SQL Saturday, or any other SQL Server event.  Not only do you network professionally, but you will inevitably meet people that share other interests as well. 

The cast:

From left: Mark Rasmussen, Mark Pratt, Mario Faraone, André Kamman. 


André Kamman - Holland
Mark Rasmussen - Denmark
Mark Pratt - United Kingdom
Mario Faraone - South Carolina
Myself - USA

Planning the ride:

Here in North Carolina we have some of the best motorcycle roads in the country, that are known worldwide.  Pretty much everyone who's been on two wheels for any length of time has heard of the "Tail of the Dragon" at Deal's Gap, and the Blue Ridge Parkway.  Both are great roads, but tend to be crowded as a result of their fame.  As the planner of this ride, I decided very early to skip "the Dragon" entirely, and just pop on to the Parkway for a little bit of the trip.  There are a bunch of other roads around here that are just as good, and far less crowded.

As I mentioned in a prior post, I used to live in Asheville.  I know the area fairly well, but I still wanted to do a little exploring of my own.  I started with some favorite roads from our usual weekend rides out of Charlotte, and figured out fun ways to get there and back from Asheville.  With that, one day of riding pretty much built itself.  Mario and I decided to make that Saturday's ride, since only one road on the route was new to us.

From there I went searching for fun roads on Google Maps and www.motorcycleroads.us to create a second nice loop for Sunday's ride.

André, Mark, and Mark needed something to ride, and after quite a bit of searching I ran across http://www.sportbikes4hire.com/.  Sportbike rentals are not an easy thing to come by.  In many areas the only motorcycle rentals available are from Harley Davidson dealers.  The bikes were delivered early, and Greg McCoy (the owner) was very friendly and easy to work with.  Those bikes have had hard lives, each having been down a few times.  That said, they had been repaired carefully, and looked well-maintained.  It seemed like everyone was satisfied overall. 


I only mapped one way for this route, because I wanted to leave it open to either a fast return via the interstate, or a leisurely one via the Parkway.  We ended up taking the quick way back to avoid rain and "leaf peepers" driving 10-20 miles per hour under the limit on the Parkway.

Our first road was US 74A from Asheville to Chimney Rock.  It's a great road with lots of twists and turns, and a fair amount of elevation change.  It's a fairly heavily-traveled road, so traffic tends to make for a slower ride.  Just before Chimney Rock, we turned North on NC 9 to head up to Black Mountain.  NC 9 had more great curves and quite a bit of elevation change.  The northern part of the road was particularly good, and one I'd like to try again heading the other direction to hit the tight turns going uphill.  

Our next destination was NC 80, also known as "The Devil's Whip" - one of my favorite rides.  Best experienced northbound, the section of 80 we ride runs from Marion up to the Blue Ridge Parkway.  It offers a great mix of hairpin turns, sweeping corners, and tons of elevation change.  

One of the many great Blind Kenny photos from NC 80: Mario headed up the hill. More pics at the end of the post.

While we stopped at the top for a break, I ran back down to say hello to Ken and Donna of Blind Kenny Powersports Photography.  They provide the great photos of visitors to "Blind Kenny Corner", road condition reports, and as I found out this time around, just great people to stop and say hello to.

Stopping for a break to enjoy the view from one of the Parkway overlooks.

After running down and back up the best part of highway 80, we took the Parkway up to US 221, and cut over to NC 181.  Another local favorite, NC 181 runs North from Morganton to Linville.  Great surface, sweeping curves, and numerous legal passing zones make for a motorcycle playground.  Just be careful, it's an easy place to get a ticket with many of the turns being possible at well over the 55 mile per hour speed limit.  After a few runs up and down the mountain, rain was coming, so we headed back to Asheville.


Our first ride on Sunday morning was a repeat of US 74A, a good warm up, but this time we went past highway 9 and turned toward Brevard on US 64.  Next time I'll find a better route to Brevard, as 64 really isn't much fun.  

André Kamman takes in the view at Looking Glass Falls, pondering this most un-flat landscape.

US 276 runs north from Brevard, past Looking Glass Falls, to Waynesville, NC.  It's a great road, but heavily traveled due to the number of attractions it passes.  Still, a great twisty road that's well-maintained, has great scenery, and is a challenging ride.  The descent out of the mountains on the North end of the road was wet, so we had to take it pretty slow.  

Our next leg proved to be the highlight of the day, NC 209, nicknamed "The Rattler."  Running from Lake Junaluska to Hot Springs, there are 239 turns and lots of elevation change.  The turns ranged from long sweeping curves to switchbacks.  There was almost no traffic, the surface was good, and there were only a couple of corners with gravel to dodge.  It's a pretty ride too, but we didn't have much time to look at the scenery.  The road demands undivided attention.

NC 63, heading back to town.  Dead tired :)

After finishing NC 209, we doubled back and did about half of it again, breaking off on NC 63 to head back into Asheville.  NC 63 proved both challenging and fun, with another steep descent with lots of switchbacks and finally some country road sweepers before turning into New Leicester Highway.  We took this back toward the West side of Asheville, picked up Interstate 40, got rained on, and headed back to the hotel to drop off the rental bikes and get changed for the ride home. 

All in all, it was an awesome experience, and one we will be looking to repeat.  Ride safely and enjoy PASS Summit!

A few more photos from NC 80:

Wednesday, August 28, 2013

Visit Asheville while in North Carolina for PASS Summit

One of the great things about the choice of Charlotte, North Carolina to host the 2013 PASS Community Summit is Charlotte’s proximity to Asheville and the surrounding area.  Located in the Blue Ridge Mountains, Asheville is approximately two and a half hours by car from Charlotte via the interstate.  Prior to moving to Charlotte, I lived in Asheville for ten years.  I still have family in the area, so I’m there frequently and know the area well.  This post is not meant to be exhaustive, but to provide some ideas for those who might want to spend some extra time in the area and explore a great nearby destination.

Famous attractions

Asheville has become a fairly well-known tourism and vacation destination, and it’s not surprising.  For a smaller city, there is quite a lot to see and do, and the mountains are beautiful.  Here are a few highlights:

  • Biltmore House  http://www.biltmore.com/
    • If Asheville has a signature tourist attraction, this is it.  The biggest private residence in the United States. 
  • Downtown  http://www.exploreasheville.com/things-to-do/discover-downtown/
    • Downtown Asheville is not huge, but very walkable.  There are lots of shops and restaurants, something for everyone.
  • Biltmore Village  http://www.biltmorevillage.com/
    • Similar to downtown Asheville, but more upscale.  Less options for places to eat, but has the nicest McDonalds you’ll ever see. 
  • Grove Arcade  http://www.grovearcade.com/
    • One of America’s first indoor shopping malls.  The Grove Arcade was renovated about 20 years ago if my memory serves me correctly.  Lots of little arts and crafts shops, be sure to check out the one that does live glass blowing.  The floors aren’t even close to level, interesting unless you are on crutches or roller skates…then just hazardous.
  • Folk Art Center  http://www.southernhighlandguild.org/pages/folk-art-center/general-info.php
    • On the parkway, an art gallery with a focus on the traditional folk arts of the area.
  • Pack Place  http://www.packplace.org/
    • Pack place is home to a number of different museums, galleries, and a theater.  The Colburn Gem and Mineral Museum is one of the more unique museums in the area. 

Bringing the kids?

Be sure to check out the Western North Carolina Nature Center http://www.wncnaturecenter.com.  This is a good destination for all ages, but is a huge hit with kids.  The nature center has a great wildlife collection, mostly made up of species that are native to the North Carolina mountains.  The river otters are the star attraction.  This was a favorite of my sister and I when we were little.

Outdoor Fun

One of the keys to Asheville's popularity is the huge variety of outdoor activities available in the area.  I've put together a list of destinations that I've enjoyed over the years.

  • Navitat Canopy Tours  http://www.navitat.com/asheville-nc/
    • Navitat does a great zipline canopy tour.  I’ve done this one personally, and recommend it highly to the adventurous traveler.   Some of the runs are very high up, and very fast.
  • Sliding Rock  http://www.ncwaterfalls.com/sliding_rock1.htm
    • A natural water slide in Pisgah Forest.  Fun, but COLD!  Can get pretty crowded on the weekends.  In October, you’d want an unseasonably hot day for this.
  • Nantahala Outdoor Center  http://www.noc.com/
    • Popular destination for rafting, kayaking, climbing, or hiking.  Again, look for a hot day if you plan on getting in the water!  The mountain rivers get really cold.


Asheville is a very popular destination for all sorts of outdoor activities, but one of the biggest is hiking.  The mountains and forests in the area are covered with trails, including part of the Appalachian Trail.  The trails tend to be fairly well marked, and easy to follow due to the volume of traffic they get.  There is a wide range of difficulty as well.  Here are a few that I have first-hand experience with: 

Where to stay

Asheville has plenty of the usual chain hotels, and most are perfectly fine, but there are a couple of upscale options in town that are true standouts:

  • Grove Park Inn  http://www.groveparkinn.com/
    • The Grove Park Inn is a large historic luxury resort.  In addition to the hotel, there is a golf course, a spa, and excellent restaurant.  The restaurant is a worthy stop,  especially for their Sunday brunch buffet, even if you are staying elsewhere.
  • Grand Bohemian Hotel  http://www.bohemianhotelasheville.com/
    • Right in the middle of Biltmore Village, the Grand Bohemian (one of Marriott’s upscale designer hotels) is not only very nice, but in a perfect location for many of the other stops I’ve listed.  Every room is unique, and it doesn’t look like a chain hotel at all.

Dining in Asheville

This could quickly get to be a massive list, but I’ll just throw out a few great spots that might otherwise be overlooked.  None of these are going to break the bank either. There is certainly no lack of variety in Asheville's restaurant selection.

  • Asheville Pizza and Brewing  http://www.ashevillebrewing.com/
    • This is one of my favorites.  It’s a combination bar, restaurant, and movie theater!  Asheville Brewing Company owns it, and provides good food, good beer, and movies to watch while you enjoy them.  There are only two theaters, so your movie options are limited, but they usually have something good.  The movies showing are usually the blockbusters but just after they leave the major movie theaters.  The upside to this is that for about the same price as a normal movie outing with a snack and a drink, you get a full dinner and a craft beer.  How can you go wrong?
  • Tupelo Honey Café  https://tupelohoneycafe.com/
    • If you aren’t from the area and want some really good southern cuisine, this is my recommendation.  Not too traditional, but true to the spirit of southern cooking, and a local favorite for good reason.
  • 12 Bones Smokehouse  http://www.12bones.com/
    • 12 Bones looks like a dive, as the location was originally a gas station, but it’s one of the best places in town for ribs or barbecue.
  • Frankie Bones Restaurant & Lounge http://fbdining.com/
    • Really good spot on the south side of town to get a bite to eat.  They have a very diverse selection, and an awesome Sunday brunch menu.
  • The Green Sage Coffeehouse & Café http://thegreensage.net/
    • If you’re looking for a healthier meal while on the road, this is a great spot.  I’m usually not the health food type, but I’ve eaten here with family a few times and always enjoyed my meal.

Scenic/Fun Routes from Charlotte to Asheville

If you’ve decided to head up to Asheville, you may want to consider one of these routes to get there as an alternative to the boring all-highway route your GPS will suggest.

  • Via NC 181, Blue Ridge Parkway, NC 80
    • Route map:  http://goo.gl/maps/Z71iz
    • This is the car/motorcycle enthusiast route.  If you have other plans don’t use it!  This takes roughly twice as long as the interstate trip, but it is roughly ten times as fun in a sports car or on a motorcycle.  I regularly ride out to the end of NC80 then come back if I’m not going to be staying in Asheville.  This route keeps you off the interstate for most, if not all of the trip.  Some of the roads I’ve outlined are challenging, so take care the first time out.  If you are on a bike, watch for debris in the roads, as the rainy year we’ve had has washed a lot of junk into the road.  NC 181 is very well-known and is an absolute blast to run up, just watch out for NC Highway Patrol.  NC 80 aka The Devil’s Whip is a tight and highly technical road with loads of elevation change.  Be ready to have your photo taken if you’re there on the weekend, as Blind Kenny www.blindkenny.com is usually out there.  This route also incorporates a nice chunk of the Blue Ridge Parkway, but I like to get off the Parkway at NC 80 instead of taking it all the way to Asheville.  Interstate 40 actually has a fun ascent up Old Fort Mountain (again, watch for speed traps).  You can double back on NC 80 (the uphill run is the most fun) and take the BRP all the way into Asheville if you want.
  • Via Chimney Rock and Lake Lure
    • Route map:  http://goo.gl/maps/dAjsj
    • More relaxed than the route above, but still a great alternative to the interstate.  Highway 74-A has some really sharp curves as well, so be ready!  This route isn’t nearly the time investment that the first one is either, it only adds about 45-60 minutes to the trip versus the interstate.  The run up NC 226 and back down US 64 is not efficient, but it avoids a big chunk of US 74, and many stoplights and speed traps.  Highway 74A is the highlight of the trip, and runs right by both Chimney Rock Park and Lake Lure.  Both are good places to stop along the way.
  • Blue Ridge Parkway info is available here: http://www.blueridgeparkway.org/
    • Be sure to check for closings, as some segments may be closed for maintenance or due to road conditions. 

I hope this list helps you with planning your visit to this area of North Carolina. Feel free to add your suggestions if you have ever visited the Asheville area.

photo credit: BillRhodesPhoto via photopin cc
photo credit: Mr G's Travels via photopin cc
photo credit: Xavier de Jauréguiberry via photopin cc