Monday, November 28, 2011

Registered Servers 101

Registered Servers are a great tool for keeping a list of all the servers you work with. This can be useful both for the servers you use every day, and for those servers you only occasionally touch. How often have you had to check a rarely used server only to forget the exact name of the server and instance?

Registered Servers come in two varieties, Local Server Groups and Central Management Servers. For today, I will cover Local Server Groups. In a future post, I will discuss Central Management Servers.

Local Server Groups

Local Server Groups are great for keeping a list of all the servers in your environment. You can group the servers by project, OS version, SQL version, time zone, or any other grouping that makes sense to you. Additionally, a server may be registered in more than one group, and groups can be nested within one another.

Creating Local Server Groups

View - Registered Servers

The first step is to tell SQL Server Management Studio that you would like to work with Registered Servers. Go to the View menu and select Registered Servers. Alternatively, you could use the Keyboard Shortcut Ctrl+Alt+G.

Once you do that, you will see two choices; Local Server Groups and Central Management Servers. As I stated previously, for this post we will focus on Local Server Groups. The good news is, everything we learn about Local Server Groups will be applicable to Central Management Servers, as well.

If you select New Server Group, you will be presented with a simple dialog to enter your group details.

New Server Group - Step 1

New Server Group - Step 2

After you have created you Server Group, you can start registering servers into it.

New Server Registration - Step 1

New Server Registration - Step 2

Here is an example with several groups and servers...

Local Server Groups Example

Exporting and Importing Servers

Now that you've spent all this time building up your server list, wouldn't it be great if you could share it with others? Thankfully, this is very easy. To export your server list, just select on one of the folders or sub-folders, right-click, and follow the prompts.

Export Server List - Step 1

Export Server List - Step 2

To import the server list, follow similar steps. Right-click on Local Server Groups and follow the prompts.

Import Server List - Step 1

Import Server List - Step 2

Multi-Instance Queries

Now that we've gone through the hassle of registering all our servers, it is time to put that to good use. The primary reason to create Registered Server lists is to run multi-instance queries. These are great for auditing systems or performing administrative tasks.

To do so, simple right-click on one of your groups and select New Query. Alternatively, you can select the group, then go double-click on one of your query.sql files.

Multi-Instance Query - Connection

There are a few things to notice that are different from a normal query window.

First, the footer is pink; this alerts you to the fact that you are connected to multiple servers at the same time. SELECTs are generally fine, but double-check before you run any INSERTS, UPDATEs, or DELETEs.

Second, it tells you how many servers were in the group it attempted to connect to, and how many it was able to reach. In this example, I was able to connect to ten out of ten. If you see less than the number you were expected, make sure you find out why those servers were not available.

Third, you will see an indicator telling you which Server Group you have connected to. This is where a good naming convention comes into play. When you are creating your groups make sure the names make sense both to you and to your coworkers.

Multi-Instance Query - Results

Here, you will notice that the results from each server has been stitched together in one results window. From here, you can save the results to Excel for better formatting or reporting.

Pros and Cons

The pros are great. You can keep track of all your servers. You can run queries or updates against multiple servers at the same time. Groups can be nested to mimic the hierarchy of your organization. Servers can even be in multiple groups at the same time.

So what's not to love? Local Server Groups are just that, local. That means, all of your hard work means nothing if you happen to logon to a different server. Or to your coworkers. Or...

Of course, you can export you list and import on other servers, and share it with your coworkers. But just like anything you print these days, it is immediately obsolete. Any time you add or update a new server, you will need to export that change to every other server or coworker that is using your list.

A Better Way

Never fear, there is a better way. Central Management Servers take everything good from Local Server Groups and none of the bad. In a nutshell, these are Server Lists that you can share among your organization and are always kept up to date.

Next time, we will look at Central Management Servers.

Monday, November 14, 2011


The past twelve months have been fantastic for me, both personally and professionally.  Heck, I even finished remodeling my house.  I embraced each positive experience and fed it into the next one down the line.  I would never have had such a good year if it were not for the wonderful people I met along the way.

Thanksgiving is as apropos as can be.

PASS Summit

Last year, I was fortunate enough to go to the PASS Summit.  PASS, as it is colloquially known, is a world-wide conference of SQL Professionals.  It is jam packed with speakers, technical sessions, networking, parties, and hot tubs.  Although, not necessarily all at the same time.

I attended tons of great sessions, many being given by the same bloggers I had been reading; one of whom was Trevor Barkhouse (blog | twitter).  On the meet and greet day, I met Wes Brown (blog | twitter), who runs the local CACTUSS group in Austin, and he mentioned the SQL Saturday conferences.

I left Seattle absolutely loving the city, ready to move there, and recharged about my career with SQL Server.  I have been working with technology for over fifteen years, but I have never experienced the camaraderie that I felt with the SQL community.

SQL Saturday

After learning about SQL Saturday, I started looking up when and where the next one was going to be.  SQL Saturday is an almost free mini-PASS conference put on your fellow DBAs in the field.  It is on a rotating schedule that moves from town to town, and even other countries.

The first SQL Saturday I attended was in Houston, where I saw a session listed by Trevor Barkhouse.  I recognized his name from PASS, so I attended his session, asked a bunch of weird questions, and won a book at the end: SQL Server 2008 Internals and Troubleshooting; which I proceeded to read furiously...

After that, I attended the Dallas SQL Saturday and met-up with Trevor, and others, again.  The SQL community was really beginning to feel like a family.


To say that it was a blessing to attend the SQLskills classes this year would be an understatement.  Paul Randal (blog | twitter), Kimberly Tripp (blog | twitter), Bob Beauchemin (blog | twitter),  and Jonathan Kehayias (blog | twitter) offer, hands down, the best training I have ever had.  Period.  Forget about SQL training, technical training, or any other training.  I have never been so challenged, felt so dumb, and felt so smart; all at the same time.

As a special treat, I was able to attend a class where Brent Ozar (blog | twitter) was an additional one of the instructors.  His is one of the first blogs I started reading when I started out as a DBA.

If you can only do one thing as a DBA next year, attend a SQLskills class.  You will come away with a sore brain, a list of projects to implement in your shop, and a ton of great scripts and demos to review later.

Grandma's 99th Birthday & Summer Mummers

This summer, my grandmother turned ninety-nine.  So, my cousin organized a secret family reunion to surprise her.  This was a fantastic event and we had people come out of the woodwork for this.  It was great to see her and other family members that I haven't seen recently.  It amazes me how lucent she still is; I hope some of her genes rub off on me.

While in Midland, I also got to check out Summer Mummers.  Summer Mummers is a gin-u-wine Wild West Vaudeville Show.  It is an incredible experience and well worth the drive through West Texas to see it.  In fact, people travel from other countries every year to see it.

If you love theatre, do yourself a favor and go see this show at least once in your life.  Once you do, you will wonder how you ever lived before.

SQL Saturday Austin

With some encouragement from Wes Brown (blog | twitter) and Trevor Barkhouse (blog | twitter), I went for broke and submitted a session for SQL Saturday #97 in Austin.  I did a trial run of my presentation at the local CACTUSS user group, and even though I went way over time, I got plenty of good feedback.  So I edited and cut my way down to 60 minutes and re-worked my demos and went for it.

SQL Saturday Austin was like a class reunion for me.  I saw several of my friends from SQLskills class: Trevor Barkhouse (blog | twitter), Vicky Harp (twitter), and Rudy Rodarte (blog | twitter | youtube).  All of us as presenters!

By taking the leap of faith and becoming a speaker, I met tons of cool folks and made some good, new friends.  Doing this really charged my batteries and primed me for the next great chapter in my career development.


November brought on another change; after much consideration, I have decided to move on from Whole Foods Market.  During the past four and a half years, I have had a wonderful tenure.  I have made some great friends, worked on some great projects, and gained tons of valuable experience.  I feel very fortunate for having worked there, and am grateful for all the opportunities they have afforded me.  Between data center migrations, multi-instance clustering and consolidation, and some interesting disaster recovery situations I feel I could not have had a better training ground as a Database Administrator.  And the location's not bad, either.  It's hard to beat 6th & Lamar; downtown Austin at its finest...

In a couple of weeks, I will begin the next chapter of my career in the healthcare industry with a global provider of orthotics and prosthetics.  I am excited to be working with an organization that does so much to improve the lives of people all around the world.  I will continue my work with clustering and Denali.  While I will no longer be downtown, the Domain is a pretty cool location, as well.  I'm sure I will find some cool places to explore...

SQLskills Mentoring

Last week, I pretty much won the lottery.  Around mid-week, Jonathan Kehayias (blog | twitter) reached out to tell me a new mentoring program that SQLskills is starting, and oh by the way, would I be interested?  They say there is no such thing as a dumb question, but I'm not so sure...  :-)

Needless to say, I was interested; and floored, honored, humbled, etc.  I am truly touched that Jonathan and Paul reached out to me and are willing to take the time to guide me along my personal and professional goals for the coming year.  My hope is that I will be able to pay this forward to someone else down the line.

So my next mission is to sort out what exactly are my goals for two thousand and twelve.  I guess I'm not gonna worry about that Mayan Calendar thing any more.  But I do think December 20th, 2012 will be a good day to buy some stocks...  :-)

Everything's Coming up Milhouse

When I look back upon this great year, it amazes me how things have developed.  If you don't think your life is going as well as you would like, you DO have the power to change things.  It takes a lot of work and is not easy, but you can create your own luck and make the life that you would like to have.  You will not always get everything you want, but as Wayne Gretzky said, 'You miss one hundred percent of the shots you don't take.'

Just go for it.