SQL Server 2005 replaces Enterprise Manager with SQL Server Management Studio to compliment the expanding role of SQL Server administrators and developers. SQL Server Management Studio provides the easiest way to configure SQL Server. In SQL Server Management Studio, you can access the properties of a registered server easily, and then use the pages and options provided to configure the server.
After you connect to a registered server in SQL Server Management Studio, you can view and manage its configuration properties using the Server Properties dialog box. To access this dialog box, complete the following steps:
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.
In the Connect To Server dialog box, use the Server Type drop-down list to select the server instance to which you want to connect, such as Database Engine.
Select or type the name of the server on which SQL Server is running in the Server Name box, such as DBSvr18.
Note
You can only connect to registered servers. If you want to work with a SQL Server that is not registered, you must register the server before you can use SQL Server Management Studio to configure it. See the section titled "Managing Servers" in Chapter 5, for details.
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.
Click Connect. You connect to the default instance (unless you have configured another default previously). To change the instance to which you connect, click Option, select the Connection Properties tab, and then use the Connect To Database drop-down list to select the instance to which you want to connect.
Right-click the server name in the SQL Server Management Studio Object Explorer view and choose Properties from the shortcut menu to open the dialog box shown in Figure 6-1.
You can now manage common SQL Server configuration settings. For more advanced settings, you need to use a stored procedure, such as sp_configure, as discussed in Chapter 4.
The Server Properties dialog box has many pages, which are listed at the top of the left pane in Figure 6-1. The rest of the sections in this chapter explain how to use the configuration options provided on these Server Properties pages. Permissions are discussed in Chapter 8.
If you want to view a summary of current settings, run the following query in query view:
use master go exec sp_configure go