SQL Server 2005 Tools


All versions of SQL Server 2005 (except SQL Server 2005 Express) are delivered with this set of management tools:

  • SQL Server Configuration Manager

  • SQL Server Management Studio

  • Database Engine Tuning Advisor

  • DTS Import and Export Wizard

  • SQL Server Profiler

  • SQL Server Books OnLine (BOL)

  • Replication Monitor (sqlmonitor.exe)

  • SQLCMD

  • osql

  • SQL Server Error and Usage Reporting

  • SQL Server Surface Area Configuration

There are also many tools that are not related to the relational database engine:

  • Business Intelligence Development Studio

  • Analysis Services | Deployment Wizard

  • Analysis Services | Migration Wizard

  • Analysis Services | Instance Rename

  • Notification Services Command Prompt

  • Reporting Services Configuration

The following sections discuss some of these tools.

SQL Server Configuration Manager

SQL Server Configuration Manager is a new tool in SQL Server 2005. You can use it to manage SQL Server 2005 services and connections. It has been developed as a Microsoft Management Console (MMC) plug-in application (see Figure 1-1), so we can safely ignore its trivial menu and toolbar content. Its window is divided into a Console tree (left pane) and a Details pane. You can manage SQL Server services and connection configurations by navigating objects in the Console tree.

image from book
Figure 1-1: SQL Server Configuration Manager

Managing Services

SQL Server 2005 is implemented as the following services:

  • SQL Server

  • SQL Server Agent

  • SQL Server Browser

  • SQL Server Integration Services

  • SQL Server Analysis Services

  • SQL Server FullText Search

  • Reporting Services

There are also several support services that can be reached through SQL Server Management Studio and Business Intelligence Studio, such as

  • Notification Services

  • Distributed Transaction Coordinator (MSDTC)

  • Database Mail

The relational database server is implemented as the SQL Server service. It receives queries from users, executes them, sends responses to calling applications, and manages data in database files. Most of this book is dedicated to programming and interaction with this service.

SQL Server Agent is an automation service that manages the scheduled execution of tasks and notifies administrators of problems that occur on the server.

Analysis Services is a service that implements infrastructure for OnLine Analytical Processing (OLAP) and data mining.

Reporting Services is a system for design and delivery of relational and OLAP reports that works on top of SQL Server and IIS.

FullText Search allows users to create word-oriented queries against string data stored in relational table columns.

Notification Services is a platform for alerting users to changes in databases.

The Distributed Transaction Coordinator (MSDTC) is a service that manages two-phase commit transactions spanned over multiple servers. This service ensures that changes that need to be made to data stored on different servers complete successfully.

Integration Services provide ETL (extract, transform, and load) functionality that replaces Data Transformation Services (DTS) from previous versions of SQL Server. Users can create packages for extracting, transforming, and loading data from one data source to another.

Database Mail is used to send and receive e-mail. It is possible to configure SQL Server to perform tasks such as receiving requests and returning result sets through e-mail to notify administrators of the success status of scheduled tasks and of errors encountered.

Services listed in SQL Server Configuration Manager can be started, stopped, and paused using the pop-up menu that opens when you right-click the particular service (that is, on each node of the tree). The SQL Server service must be running if you want to work on relational databases as described in this book.

Managing Connections

SQL Server and client tools can use different protocols to communicate with SQL Server:

  • Shared Memory

  • Named Pipes

  • TCP/IP

  • VIA

Shared Memory is a protocol designed to allow users to connect only locally—from the machine on which SQL Server is running. After installation, it is possible to communicate with SQL Server using only Shared Memory. TCP/IP and Named Pipes are protocols designed to allow users to access and manage SQL Server from networked workstations. The VIA protocol is used on legacy (mainframe) networks.

From SQL Server Configuration Manager, you can control how your server connects to the network using SQL Server 2005 Network Configuration. This node contains separate subnodes for each instance of the database server (see Figure 1-2).

image from book
Figure 1-2: Network configuration in SQL Server Configuration Manager

Note 

Unless your network administrator tells you differently, you should probably enable the TCP/IP and Named Pipes protocols to allow users on the LAN to connect to it. However, if you are installing SQL Server on a computer that is connected directly to the Internet and which will typically be used by clients installed locally (as would be the case on your home computer), you should accept the default network configuration (only Shared Memory enabled).

In SQL Native Client Configuration | Client Protocols node you can set how client programs on the local machine (such as Management Studio or SQLCMD) connect to servers. In SQL Native Client Configuration | Aliases node you can set new names (aliases, pseudonyms) and connection parameters for servers on the network.

SQL Server Management Studio

This tool, shown in Figure 1-3, is a new feature in SQL Server 2005. It replaces Enterprise Manager and Query Analyzer from earlier versions. It has been developed using a Visual Studio shell as a base.

image from book
Figure 1-3: SQL Server Management Studio

It follows the paradigm of Visual Studio, in which most tools are organized as tabbed, dockable, or floating windows.

The Registered Servers pane lets you view and manage parameters for connecting to your servers. Its toolbar allows you to select a server (service) type—SQL Server, Analysis Services, Reporting Services, SQL Server Mobile, or Integration Services. This book focuses on using SQL Server.

Note 

When you install SQL Server, you can connect only to local servers. You need to add (register) other servers to see them listed here.

The Object Explorer pane displays database and server objects in a hierarchy designed for easy navigation (see Figure 1-4). It works in the same way as any other GUI tree object. You can click the + symbol next to any node on the tree or press the RIGHT ARROW key on the keyboard to expand the node. When you right-click a node, a context-sensitive menu is displayed. Unlike previous versions, system objects (such as databases, tables, and stored procedures) are grouped together in a special folder and not mixed with user-defined objects. Users can check for the existence of a database object; explore its contents (that is, view records in a table); execute and debug objects such as stored procedures; view the structure and dependencies of an object; view and edit extended properties of the object; and drag the name of a database object to the Query window or create a script of an object in a new Query window, file, or Clipboard.

image from book
Figure 1-4: Object Explorer

The Summary page shows details of the node (object) selected in the Console tree. If the user selects a folder with tables or stored procedures, the Summary page lists the tables or stored procedures in the current database and shows other significant information related to selected objects.

When you open the Properties sheet of an object or start some operation that requires additional information, Management Studio opens a window that is resizable and nonmodal (see Figure 1-5). Users can switch back to the Object Browser and/or open another window to get additional information needed to complete the first window. When required information is entered, you can choose to execute the change immediately, schedule it for later, or save the script file that will be used to implement the change later.

image from book
Figure 1-5: Resizable, scriptable, nonmodal window

SQL Server Management Studio supports relational (SQL), Analysis Services (MDX, DMX, XMLA), and SQL Mobile queries. When you open a Query window (see Figure 1-6), you can type in the query and execute it (Query | Execute).

image from book
Figure 1-6: Script and project management in Management Studio

If you need assistance with your SQL queries you can choose Query | Design Query in Editor and Management Studio will open a Query Designer window with panes for adding and joining tables and selecting output columns.

Management Studio treats queries the same way Visual Studio treats other documents with source code—they can be saved as script files and organized within projects and solutions. Script files are central objects for designing, debugging, and executing Transact-SQL (T-SQL) statements (such as queries) against a SQL Server database.

Template Explorer is a window with a useful set of script templates for managing how database and server objects are organized (see Figure 1-7). You just drag a template to your script window and replace parameter placeholders with your custom values. Placeholders have < and > as delimiters and name, data type, and default values as parameters. Alternatively, you can choose Query | Specify Values for Template Parameters and Management Studio will prompt you to set them.

image from book
Figure 1-7: Template Explorer

Tip 

If the parameter that you need is a database object, you can also drag it from the Object Browser to a query or script, instead of typing it.

SQLCMD Utility

Before graphical utilities (such as Management Studio in SQL Server 2005 and Query Analyzer and ISQL for Windows in earlier SQL Server versions), DBAs used command line utilities such as isql and osql to run Transact-SQL statements against the server. Such tools are reminiscent of UNIX environments and they are seldom used now that GUI applications, such as Management Studio, are available.

SQLCMD uses OLE DB to execute Transact-SQL batches, while Management Studio uses .NET SqlClient. This difference sometimes leads to different behavior when the same scripts are executed in them.

Figure 1-8 shows the utility's parameters, as well as a couple of Transact-SQL batch commands.

image from book
Figure 1-8: SQL CMI command line utility

SQL Server Profiler

Profiler is a component of SQL Server designed to monitor activities on servers and in databases (see Figure 1-9).

image from book
Figure 1-9: SQL Server Profiler

You can use this utility to capture queries against a database, the activities of a particular user application, login attempts, failures, errors, and transactions. It is often used to improve the performance of a system, and you can also use it to troubleshoot and debug stored procedures and Transact-SQL scripts.

The Help Subsystem and SQL Server Books On Line

Traditionally, due to the nature of the environment, SQL Server client tools have been light on context-sensitive help, but SQL Server has a subsystem that is a great tool for browsing through its documentation—SQL Server Books OnLine (BOL). This subsystem contains the complete set of SQL Server Reference documents—which used to be delivered on paper—in the form of an online, searchable, and indexed hierarchy of documents.

You can start SQL Server Books OnLine from Start | Programs | Microsoft SQL Server 2005 | Documentation and Tutorials. You can also launch it using Management Studio's Dynamic Help feature: Just highlight a keyword in a query and select the Help | Dynamic Help menu to open the pane.

In the Contents tab, you can browse through the hierarchy of the material, as in Windows Explorer, or you can switch to either the Index tab to see a list of keywords or to the Search tab to define search criteria. The Help Favorites tab enables you to bookmark pages that you want to refer to later.




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net