Author: Carl Rabeler

Contributor: Josh Caplan

Reviewers: Prem Mehra, Denny Lee, John Desch, Wayne Robertson, Steve Pontello, Thomas Kejser

This technical note describes the consolidation options, considerations, and best practices for Microsoft® SQL Server® 2008 Analysis Services that the SQL CAT team has derived from working with Analysis Services customers. In this technical note, we also discuss an Analysis Services load balancer solution that one of our internal customers uses to load balance queries from Microsoft Excel® and SQL Server Reporting Services clients across multiple query servers to increase query performance and provide availability.

Why Consolidate

Our customers are consolidating Microsoft SQL Server 2008 Analysis Services for the following reasons:

• Better utilization of hardware resources, either of existing servers or in conjunction with the acquisition of new, more powerful servers
• Reduction of sprawl, particularly of many small to medium-sized Analysis Services cubes
• Increased importance of availability, as business intelligence solutions become more business critical
• Green IT, with fewer more powerful servers utilizing less energy resources than more, less powerful servers

Consolidation Solutions

In working with our customers, we have implemented either one or both of the following Analysis Services consolidation solutions:

• Co-locating the SQL Server relational data warehouse with the Analysis Services instance on a single physical server (but NOT with the Online Transactional Processing (OLTP) system).
• Co-locating multiple Analysis Services databases onto a single physical server, either within a single Analysis Services instance or through the use of multiple Analysis Services instances

Co-Locating the Relational Data Warehouse with the Analysis Services Instance

Frequently, an Analysis Services database is located on a dedicated Analysis server and Analysis Services processing queries are sent across the wire over TCP/IP or named pipes to a SQL Server relational data warehouse on its own, dedicated server. In this scenario, network congestion can be the processing performance bottleneck, particularly when the volume of data being loaded into the Analysis Services database for each processing operation is large. If this is the case, the ASYNC_NETWORKIO wait will appear high in the SQL Server wait stats list.

Network congestion can be addressed by improving the network connection performance in one or more of the following manners:

• Using Jumbo frames and tuning network parameters
• Utilizing a dedicated network connection or employing faster Ethernet connections, such as 10-gigabit Ethernet and InfiniBand
• Reducing the amount of data involved with each processing operation, such as by utilizing incremental updates
• Network congestion can also be addressed by co-locating the relational data warehouse onto the same physical server as the Analysis Services database. Co-location facilitates can improve processing performance because Analysis Services can connect to SQL Server by using the shared memory network protocol. The shared memory protocol is enabled by default on the SQL Server relational engine and, if it is enabled as a client protocol, is always used by Analysis Services to communicate with the relational engine on the same physical server.

Co-locating the SQL Server relational data warehouse with the Analysis Services instance on a single physical server is particularly appealing as servers become more powerful (multiple cores and more memory) and less expensive. On these more powerful servers, dedicating one powerful server for the relational data warehouse and another powerful server for the Analysis Services instance may not fully utilize the processor and memory resources on either server. Co-locating the relational data warehouse with the Analysis Services instance may enable better utilization of available hardware resources. Co-location requires that you address and resolve resource contention issues. These issues are discussed later in this paper.

Co-Locating Multiple Analysis Services Databases onto a Single Server (Using a Single Instance or Multiple Instances)

To more fully utilize these more powerful and less expensive servers, it is tempting for IT to consolidate multiple small and medium-sized Analysis Services databases onto a single physical server. In many environments, there are numerous small to medium-sized cubes spread out across the enterprise that do not fully utilize the hardware resources of the servers on which they are running. Furthermore, managing and monitoring this sprawl requires significant human effort and power resources to keep running on a 24 x 7 basis. This phenomenon is exacerbated as older servers are retired and newer servers are purchased.

Consolidating Analysis Services databases onto a single physical server reduces the number of servers, which results in better utilization of hardware and a reduction in power usage. Managed appropriately, such consolidation also results in a reduction in management and monitoring costs by the IT department.

Considerations

Each of these consolidation solutions addresses specific needs. These consolidation solutions can be used separately or can used in combination. There are some common considerations for these consolidation solutions and some unique considerations for each solution.

Common Considerations When Consolidating

Consolidation will result in competition for server resources such as processor, memory, and I/O resources. In some circumstances, it may also result in locking issues or inefficient use of existing resources. When you are consolidating separate systems onto a single server platform, proper control of the utilization and sharing of resources is vital to the success of the consolidation effort. The following resources are affected by consolidation:

• Memory – when multiple processes run on a single server, all share the available memory on the server. The use of substantial memory by any single process may leave insufficient memory for other processes.
• Processor – when multiple processes run on a single server, all share the available processor resources on the server. The use of substantial processor resources by any single process may leave insufficient processor cycles for other processes. On large multi-processor servers, the relational engine may generate parallel execution plans for each individual query in a processing operation. When there are a large number of concurrent relational queries, which is typical of Analysis Services processing operations, excess parallelism can result in contention for processor resources and inefficient use of L1 and L2 caches.
• I/O – when multiple processes run on a single server, all share I/O resources on the server. In the absence of proper disk layout and an adequate disk I/O subsystem, there is contention for the same I/O resources.

Considerations When Co-Locating the Relational Data Warehouse with the Analysis Services Instance

Co-locating the SQL Server relational data warehouse with the Analysis Services instance on a single physical server can result in significant contention for hardware resources during processing as both the SQL Server relational instance and the Analysis Services instance each require processor, memory, and I/O resources to complete the processing operation. The SQL Server relational engine and the Analysis Services multidimensional engine will each, by default, attempt to utilize all available server resources. This may result in contention for these resources during processing. For example, the SQL Server relational engine may utilize a substantial percentage of memory on the server and be slow to give up these allocated memory resources to Analysis Services when memory resources are needed during the indexing and aggregation phase of the Analysis Services processing operation.

Additionally, this resource contention may negatively affect querying, if querying is occurring concurrently with processing. For example, contention for memory resources may result in the clearing of Analysis Services caches that would otherwise be used to improve query performance. Additionally, locking during processing may result in short queries taking longer than expected and new connections failing when a long-running user query blocks the commit phase of a processing operation. When processing is affecting query performance, utilizing separate query and processing servers can resolve this resource contention. The use of separate query and processing servers is discussed later in this article.

Considerations When Co-Locating Multiple Analysis Services Databases onto a Single Server (Using a Single Instance or Multiple Instances)

Co-locating multiple Analysis Services databases onto a single server can result in several types of resource contention issues:

• The resources required to process any Analysis Services database can negatively impact the query performance of all other databases on the server while the processing of any database is occurring. With multiple databases on a single physical server, processing operations may affect querying for longer periods of time, and you will generally not want to process too many databases simultaneously (due to resource requirements of each processing operation).
• A single resource-intensive user query (such as multi-threaded storage engine intensive queries) against any single cube in any database can consume a significant portion of the resources on the entire server and negatively affect the performance of all other user queries on that server. For more information, see the Analysis Services Performance Guide.

Co-locating multiple Analysis Services databases onto a single server can result in several types of maintenance issues. When you consolidate multiple databases into a single physical server, down time related to operating system or hardware issues will negatively affect more databases and consequently more users. For example, applying an operating system service pack or upgrading hardware could take all of the databases on the consolidated physical server offline for a period of time. Furthermore, if you consolidate using a single instance of Analysis Services for all databases, instance management (such as SQL Server service packs and cumulative updates) can also impact availability of the databases in a consolidated environment.

The best practices discussed in the remainder of this paper describe how we have resolved these resource contention issues.

Best Practices

The following best practices describe the consolidation solutions that we have successfully implemented in customer environments to realize the benefits and mitigate the risks inherent in each of these consolidation solutions. Utilizing these best practices, we have increased processing performance and hardware utilization, decreased power consumption, simplified manageability through standardization, and increased availability.

Co-Locating the Relational Data Warehouse with the Analysis Services Instance

The challenge when the relational data source is collocated with the Analysis Services instance is to realize the benefits of improved processing performance and increased hardware utilization without impacting user queries. We implemented this consolidation option in two different ways to avoid resource contention with user queries.

Utilizing a Processing Window

A processing window is a period of time during which the Analysis Services instances on a single server are resolving few or no user queries and one that is long enough for all required processing operations to complete. A typical scenario is an environment where querying is not occurring overnight (although the definition of overnight is getting shorter). The benefit of this scenario is that the co-location of the SQL Server relational warehouse with the Analysis Services instance on a single physical server enables you to reduce the size of this processing window by increasing the speed of processing. If you have such a processing window, you will be able to complete processing during a period in which there is little or no resource contention on the server related to querying. However, as you place more Analysis Services databases on a single server, you need either more server resources or more time.

When you have such a processing window, the resource contention is only between the SQL Server relational engine and the SQL Server analysis engine for processing operations, and it does not affect a significant number of user queries. Solving contention between these two processes during the Analysis Services processing operation requires the application of a number of techniques.

Restricting Memory and Processor Utilization in the SQL Server Relational Engine

You can restrict the amount of memory and processors resources used by the SQL Server relational engine to ensure sufficient resources are available to Analysis Services. You can also use disk layout techniques to limit I/O contention between the SQL Server relational engine and Analysis Services. For more information about limiting I/O contention, see Resolving Disk Contention and Maximizing I/O Resources.

Restricting Memory Utilization in the SQL Server Relational Engine

The SQL Server relational engine uses memory in its buffer pool and memory outside of its buffer pool.

• You can directly restrict the memory used by SQL Server in its buffer pool.
• You can indirectly restrict the memory used for connections by limiting the number of connections.
• You cannot restrict the amount of memory used by SQL Server for threads, extended stored procedures, and the CLR.

To manage the size of the SQL Server buffer pool, you can use:

• The max server memory setting of the sp_configure system stored procedure to place an upper limit the amount of buffer pool memory used by an instance of the SQL Server relational engine.
• Resource Governor in SQL Server 2008 to set different amounts of buffer pool memory within an instance on a per workload basis, guaranteeing a minimal amount of memory for each workload. For more information about Resource Governor, see Managing SQL Server Workloads with Resource Governor and Using the Resource Governor.

You can use these options together. For example, if you have users querying the relational data warehouse for reporting purposes and Analysis Services querying the relational data warehouse for processing purposes, you can use the max server memory setting to set an upper limit on usage of buffer pool memory and then use Resource Governor to guarantee the Analysis Services processing queries (or the reporting queries) a specified amount of memory resources.

Important: You should use the capabilities built into the SQL Server relational engine to restrict its use of memory; you should not use Windows® System Resource Manager (WSRM) for restricting memory use by the SQL Server relational engine. For more information about WSRM and SQL Server, see Scaling Up Your Data Warehouse with SQL Server 2008 and Consolidation Using SQL Server 2008.

Restricting Processor Utilization in the SQL Server Relational Engine

You can restrict the use of processor resources by the SQL Server relational engine either by limiting the use of all processor resources by the SQL Server relational engine or by allocating specific processors to the SQL Server relational engine:

• Use WSRM to limit the amount of processor resources utilized by the SQL Server relational engine across all processors. WSRM is an optional (and free) component of the Enterprise and Datacenter editions Windows Server® 2003 and of all editions of Windows Server 2008. WSRM uses Windows Management Instrumentation (WMI) to monitor running processes, monitoring the memory size (virtual and physical) and processor utilization, and to prevent any process from exceeding specified upper limits. WSRM is our preferred approach to regulating the utilization of processor resources by all processes. For more information about WSRM, see WSRM for Windows Server 2008 R2, WSRM for Windows Server 2008, and WSRM for Windows Server 2003.
• You can also use processor affinity within the SQL Server relational engine to restrict processor utilization to specific processors by means of two affinity mask options, affinity mask and affinity I/O mask, thus leaving remaining processor cores for the SQL Server analysis engine.

Important: On large multi-processor servers, you may need to set MAXDOP to 4 (or less) to prevent excessive parallelization of Analysis Services processing queries by the SQL Server relational engine.

 

Note: If you are running SQL Server 2008, you can also use Resource Governor to throttle CPU consumption by processing queries.

 

Tip: You can use WMI to dynamically configure these resource utilization settings during processing and then reconfigure them when processing completes. For more information, see WMI Provider for Configuration Management Concepts.

Restricting Memory and Processor Resource Utilization in Analysis Services

You cannot directly restrict the SQL Server analysis engine to specific memory and processor resources.

Utilizing Settings in Analysis Services to Restrict Memory and Processor Utilization

Analysis Services does not support processor affinity, and the max memory settings for Analysis Services are soft settings, not hard settings. By soft, we mean that when specified maximum memory settings are exceeded, objects in cache will be evicted to attempt to avoid exceeding the memory settings, but you can still blow through these memory settings under a variety of circumstances. For more information, see the SQL Server Analysis Services 2008 Performance Guide. There is a HardMemoryLimit setting in the msmdsrv.ini file in SQL Server 2008 Analysis Services that enables you to specify a threshold beyond which Analysis Services will start cancelling operations if the memory exceeds a specified threshold. However, the use of this setting will not prevent Analysis Services from exceeding the specified threshold under all circumstances.

The buffered file mode that Analysis Services uses can cause the Windows system file cache to grow quite large and starve the relational engine of all memory resources during Analysis Services processing, resulting in an apparent “hang” during processing. If you encounter this scenario, you can limit the amount of system file cache used by Analysis Services by modifying the LimitSystemFileCacheSizeMB setting in the msmdsrv.ini file. Limiting the amount of system file cache used by Analysis Services will ensure that memory remains available for the relational engine.

Utilizing WSRM to Restrict Memory and Processor Utilization in Analysis Services

WSRM is our preferred approach to place hard restrictions on the usage of memory and processor resources used by the Analysis Services process.

Important: When you utilize WSRM to place a hard restriction on the usage of memory resources by Analysis Services, the Analysis Services process will still see the total amount of memory on the server. Therefore, you need to adjust the values for the TotalMemoryLimit and LowMemoryLimit properties to reflect the appropriate percentage of the total physical memory on the server that WSRM is allowing Analysis Services to utilize in order for these settings to function as designed.

Note: If you utilize the Preallocate setting in the msmdsrv.ini file, do not grant the Lock Pages in Memory local user policy to the Analysis Services service account if you utilize WSRM. If you use Local System as the service account, be aware that Local System has this privilege by default.

Resolving Disk Contention and Maximizing I/O Resources

You can resolve disk contention between the relational engine and Analysis Services by utilizing separate LUNs, utilizing one set of LUNs for Analysis Services and another set for the relational engine. For more information, see Accelerating Microsoft adCenter with Microsoft SQL Server 2008 Analysis Services.

Maximizing Processing Performance in a Consolidated Environment

For many consolidated environments, you may want to separate the Process Data and Process Index phase of the Analysis Services processing operation to improve the performance of the entire processing operation. Separating these processing operations enables you to reconfigure the SQL Server relational engine after the Process Data phase of the processing operation completes to enable all server resources to be made available to Analysis Services during the Process Index phase of the processing operation. The Process Index phase is much more processor and memory intensive than the Process Data phase from a single thread perspective. By running the Process Index phase separately, you can parallelize the indexing operation to make use of all available resources, because the Process Index phase does not require access to the SQL Server relational engine. For more information, see the Analysis Services Performance Guide.

Utilizing a Dedicated Processing Server

In the absence of a processing window, we implemented this consolidation solution by performing all processing on physical servers not utilized for querying (called processing servers) and directing all user queries to one or more physical servers not utilized for processing (called query servers). Utilizing separate servers for querying and processing eliminates the resource contention during processing that reduces query performance while enabling you to maximize processing performance. For more information about the benefits of utilizing multiple query servers, see Utilizing Multiple Query Servers.

Utilizing a dedicated processing server and one or more query servers may, at first, seem inconsistent with consolidation. Indeed, it is if you are only looking at consolidating a very small number of Analysis Services databases. However, if you are looking to consolidate dozens of older, department-sized servers onto a small number of newer, server-class servers, consolidating to a small number of servers works very well – each server with its own standardized role.

Note: When utilizing a dedicated processing server, we have frequently consolidated the relational data warehouse on the processing server.

This solution requires you to use one of several options to synchronize the newly processed objects from the processing server to the query server(s):

• Analysis Services Synch Method – You can use the Analysis Services synch method to perform an online synchronization of the newly processed database on the processing server with the database on one or more query servers. This method synchronizes only the changed files between the processing server and the query server(s), provided that your folder structures match between the source and destination databases. If the folder structure does not match, all files are copied and synchronized rather than only the changed files.
• Backup and Restore Method – You can back up the newly processed database on the processing server, copy the backup file to the query server(s), and then restore the database while querying is occurring (a database lock is required at the end of the restore operation). To maximize query performance during restoration, you can restore the database on the query server using a different database name and then, after the restore is complete, drop the existing database and rename the newly restored database. This latter option also enables you to perform any quality checks on the restored database before you present it to users.
• Attach and Detach Method – You can detach the newly processed database from the processing server, copy the detached database folder to the query server(s), and then attach it to the query server(s). You cannot attach over an existing database, and you cannot change the name of the database while you are attaching. You can attach it as read/write to a single query server or as read-only to multiple query servers. This latter option is quite useful if your query workload is processor and memory intensive rather than I/O intensive. You can attach the newly processed database to a physical or a virtual server. For more information about utilizing a virtual server, see Utilizing Hyper-V in a Consolidated BI Environment.
• Robocopy Method – You can use Robocopy to copy only the delta files from your processing server to your query server(s). This method, however, requires you to either stop the Analysis Services engine or detach the database that is being synchronized. For more information, see Sample Robocopy Script. In Windows 7 and Windows Server 2008, Robocopy has been enhanced with the multi-thread support. For syntax, see Robocopy.

For more information about these options, see Analysis Services Synchronization Best Practices.

Co-Locating Multiple Analysis Services Databases onto a Single Server

In order to realize the benefits of increased hardware utilization when co-locating multiple Analysis Services databases onto a single server, we have implemented this consolidation option either with a single Analysis Services instance or with multiple Analysis Services instances. When consolidating multiple databases, we have generally utilized multiple query servers and one or more processing servers, and we have co-located the relational database source with its corresponding processing server. When we utilize multiple query servers, we use a number of techniques to load-balance user queries.

Important: We have only implemented this type of consolidation with small to medium sized Analysis Services databases; consolidating multiple very large or very complex databases onto a single instance simply has not made sense from a resource contention point of view. While the definition of a small or medium sized Analysis Services database is at best imprecise, our definition includes databases against which individual queries do not consume a significant portion of the server resources to resolve and which are primarily constrained by concurrency rather than query complexity. For more information about resource usage by queries, see the SQL Server Analysis Services 2008 Performance Guide.

Utilizing a Single Instance or Multiple Instances

We have rarely seen the need to spread Analysis Services databases across multiple instances of Analysis Services on a single physical server to improve query performance. We have used multiple instances of Analysis Services on a single instance when users are querying a database in one instance while the same database in a second instance is being synchronized. This configuration is used to enable user queries to be directed to the newly synchronized database on the second instance without downtime.

Note: Multiple instances of the SQL Server relational engine are sometimes used to manage the priority of queries. With Analysis Services, you can use the CoordinatorQueryBalancingFactor and CoordinatorQueryBoostPriorityLevel properties to prioritize long-running queries versus short queries. For more information, see the SQL Server Analysis Services 2008 Performance Guide.

When choosing between querying all databases in a single instance versus spreading the databases across multiple instances on a single server, consider the following:

• Utilizing a single instance rather than multiple instances for the same number of databases generally uses fewer system resources.
• All data security operations can be performed with database level permissions such that each database can have a different set of database administrators and users with read permissions.
• Processing and synchronization operations are generally scripted and performed by SQL Server Agent or other scheduling tools, and they are executed using server-level permissions. For these types of operations, it generally does not make a difference whether a single or multiple instances are used.
• If you have different sets of instance-level administrators, you need multiple instances. For example, to perform a trace of data within a database or to clear the cache of a database, you must be a server-level administrator. If you need to allow the administrators of a database to trace queries in their database without being able to see the data in another database, you will need to place that database within another instance.
• If you have different instance-level properties for different databases, you need multiple instances. For example, you may want to have different priorities for long-running queries on one instance versus another instance. We have not, however, found this need to date in the customer organizations with which we have worked.

Utilizing Multiple Processing Servers and Query Servers

With multiple Analysis databases on a single physical server, the processing of any single database can affect the query performance of any query against any database on the server. A long-running query against a database can prevent the processing of that database from completing, unless the long-running query is forcibly terminated. Furthermore, within a single instance, a long-running query can prevent a processing operation from completing its commit phase and block subsequent queries as well as all new connections until the commit operation completes successfully or times out.

When consolidating databases onto a query server, we have generally utilized one or more processing servers and multiple query servers, and we have co-located the relational database source with its corresponding processing server. These servers provide the following benefits:

• Processing servers – We have sometimes utilized multiple processing servers so that multiple databases can easily be processed simultaneously. Processing databases on servers that are not being used for querying insulates the query performance of all Analysis databases from resource contention due to the processing of any single database. Your need for one or more processing servers will depend upon the frequency with which each Analysis database is processed and the number of databases that you are processing. Co-locating the relational data warehouse with its corresponding processing server increases processing performance and server utilization for the reasons previously discussed.
• Query servers – When using separate processing and query servers, we have always utilized multiple query servers for performance and availability. With multiple query servers, we can load balance our workload across multiple query servers to maximize performance or take a server offline without impacting availability.

Synchronizing a processed database from its processing server to multiple query servers adds minimal additional complexity or synchronization time compared to synchronization with a single query server. You can synchronize all query servers either simultaneously or in two (or more) groups. For example, you could synchronize a processed database to your first group of query servers while directing all new queries against that database to the second group of query servers. If you use Server Sync, this approach allows existing queries against that database to complete while synchronization is occurring and new queries to execute against the servers that are not being synchronized – to maximize query performance. After synchronization is complete for the first group, all queries against that database can then be directed to the first group of query servers so that the newest data is being queried while the second group of query servers is synchronized. This approach is illustrated in figures 1 and 2.

Figure 1: Synchronizing First Query Server

Figure 2: Synchronizing Second Query Server

Load Balancing User Queries for Performance and Availability

When you utilize multiple query servers, you need to load balance the user queries across the query servers for performance and availability. We are taken several approaches to load balance user queries across multiple query servers.

Load Balancing at the Presentation Layer

One approach is to load balance at the presentation layer. This approach utilizes multiple Web servers, with some Web servers directing MDX queries to one query server and other Web servers directing MDX queries to a second query server. This approach is discussed in Scale-Out Querying with Analysis Services and is illustrated in figure 3.

 

Figure 3: Query Scale-Out Topology with Multiple Web Servers

This approach works well as long as the workload directed to each Web server is evenly balanced, but it does not provide load balancing where that is not the case. For example, suppose some users are connecting from Excel to one query server and through Reporting Services to a second query server. In this scenario, the load from each set of users may well not be balanced.

Load Balancing using Network Load Balancing Software or Hardware Solutions

Another approach is to use network load balancing software, such as Microsoft’s Network Load Balancing (NLB) software solution, or network load balancing hardware, such F5.

Using Network Load Balancing Software

NLB is an optional component in the Windows Server 2008 and Windows Server 2008 R2 operating systems and is built into Windows Server 2003. NLB load balances network traffic among multiple servers in an IP cluster. For more information, see Windows Server 2008 NLB and Windows Server 2003 NLB.

 

NLB is a software solution to create a cluster of machines, each with two IP addresses – a private unique one and a virtual one that is shared by all machines in the cluster. Each machine in the cluster runs an algorithm that determines whose turn is next in responding to requests. These machines also exchange heartbeats with each other, so they all know if one server goes down and won’t allocate any more requests to such a machine until it is healthy again.

NLB supports a number of affinity options depending on your version of Windows Server. When no affinity is specified, all network requests are load-balanced across the cluster without respect to their source. In general, when affinity is enabled, all client requests from the same IP address are responded to by the same cluster host.

However, NLB has several limitations when working with multiple Analysis Services query servers, particularly when each query server has a subset of the Analysis Services databases to which queries are being directed (for example, all databases are located on at least two servers for availability, but all databases are not located on all servers):

• NLB load balances at the IP address level, rather than at the database level. For NLB to load balance across multiple Analysis Services query servers, each must contain the same set of databases.
• NLB monitors availability at the server level, rather than at the Analysis Service process or database level. As a result, if the Analysis Services process is not responding for any reason, NLB will continue to forward user queries to the nonresponsive Analysis Services query server. In addition, as an Analysis Services database is being updated or is out-of-date, NLB has no ability to remove that database from the cluster. Rather, an NLB administrator would have to remove the entire server from the cluster during an Analysis Service synchronization process.
•  NLB has no ability to monitor processor load on each of the servers in an NLB cluster and direct user queries to the server with the least load.

Due to these limitations, NLB is not our recommended solution for load balancing user requests across multiple Analysis Services query servers.

Using Network Load Balancing Hardware

Network load balancing hardware solutions have more sophisticated affinity options, such as cookie persistence, and they include dynamic load balancing algorithms that track server performance. However, these solutions do not solve the problem of removing a single database on an Analysis Services query server from the cluster while continuing to direct user queries to the other databases on that query server. In addition, these hardware solutions do not, out of the box, monitor the availability of the Analysis Services process itself.

Due to these limitations, using network load balancing hardware solutions is not our recommended solution for load balancing user requests across multiple Analysis Services query servers.

Load Balancing Using a Custom Load Balancing Solution

When using multiple query servers with multiple databases on each server, we have found the need to load balance queries at the database level as well as the need to monitor service availability at the Analysis Services level. To accomplish this, we wrote a custom Analysis Services load balancing solution (ASLB) that consists of a load balancing Web service backed by a SQL Server load balancing metadata database to load balance MDX queries across multiple query servers. The metadata database contains information about each Analysis Services query server to which queries can be redirected by the Web service.

Note: This load balancing Web service can be clustered using NLB for availability, and the load balancing metadata database can also be mirrored for availability.

The ALSB solution solves the problems previously discussed as follows:

• Process availability monitoring – A SQL Server Agent job monitors the availability of the Analysis Service process on each query server. If the process fails to respond, this information is recorded in the metadata database so that no further user queries are directed to that instance of Analysis Services until it is healthy.
• Resource Utilization Monitoring – A SQL Server Agent job monitors the processor utilization on each query server and records this information into the metadata database. The Web service uses this information to direct user queries to a query server that is not overloaded and which has a copy of the requested database.
• HTTP Redirection – As illustrated in figure 4, the user application (such as Excel or Reporting Services) connects to the ASLB Web service specifying a particular Analysis Services database in the URL. The Web service performs a table lookup to retrieve the name of a query server that is running, that contains the most up-to-date version of the requested database, and that has the lowest workload. The load balancing Web service constructs the URL for the appropriate Analysis server and redirects the user application to that URL.

 

Figure 4: Individual Query Using the Analysis Services Load Balancer

• Removing individual databases from a cluster – ASLB enables you to remove a database from being available to accept new queries by simply modifying a value in the appropriate table in the metadata database. Figure 5 illustrates this concept during the synchronization of cube B from the processing server to a query server, directing all new queries to the second query server until server synchronization completes. Meanwhile, queries to all other databases on the first query server continue unabated, enabling you to drain a server workload without terminating running queries.

Figure 5: Querying and Processing Using the Analysis Services Load Balancer

• Utilizing different databases on each server – If you utilize more than two query servers, each query server does not have to have the same list of Analysis Services databases because ASLB has a record in the metadata database of which servers have which databases. For example, if you have four query servers, you can have your most heavily queried databases on all four query servers and your least frequently queried servers on only two of the query servers. This configuration provides load balancing and availability benefits. Finally, you can also have Analysis Services databases on multiple query servers and use ASLB to load balance among some of these query servers while leaving other query servers “in reserve” – for example, bringing them online for a particular database only during peak time periods. The query servers “in reserve” can also be virtual servers that are provisioned automatically based on demand (more on this in another paper).

Important: This solution uncovered several bugs that have been fixed in the most recent cumulative updates to SQL Server 2008 Service Pack 1 (SP1). To receive these bug fixes, you should patch your SQL Server using Service Pack 1 Cumulative update 4 or newer. For more information about the newest cumulative updates, see Knowledge Base article #970365.

Note: The code for the ASLB solution as well as an installation and configuration document is available for download on the Microsoft download center at http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=c7737e80-8dfd-4cb6-a27e-69bbe03b2f9e.

Links

SQL Server Consolidation at Microsoft

http://technet.microsoft.com/en-us/library/dd557540.aspx

Green IT in Practice: SQL Server Consolidation in Microsoft IThttp://msdn.microsoft.com/en-us/architecture/dd393309.aspx

Conclusions

Server consolidation in the Analysis Services environment can be used to increase processing performance, fully utilize server resources, reduce management costs, and save energy. Analysis Services server consolidation may consist of co-locating the relational data warehouse with the Analysis Services instance and / or standardizing on Analysis Services processing and query servers. To realize these benefits and mitigate the risks, you must manage the resource contention inherent in Analysis Services consolidation. Properly managed, Analysis Services server consolidation can be used to increase performance as well as availability.