Requirements and Considerations for Analysis Services Deployment
Before you deploy a Microsoft SQL Server Analysis Services project, there are important issues to consider to improve the reliability and performance of your Analysis Services installation. For example, it may be necessary to increase hardware resources if you are adding another instance of Analysis Services to existing instances on the server, or if you will be processing complex cubes in your project. You should also take steps to ensure the availability of your project if a hardware or software failure occurs, and during certain processing tasks. Finally, you may want to consider scaling instances of SQL Server or Analysis Services across multiple computers based on performance needs.
Requirements and Considerations
Requirements and considerations for deployment are addressed in the following sections:
Before you deploy your Analysis Services project, consider the resource requirements of your installation. In particular, consider memory and processor needs, and disk space requirements.
Memory and Processor Requirements
Analysis Services needs more memory and processor resources in the following cases:
When processing large or complex cubes. These require more memory and processor resources than small or simple cubes.
When the number of cubes within a single database increases.
When the number of databases within a single instance of Analysis Services increases.
When the number of instances of Analysis Services on a single computer increases.
When the number of users who are accessing Analysis Services resources simultaneously increases.
The amount of memory and processor resources that are available to Analysis Services varies depending on which version of Microsoft Windows is installed on the server computer. For more information about maximum processor and memory by operating system, see Hardware and Software Requirements.
Analysis Services can address a maximum of 3 GB of memory on any 32-bit version of Windows, regardless of the amount of actual memory that is installed on the computer. For more information on the /3GB switch see Microsoft Knowledge Base (KB) article 283037.
Disk Space Requirements
Different aspects of your Analysis Services installation and the tasks related to object processing require different amounts of disk space. The following list describes these requirements.
Cubes that have large fact tables require more disk space than cubes that have small fact tables. Similarly, although to a lesser extent, cubes that have many large dimensions require more disk space than cubes that have fewer dimension members. Generally, you can expect that an Analysis Services database will require approximately 20 percent of the amount of space required for the same data stored in the underlying relational database.
Aggregations require additional space proportional to aggregations added—the more aggregations there are, the more space is required. If you avoid creating unneeded aggregations, the additional disk space that is needed for aggregations typically should not exceed approximately 10 percent of the size of the data that is stored in the underlying relational database.
By default, mining structures cache to disk the dataset with which they are trained. To remove this cached data from the disk, you can use the Process Clear Structure processing option on the mining structure object. For more information, see Processing Data Mining Objects.
During processing, Analysis Services stores copies of the objects it is processing in the processing transaction on disk until the processing is finished. When the processing is finished, the processed copies of the objects replace the original objects. Therefore, you must provide sufficient additional disk space for a second copy of each object to be processed. For example, if you plan to process a whole cube in a single transaction, you need sufficient hard disk space to store a second copy of the whole cube.
Back to Top
In an Analysis Services environment, a cube or mining model may be unavailable for querying because of a hardware or software failure. A cube also may be unavailable because it needs to be processed.
Providing Availability in the Event of Hardware or Software Failures
Hardware or software may fail for various reasons. However, maintaining availability of your Analysis Services installation is not only about troubleshooting the source of those failures, but also about providing alternative resources that enable the user to continue using the system if a failure occurs. Clustering and load balancing servers are typically used to provide the alternative resources that are necessary to maintain availability when hardware or software failures occur.
To provide availability in the event of a hardware or software failure, consider deploying Analysis Services into a failover cluster. In a failover cluster, if the primary node fails for any reason or if it must be rebooted, Microsoft Windows Clustering fails over to a secondary node. After the failover, which occurs very quickly, when users run query they are accessing the instance of Analysis Services that is running on the secondary node.
Another solution for availability issues is to deploy your Analysis Services project onto two or more production servers. You can then use the Network Load Balancing (NLB) feature of Windows servers to combine the production servers into a single cluster. In an NLB cluster, if a server in the cluster is unavailable due to hardware or software issues, the NLB service directs user queries to those servers that are still available. For more information about Windows clustering and NLB, see Clustering Services under Technology Centers on the Microsoft Windows Server 2003 Web site.
Providing Availability While Processing Structural Changes
Certain changes to a cube can cause the cube to be unavailable until it is processed. For example, if you make structural changes to a dimension in a cube, even if you reprocess the dimension, each cube that uses the modified dimension must also be processed. Until you process those cubes, users cannot query them, nor can they query any mining models that are based on a cube that has the modified dimension.
To provide availability while you process structural changes that may affect one or more cubes in an Analysis Services project, consider incorporating a staging server and using the Synchronize Database Wizard. This feature lets you update data and metadata on a staging server, and then to perform an online synchronization of the production server and the staging server. For more information, see Synchronizing Analysis Services Databases.
To transparently process incremental updates to source data, enable proactive caching. Proactive caching updates cubes with new source data without requiring manual processing and without affecting the availability of cubes. For more information, see Proactive Caching (Partitions).
Back to Top
Multiple instances of Microsoft SQL Server and Analysis Services on the same computer may cause performance issues. To solve these issues, one option may be to increase the processor, memory, and disk resources on the server. However, you may also need to scale the instances of SQL Server and Analysis Services across multiple computers.
Scaling Analysis Services Across Multiple Computers
There are several ways to scale an installation of Analysis Services across multiple computers. These options are described in the following list.
If there are multiple instances of Analysis Services on a single computer, you can move one or more instances to another computer.
If there are multiple Analysis Services databases on a single computer, you can move one or more of the databases onto its own instance of Analysis Services on a separate computer.
If one or more relational databases provide data to an Analysis Services database, you can move these databases to a separate computer. Before you move the databases, consider the network speed and bandwidth that exist between the Analysis Services database and its underlying databases. If the network is slow or congested, moving the underlying databases to a separate computer will cause affect processing performance.
If processing affects query performance, but you can’t process during times of reduced query load, consider moving your processing tasks to a staging server and then performing an online synchronization of the production server and the staging server. For more information, see Synchronizing Analysis Services Databases. You can also distribute processing across multiple instances of Analysis Services by using remote partitions. Processing remote partitions uses the processor and memory resources on the remote server, instead of the resources on the local computer. For information on remote partitions management, see Managing Analysis Services Partitions.
If query performance is poor but you cannot increase the processor and memory resources on the local server, consider deploying an Analysis Services project onto two or more production servers. Then you can use Network Load Balancing (NLB) to combine the servers into a single cluster. In an NLB cluster, queries are automatically distributed across all the servers in the NLB cluster. For more information, see Clustering Services under Technology Centers on the Microsoft Windows Server 2003 Web site.
Back to Top