Section 2.2. SQL Server Configuration Manager

2.2. SQL Server Configuration Manager

SQL Server 2005 runs as a collection of servicesapplication types that run in the system background. SQL Server Configuration Manager is a Microsoft Management Console (MMC) snap-in that lets you start, stop, pause, resume, restart, and configure services, including:

• SQL Server

• SQL Server Agent

• SQL Server Analysis Services

• SQL Server Browser

• SQL Server Full-Text Search

• SQL Server Integration Services

• SQL Server Reporting Services

Start SQL Server Configuration Manager by selecting Start All Programs Microsoft SQL Server 2005 Configuration Tools SQL Server Configuration Manager from the taskbar. Figure 2-17 shows SQL Server Configuration Manager.

Figure 2-17. SQL Server Configuration Manager

In addition to managing services, SQL Server Management Studio lets you manage server and client network protocolsyou can enable or disable protocols and force protocol encryptionand manage server aliases.

SQL Server Configuration Manager combines the functionality of the following: Server Network Utility, Client Network Utility, and Service Manager from SQL Server 2000.

2.3. SQL Server Surface Area Configuration

SQL Server Surface Area Configuration lets you enable, disable, start, and stop features, services, and remote connectivity of SQL Server 2005 installationsthis helps to secure both local and remote systems.

Start SQL Server Surface Area Configuration Manager by selecting Start All Programs Microsoft SQL Server Configuration Tools SQL Server Surface Area Configuration from the taskbar.

The sac.exe command-line utility imports Microsoft SQL Server 2005 surface settings so that you can apply them to other SQL Server instances. The utility is in the C:\Program Files\Microsoft SQL Server\90\Shared directory (assuming that you installed SQL Server to the default directory).

2.4. Database Engine Tuning Advisor

Database Engine Tuning Advisor (DTA) helps you improve query processing without requiring you to understand the database structure or how SQL Server processes queries. DTA helps you select and create an optimal set of indexes, indexed views, and partitions. It analyzes a workloada set of T-SQL statements that runs against the databaseagainst the implementation of one or more databases and recommends changes to the database that reduce the estimated workload cost of the query optimizer. These modifications include adding, deleting, or modifying clustered indexes, nonclustered indexes, indexed views, and partitions.

You launch Database Engine Tuning Advisor in one of three ways:

• Select Start All Programs Microsoft SQL Server 2005 Performance Tools Database Engine Tuning Advisor from the taskbar.

• Database Engine Tuning Advisor.

2.5. SQL Server Profiler

SQL Trace captures database-engine events in real time to a trace file. Traces are based on event class instances that you choose to monitor. Trace information is used to monitor and assess performance, audit activity, and debug SQL statements and stored procedures. Traces are usually managed and accessed through SQL Server Profiler, a graphical user interface to SQL Trace.

SQL Server Profiler lets you create and manage traces, create trace templates, and replay trace results. With SQL Server Profiler, you can monitor how queries are resolved and capture SQL Server events from the Database Engine or Analysis Services to a trace file for analysis. You can later replay the trace events to help diagnose problems.

You launch SQL Server Profiler in one of three ways:

• Select Start All Programs Microsoft SQL Server 2005 Performance Tools SQL Server Profiler from the taskbar.

• SQL Server Profiler.

• SQL Server Management Objects (SMO) provides classes that can be used to create and manage traces for SQL Server or Analysis Server. They are discussed in detail in Chapter 12.