SQL Server 2005 delivers an entirely new set of management tools for both DBAs and developers. With the addition of some new services in SQL Server 2005, service management is becoming a more important area of DBA concern. The new SQL Server Management Studio replaces both the SQL Server 2000 Enterprise Manager and the Query Analyzer. You need to be aware of two supporting tools: the SQL Server Configuration Manager and the Surface Area Configuration tool. Let's look at each in turn before turning to the SQL Server Management Studio.
Note that SQL Server 2005 is going through its last beta cycle as a series of Community Technology Previews, which are interim beta-level, pre-release builds. Some minor changes in these management tools may occur before release. The information in this article is based on the feature-complete April Community Technology Preview (CTP).
Configuration Management Tools
You'll need to use three new small tools whenever you install SQL Server 2005. For increased security, SQL Server 2005 sets most services and external connectivity features off by default, so if you need anything more than the options you chose during setup, you'll have to use these tools. With the first tool, you can manage SQL Server services; the other two tools form a set that you can use to manage the interfaces through which unintended or malicious access to SQL Server can occur. We call this set of interfaces SQL Server's attack surface area.
SQL Server Configuration Manager. The SQL Server Configuration Manager is a helper tool that you'll need to manage the Windows login accounts for the various SQL Server 2005 services on your server as well as the services themselves. You can't start or stop SQL Server 2005 services directly from SQL Server Management Studio as you can with the Enterprise Manager, so you'll need to learn the SQL Server Configuration Manager right away. You can invoke it from the Windows Start Menu or from the Computer Management applet in the Windows Administrative Tools dialog. If you're running SQL Server Management Studio, which you'll learn about in the next section, you can launch it from the Management Studio Registered Servers dialog.
You use the SQL Server Configuration Manager to manage SQL Server services, network libraries, and the SQL Native Client, as Figure 1 illustrates. Here, you can set and change service accounts and enable and disable services. This tool has some overlap with the next two surface-area configuration tools, which manage these services and other features from a security-threat management standpoint.
Surface-area configuration tools. Many new services are available in SQL Server 2005. In addition to the standard SQL Server, SQL Agent, and Analysis Server services, you can now configure services for Integration Services (formerly Data Transformation Services—DTS), Reporting Server, Full-Text Search, and SQL Browser.
These services are part of the potential attack surface of a SQL Server 2005 installation. To minimize the potential attack surface, Microsoft has provided two new surface-area configuration tools, one for managing services, and another for connectivity features. You'll notice links for these tools at the end of a SQL Server 2005 setup process, but you can also get to them from the Windows Start Menu. Both of these tools help join the disparate services and features that expose a SQL Server to potential attack, and you can enable and disable those services directly through these tools.
The Surface Area Configuration for Services and Connections tool allows you to enable or disable the various services, as shown in Figure 2. You can also disable and enable the SQL Browser service, which listens on UPD port 1434 and handles connections to named instances.
In the Surface Area Configuration for Features tool, which Figure 3 shows, you can enable connectivity features, including ad-hoc remote queries, CLR integration, Database Mail (SMTP) stored procedures, remote Dedicated Administrator Connections, native Web services (SOAP) endpoints, the OLE automation extended stored procedures, Service Broker endpoints, SQL Mail (MAPI) stored procedures, xp_cmdshell, and the Web Assistant. By default, all these features are disabled and must be explicitly enabled. You can also use the sp_configure system stored procedure to enable many of the features and the relevant T-SQL commands to enable the endpoints.
Management Studio
SQL Server 2005 delivers a consolidated management tool with the SQL Server Management Studio, a comprehensive tool that combines features relevant to both DBAs and developers. Management Studio is a complex tool and contains too many features to cover in detail in this article. The major new changes can be organized along three lines: server management, query editing, and performance analysis.
Management Studio is the great "tool consolidator": Microsoft deliberately combined the best administrative features of Enterprise Manager and the best query-editing and analysis features of Query Analyzer into an entirely new tool. At the same time, Management Studio adds many new features not available in either tool. We'll take a closer look at the new features later in the article.
When you first launch Management Studio, you'll notice a Visual Studio look and feel. But even though Management Studio is based on the Visual Studio shell and has some of the same layout, don't let looks fool you: Management Studio is a completely new tool written in managed code.
What you'll notice immediately are two basic types of dialogs: the central, non-movable area of the Management Studio Window is the document window area, and the peripheral areas are called components. By default, the document window initially displays the Summary page, which contains several reporting functions about registered SQL Servers. When you first start up Management Studio, you'll see the Registered Servers and Object Explorer component dialogs on the left side of the window and the Summary page in the document window on the right, as Figure 4 shows. Note that this is the default set of initial components displayed, but you can change them to your own preferred components in the locations you choose. The Summary page lists the contents of an Object Explorer node or produces a report on the selected node. As you work more with the components, you'll invoke a third type of dialog, the non-modal dialog windows that are independent of Management Studio's main window.
Each of the component dialogs is dockable and hideable, just as in Visual Studio. A pushpin icon in the top button bar of each window provides an auto-hide option, which you can also reach by right-clicking at the top of the window. Automatically hiding windows can be useful when you need extra screen space for expanding the Object Browser, authoring queries, and so on. You can change the behavior of these dialogs by clicking the Window Position option, a small, triangular down arrow at the top right of each component dialog.
You can activate many other component dialogs from the View menu. The default Registered Servers and Object Explorer component dialogs are most useful for administration. Other component dialogs such as the Solution Explorer, Template Explorer, and Properties dialogs are more useful for query editing and analysis. Management Studio remembers your most recent windows settings and uses your last configuration when you restart the tool. If you want to reset your windows to the default setting, choose Reset Window layout from the Window menu.
Management Studio supports administration and script editing for multiple products: instances of the SQL Server database engine, along with instances of the Analysis Services server, Report Server, Integration Services, and SQL Server Mobile. You can register all these types of services in Management Studio's Register Servers component and manage them using Object Explorer. You can also edit T-SQL scripts, Analysis Services scripts, and SQL Server Mobile scripts and organize them in projects using the Solution Explorer component, as we show in the Query Editing section.
Prev. page
[1]
2
next page 
