Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 9 down vote favorite
1

My current project involves a lot of database shuffling as we upgrade from SQL 2008 -> 2012, re-task hardware, repeat.

One minor annoyance is the connection dialog in SSMS re-shuffling the server order to put the most recent on top. Given that on any particular day I'm connecting to 6-10 DB servers and want to quickly find a specific one in the list, this behavior is much more of a hindrance than a help.

Is there a way to disable this behavior, having the servers listed alphabetically, or in the order they were added...or anything that doesn't change every time I connect to a server?

Barring that, is there an entirely different way to better manage connecting to a specific server from a list/farm of them?

I'm using SSMS for SQL 2012 (though I'm curious about 2008 solutions as well).

share|improve this question

3 Answers

up vote 4 down vote accepted

Use Registered Servers or set up a Central Management Server (CMS). I recommend the latter if you can take the (small) time to set up the infrastructure because it will allow everyone to see the same list of servers, instead of just you (Registered Servers are stored in the registry). The only "drawback" with a CMS is that you have to use Windows Authentication because passwords aren't persisted to disk.

Both these features allow you to maintain an ordered/grouped list of servers, and manage servers as groups. This is very powerful because Policy-Based Management is tightly integrated with the GUI.

This works with all server versions -- I run SSMS 2012 and manage 2005-2012 servers. A CMS server can be set up on an Express edition, so licencing isn't a concern; however, I do recommend taking system database backups so the server list is recoverable.

share|improve this answer
The only drawback we ran into with this before was (correct me if I'm wrong) the CMS can't be a member of its own group...so you need an extra instance just to be a CMS, correct? How do you get around this/make it useful?...or do you just eat having an extra instance somewhere? One more item: most of us work remotely, not always connected to the domain, how would this behave when launching SSMS for local development, lag, no impact? – Nick Craver♦ Aug 3 '12 at 14:26
@Nick: Yes, I recommend setting up a new instance just for the CMS. This way it's separated from the actual SQL infrastructure. That's why I touched on licencing in my answer, and noted it would take a small amount of extra effort to set up. – Jon Seigel Aug 3 '12 at 14:29
@Nick: I'd need to know more about the infrastruture set up -- something is not making sense. You don't connect directly from your home machine to the production SQL servers, do you (I'm assuming not)? If you need to include local servers, use Registered Servers for that part because the list is stored locally. – Jon Seigel Aug 3 '12 at 14:32
Oh yes we do :) This is my dev machine, so I'm connected to all SQL servers in the SE infrastructure from here (but sometimes not, doing local dev and not on the VPN...especially on the laptop). Licensing isn't a major issue, but the totally separate instance (and where to keep it) are a bit of an unknown...not sure which server we'd shove that on. My concern is when SSMS can't reach the CMS, is there some sort of timeout when you open SSMS? – Nick Craver♦ Aug 3 '12 at 14:39
@Nick: Yeah, it's much easier when everything is virtualized. :) We keep ours on a management VM. Another option is to use a Developer edition instance instead of Express, which gives you SQL Agent to tell you if something blows up. No, there's no timeout when opening SSMS. You only get a wait when you try to expand the CMS node in the Registered Servers tab (to be expected). – Jon Seigel Aug 3 '12 at 14:44
show 2 more comments
up vote 1 down vote

You actually do not need to hack anything, as far as I have solved this problem in my SSMS Add-in SSMSBoost (.com) - you can download it and use for free. You simply define a list of your preferred connections in settings (see picture) and then this will be available as drop-down on toolbar, so even switching between servers takes only one click, as you you would switch between databases in native database-list drop down.

Now some pictures:

Settings Dialogue: enter image description here

If you look at other options you will also find "Important DB alert" - this one might be useful for you, as far as you switch between new and old DBs, test and real server during work on your project. You can configure Alert, that will be displayed as BIG Tooltip at specified position at the screen as far as you connect to "important" database, so you will notice that you are working on REAL or Test environment.

Next picture shows contents of toolbar drop-down, where you see your preferred connections listed above, and below all databases from current server (same list as in native databases list drop-down)

enter image description here

As far as I work permanently on improvements you are welcome to write me your proposals, so that we will make add-in even more better.

share|improve this answer
Thanks for following me on Twitter -- I only registered that account to lock up the name, though. That said, I'm in the process of setting up a blog which will have a dedicated Twitter account, and you can follow that if you'd like when it's released (I'll message you here with the details). Thanks. – Jon Seigel Aug 8 '12 at 21:40
As promised, twitter.com/VoluntaryDBA – Jon Seigel Aug 15 '12 at 2:06
up vote 4 down vote

You can't control this order unless you hack into SqlStudio.bin, or remove entries and re-add them in the order you want. Workarounds include:

Note that the order will get messed up as soon as you connect to a server that's not in the first position, since it really is an MRU list (where the most recent connection is listed first).

You can see more details in these SO questions:

And you can see the bug that I filed that initiated the ability to use the delete key:

And other bugs that have sprung up as a result:

share|improve this answer
1  
+1. I didn't know you could hit del with a mouse over in SSMS. That is worth it's weight in gold. – Thomas Stringer Aug 3 '12 at 22:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.