Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)

The basic installation process is the same whether you are upgrading, migrating, or creating a new SQL Server 2005 instance. Regardless of what components you previously installed, when you run setup for SQL Server 2005, you'll need to choose which components you want to include with the installation. (See Figure 1-1 for the list.) I am not running on a Windows cluster, so the option to install a SQL Server failover cluster is not available. Also, the Virtual PC where I am installing SQL Server 2005 does not have IIS installed, and that is a prerequisite for Reporting Services, so the option to install Reporting Services is also grayed out.

Following is the list of all the optional components you can select for installation or upgrade. Note that the database engine itself does not have to be selected. You can choose to simply install the client components, Analysis Services, or another combination of SQL Server 2005 components.

• SQL Server Database Services (Database Engine)

• Analysis Services

• Reporting Services

• Notification Services

• Integration Services

• Workstation components, Books Online, and development tools

Depending on which of these components you select, additional choices will be available on the subsequent screens.

SQL Server Database Services (Database Engine)

The database engine is the core service for storing, processing, and securing data. It provides controlled access and rapid transaction processing to meet the requirements of the most demanding data-consuming applications in your enterprise.

The core database engine is the main focus of this book. If you choose Advanced Options from the main component screen, you'll see that you can select some additional items to include as part of the database engine.

• Replication Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. By using replication, you can distribute data to different locations and to remote or mobile users by means of local and wide area networks, dial-up connections, wireless connections, and the Internet.

• Full-text search Full-text search lets you issue full-text queries against plain character-based data in SQL Server tables. Full-text queries can include words and phrases or multiple forms of a word or a phrase.

• Other options The database engine also provides additional support for sustaining high availability using failover clustering. If you are installing on a system that already has MSCS installed, you will see a dialog box for specifying the Virtual SQL Server Name right after the dialog box that asks for your instance name. Although installing SQL Server 2005 on a cluster is straightforward, the internals of clustering and the issues involved with configuring your machine and managing your cluster are beyond the scope of this book.

Analysis Services

Analysis Services delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by allowing you to design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. For data mining applications, Analysis Services enables you to design, create, and visualize data mining models. You can construct these mining models from other data sources by using a wide variety of industry-standard data mining algorithms.

Reporting Services

Reporting Services delivers enterprise, Web-enabled reporting functionality so you can create reports that draw content from a variety of data sources, publish reports in various formats, and centrally manage security and subscriptions.

Notification Services

Notification Services is an environment for developing and deploying applications that generate and send notifications. You can use Notification Services to generate and send timely, personalized messages to thousands or millions of subscribers, and you can deliver the messages to a variety of devices.

Integration Services

Integration Services is a platform for building high-performance data integration solutions, including packages that provide extract, transform, and load (ETL) processing for data warehousing. SQL Server Integration Services in SQL Server 2005 replaces SQL Server 2000 Data Transformation Services (DTS).

Workstation Components, Books Online, and Development Tools

The Workstation Components, Books Online, and Development Tools selection offers many options. You'll definitely want to install the management tools and SQL Server Books Online. Most of the other optional components don't take up too much disk space, so for a test server, I suggest installing them all until you can determine whether you need them. You will also have a chance to choose which sample applications you want; you should choose based on the components you are installing and testing. For example, if you are not installing Analysis Services, you need not install the sample applications for Analysis Services. I also suggest installing the sample database called AdventureWorks, at least on a test server. Many of the examples in Books Online refer to this sample database. Note that there is also a sample database for Analysis Services called AdventureWorksDW. Do not use that one for the examples in this book. You'll also find that many of the articles written about SQL Server, as well as many books (including this one), use examples that include sample data from the AdventureWorks database.

If you're upgrading from SQL Server 7.0 or SQL Server 2000, one of the most important changes you'll notice about the client tools is that the two graphical tools from the earlier versions, SQL Enterprise Manager and SQL Query Analyzer, have been combined into a single tool called SQL Server Management Studio. This tool allows you to create, test, and analyze queries, and it also allows you to manage all the administrative tasks such as creating jobs and alerts, taking or scheduling backups, managing your metadata and scripts, and monitoring any number of SQL Server instances. One reason that the development and administrative tasks were combined into a single tool is that many people who work with SQL Server are responsible for both those jobs. SQL Server developers often oversee aspects of database management, and many database administrators have to develop, test, and tune queries. The Management Studio tool gives you one tool for all your direct access to SQL Server.

If you prefer a query tool with a much smaller footprint, either for testing queries or running commands to troubleshoot problems, you can use another client tool: SQLCMD. SQLCMD is a command-line tool that is intended to replace osql. It allows you to run pre-created scripts and capture the output, or work interactively, submitting single batches and then inspecting the results. SQLCMD is much richer than osql because it allows full parameterization of any pre-created scripts.

Both SQL Server Management Studio and SQLCMD provide an option to connect to SQL Server 2005 using a special reserved connection called Dedicated Administrator Connection, or DAC. This connection is guaranteed to be always available, even if misuse of server resources doesn't allow any other connections to SQL Server to be opened. I'll tell you more about DAC in the next chapter, when I talk about SQL Server architecture.