Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

15.6. Scale Out with Read-Only Database

After performing all these query optimization techniques you might still find query performance degradation when multiple users are connected to your Analysis Services instance and actively querying the database. This is one of the problems Analysis Services customers face when the customer load on a specific database increases. You can try to use larger machines with more CPUs if memory or CPU is the bottleneck. You can also move to 64-bit machines if you are currently using 32-bit hardware. Of course, as you scale up, the cost of your machines will become higher. Analysis Services 2008 provides the new read-only database feature, which is discussed in Chapter 7. If your customer needs are only to improve query performance and your customers are only performing read-only queries (no updates, no writeback), you can use the read-only database feature and create a scale out strategy as shown in Figure 15-21 to improve query performance for this type of multi-user scenario.

We recommend that you have multiple Analysis Services 2008 servers configured to read from a single database on a shared SAN (Storage Area Network) to form Scalable Shared Databases that can be queried by multiple users. These servers need to be load balanced using a network load balancer as shown in Figure 15-22. You need a separate isolated machine for processing the database when there are data updates. Once the database has been processed you can detach the database ...