SQL Server Overview

Microsoft SQL Server 2005 is a database platform for large-scale online transaction processing (OLTP), data warehousing, and e-commerce applications; it is also a business intelligence platform for data integration, analysis, and reporting solutions. Components in SQL Server 2005: Database Engine Reporting Services Analysis Services Notification Services Integration Services Full-Text Search Replication Service Broker

The diagram shows the relationships among SQL Server 2005 components and identifies interoperability between components.

Security Note:
SQL Server 2005 includes a variety of highly precise, configurable security features. These features can empower administrators to implement defense-in-depth that is optimized for the specific security risks of their environment. To learn about security features, see Security Considerations for SQL Server.

SQL Server 2005 introduces "studios" to help you with development and management tasks: SQL Server Management Studio and Business Intelligence Development Studio. In Management Studio, you develop and manage SQL Server Database Engine and notification solutions, manage deployed Analysis Services solutions, manage and run Integration Services packages, and manage report servers and Reporting Services reports and report models. In BI Development Studio, you develop business intelligence solutions using Analysis Services projects to develop cubes, dimensions, and mining structures; Reporting Services projects to create reports; the Report Model project to define models for reports; and Integration Services projects to create packages.

Both of these studios are closely integrated with Microsoft Visual Studio and the Microsoft Office System. For more information, see Introducing SQL Server Management Studio and Introducing Business Intelligence Development Studio.

In the studios, SQL Server 2005 provides the graphical tools you need to design, develop, deploy, and administer relational databases, analytic objects, data transformation packages, replication topologies, reporting servers and reports, and notification servers. Additionally, SQL Server 2005 includes command prompt utilities to perform administrative tasks from the command prompt. To quickly get to important high-level topics for tools and utilities, go to Tools and Utilities Documentation Map.

SQL Server 2005 provides a number of ways to submit feedback about the product and the documentation, as well as ways to send error reports and feature usage data automatically to Microsoft. To learn how you can provide feedback, go to Providing Feedback on SQL Server 2005.

Database Engine

The Database Engine is the core service for storing, processing, and securing data. The Database Engine provides controlled access and rapid transaction processing to meet the requirements of the most demanding data consuming applications within your enterprise.

Use the Database Engine to create relational databases for online transaction processing or online analytical processing data. This includes creating tables for storing data, and database objects such as indexes, views, and stored procedures for viewing, managing, and securing data. You can use SQL Server Management Studio to manage the database objects, and SQL Server Profiler for capturing server events.

To learn more about the Database Engine, see SQL Server Database Engine and SQL Server Tools Tutorials.

To quickly get to important high-level topics for the Database Engine, go to Database Engine Documentation Map.

Analysis Services

Analysis Services is the core service for supporting rapid analysis of business data, delivering online analytical processing (OLAP) and data mining functionality in business intelligence applications.

OLAP

Analysis Services allows you to design, create, and manage multidimensional structures that contain detail and aggregated data from multiple data sources, such as relational databases, in a single unified logical model supported by built-in calculations. Analysis Services provides fast, intuitive, top-down analysis of large quantities of data built on this unified data model, which can be delivered to users in multiple languages and currencies. Analysis Services works with data warehouses, data marts, production databases and operational data stores, supporting analysis of both historical and real time data.

To learn more about OLAP, see SQL Server Analysis Services and Analysis Services Tutorials.

To quickly get to important high-level topics for OLAP, go to Analysis Services Documentation Map.

Data Mining

Analysis Services contains the features and tools you need to create complex data mining solutions.

• A set of industry-standard data mining algorithms.
• The Data Mining Designer, which you can use to create, manage, explore, and create predictions from mining models.
• The DMX language, which you can use to manage mining models and to create complex prediction queries.

You can use a combination of these features and tools to discover trends and patterns that exist in your data, and then use the trends and patterns to make intelligent decisions about difficult business problems.

To learn more about data mining, see SQL Server Analysis Services and Data Mining Tutorials.

To quickly get to important high-level topics for data mining, go to Analysis Services Documentation Map.

Integration Services

SQL Server 2005 Integration Services (SSIS) is the extract, transform, and load (ETL) component of SQL Server 2005. It replaces the earlier SQL Server ETL component, Data Transformation Services (DTS).

Integration Services is a platform for building enterprise-level data integration and data transformations solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. The packages can work alone or in concert with other packages to address complex business needs. Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.

Integration Services includes a rich set of built-in tasks and transformations; tools for constructing packages; and the Integration Services service for running and managing packages. You can use the graphical Integration Services tools to create solutions without writing a single line of code; or you can program the extensive Integration Services object model to create packages programmatically and code custom tasks and other package objects.

To learn more about Integration Services, see SQL Server Integration Services and Integration Services Tutorials.

To quickly get to important high-level topics for Integration Services, go to Integration Services Documentation Map.

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. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet. SQL Server provides three types of replication, each with different capabilities: transactional replication, merge replication, and snapshot replication.

Transactional replication is typically used in server-to-server scenarios that require high throughput, including: improving scalability and availability; data warehousing and reporting; integrating data from multiple sites; integrating heterogeneous data; and offloading batch processing. Merge replication is primarily designed for mobile applications or distributed server applications that have possible data conflicts. Common scenarios include: exchanging data with mobile users; consumer point of sale (POS) applications; and integration of data from multiple sites. Snapshot replication is used to provide the initial data set for transactional and merge replication; it can also be used when complete refreshes of data are appropriate. With these three types of replication, SQL Server provides a powerful and flexible system for synchronizing data across your enterprise.

To learn more about replication, see SQL Server Replication.

To quickly get to important high-level topics for replication, go to Replication Documentation Map.

Reporting Services

SQL Server 2005 Reporting Services (SSRS) is a server-based reporting platform that provides comprehensive data reporting from relational and multidimensional data sources. Reporting Services includes processing components, a complete set of tools that you can use to create and manage reports, and an application programming interface (API) that allows developers to integrate or extend data and report processing in custom applications. The reports that you build can be based on relational or multidimensional data from SQL Server, Analysis Services, Oracle, or any Microsoft .NET Framework data provider, such as ODBC or OLE DB.

With Reporting Services, you can create interactive, tabular, or free-form reports that retrieve data at scheduled intervals or on-demand when the user opens a report. Reporting Services also enables users to create ad hoc reports based on predefined models, and to interactively explore data within the model. All reports can be rendered in both desktop and Web-oriented formats. You can choose from a variety of viewing formats to render reports on demand in preferred formats for data manipulation or printing.

Reporting Services is a server-based solution, and thus provides a way to centralize report storage and management, provide secure access to reports, models, and folders, control how reports are processed and distributed, and standardize how reports are used in your business.

To learn more about Reporting Services, see SQL Server Reporting Services and Reporting Services Tutorials.

To quickly get to important high-level topics for Reporting Services, go to Reporting Services Documentation Map.

Notification Services

SQL Server 2005 Notification Services is a platform for developing applications that generate and send notifications, and it is also an engine that runs those applications. You can use Notification Services to generate and send timely, personalized messages to thousands or even millions of subscribers, and deliver the messages to a wide variety of applications and devices.

The Notification Services platform enables the development of rich notification applications. Subscriptions, which express subscribers' interest in specific information (called events), can be evaluated based on the arrival of events or based on a schedule. The event data itself can originate from within the database, from other databases, or from external sources. Notifications, which result from the matching of events and subscriptions, can be richly formatted before being sent to the subscriber.

The Notification Services engine works in concert with the SQL Server Database Engine. The Database Engine stores the application data and performs the matching between events and subscriptions. The Notification Services engine controls the flow and processing of data, and can be scaled-out across multiple computers. This can improve the performance of very large and demanding applications.

To learn more about Notification Services, see SQL Server Notification Services and the Notification Services Tutorial.

To quickly get to important high-level topics for Notification Services, go to Notification Services Documentation Map.

Full-Text Search

SQL Server contains the functionality you need to issue full-text queries against plain character-based data in SQL Server tables. Full-text queries could include words and phrases or multiple forms of a word or phrase. Full-Text Search allows fast and flexible indexing for keyword-based query of text data stored in a Microsoft SQL Server database. In SQL Server 2005, Full-Text Search delivers enterprise-level search functionality.

Use Full-Text Search to search for plain, character-based data, in multiple fields in multiple tables at the same time. The performance benefit of using Full-Text Search can be best realized when querying against a large amount of unstructured text data. For example, a Transact-SQL LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query may take only seconds or less against the same data, depending on the number of rows that are returned. You can build full-text indexes on data stored in a char, varchar or nvarchar column or formatted binary data, such as Microsoft Word documents, stored in a varbinary(max) or image column.

To learn more about Full-Text Search, see Full-Text Search.

To quickly get to important high-level topics for Full-Text Search, go to Full-Text Search Documentation Map.

Service Broker

SQL Server 2005 Service Broker provides the SQL Server Database Engine native support for messaging and queuing applications. This makes it easier for developers to create sophisticated applications that use the Database Engine components to communicate between disparate databases. Developers can use Service Broker to easily build distributed and reliable applications.

Application developers who use Service Broker can distribute data workloads across several databases without programming complicated communication and messaging internals. This reduces development and test work because Service Broker handles the communication paths within the context of a conversation. It also improves performance. For example, front-end databases supporting Web sites can record information and send process intensive tasks to queue in back-end databases. Service Broker ensures that all tasks are managed in the context of transactions to ensure reliability and technical consistency.

To learn more about Service Broker, see Service Broker.

To quickly get to important high-level topics for Service Broker, go to Service Broker Documentation Map.