With SQL Server Setup, you can create new instances of SQL Server, add components, rebuild the SQL Server registry, uninstall SQL Server, and perform other common setup tasks. Prior to setup and configuration, you need to decide how SQL Server 2005 will be used in your environment. When you have decided on the role SQL Server 2005 will have, you can plan for your deployment and then roll out SQL Server.
SQL Server 2005 is designed as a comprehensive Business Intelligence platform that can be used for:
Extraction, Transformation, and Loading (ETL)
Relational Data warehouses
Multidimensional databases and data mining
SQL Server 2005 Analysis Services
Managed reporting
In SQL Server 2005, Data Transformation Services (DTS) have been renamed SQL Server Integration Services (SSIS) and redesigned to provide a complete enterprise ETL platform that is fully programmable and extensible. Although basic SSIS packages can be created using SQL Server Management Studio, true ETL packages can only be created with the Business Intelligence Development Studio. With the redesigned services, you no longer have to write self-modifying packages. Instead, you should use package variables and the package configuration framework to customize the way the package runs in different circumstances.
This book will use the terms DTS 2000 and SSIS to differentiate between DTS packages designed for SQL Server 2000 and SSIS packages designed for SQL Server 2005. You can use the DTS 2000 Package Migration Wizard to migrate DTS packages designed for SQL Server 2000 to SSIS packages for SQL Server 2005. A DTS 2000 runtime is also provided so that you can run DTS 2000 packages without upgrading them.
SQL Server 2005 continues to provide a best-of-class relational database platform in the tradition of SQL Server 2000. Many new features of SQL Server 2005 fundamentally change the way you perform administration, however. Integration with the .NET Framework enables you to build a new class of database application—one that uses managed code rather than Transact-SQL.
Your managed code can be organized into classes and namespaces for ease of management and maintenance. In many cases, you will find that managed code is better than Transact-SQL at processing numbers, managing complicated execution logic, and manipulating data strings with regular expressions. Transact-SQL remains a good choice to perform data access with little or no procedural logic.
As with Transact-SQL, managed code runs on your SQL Server. This keeps the server functionality and the data close together without requiring an additional layer in your infrastructure. It also allows you to take advantage of the server’s processing power while reducing network traffic between the database servers and the middle tier.
In SQL Server 2005, Analysis Services have been enhanced to provide better support for multidimensional databases and data mining. Analysis Services have two key components: an Online Analytical Processing (OLAP) engine and a data mining engine. You can build an analytic database from any data source, including a relational database. You would then define the analytic structure, the data mining models, and the views into this structure.
SQL Server 2005 Analysis Services uses the Unified Dimension Model (UDM). UDM combines the best features of the relational and OLAP data models, serving to blur the lines between traditional relational databases and multidimensional OLAP databases. A set of cubes and dimensions defined in SQL Server 2005 is referred to as a Unified Dimension Model. This model improves query performance and flexibility.
The Data Definition Language for SQL Server 2005 Analysis Services is XML. Because of this, the metadata repository is removed and replaced by XML files that are stored and managed on the SQL Server 2005 Analysis Services server. Additionally, unlike SQL Server 2000 Analysis Services, SQL Server 2005 Analysis Services performs all calculations on the server rather than on the client. This eliminates the need for client-side caching and can improve query performance for complex calculations. To reduce latency and improve performance, proactive caching is used. The way proactive caching works can be customized so that you can configure how often the cache is rebuilt, how queries are answered while the cache is being rebuilt, whether the cache is automatically refreshed when transactions occur, and control other characteristics of the cache as well.
SQL Server 2005 Reporting Services are designed to help you create a complete solution for creating, distributing, and managing reports. Reporting Services include a set of tools for working with and viewing reports, an engine for hosting and processing reports, and an extensible architecture for integration with existing IT infrastructure. For example, Reporting Services can be integrated easily with Microsoft SharePoint Portal Server so that a SQL Server 2005 Report Server can deliver automatically generated reports to a SharePoint portal.
As an administrator, you can use the Report Server Web Application to:
Define role-based security for reports.
Schedule report generation and delivery.
Track reporting history.
Reports can be delivered in a variety of ways and formats. You can configure Reporting Services to deliver reports to a portal on a SharePoint Portal Server, send reports by e-mail to users, or allow users to access reports on the Web-based report server. Reports can be created in HTML, PDF, TIFF, Excel, XML, and CSV, as well as other formats. HTML reports are ideal for viewing on the Web. Adobe PDF and TIFF are good formats to use for reports that will be printed. Excel, XML, or CSV reports work well if the data in a report needs to be stored in a database or if the user needs to manipulate the report data.