Booknews
This six-volume set concentrates all the essential SQL Server 2000 reference materials. focuses on how to create and maintain databases, create data warehouses, and optimize SQL Server 2000. provides an overview and details about SQL Server 2000 analysis servers, including how to install all the services. tells how to plan for and implement replication and offers complete information about replication types, tools and options, data considerations and heterogeneous sources, monitoring, security, enhancing performance, and backing up and restoring, as well as the English-language query services. gives esential programmatic information about the T-SQL language. presents vital programmatic information about how to use the new stored procedures. Finally, describes the feature set and analyzes SQL Server 2000 architecture. Edited by Iseminger, an independent consultant at Microsoft. Annotation c. Book News, Inc., Portland, OR (booknews.com)
Microsoft SQL Server 2000 Reference Library
by David Iseminger, Microsoft Corporation Staff
This reference library provides the ideal set of reference materials for programming SQL Server applications-direct from the SQL Server development group. This well-conceived, completely indexed series of volumes sensibly organizes and condenses the vast sea of available SQL Server technical reference information.See more details below
Overview
This reference library provides the ideal set of reference materials for programming SQL Server applications-direct from the SQL Server development group. This well-conceived, completely indexed series of volumes sensibly organizes and condenses the vast sea of available SQL Server technical reference information.
Editorial Reviews
Product Details
- ISBN-13:
- 9780735612808
- Publisher:
- Microsoft Press
- Publication date:
- 12/29/2000
- Edition description:
- BK & DVD
- Pages:
- 3600
- Product dimensions:
- 7.73(w) x 9.81(h) x 9.82(d)
- Age Range:
- 13 Years
Read an Excerpt
Chapter 2.
Installing Analysis Services
- Hardware and Software Requirements for Installing Analysis Services
- Running Setup
- Setup Parameters and Silent Installation
- Reinstalling Analysis Services
- Stopping or Removing Analysis Services
- Upgrading from an Earlier Version
- Backward Compatibility
- 7.0 Analysis Services Client and Local Cube Support
- Supported Migration Paths for Analysis Services Repositories
- Archiving and Restoring Databases Between Versions of Analysis Services
Chapter 2 Installing Analysis Services
This chapter contains information about installing Microsoft SQL Server 2000 Analysis Services only. This chapter contains the following topics:
| Topic | Description |
| Hardware and Software Requirements for Installing Analysis Services | Provides the hardware and software requirements for installing and running Analysis Services. |
| Running Setup | Provides step-by-step instructions to install Analysis Services. |
| Setup Parameters and Silent Installation | Describes the parameters for the Analysis Services Setup program (Setup.exe). |
| Reinstalling Analysis Services | Describes how to reinstall Analysis Services. |
| Removing Analysis Services | Describes how to remove Analysis Services. |
| Upgrading from an Earlier Version | Describes how to upgrade from an earlier version of Analysis Services. |
| Backward Compatibility | Provides information about compatibility with previous versions of Analysis Services (formerly called OLAP Services). |
Related Documents
The Readme.html file in the root directory of the SQL Server 2000 CD-ROM contains information about Analysis Services. You can also view the release notes by clicking Read the Release Notes on the SQL Server 2000 Setup program (Autorun.exe) menu.
Hardware and Software Requirements for Installing Analysis Services
Before you can install Microsoft SQL Server 2000 Analysis Services, your computer must meet the following requirements.
| Hardware/software | Requirements |
| Computer | Intel or compatible (Pentium 133 MHz or higher, Pentium PRO, Pentium II, or Pentium III) |
| Memory (RAM) | 32 megabytes (MB) minimum (64 MB recommended) |
| Disk drive | CD-ROM drive |
| Hard disk space (1) | 50�90 MB (130 MB for all components including common files and samples), 12 MB for the client only |
| Operating system | Microsoft Windows 2000 Server (3) -or- Microsoft Windows NT Server 4.0 with Service Pack 5 or later (3) For client components on client computers only, the following systems also qualify: Windows 2000 Professional Windows NT Workstation 4.0 with Service Pack 5 Windows 98 Windows 95 + DCOM95 Windows 95 OSR2 + DCOM95 |
| Network software | Windows 2000, Windows NT 4.0, Windows 98, or Windows 95 built-in network software; and TCP/IP (included with Windows). |
| Online product documentation viewer | Microsoft Internet Explorer version 5.0 or later (2). You must install Windows NT 4.0 Service Pack 5 or later before you install Internet Explorer version 5.0. |
| Access permissions | To install the services for Analysis server, you must be logged on to the server with Administrator permissions. |
- Setup installs a number of components that can be shared by other applications and may already exist on the computer.
- Internet Explorer is required for Microsoft Management Console (MMC) and HTML Help. A minimal installation is sufficient, and Internet Explorer does not need to be your default browser. Internet Explorer is not required for the client-only installation.
- Analysis Services should not be installed on a domain controller; this installation configuration is not supported.
For more information about supported hardware, see the Microsoft Windows Hardware Compatibility List at www.microsoft.com/hcl/default.asp. For more information about Windows 2000-compatible hardware, use the Microsoft Windows 2000 compatible hardware devices search tool at www.microsoft.com/windows2000/upgrade/compat/search/devices.asp.
This topic describes how to install Microsoft SQL Server 2000 Analysis Services.
If you are upgrading from an earlier version of Analysis Services (formerly called OLAP Services), you should take certain steps before performing the following procedure. For more information, see Upgrading from an Earlier Version.
If you are reinstalling Analysis Services, you should take certain steps before and after performing the following procedure. For more information, see Reinstalling Analysis Services.
Although Analysis Services can connect to multiple instances of SQL Server running on a single computer, you cannot install multiple instances of Analysis Services on a single computer.
To install Analysis Services, use the Analysis Services Setup program or the SQL Server 2000 Setup program.
To install Analysis Services
- Exit all Microsoft Windows applications.
- Insert the SQL Server 2000 CD into the CD-ROM drive. This starts the SQL Server 2000 Setup program. If the Setup program does not start automatically, run the Autorun.exe program in the root directory of the CD-ROM.
- Click Install SQL Server 2000 Components.
- Click Analysis Services to start the Analysis Services Setup program.
- In the Welcome step, click Next.
- In the Software License Agreement step, read the license agreement, and then do one of the following:
- To accept the license agreement, click Yes. You must select this option to install Analysis Services.
- To reject the license agreement, click No. If you select this option, the program will ask you to confirm exiting. If you select Exit Setup, the program closes and the installation is canceled. To continue Setup, click Resume.
- The Setup program prompts you to enter the CD key. Type the 10-digit CD key for the product, and then click OK.
- The Setup program displays the complete product ID, which you can record for future reference. After you record the product ID, click OK.
- In the Select Components step, select the components you want to install. All of the options are selected by default. You cannot clear the check box of any component on which another selected component depends.
- In the Data Folder Location step, you can change the location of the Data folder, which is the data storage location of the Analysis server.
- In the Select Program Folder step, accept the default program folder name or enter a new one. This determines the location of the Analysis Services menu items on the Start menu. Click Next.
- Analysis Services installation begins. After Setup notifies you that the installation is complete, click Finish.
- If you are prompted to restart your computer, do one of the following:
- Click Yes, I want to restart my computer now, and then click Finish.
- Click No, I will restart my computer later, and then click Finish. If you select this option, the installation is not complete until after you restart the computer.
- If you are finished installing SQL Server 2000 components, click Exit in the SQL Server 2000 Setup program.
Unless you are installing the client components on a client computer, installing all components is recommended. The following components are available for installation.
| Component | Description |
| Analysis server | Binary executables and other server-related files required for an installation of an Analysis server. Includes the FoodMart 2000 sample database used by the tutorial. Requires the client components. |
| Analysis Manager | Binary executables and other files that support the user interface for administering the Analysis server. Includes the MDXSample executable file. Requires Decision Support Objects (DSO) and the client components. |
| Decision Support Objects | The object model for administering the Analysis server and managing meta data. Requires the client components. |
| Client components | Binary executables and related files for the Analysis Services client. Client components include PivotTable Service. |
| Sample applications | Sample applications include the MDXSample source files, the FoodMart 2000 database, and programming samples. Requires the client components. |
| Books Online | The entire documentation set for SQL Server 2000, including Analysis Services. This file is approximately 30 megabytes (MB). If space is at a premium, you can choose not to install Books Online. However, product documentation will not be available in the user interface until it is reinstalled. |
To change the destination drive or folder, click Browse. Although remote network drives are listed in these dialog boxes, installation to locations on remote network drives is not supported.
Space Required and Space Available indicate disk drive space and help you determine what components to install. If your current disk drive does not have enough space available, you can click Disk Space to determine which disks on your computer have enough space to install Analysis Services.
After you select the components to install, click Next. The steps that follow may change depending on which components you selected to install.
The default location for the Data folder is C:\Program Files\Microsoft Analysis Services\Data (unless you specified another location for Analysis Services in the previous step). You can specify a different location by clicking Browse. If you change the default folder or drive, be sure to enter a fully qualified path. To specify a data storage location other than the computer on which the server is installed, you must have full control access permissions on that computer.
IMPORTANT:
The Data folder stores security files that control end users� access to objects on the Analysis server. For this reason, the Data folder must be secured against unauthorized access.
After you select the location of the Data folder, click Next.
If in Step 10 you specified a data storage location other than the computer on which the server is installed, you must configure your Analysis server service (MSSQLServerOLAPService) to log on as your user account, instead of the default, which is to log on as the system account. To do this, use the Services application, which is in Control Panel in Windows NT 4.0 or the Administrative Tools folder in Control Panel in Windows 2000.
Setup Parameters and Silent Installation
You can start the Analysis Services Setup program (\Msolap\Install\Setup.exe on the SQL Server CD-ROM) with the following optional command line parameters:
-r
This option causes Setup.exe to automatically generate a silent response file (.iss), which is a record of the installation input, in the systemroot folder (typically C:\WinNT).
-s
This option performs a silent (unattended) installation.
-f1<path\ResponseFile>
This option allows you to specify the alternate location and name of the response file (.iss file). If the -f1 switch is not used when you run silent installation, Setup searches for the response file Setup.iss in the same folder as Setup.exe.
-f2<path\LogFile>
This option allows you to specify an alternate location and name of the log file. By default, the Setup.log log file is created and stored in the systemroot folder (typically C:\Winnt).
If you use the -r option you can create a record of any installation scenario. You can use this record to perform a silent (unattended) installation. For example, the following command initiates a silent installation of the components specified in the Setup.iss response file previously recorded when you used the -r option:
Setup.exe -s -f1C:\temp\setup.iss-z
Prevents Setup.exe from checking the available memory during initialization. This switch is necessary when running Setup on a computer with more than 256 megabytes (MB) of memory. If it is not used, Setup.exe reports insufficient memory and exits.
Reinstalling Analysis Services
To reinstall Microsoft SQL Server 2000 Analysis Services, follow these steps:
- If you have made changes to the FoodMart 2000 sample database and want to preserve changes, back up FoodMart2000.mdb, which is installed by default to: C:\Program Files\Microsoft Analysis Services\Samples. Otherwise, this file is overwritten during the installation process.
- Install Analysis Services. For more information, see Running Setup.
NOTE:
Reinstalling Analysis Services does not delete the Analysis Services repository (Msmdrep.mdb), which contains Analysis Services meta data. However, you must process all cubes in the repository after reinstallation. If you have backed up the FoodMart 2000 sample database before reinstallation, restore FoodMart2000.mdb to recover your changes to the file.
Stopping or Removing Analysis Services
To stop Microsoft SQL Server 2000 Analysis Services, follow these steps:
- Open Control Panel.
- If your computer�s operating system is Windows 2000, open the Administrative Tools folder, and then double-click Services.
- Select MSSQLServerOLAPService, and then on the Action menu click Stop.
- Wait until the application notifies you that the service has stopped.
If your computer�s operating system is Windows NT 4.0, double-click Services.
To remove Analysis Services, use the Add/Remove Programs application in Control Panel. Removing Analysis Services does not delete the Analysis Services repository (Msmdrep.mdb), which contains Analysis Services meta data, or the query log (Msmdqlog.mdb). If you want to fully remove Analysis Services, you must delete these files manually.
Upgrading from an Earlier Version
To upgrade from an earlier version of Microsoft SQL Server 2000 Analysis Services (previously OLAP Services), perform the following actions:
- Back up the Analysis Services repository and query log.
- Before you install Analysis Services, as a precaution against data loss, back up the Analysis Services repository (Msmdrep.mdb), which contains Analysis Services meta data, and the query log (Msmdqlog.mdb). These files are located in the Bin folder in the Analysis Services folder.Run Setup.
Install Analysis Services by running the Analysis Services Setup program. For more information, see Running Setup.
When you upgrade from an earlier version, Setup does not delete or replace the Analysis Services repository or the query log.
NOTE:
The default location for Analysis Services has changed from C:\Program Files\OLAP Services in earlier versions of Analysis Services to C:\Program Files\Microsoft Analysis Services in this version of Analysis Services.
Microsoft SQL Server 2000 Analysis Services is compatible with SQL Server version 7.0 OLAP Services. Cubes that were created in SQL Server 7.0 OLAP Services need to be migrated to the updated meta data repository format and reprocessed. Otherwise, the existing structures for cubes, roles, shared dimensions, and so on do not need to be changed. For more information about migrating the SQL Server 7.0 OLAP Services repository to SQL Server 2000 Meta Data Services, see Migrating Analysis Services Repositories.
The following sections concern backward compatibility with SQL Server 7.0 OLAP Services.
Administration of Analysis Services
Analysis Manager is backward compatible with SQL Server 7.0 OLAP Services. It is capable of administering both OLAP servers (the server that ships with SQL Server 7.0 OLAP Services), and Analysis servers (the server that ships with SQL Server 2000 Analysis Services) concurrently. When administering an OLAP server, the OLAP Services portion of SQL Server 7.0 Service Pack 2 code is used to assure complete backward compatibility. The add-in programs in Service Pack 2 are now integrated with Analysis Manager and do not need to be installed to administer OLAP servers.
Client and Local Cube Support
Some features in SQL Server 2000 Analysis Services are not supported by the SQL Server 7.0 OLAP Services client components or in a local cube. For more information, including a list of features, see 7.0 Analysis Services Client and Local Cube Support.
Decision Support Objects
Analysis Services now includes an updated version of Decision Support Objects (DSO), which is automatically installed during Setup. Programs must use this updated version of DSO when administering an Analysis server (the server that ships with SQL Server 2000 Analysis Services). No other change to these programs is necessary. Programs that use the updated version of DSO are compatible with and can administer OLAP servers (the server that ships with SQL Server 7.0 OLAP Services); however, new features will not be available on the OLAP server.
PivotTable Service
SQL Server 2000 Analysis Services includes an updated version of PivotTable Service. Client applications that use PivotTable Service do not need to use this new version when connecting to an Analysis server unless you need access to objects that include new features. The objects that use these new features (such as data mining models and cubes that include parent-child dimensions) are not seen by the client applications that use the earlier version of PivotTable Service. Client applications that use the updated version of PivotTable Service can connect to any server, regardless of its version. Client applications that use the updated version of PivotTable Service can configure their compatibility settings using the following properties:
- MDX Compatibility property
- MDX Unique Name Style property
- Secured Cell Value property
- Visual Mode property
Custom Add-in Programs
Custom add-in programs that were developed for use with SQL Server 7.0 OLAP Services will continue to work with SQL Server 2000 Analysis Services. No changes are necessary to use them.
Archiving, Restoring, and Migrating Data
Analysis Services supports some but not all permutations of archiving and restoring databases and migrating repositories between versions of the product. For information about supported migration paths, see Supported Migration Paths for Analysis Services Repositories. For information about archiving and restoring data between versions of the product, see Archiving and Restoring Databases Between Versions of Analysis Services.
7.0 Analysis Services Client and Local Cube Support
This table shows support for new server features by the Microsoft SQL Server 7.0 OLAP Services client components and in a SQL Server 2000 Analysis Services local cube. When a feature may cause data to be translated incorrectly by a 7.0 client application, the server prevents the cube from being visible and prevents the client connection to the cube. If the absence of a feature in a local cube might change data values presented to the user, then a local cube using the feature cannot be created.
For each feature listed here, the table shows whether a cube containing a feature is visible on a 7.0 client application and if the cube is visible whether the feature itself is available on the 7.0 client application. For each feature, the table also shows whether a local cube can be created using the feature and whether the feature itself is supported in a local cube.
| Feature | Cube is visible on 7.0 client | Feature available on 7.0 client | Can create local cube using feature | Supported in a local cube |
| Actions | Yes | No | Yes | No |
| Additional authentication methods | Yes | Yes | Yes | (2) |
| Calculated cells | No | No | No | No |
| Changing dimensions | Yes | No | Yes | No |
| Custom member formulas | No | No | No | No |
| Custom rollup formulas | No | No | No | No |
| Default members | No | No | Yes | Yes |
| Dimension security | No | No | No | No |
| DistinctCount | No | No | No | No |
| Drillthrough | Yes | No | Yes | No |
| Enhanced cell Security | Yes | Yes | Yes | No |
| Enhanced virtual dimensions(1) | Yes | Yes | Yes | Not applicable |
| Exceeding 7.0 Limits(3) | No | No | Yes | Yes |
| Linked cubes | Yes | Not applicable | Yes | No |
| Member groups | Yes | Yes | Yes | Yes |
| Members with data | Yes | Yes | Yes | Yes |
| New MDX functions | Yes | No | Yes | (4) |
| Parent-child dimensions | No | No | Yes | Yes |
| Ragged dimensions | Yes | Yes | Yes | Yes |
| ROLAP dimensions | Yes | Not applicable | Yes | No |
| Siblings with same names | No | No | Yes | Yes |
| Write-enabled dimensions | Yes | No | Yes | No |
- The earlier limit of 760 members in a virtual dimension does not apply.
- Cell security is not supported on local cubes.
- Exceeding 127 measures in a cube, 63 dimensions in a cube, or 128 levels in a cube. For information about SQL Server 2000 Analysis Services limits, see Specifications and Limits.
- For the SQL Server 7.0 OLAP Services client, new Multidimensional Expressions (MDX) functions are not supported. For local cubes, new MDX functions are available, except for LookUpCube. Calculated members using LookUpCube in local cubes are not created.
Supported Migration Paths for Analysis Services Repositories
You can migrate a Microsoft SQL Server 2000 Analysis Services repository from the default Microsoft Access (Microsoft Jet 3.5 or 4.0) database to a SQL Server database on the same or a different computer. You cannot migrate a SQL Server repository to a Microsoft Access repository. You can change the format from native to SQL Server 2000 Meta Data Services format when you migrate a database. To migrate a SQL Server database repository between native and Meta Data Services formats, you must migrate it from one SQL Server database to another. The following table shows supported migration paths for repository databases.
| To native Jet 3.5/4.0 | SQL Server 7.0/2000 | To MDS SQL Server 2000 | ||
| From native | Jet 3.5/4.0 | No | Yes | Yes |
| SQL Server 7.0/2000 | No | Yes(2) | Yes(2) | |
| From MDS(1) | SQL Server 2000 | No | Yes(2) | Yes(2) |
- MDS represents the Meta Data Services (previously named Microsoft Repository) format supported by SQL Server 2000.
- Source and destination must be different databases.
Related Topics
Migrating Analysis Services Repositories, OLE DB Provider for Jet
Archiving and Restoring Databases Between Versions of Analysis Services
On an Analysis server (the server that ships with Microsoft SQL Server 2000 Analysis Services), you can restore databases that were archived using an OLAP server (the server that ships with SQL Server 7.0 OLAP Services) or an Analysis server. The following table shows all the restoration paths supported for databases archived while in SQL Server 7.0 OLAP Services or SQL Server 2000 Analysis Services using native or SQL Server 2000 Meta Data Services formats with SQL Server or the Microsoft Jet 3.5 or 4.0 OLE DB provider.
| To native Jet 3.5/4.0 7.0 | 2000 | SQL Server 7.0 | 2000 | To MDS SQL Server 2000 | |||
| From native(1) | Jet 3.5/4.0 | 7.0 | Yes | Yes | Yes | Yes | Yes |
| 2000 | No(3) | Yes | No(3) | Yes | Yes | ||
| SQL Server | 7.0 | Yes | Yes | Yes | Yes | Yes | |
| 2000 | No(3) | Yes | No(3) | Yes | Yes | ||
| From MDS(2) | SQL Server | 2000 | No(3) | Yes | No(3) | Yes | Yes |
- From specifies the repository format, database engine, and version of OLAP Services or Analysis Services that archives a database; To specifies the repository format, database engine, and version of OLAP Services or Analysis Services that restores a database.
- MDS represents the Meta Data Services (previously named Microsoft Repository) format supported by SQL Server 2000.
- OLAP servers do not support restoration of Analysis Services databases.
Related Topics
Archiving and Restoring Databases, OLE DB Provider for Jet
Customer Reviews
Average Review: