Analysis Services scale-out processing architecture consideration to keep balance between processing and query workloads. It is also important when business expects nearly real time processing and cannot create an exclusive processing window.
Another benefit to achieve high availability if multiple query servers in a scale-out farm, some of them can fail but the system will remain online.
AS scale-out processing architectures choices:
- Dedicated processing architecture
- Query/processing flipping architecture
1. Dedicated Processing Architecture
In this, an instance of Analysis Services is reserved to process all new, incoming data. After processing is done, the result is copied to query servers. The advantage of this architecture is that the query servers can respond to the queries without being affected by the processing operation. A lock is required only when data is updated or added to the cube.
In this business need to consider how to get the data from the processing instance to the query servers. There are several ways to achieve this.
- Analysis Services Cube Synchronization: By using this built-in Analysis Services functionality, you can move the delta data directly to the query servers.
- Robocopy or NiceCopy: By using a high-speed copying program, you can quickly synchronize each query instance with its own copy of the changed data. This method is generally faster than cube synchronization, but it requires you to set up your own copy scripts.
- SAN Snapshots or Storage Mirrors: Using SAN technology, it is possible to automatically maintain copies of the data LUN on the processing servers. These copies can then be mounted on the query servers when the data is updated.
- SAN Read -Only LUN: Using this technique, which is available only in SQL Server 2008 and SQL Server 2008 R2, you can use read-only LUN to move the data from the processing instance to the query servers. A read-only LUN can be shared between multiple servers, and hence enables you to use more than query server on the same physical disk.
2. Query/Processing Flipping Architecture
The dedicated processing server architecture solves most scale-out cases.
However, the time required to move the data from the processing server to the query servers may be restrictive if updates happen at intervals shorter than a few hours. Even with SAN snapshots or read-only LUN, it will still take some time to dismount the LUN, set it online on the query server, and finally mount the updated cube on the query servers. In the query/processing flipping architecture, each instance of Analysis Services performs its own processing.
Because each server does it own processing, it is possible that some servers will have more recent data than others. This means that one user executing a query may get a later version of the data than another use executing the same query concurrently.
However, for many scenarios where you are going near-real time, such a state of loose synchronization may is an acceptable tradeoff. If the tradeoff is not acceptable, you can work around it with careful load balancing – at the price of adding some extra latency to the updates.
In the below diagram, you can see that the source system receives more processing requests than in the dedicated processing architecture. You should scale the source accordingly and consider the network bandwidth required to read the source data more than once.
The query/processing flipping architecture also has a build in high availability solution. If one of the servers fails, the system can remain online but with additional load on the rest of the servers.
o Query Load Balancing: In any scale-out architecture with more than one query server, business needs to have a load balancing mechanism in place. The load balancing mechanism serves two purposes. First, it enables to distribute queries equally across all query servers, achieving the scale-out effect. Second, it enables to selectively take query servers offline, gracefully draining them, while they are being refreshed with new data.
When business uses any load-balancing solution, be aware that the data caches on each of the servers in the load-balancing architecture will be in different states depending on the clients it is currently serving. This results in differences in response times for the same query, depending on where it executes.
There are several load balancing strategies to consider. These are treated in the following subsections. As you choose the load balancer, bear in mind the granularity of the load balancing and how this affects the process to query server switching. This is especially important if you use a dedicated processing architecture. For example, the Windows Network Load Balancing solution balances users between each Analysis Services Instance in the scale-out farm. This means that when you have to drain users from a query server and update the server with the latest version of the cube, the entire instance has to be drained. If you host more than one database per instance, this means that if one database is updated the other databases in the same instance must also be taken offline. Client load balancing and Analysis Services Load Balancer may be better solutions for you if you want to load-balance databases individually.
o Client Load Balancing: In the client load balancing, each client knows which query server it will use. Implementing this strategy requires client-side code that can intelligently choose the right query server and then modify the connection string accordingly. Excel add-ins are an example of this type of client-side code. Note that you will have to develop your own load balancer to achieve this.
o Hardware-Level Load Balancing: Using technologies such as load balancers from F5, it is possible to implement load balancing directly in the network layer of the architecture. This makes the load balancing transparent to both Analysis Services and the client application. If you choose to go down this route, make sure that the load-balance appliance enables you to affinitize client connections. When clients create session objects, state is stored on Analysis Services. If a later client request, relying on the same session state, is redirected to a different server, the OLE DB provider throws an error. However, even if you run affinity, you may still have to force clients off the server when processing needs to commit. For more information about the ForceCommitTimeout setting, see the locking section.
o Windows Network Load Balancing: The Microsoft load-balancing solution is Network Load Balancing (NLB), which is a feature of the Windows Server operating system. With NLB, you can create an NLB cluster of Analysis Services servers running in multiple-host mode. When an NLB cluster of Analysis Services servers is running in multiple-host mode, incoming requests are load balanced among the Analysis Services servers.
o Analysis Services Load Balancer: Analysis Services is used extensively inside Microsoft to serve our business users with data. As part of the initiative to scale out our Analysis Services farms a new load balancing solution was built.
The advantages of this solution are that you can load balance on the database level and that you use a web API to control each database and the users connected to it. This customized Analysis Services load balancing solution also allows fine control over the load balancing algorithm used. Be aware that moving large datasets over the web API has a bandwidth overhead, depending on how much data is requested by user queries. Measure this bandwidth overhead as part of the cube test phase.
Source: Microsoft.com and SQLCat.com