The SQL Server Management Studio graphical point-and-click interface makes server, database, and resource management easy to do. Using SQL Server Management Studio, you can manage both local and remote server instances by establishing a connection to SQL Server and then administering its resources. If you have disabled remote server connections to a particular server, however, you can only work with the server locally (by logging on to the system at the keyboard or by establishing a remote Terminal Server session in Windows and then running the local management tools) or through a Telnet session.
To run SQL Server Management Studio, click the Start button, point to Programs or All Programs, and then select Microsoft SQL Server 2005 | SQL Server Management Studio, or type sqlwb at a command prompt. Then you must connect to the server with which you want to work. There are several ways to do this:
Connect using a standard login to a server instance.
Connect using a login to a specific database.
Connect using server groups and registered servers.
Connecting to a server instance allows you to work with that particular server and its related components. Typically, you will want to connect to a server’s Database Engine. As you can see in Figure 5-2, the Database Engine gives you access to:
Databases. Manage system databases including master and model, as well as user databases and database snapshots. You can also access the ReportServer and Report ServerTempDB databases under this node.
Security. Manage SQL logins, server roles, linked servers, and stored credentials.
Notification Services. Register, list, and unregister Notification Services instances.
Replication. Configure distribution, update replication passwords, and launch Replication Monitor.
Management. Configure SQL Server logs, maintenance plans, Full-Text Search, Distributed Transaction Coordinator, and Database Mail. You can also configure legacy features, such as SQL Server 2000 database maintenance plans, SQL Mail, and DTS 2000 packages.
Server Objects. Configure backup devices, HTTP endpoints, linked servers, and server triggers.
SQL Server Agent. Configure SQL Server Agent jobs, alerts, operators, proxies, and error logs.
If you are not automatically connected or you exited the Connection dialog box, you can connect to a server instance by clicking Connect in Object Explorer view. You store server and login information using the registration feature. Registered servers can be organized using server groups and then can be accessed quickly in Registered Servers view. Methods to manage server groups and register servers are discussed in sections later in this chapter titled "Managing SQL Server Groups" and "Managing Servers," respectively.
To connect to a specific server instance using a standard login, follow these steps:
Start SQL Server Management Studio by clicking the Start button, pointing to Programs or All Programs, Microsoft SQL Server 2005 and then selecting SQL Server Management Studio. Or type sqlwb at a command prompt.
In the Connect To Server dialog box, use the Server Type drop-down list to select the database component to which you want to connect, such as Database Engine.
In the Server Name box, type the fully qualified or host name of the server on which SQL Server is running, such as corpsvr04.cpandl.com or CorpSvr04. Or select Browse For More on the related drop-down list. In the Browse For Server dialog box, select the Local Servers or Network Servers tab as appropriate.
After the instance data has been retrieved, expand the nodes provided, select the server instance, and then click OK.
Use the Authentication selection menu to choose the authentication type, either Windows Authentication or SQL Server Authentication (based on the authentication types selected when you installed the server). Provide a Windows user name or SQL Server login ID and password as necessary.
Windows Authentication. Uses your current domain account and password to establish the database connection. This authentication type works only if Windows authentication is enabled and you have appropriate privileges.
SQL Server Authentication. Allows you to specify a SQL Server login ID and password. To save the password so that you do not have to re-enter it each time you connect, select Remember Password.
Click Connect. You can now use Object Explorer view to work with this server.
To connect to a specific database using a standard login, follow these steps:
Start SQL Server Management Studio by clicking the Start button, pointing to Programs or All Programs, Microsoft SQL Server 2005 and then selecting SQL Server Management Studio. Or type sqlwb at a command prompt.
In the Connect To Server dialog box, use the Server Type drop-down list to select the database component to which you want to connect, such as Database Engine, and then, in the Server Name box, type the fully qualified or host name of the server on which SQL Server is running, such as coprsvr04.cpandl.com or CorpSvr04.
Use the Authentication selection menu to choose the authentication type, either Windows Authentication or SQL Server Authentication (based on the authentication types selected when you installed the server). Provide a Windows user name or SQL Server login ID and password as necessary.
Click Options to display the advanced view of the Connect To Server dialog box. Select the Connection Properties tab, as shown in Figure 5-3.
Type the name of the database to which you want to connect, such as Personnel. Or select Browse Server on the related drop-down list. When prompted, click Yes to establish a connection to a previously designated server.
In the Browse Server For Database dialog box, select the database you want to use, and then click OK.
Select the network protocol and any other connection properties if you are prompted to do so. Shared Memory is the default network protocol for local connections. TCP/IP is the default for remote connections.
Click Connect. You will then be able to work with the specified database in Object Explorer view.
