How to have SQL Server Management Studio 2008 connect to the instances automatically

sql-server-2008ssms

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? 🙁

Best Answer

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.

Related Solutions

SQL Server – Cannot Connect to Local SQL Server 2008

Ok, can you open your services console and scroll down to S for SQL Server. You should now see the services. Please ensure SQL Server (SQLEXPRESS) is running and then try .\SQLEXPRESS instead of (local).

So as per your example:

Server type: Database Engine
Server name: .\SQLEXPRESS
Authentication: Windows Authentication

Hope this helps

Update: These instructions are because I assume you are running Express Edition not Dev/Std/Ent edition of SQL Server

Try ensuring the appropriate protocols are enabled:

Start the SQL Configuration Manager (ie: Start->Programs->SQL Server->Configuration Tools)
Expand the SQL native Client configuration
Click Client Protocols (you may have a 32-bit and a 64-bit, apply to both)
Ensure Shared memory, TCP/IP, Named Pipes are enabled in that order
Expand SQL Server Network Configuration
Ensure Shared Memory for either SQLEXPRESS and/or MSSQLSERVER is enabled
Click SQL Server Services
Restart any running services

You should now be able to login to the instance

If you find you cannot login at all you may need to follow these instructions to get SQL Server into single user mode. See here for the full instructions from Microsoft.

By default, sqlservr.exe is located at C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn. If a second instance of SQL Server is installed, a second copy of sqlservr.exe is located in a directory such as

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\binn.

You can start one instance of SQL Server by using sqlservr.exe from a different instance, but SQL Server will start the version of the incorrect instance as well, including service packs, which may lead to unexpected results.

To avoid this, use the MS-DOS change directory (cd) command to move to the correct directory before starting sqlservr.exe, as shown in the following example.

cd \Program Files\Microsoft SQL Server\MSSQL10_50.1\MSSQL\Binn

To start the default instance of SQL Server in single-user mode from a command prompt

From a command prompt, enter the following command:

sqlservr.exe -m

Single-user mode can be useful for performing emergency maintenance when you do not want other users to connect to SQL Server, but any user can become the single user, including the SQL Server Agent service.

You should now be able to login to the instance and add yourself to the security tab and grant full access.

Alternate Method:

THere is a script here that claims to add the current user to the SQL Server sysadmin role. This may work in single user mode but I have not verified it

SQL Server Management Studio – Error connecting to remote DB

I'm assuming you've remembered to include the instancename (server\SQLEXPRESS)?

Try to create an ODBC against the same server. If you get the same error; create another ODBC, but use regular "SQL Server" driver, and on the "Client Configuration" settings choose "Named Pipes".

Also, SQLChickens tip about making sure the SQL Browser is started on the server is valid.

Also, try using IP-address instead of the NetBios name.

Best Answer

Related Solutions

SQL Server – Cannot Connect to Local SQL Server 2008

SQL Server Management Studio – Error connecting to remote DB

Related Topic