Tuesday, August 27, 2013

How to Tell If Your Users are Connecting to the Availability Group Listener

You've spent a lot of time planning and building out a new SQL Server 2012 environment complete with Availability Group Listeners, but how can you be sure the end users are connecting to the listener and not directly to the SQL Server instance?

So why would we care about this?  To begin with, if the users are not connecting to the listener, then upon a failover to another replica, those users would have to connect to a different SQL Server instance name.  Having a single point of connection is crucial for the high availability process to work correctly.

In a previous blog post, we setup an Availability Group Listener, AdventureWorks.mcp.com, with two IP addresses:   192.168.1.55 & 192.168.2.55.  We'll use this one for our example.


The DMV, sys.dm_exec_connections, contains information about each connection to a SQL Server instance, and can be used to answer our question.

Open a TSQL connection to either the Availability Group listener, and execute the following command.

SELECT
     session_id
    ,local_net_address
    ,local_tcp_port
FROM sys.dm_exec_connections;
GO


The local_net_address and local_tcp_port columns will display the IP address and port number of the client's connection target.  This will be the connection string the users entered to connect to the SQL Server instance.

If the IP address and port number match the Availability Group IP, then you're in good shape.  If they do not match, then some users are likely connecting directly to the SQL Server instance, and that will need to be changed.

By joining the sys.dm_exec_sessions DMV, you'll also be able to get the hostname and program name of each connection.

SELECT
     ec.session_id
    ,es.host_name
    ,es.program_name
    ,local_net_address
    ,local_tcp_port
FROM sys.dm_exec_connections ec
    JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id;
GO


As you can see in this picture, we have one connection on session_id 62 that is connecting directly to the SQL Server instance and not the to the Availability Group Listener.  At this point, I would track down that user, and have them use the correct connection string.

Using this DMV will allow you to verify the users are connecting to SQL Server using the correct connection strings, and help prevent unneeded outages during a failover between replicas.

Monday, August 19, 2013

PASS Summit 2013 - You Ain't From Around Here Are Ya?

I know what y'all are thinkin', what's Charlotte got to do with SQL Server?  Just hear me out.  There's a lot more to Charlotte than NASCAR, fried chicken, and rednecks. I assume most of the 5000 attendees have never been to Charlotte, and probably don't know much about the area.  To help everyone out, I have made a list of useful tips.






My history and why you should listen to me. I begged my management for nearly a decade to send me to the PASS Summit, and this year they finally granted my request.  And to top it off even more, I just happen to live in Charlotte and work in the building right across the street from the Charlotte Convention Center.  I'm native to North Carolina and I have lived in Charlotte for about 17 years.  I even graduated from The University of NorthCarolina at Charlotte.

Queen City History. Charlotte is named in honor of Charlotte of Mecklenburg-Strelitz who was married to King George III of Great Britain.  This is why the city is nicknamed the "Queen City".  It is currently the 17th largest city in the US and it's the 2nd largest financial city, trailing only New York City. The city center is called uptown instead of downtown. The term downtown gives off a negative vibe; hence the term Uptown Charlotte.

Hotels. Just pick one, they're all about the same. However, if you are staying in a hotel on the south side of town near Pineville or Ballentyne, be prepared for I-77 and I-485 to be a parking lot during rush hour. Trust me on this one.

Transportation. The good news for anyone staying on the south side of town is the Lynx light rail. There is only one rail line but it runs from the center of town all the way south to Pineville. My suggestion is to take the light rail if it's near your hotel. Just get off at the 3rd St/Convention Center station, and the convention center is right across the street.



The CATS bus sytem is also not a bad option. The main transit center in uptown is only 3 blocks from the convention center. Any of the bus lines that end in an X are express routes (i.e. 54X) that pick you up from the commuter lots and head directly uptown. In uptown, there is a free bus line called the Goldrush. It different buses and only runs east/west along Trade Street.  It's helpful if you are staying in one of the hotels along that street.  And the best part is it's free.  Check out RideTransit.org for a complete system map.


If you like riding bicycles, the you'll want to check out CharlotteBcycle. There are about a dozen bicycle rentals around uptown. You just pay a small fee at the automated kiosk to share a bike, even if it's for a one way trip.


For those of you driving uptown, you'll need a place to park. There are over 40,000 parking spaces uptown, but you will have to compete with the daily workforce, like me. Most parking decks will run you about $15-20 per day. Once you get uptown, look for the giant "P" signs outside each of the parking decks. The signs will tell you the number of spaces available.


The parking lots are usually cheaper than the decks, $3-10 per day, and most of those you can pay by credit card at the kiosk. Some lots even allow you to pay using the Park Mobile app (Apple | Android | Windows). Just look for the Park Mobile sign near the kiosk for the lot number.

 

You might wonder what these over-street walkways are used for.  This is part of the Overstreet Mall.  It's a maze of walkways that interconnect some of the buildings and it's full of restaurants and shops.  Even if you're not interested in the shops, it's a nice way to get from building to building when it's raining.




While walking around uptown, you'll see these "You Are Here" street signs. The maps divide uptown four color-coded regions, North, South, East, and West. Each map provide you with information about attractions, hotels, and parking. 

















Dining. You shouldn't have any issue finding a place to eat uptown; however, there are a few places of interest you should try out.  

For breakfast:
For lunch:
For dinner:
Also, if you're thinking of going to Ruth's Chris Steakhouse, then chose Sullivan's Steakhouse or Morton's Steakhouse instead.  I've never had a good experience at the uptown location, but that's just my opinion.

On a side note, when eating out, just keep in mind that you're in the south.  If you order iced tea, it WILL be sweet tea.  If you want unsweet tea, then ask for it.

Entertainment. There's plenty to do uptown as well as around town after the conference is over.  Next door to the convention center is the NASCAR Hall of Fame.  There are several other museums: Mint Museum, Bechtler Museum of Modern Art, etc.  The EpiCentre is a multi-use entertainment complex only 2 blocks from the convention center. There are restaurants, bars, and other entertainment there.  For beer lovers, there are plenty of bars uptown.  There are way too many to list, but a few are:
For wine lovers, check out Threes and The Wooden Vine.  Both have a wide range of selections.

The NC Music Factory is about 2 mile walk north from the convention center or only a 4 or 5 minute drive, but they do have free parking.  It's an entertain complex with live music, restaurants, bars, and even stand up comedy at The ComedyZone.  If you head over that way, be sure to visit the VBGB Beer Hall and Garden; definitely the best bar at the music factory.

Don't forget about the Carolina Panthers.  They'll have a home game on Sunday, October 20th at 1PM.  It might be your only chance to see the future superbowl champions in action!  

I know some of you might health nuts and would like find a place to workout besides your hotel gym.  The YMCA has a location uptown in my building.  $10 will get you a day pass, and $20 will get a 7-day pass.

If you prefer jogging outdoors, any of the streets uptown will work nicely.  However, if you like a little more scenery for your job, then head over to the Little Sugar Creek greenway.  The Charlotte Parks and Recreation built 35 miles of greenways around town.


This one is a beautiful, winding route nearly 6 miles long, and located just outside the south side of the I-277 belt loop uptown.


Finally, for the super adventurous attendees, the US National Whitewater Center is about 15 miles west of uptown, or head north to take a ride at 150mph at the Richard Petty Driving Experience.  It's only about 20 miles north of uptown at the Charlotte Motor Speedway.

As a bonus item, the very popular Showtime original Homeland is filmed right here in Charlotte.  If you have the time, why not try out as an extra for the show.

Other links with information about Charlotte:

I think I covered a lot, but if anyone has questions about Charlotte, please don't hesitate to contact me.