Microsoft SQL Server 2000 (64-bit) Analysis Services: Why Migrate, and What to Expect If You Do

Updated : July 22, 2003

Writers: Dave Wickert, Sean Boon, Len Wyatt, John H. Miller

Applies To: SQL Server 2000 (64-bit)

Summary: For users of the online analytical processing (OLAP) and data mining component of SQL Server 2000, there are some real performance advantages to be gained by using the new 64-bit release of the product. This paper helps you evaluate whether migrating from SQL Server 2000 (32-bit) to SQL Server 2000 (64-bit) is right for you, particularly in terms of your use of Analysis Services. The paper also describes key technical differences between 32-bit and 64-bit versions of Analysis Services, and, should you decide to adopt the 64-bit version, what you will see when you first install the software and start to configure it.

On This Page

Introduction
Should You Migrate? Answer These Questions First
Getting Started with SQL Server 2000 (64-bit) Analysis Services
Conclusion
Appendix: Differences Between 64-bit and 32-bit Releases
Setup and Installation
Documentation for Both SQL Server 2000 SP3 and SQL Server 2000 (64-bit)
Maintenance
Editions and Components
Analysis Services
Tools and Utilities
Failover Clustering

Introduction

Microsoft® SQL Server™ 2000 (64-bit) is optimized to run on Intel® Itanium™-based servers and Microsoft Windows® Server 2003, to deliver exceptional performance and scalability while maintaining the reliability, ease of migration, and total cost of ownership (TCO) advantages that are the hallmarks of SQL Server 2000. Built to take advantage of hardware enhancements of the new breed of 64-bit servers, SQL Server 2000 (64-bit) offers higher levels of single-system scalability (scale-up) for the most memory-intensive data and business intelligence (BI) applications.

Because it is no longer bound by the 32-bit memory limit of four gigabytes (GB), SQL Server 2000 (64-bit) is able to perform complex queries on large data sets without performance loss. Additional memory is available to the system to use directly for essential database operations, improving throughput and performance. By expanding the headroom necessary to access large amounts of data and to support large numbers of concurrent database users and client applications, SQL Server 2000 (64-bit) enables new levels of scalability for enterprise applications.

The online analytical processing (OLAP) and data mining component of SQL Server is SQL Server Analysis Services. Analysis Services uses the expanded resources of this 64-bit release of SQL Server 2000 to provide capabilities beyond what is possible in the 32-bit release. Customers can create and deploy applications that were not possible prior to the 64-bit release. For example:

  • They can create multiple, extremely large dimensions with tens of millions of members.

  • The processing of cubes and partitions is greatly improved because large (several GB) process buffers can be used so that writing to temporary files is not needed.

  • No longer does the memory allocated to the query cache have to compete with the processing buffers. There is virtual memory enough to have both allocated at the same time.

  • Having a large number of replica dimensions (built for individual users when using dynamic security, or with systems that have a large number of roles) no longer impacts other memory needs, such as a large query cache and large processing buffers.

Should You Migrate? Answer These Questions First

Before moving your Analysis Services and BI applications to SQL Server 2000 (64-bit), examine the following characteristics of your existing 32-bit system:

  • What are the largest dimensions? Are there a large number of member properties? If large dimensions or a large number of member properties are causing memory paging or causing Analysis Services to approach the 3 GB virtual address limit for the 32-bit system, consider employing a 64-bit system instead. A 64-bit system does not have a 3 GB virtual address limit. Remember that all dimension members (and their member properties) in all databases must be loaded into memory when Analysis Services starts up.

  • Are there a large number of databases on the same Analysis server (even if individually each database is small to medium)? Remember that Analysis Services loads all dimension members from disk for each of the cubes in each database serially at startup time. This serial reading of dimension data for all cubes must complete before Analysis Services can begin responding to queries.

  • Do you have business requirements that result in users performing a high level of querying while partitions are being processed? A 64-bit system allows Analysis Services to have a large query results cache and large process buffers at the same time.

  • How large is the file system cache? A 64-bit system allows the operating system to have a large file system cache.

  • Are you unable to increase the size of the process buffer to a size large enough to avoid the use of temporary files during processing? If so (and you have not designed excessive aggregations), then a 64-bit system allows Analysis Services to have an extremely large process buffer size (10-20 GB), which can significantly improve your processing times.

  • Are you using dynamic security? Do you have a large number of roles, or a large number of users assigned to unique combinations of roles? If any of these three conditions exist then there could potentially be a large number of "replica" dimensions created. In Analysis Services when dimension security is used, the runtime engine creates a subset of the dimension based on what members a role (or user for dynamic security) is allowed to see. This subset of a dimension is called a replica. Like all dimensions, these replicas are kept in virtual memory. Replicas can be reused between connections if they have the same unique combination of roles (if using normal role-base security) or if they have same username (if using dynamic security). Replicas are created in memory as needed, that is, when a user query requests a replica that has not been built yet. However, they are not unloaded until the base dimension is reprocessed (or if the Msmdsrv service is restarted).

  • Projecting the amount of memory needed for replicas is difficult because it depends on so many factors (for example, the type of security used, the user mix, the number of roles shared between them, the number of dimension members visible to each, and so on). Clearly replicas can be a limiting factor in some 32-bit system designs. However, on a 64-bit system, the large virtual address space allows you to have several orders of magnitude more and larger replicas without impacting the overall performance of the system or amount of memory allocated to other objects.

It is worth reiterating how significant the additional capability of the 64-bit virtual address space is to the performance of Analysis Services. Eliminating the 3 GB memory limit allows Analysis Services to construct large dimensions with tens of millions of members, consolidate large numbers of databases to a single server, and use additional memory for large process buffers, large query caches, and large numbers of replica dimensions.

While 64-bit systems provide a lot more memory and computing resources, and have better I/O characteristics, they do have some disadvantages. First, they are more expensive than 32-bit systems and this will probably continue for some time. Second, at any one point in time, 64-bit systems may or may not perform faster than their 32-bit counterparts. This is because the chip technology is different between the two architectures. As newer and faster CPUs come out in each processor line, performance results for the 64-bit and 32-bit systems mirror these processor advances.

Lastly, do not commit to migration before you have all of the prerequisite components available and in-place for a complete 64-bit system. For example:

  • Do you have access to 64-bit OLE DB providers for all of your data sources? SQL Server 2000 (64-bit) includes a 64-bit OLE DB provider for SQL Server. If you are using other RDBMS products, such as Oracle, DB2, Informix, or others, to load data directly into Analysis Services, you need to verify that the prerequisite OLE DB providers are available.

  • Do you have any user-defined functions (UDFs) or other components written in Microsoft Visual Basic®? Not all software available for a 32-bit platform is available for the 64-bit platform. A notable case-in-point is Visual Basic 6, which is not available in 64-bit. You should verify this in Analysis Services, SQL Server, and all other software that you will be using on the 64-bit system.

  • Is it a requirement to run Data Transformation Services (DTS) on the 64-bit system? The DTS runtime and its designer tools are not available on the 64-bit platform. You can read data from and write data to a 64-bit system from a 32-bit system running DTS, and you can physically store your DTS packages in a 64-bit SQL Server database. But the DTS packages themselves will not run on the 64-bit platform.

Getting Started with SQL Server 2000 (64-bit) Analysis Services

This section of the paper covers information you will need to know if you decide to migrate: compatibility with existing 32-bit SQL Server installations, moving user databases from 32-bit to 64-bit systems, and various functionality differences to be aware of when using SQL Server 2000 (64-bit) Analysis Services.

SQL Server 2000 Enterprise Edition (64-bit) includes a 64-bit database engine, a 64-bit server agent, and a 64-bit OLAP and data mining component (Analysis Services). These components of SQL Server 2000 Enterprise Edition (64-bit) are code compatible with the 32-bit version of SQL Server 2000, making it easy to integrate a 64-bit server with other SQL Server 2000 (32-bit) database servers. The same Transact-SQL query language and stored procedures are used on both. Multidimensional expression (MDX) statements and OLAP reports written against 32-bit versions of Analysis Services run exactly as those written against a 64-bit version.

For the database administrator, however, working with the 64-bit server differs in several ways from working with the 32-bit server. For example, the 64-bit server itself is administered remotely from a 32-bit workstation or server. Most of the tools, such as SQL Server Enterprise Manager, SQL Query Analyzer, SQL Profiler, and all of the Analysis Services utilities are not available on SQL Server 2000 (64-bit).

The DTS run-time and design environment is not available on 64-bit systems. Packages can be stored in the SQL Server 2000 (64-bit) database, but the execution environment is on a 32-bit server only. You have to use a secondary 32-bit system that pushes or pulls data with the 64-bit server. However, all of the capabilities are the same as with 64-bit systems. For example, to process 64-bit dimensions, cubes, and partitions remotely, use the DTS OLAP Processing task.

You might ask: "Why can't I just run my standard programs on this new server and automatically gain all of the additional capacity that 64-bit systems deliver?" The 64-bit machine architecture is more, however, than just a standard PC with additional address lines. It has an entirely new instruction set: it is a different machine architecture. The native SQL Server 64-bit components had to be ported and re-compiled to run on a 64-bit machine. While there is an emulation subsystem (called WOW) in Windows 2003 that allows 32-bit applications to be run on the 64-bit platform, it is a subset of Windows and not all facilities are available. Also since it is an emulation of a different instruction set, it is considerably slower than a native 64-bit program.

Because of the processor architecture modifications and the resulting design changes in Windows Server 2003 (64-bit), be aware that some standard Microsoft 32-bit applications are not supported in this environment. The native 64-bit msmdsrv service cannot call 32-bit OLE DB providers. Also you cannot mix and match between 64-bit and 32-bit processes. For example, Analysis Services user-defined functions (UDFs) written in Visual Basic would have to run under WOW, and therefore cannot be called from a native 64-bit process like the Msmdsrv service.

Easy Migration from a 32-Bit SQL Server 2000 Database

Because the on-disk format is the same for both 32-bit and 64-bit platforms, database administrators can move user databases over to the 64-bit server with minimal conversion effort, thereby protecting key database investments made previously on a 32-bit server. To migrate a database, simply use standard procedures to detach the database, copy it to the disk subsystem of the 64-bit server, and attach it to the 64-bit database server. To migrate Analysis Services databases, cubes, and partitions, archive them on the 32-bit server and then restore the images on the 64-bit server.

Some Important Functionality Differences

While SQL Server 2000 (64-bit) is similar to SQL Server 2000 (32-bit), the two releases differ in a few areas. The first difference that you will see involves the installation process. You will notice that SQL Server and Analysis Services are integrated into a single installation process (you no longer have two separate installation steps). Installation time is further reduced because SQL Server takes advantage of new services now included in Windows such as Microsoft Data Access Components (MDAC), Distributed Transaction Coordinator (DTC), and Microsoft Management Console (MMC).

SQL Server 2000 (64-bit) uses Windows Installer rather than Install Shield. This change allows SQL Server Setup to be integrated into the setup for Independent Software Vendor (ISV) applications, providing a seamless install process of the overall solution. Windows Installer-based setup programs keep a master set of installation tables where every application resource (files, registry keys, and so on) is clearly tied to the component or feature it supports. If an application system file gets corrupted or accidentally deleted, the application can be repaired. The installation data is checked and any missing or corrupt files are repaired or replaced.

During installation, two new SQL Server databases are created for use by Analysis Services:

  • OLAPQueryLog

  • OLAPRepository

On 32-bit systems, these databases are stored in Microsoft Access databases, which is not available on 64-bit systems. Unlike Analysis Services (32-bit), Analysis Services (64-bit) requires access to a local SQL Server relational database management system (RDBMS). When you start the msmdsrv service, it automatically starts the SQL Server RDBMS service. Also, when you install SQL Server (and the Analysis Services components listed above) into a different named instance, the instance name is included in the links that Analysis Services uses for repository and query log access.

After the installation, if you browse through the registry, you will notice that just about all of the Analysis Services settings are the same as those in the 32-bit version (SQL Server 2000 Service Pack 3). However, Analysis Services (64-bit) has a new registry setting called MemoryAsMB. If this setting is set to 1, then all of the memory (and memory-related) registry settings are specified as MB. If MemoryAsMB is set to 0, then the values are expressed in terms of bytes (as they are on 32-bit systems). Analysis Manager SP3 hides this complexity from the user by automatically adjusting its values to MB, as appropriate. This change is relevant only if you are working with the registry manually.

For a complete list of the functional differences, see the topic "Differences Between 64-bit and 32-bit Releases" in SQL Server 2000 (64-bit) Books Online. For your convenience, the topic is reproduced in this paper in the appendix.

Utilities and Data Access Software

After installing your first 64-bit server, look at the Start menu for SQL Server. You will notice that a 64-bit system includes only the following standard SQL Server utilities:

  • Books Online

  • Server Network Utility

  • Service Manager

Other utilities, such as SQL Server Enterprise Manager, Analysis Services Analysis Manager, and SQL Profiler, must be remotely administered from a 32-bit SQL Server with SQL Server 2000 SP3 installed on it.

The only data access software available from Microsoft for the SQL Server 64-bit platform is the SQL Server 2000 native OLE DB provider. Since Analysis Services uses OLE DB for all of its data access requirements (for example, when it processes dimensions, cubes, and partitions), this means that RDBMS data sources must be:

  • SQL Server 2000 (on the 64-bit server or on another 32-bit server), or

  • The customer must provide the specific OLE DB provider for their data source. Microsoft does not provide any 64-bit data access software other than SQL Server. At the time of publication of this paper, Oracle and IBM DB2 have beta versions of OLE DB providers. In the future, additional OLE DB providers may be created.

Windows Server 2003 includes a 64-bit version of the ODBC Administrator, and a 64-bit ODBC driver is provided for SQL Server 2000. However, the Microsoft OLE DB Provider for ODBC was not ported to 64-bit and is not available. This means that neither 32-bit nor 64-bit ODBC drivers can be used by Analysis Services (64-bit).

For several reasons, Microsoft does not recommend creating linked servers on a 32-bit version of SQL Server 2000 to link to other data sources. Among them:

  • Performance The SQL Server distributed query optimizer was not designed to be used in this way. Query performance, especially for queries that join multiple remote tables, is variable.

  • Management In some systems, to implement the linked server technique would require potentially hundreds of views that would have to be created manually.

  • Security The security credentials for the target RDBMS are contained in the SQL Server linked server definition; they are not associated with the user accessing the guest database. This means that a DBA must create a security environment for the guest database that resembles the target RDBMS, and enforce access rights within SQL Server.

Remote Administration

SQL Server 2000 (64-bit) requires a 32-bit workstation or server that will be used for remote administration of the 64-bit server. The remote SQL Server 32-bit workstation or server must be running any edition of SQL Server 2000 SP3. To administer Analysis Services, you must install a full Analysis Services SP3 installation (as opposed to installing tools only). Installing without SP3 results in the following error message:

Unable to connect to the registry on the server (server_name), or you are
not a member of the OLAP Administrators group of this server.

Since remote administration is required, there are several implied circumstances that may impact your environment. These items include:

  • Domain Infrastructure The 64-bit server and the 32-bit server must be in the same domain. If for any reason it is not possible to place both in the same domain, use the following common workaround to ensure proper functioning of Analysis Services: configure an OLAP administrator account with matching username and password combination between both the 32-bit and 64-bit servers. In that environment, Windows will allow access as if they were the same account.

  • Network Infrastructure Because not all of the networking hardware and software is present on the 64-bit platform, it is not uncommon to have the 32-bit and 64-bit servers on different network technologies. For example, the 32-bit server may be on Gigabit Ethernet and the 64-bit server on 100Mb Fast Ethernet. In this scenario, some routing infrastructure is required, which may slow the connectivity and perceived user interface rendering on the 32-bit server. Best practice is to use the same network technology wherever possible, on the same subnet, using a high-speed switch. Ensure a high-bandwidth connection is available between the 64-bit server and the remote 32-bit server.

  • Archive/Restore Utility This utility is used to back up and restore Analysis Services databases. When you are using remote administration, however, the utility runs on the 32-bit server, and the data is read from the Analysis Services data folder and written to the .cab file across the network. All of the data must be moved from the 64-bit server to the 32-bit server, and then moved back to the 64-bit server when the .cab file has been written. As a result, we recommend using file system backups rather than the MSMDARCH utility to back up cube data. Bear in mind that the meta data in the relational database also needs to be backed up. The best practice is to run a SQL Server maintenance job that backs up the OLAPRepository SQL Server database into a file at the root of the Analysis Services data folder, and then use a file system backup from there. This procedure ensures that the meta data backup is synchronized with the cube data backup.

  • Mirrored 64-bit and 32-bit environments Analysis Services creates and processes OLAP objects in two different environments: design time and runtime. When using remote administration, the "design time" environment that Analysis Services uses is the 32-bit server itself (where Analysis Manager is running). When Analysis Manager creates a list of OLE DB/ODBC providers, DSNs, tables, and fields, all of these objects come from the client 32-bit server where Analysis Manager is running. However, when you process the resulting dimensions, cubes, and partitions (the "runtime" environment), the actual database access is being executed by the msmdsrv service on the 64-bit system. Obviously problems can arise if the settings are different between the two systems. Best practice is to configure the data source structures, OLE DB providers, and access rights identically on both the 32-bit and 64-bit systems—mirrored 64-bit and 32-bit environments.

Conclusion

In this paper, you have learned about a variety of capabilities and limitations in the 64-bit release of SQL Server 2000, especially as pertains to the use of the Analysis Services component. The requirement for remote administration and the absence of various data access components are important to be aware of. In terms of overall performance, however, SQL Server 2000 (64-bit) represents a big step forward for customers making use of Analysis Services: it opens up an entirely new class of applications that are not technically possible using the existing 32-bit software. These applications are not common. The 32-bit software is well suited for addressing most situations. But SQL Server 2000 (64-bit) is an important addition to our strategic, enterprise-class platform.

Appendix: Differences Between 64-bit and 32-bit Releases

This section describes differences you need to consider when installing and using the 64-bit version of Microsoft® SQL Server™ 2000.

Setup and Installation

Windows Installer-Based

SQL Server 2000 (64-bit) uses the Windows Installer to completely integrate the installation of SQL Server features in a single feature tree. Minimum and typical installation modes are no longer implemented. Setup initially displays the feature tree with all available features selected. Administrators can customize installations by choosing items on the feature tree and changing installation paths.

Setup Feature Tree Interaction with Parent/Child Features

In this release of SQL Server 2000 (64-bit), child feature selection is more limited than in the 32-bit version of SQL Server 2000. For example, the Tools feature installs all tools; there is no option to select individual tools. Similarly, Server Components are limited to SQL Server and the Full-Text Search Engine. The full selection of child features will be enabled in the next major release.

Connectivity-Only Option Not Supported

The Connectivity-Only option is not implemented during the SQL Server 2000 (64-bit) Setup. The Connectivity-Only option in previous versions installed Microsoft Data Access Components (MDAC) and JET. JET is not available for the 64-bit version of SQL Server 2000. MDAC 64-bit components are installed by the 64-bit versions of the Windows® Server 2003 family as part of its core components.

Remote Installation

SQL Server 2000 (64-bit) does not support remote installation.

Previous Version Upgrades

Previous versions of SQL Server for the 32-bit computer are not supported to run on the 64-bit platform; upgrades of 32-bit instances are not supported in this release.

Data Migration

Data migration from SQL Server 2000 to SQL Server 2000 (64-bit) is supported. Attaching a 32-bit database to a 64-bit instance is possible by using detach/attach or backup/restore in 32-bit Enterprise Manager. You can move databases back and forth between 32-bit and 64-bit versions of SQL Server. Migrating data from SQL Server version 7.0 is also supported using the same methods. Downgrading data to SQL Server 7.0 from SQL Server 2000 (64-bit) is not supported.

Documentation for Both SQL Server 2000 SP3 and SQL Server 2000 (64-bit)

This release of Books Online includes documentation for both SQL Server 2000 Service Pack 3 (SP3) and SQL Server 2000 (64-bit). The Books Online table of contents is organized by platform: 64-bit content is grouped together in a node entitled "SQL Server 2000 (64-bit)," and 32-bit topics are grouped together in a node called "SQL Server 2000."

Content specific to the 64-bit platform is identified below the topic title as follows: "This topic applies only to SQL Server 2000 (64-bit)." All 64-bit topics also include "64-bit" in the topic title. Topics that include content for SP3 changes in behavior include the following tag below the topic title: "New Information – SQL Server 2000 SP3." Not all 32-bit topics have been updated for SP3.

Index entries for 32-bit and 64-bit content have been combined. As a result, keyword searches may return a combined list of 32-bit and 64-bit topics. Topics that apply to the 64-bit SQL Server can be identified by the "64-bit" tag in the topic title, and also by the tag below the topic title that states, "This topic applies only to SQL Server 2000 (64-bit)."

Maintenance

Feature Maintenance

SQL Server 2000 (64-bit) uses Add or Remove Programs in Control Panel to fully support the addition and removal of individual features, as well as to remove instances of SQL Server. You can also use the Setup user interface or command line for installation maintenance. Feature maintenance for clustered installations is not supported.

Instance Maintenance

Maintenance activities on existing installations are supported with Add or Remove Programs in Control Panel as well as by the Setup program. Each installed instance (except for clustered installations) is listed in the Add or Remove Programs dialog box. Any non-cluster instance may be removed or changed with the Add or Remove Programs. Instance maintenance may also be performed on the setup command line by specifying features on the command line or in an .ini file. For information about cluster maintenance, see the section on failover clustering at the end of this topic.

Editions and Components

Supported Editions

For this release, only the Enterprise Edition is available. The SQL Server 2000 (64-bit) Desktop Engine is not available.

Network Libraries

The Network Library configuration page is not included in SQL Server 2000 (64-bit). Setup initially enables Shared Memory, Named Pipes, and TCP/IP. Use the SQL Server Network Utility to change the settings that enable or disable protocols. Configuration of Network Libraries is not supported in Setup, but is still supported by the SQL Server Network Utility. Configure client network libraries using the Client Network Utility (cliconfg.exe) included with the 64-bit versions of the Windows Server 2003 family.

Services

The Service Account dialog supports the SQL Server Service (MSSQLSERVER, MSSQL$<instancename>), the SQL Agent Service (SQLSERVERAGENT, SQLAgent$<instancename>), and the Analysis Services (MSSQLServerOLAPService) account. The Remote Account Information dialog supports the Cluster Setup Admin account for use when installing a virtual server. Command-line support for configuration of these accounts is also provided.

Code Samples

Code samples are not installed by Setup.

English Query

English Query is not supported.

Analysis Services

Analysis Services Integrated into SQL Server Setup

Analysis Services can be installed at the same time as the SQL Server relational database engine and other SQL Server components. It is part of the Windows Installer feature tree, in which you can select the features you want to include in your installation. Analysis Services appears in the feature tree with Data and SQL Repository child features.

Analysis Services Uses the SQL Server Repository

The 64-bit version of Analysis Services uses SQL Server instead of Jet (.MDB) for its meta data repository. If the full SQL Server is not installed with Analysis Services, the SQL Repository child feature installs the core SQL Server files and enables you to specify the location of these files.

Tools and Utilities

Graphical User Interface Management Tools

To administer a 64-bit SQL Server or Analysis Server with management tools like Enterprise Manager or Analysis Manager, you must use the 32-bit tools on a 32-bit server to remotely administer the 64-bit server. The only 64-bit tools included with this release are the Server Network Utility, the Service Manager, and the command line utilities.

Distributed Transaction Coordinator (DTC) Installed with the 64-bit versions of the Windows Server 2003 family

DTC is part of the 64-bit versions of the Windows Server 2003 family operating system and is no longer installed as part of the SQL Server 2000 (64-bit) setup. The DTC service can no longer be managed from the SQL Server Service Manager. To manage this service, use Services in the 64-bit versions of the Windows Server 2003 family Administrative Tools.

Data Transformation Services (DTS) components for 64-bit servers are not available in this release. Note the resulting functionality changes:

  • A DTS package can be saved on the 64-bit server, and a DTS package can be run against a SQL Server 2000 (64-bit) dataset, but the package must run from a 32-bit machine that is set up with SQL Server 2000 tools.

  • The Copy Database Wizard will not be able to run on a 64-bit server or target a 64-bit server. Data migration from SQL Server 2000 to SQL Server 2000 (64-bit) is supported. Attaching a 32-bit database to a 64-bit instance is possible by using either the detach/attach or the backup/restore technique. You can move databases back and forth between 32-bit and 64-bit versions of SQL Server.

  • If you are using Meta Data Services (MDS) to store a DTS package, you must manage and store the DTS package on a 32-bit server.

  • Transformable push subscriptions are not supported. Pull subscriptions from a 32-bit installation of SQL Server 2000 should work with a 64-bit Publisher or Distributor.

SQL Mail

SQL Mail is not supported in this release of SQL Server 2000 (64-bit). The following extended procedures and stored procedures are not supported, as they are part of the SQL Mail functionality:

  • xp_startmail

  • xp_stopmail

  • xp_sendmail

  • xp_readmail

  • xp_deletemail

  • xp_findnextmsg

  • sp_processmail

However, SQL Agent Mail in SQL Server 2000 (64-bit) can be configured remotely by Enterprise Manager, if your client remotely connects using SQL Server 2000 Service Pack 3.0 or later.

To configure SQL Agent Mail, you must use Microsoft Outlook® Express to use an existing mail account.

To create a mail account for SQL Agent Mail using Outlook Express

  1. From the Start menu, select Outlook Express.

  2. From the Tools menu, select Accounts. . .

  3. On the Internet Accounts dialog box, click Add, and then click Mail. This will allow you to create a new mail account by launching the Internet Connection Wizard.

  4. On the Your Name dialog box in the Display name box, enter the name you would like to be the sender of all SQL Agent e-mail messages.

  5. On the Internet E-mail Address dialog box in the E-mail address box, enter the e-mail account to use to send SQL Agent e-mail messages. For example: someone@example.com.

  6. On the E-mail Server Names dialog box in the My incoming mail server is a box, select POP3 for the server type.

  7. In the Incoming mail (POP3, IMAP, or HTTP) server box, enter the name of the POP3 server. If you are only using this account for SQL Agent Mail, it is not necessary to select a valid POP3 server name, as SQL Agent Mail does not receive mail.

  8. In the Outgoing mail (SMTP) server box, enter a valid SMTP server name to be used to send SQL Agent messages.

  9. On the Internet Mail Logon dialog box in the Account Name box, enter the account you wish to use for sending SQL Agent Mail notifications.

  10. Click Next.

  11. Click Finish.

    If you are using Outlook Express 6.0 or later, you must configure this application so that it does not send an alert when SQL Agent Mail is accessing your Outlook Express account.

To configure SQL Agent Mail with Outlook Express 6.0

  1. From the Outlook Express Tools menu, select Options . . .

  2. Select the Security tab, and clear the "Warn me when other applications try to send mail as me" check box.

  3. Click OK.

    If you already have an SMTP Outlook Express profile created, you can use that profile for SQL Agent Mail by setting it as the default account.

To use an existing SMTP Outlook Express Mail Account for SQL Agent Mail

  1. From the Start menu, select Outlook Express.

  2. From the Tools menu, select Accounts. . .

  3. On the Internet Accounts dialog box, select the mail account to set as the default, and click the Set as Default button. This must be a POP3/SMTP account for SQL Agent Mail to work.

    If you would like to change the name of your mail account after you have set it as the default, you can rename it to SQLAgentMail. The same mail account name will be used in configuring SQL Agent Mail in Enterprise Manager.

To change the name of your profile for SQL Agent Mail

  1. From the Start menu, select Outlook Express.

  2. From the Tools menu, select Accounts. . .

  3. On the Internet Accounts dialog box, select the default account and click the Properties button.

  4. On the Properties dialog box, type the name for the profile in the Mail Account box.

    After you have created a mail account for SQL Agent Mail, you can configure SQL Agent Mail using Enterprise Manager.

To select the mail account in Enterprise Manager for SQL Agent Mail

  1. On the Agent Properties dialog box, select the General tab.

  2. In the Mail profile box, select the previously configured Outlook Express mail account.

For more information about SQL Agent Mail functionality, see the 32-bit SQL Server 2000 Books Online.

Client Tools Only

Installing only the Tools feature from the feature tree can facilitate a Client Tools Only installation. Note that the SQL Server graphical user interface management tools are not included with this release, except for the SQL Server Network Utility and the Service Manager. The command-line tools are available.

Supported Tools

Tools must be installed from the SQL Server 2000 (64-bit) RTM CD. Tools from any other CD cannot be installed as part of an instance of SQL Server 2000 (64-bit). To utilize management tools like Enterprise Manager, use a 32-bit instance of SQL Server where management tools are installed to remotely administer instances of SQL Server 2000 (64-bit).

Record Unattended File

A Template.ini file is provided and can be modified for unattended installations. SQL Server 2000 (64-bit) does not support the record unattended file functionality.

Development Tools

The Development Tools feature is not included in the feature tree. The SQL Debugging Interface (SDI) is installed by Setup, but is included in the core SQL Server Engine component. The other development tools available in SQL Server 2000 (32-bit) (headers and libraries, MDAC SDKs, and the Backup/Restore API) are not installed by Setup.

For more information on MDAC and the status of its components, see the MDAC Roadmap.

DB-Library

The DB-Library programming model is not supported in SQL Server 2000 (64-bit), and it has not been ported to the 64-bit Windows operating system. The DB-Library API has not been enhanced beyond the level of SQL Server version 6.5. All DB-Library applications can work with SQL Server 2000, but only as 6.5-level clients. Features introduced in SQL Server 2000 and SQL Server version 7.0 are not supported for DB-Library applications.

Registry Rebuild and Rebuild Master

Registry Rebuild and Rebuild Master are implemented differently in the 64-bit version of SQL Server 2000 than in the 32-bit version. In the 64-bit version, SQL Server 2000 takes advantage of the self-repairing features provided by Windows Installer. In addition, the REINSTALL and REINSTALLMODE properties on the setup command line are available to explicitly rebuild the registry, reinstall corrupted or missing files, reinstall corrupted or missing shortcuts, and rebuild the master databases. These properties can be specified on the command line or in an .ini file.

Failover Clustering

Cluster Support

Installation of a failover cluster is supported for a single cluster definition. Feature maintenance for clustered installations is not supported. A template .ini file is provided that enables all supported cluster features.

Cluster Maintenance

Because only a single cluster definition is supported, feature maintenance with Add or Remove Programs in Control Panel for clustered installations is not supported. Clustered instances are displayed by Add or Remove Programs, but changes are not allowed. Setup supports adding or removing a node with either the graphical user interface or the command line. Setup also supports changing or adding an IP address. Cluster uninstall is supported by Setup.