How can I have SQL Server Management Studio 2008 connect to my instances automatically?

Question

I'm running a server with 3 database instances.
Right now, when I open Management Studio, it opens the "Connect" prompt that lets me choose which instance I want (or connect to anything else).

Would it be possible to have it so that, when I open it, it's already connected to these 3 instances, so I can start working right away and save a few clicks? It'd save me hundreds of clicks a day, honestly.

This would be equivalent to what we had in SQL 2000 Enterprise Manager, where you could "register" servers, and they'd always be there just a click away.

EDIT: To clarify what I'm looking for... Before we had Enterprise Manager and Query Analyzer. Now SSMS is the blend between those two, but the "connection" part works exactly like Query Analyzer.
What I do every day is open SSMS to manage the DBs, check their stats, rebuild an index here and there, I'm not using it to run that many queries, so I'm looking for a way to have the SQL instances available on the Object Explorer automatically when logging in. Is this impossible now? :-(

Solution 1

I just discovered it this evening, so let me share my joy: Registered Servers are still available in SQL Server Management Studio!

In the View menu, choose Registered Servers. That will open the window for them. Then, drag that window to the side to dock it.

Along the top of the window you'll see icons for the types of servers (SQL, SSAS, SSIS, SSRS). Choose the type you're registering.

Add your instances to the Local Server Groups by right-clicking and selecting New Server Registration, then complete configuration for the connection.

Once that's done, you can double-click on a server to open the Object Explorer for that server.

Solution 2

OK, so this is what I did (I'd like to edit the accepted answer because I based this on his idea, but I don't have enough points...)

1. I set SSMS to start with "an empty environment", so it doesn't prompt me for a login (Tools-> Options->Environment->Startup).
2. I opened Object Explorer and docked it to the left
3. I opened Registered Servers and made it a "Tabbed Document" instead of a "Dockable" window.
4. In the middle pane, I opened Registered Servers and Object Explorer Details, which SSMS apparently remembers from session to session.

So now when I get in, I have the list of my servers right there and I can connect with a few clicks. It's not perfect, but it's close enough that i'll stop fighting it :-)

Thank you sphilp!

Solution 3

Here you will find information on how to start the SQL Server Management Studio (SSMS) from a shortcut or from the command line.

The following will open the SSMS by using Windows Authentication and will bypass the splash screen. The query editor will be set to the SMS_XXX database:

For SQL Server 2005 Management Studio (sqlwb.exe):

sqlwb -E -S <serverName> -d <databaseName> –NoSplash

For SQL Server 2008 Management Studio and later, including 2016, ssms.exe):

ssms -E -S <serverName> -d <databaseName> –NoSplash

You don't need to specify the -d <databaseName> argument - and in SQL Server 2016 Management Studio (at least) it will connect both Object Explorer and create a new Query document connected to the same server instance.

Note: Change SQLServerName as needed.

SqlWb Arguments:

SqlWb (Options) Scriptfile Specifies one or more script files to open. Projectfile Specifies a script project to open. Solutionfile Specifies a solution to open.

-S Server Name
-d Database Name
-U Username
-P Password
-E Windows Authentication (as opposed to the -U and -P options)
-Nosplash Bypasses the splash screen
-? Help

Referenced from: http://myitforum.com/cs2/blogs/dhite/archive/2008/05/26/opening-the-sql-server-management-studio-from-the-command-line.aspx

I don't think you will be able to automate the 3 connections. But you could create linked servers so that you connect to 1 and link to the other 2

Solution 4

I tried adding additional info to the answer by sphilp but that edit got rejected because some people seem to think it's incorrect. I can assure you: it is not.

What I wanted to add:

The Registered Servers functionality in SSMS can be used to connect to several servers in just a couple of clicks. If you group the servers together by creating a New Server Group under the Local Server Groups node, you'll be able to right-click that group and select Object Explorer. This will open all servers in ... Object Explorer!

More details: SSMS: Connect To Several Servers In One Click (Okay, Two)

You can even influence the order in which the connections are opened (alphabetically).

Solution 5

If you use the SSMSBoost plugin, then you can set any of your preferred connections to "Connect object explorer at startup".

This option can be found on the SSMSBoost->Settings->Preferred Connections->List page of the connection.

Since I like having the master databases of all the instances I routinely work with at the top of my preferred list, I just enable this option for the ones I want to always have available in the Object Explorer.

View more solutions

Comments

[mg1075]

It would be nice if Microsoft provided a fix for this issue. SQL 2000 Enterprise Manager is definitely more user-friendly in this regard.

[Daniel Magliola]

Hmmmm, the main problem I have with this approach is that it does connect, but not in Object Explorer which is what I use every day...

[Daniel Magliola]

Yeah, I had found this... It's not ideal, though, because using this takes about as many clicks as using the login dialog. :-( I think I'll have to use this anyway...

[Valentino Vranken]

@DanielMagliola you can right-click the groups as well and connect to all servers in it, see my answer for more details.

[Mike C]

Do you know how to make the "Local Server Groups" stay expanded. Everytime I open SSMS, the "Database Engine" list of registered servers is always collapsed.

[enderland]

This is great!!

[Deer Hunter]

Are you affiliated with the linked company?

[samp]

No, just a satisfied user.