Report Portal

SQL Server ifrastructure planning and design paper (SSAS)

Written by Microsoft on . Posted in Design

The Infrastructure Planning and Design (IPD) guides are the next version of Windows Server System Reference Architecture. The guides in this series help clarify and streamline design processes for Microsoft infrastructure technologies, with each guide addressing a unique infrastructure technology or scenario.

SQL Server 2008 infastructure planning and design paper contains paragraph on Analysis Services infrostructure design and planning.

SQL Server 2008 infrastructure planning and design paper can be downloaded here (File SQL Server 2008.zip).

Below is re-published part of that document related to SSAS:

Step 5: Design the SQL Server Analysis Services Infrastructure


In Step 2, the roles that need to be implemented were determined. If it was determined that SQL Server Analysis Services (SSAS) is required in the organization, the SSAS infrastructure will be designed in this step. If it was determined that SSAS is not required, go to the next step.

Task 1: Determine Resource Requirements

SSAS uses online analytical processing (OLAP) multi-dimensional databases, also called cubes, which are stored in a folder on the file system.
Before deploying SSAS, consider the resource requirements of the installation. In particular, consider disk storage requirements and memory and processor needs.

Disk Storage Requirements

Cube sizes depend on the size of the fact tables and dimension members. If no other data is available, a good starting point may be to plan to allocate approximately 20–30 percent of the amount of storage required for the same data stored in the underlying relational database, if that’s where the data is originating.

In addition to the storage needed for the cube, consider the size of the aggregations. Aggregations are typically less than 10 percent of the size of the data stored in the underlying relational database, but they can vary based on the number of aggregations.

During processing, SSAS stores copies of the objects in the processing transaction on disk until it is finished, and then the processed copies of the objects replace the original objects. Therefore, sufficient additional disk storage must be provided for a second copy of each object. For example, if a whole cube will be processed in a single transaction, sufficient hard disk space to store a second copy of the whole cube will be required.

Determine the disk storage required, and record this in Table A-1 in Appendix A.

IOPS and Throughput Requirements


Disk throughput is a measure of the data transfer rate through an I/O subsystem. The processing of OLAP databases is read and write-intensive, while queries will result in random reads. Design the I/O subsystem to support the expected IOPS and throughput that will be required.

Memory and Processor Requirements

The amount of memory and processor resources that are available to Analysis Services varies depending on which version of the Windows operating system is installed on the server. For more information on running SSAS on Windows Server® 2008, see http://technet.microsoft.com/en-us/library/ms175672.aspx.
The product group recommends 4–8 GB of memory per processor core, but this can be more if queries return very large result sets.

Task 2: Determine SQL Server Version

Evaluate the application for which SQL Server is being designed to select the required version. SQL Server is available in 32-bit and 64-bit versions. Depending on the ODBC drivers available for the source and destination, a specific version may be required. Many legacy ODBC drivers are only available in 32-bit versions. Even though most 32-bit ODBC versions will work on a 64-bit machine, the performance will not be as efficient as using a 64-bit version of the driver.
Use Table A-1 in Appendix A to record the version of each SSAS server that will be required in order to support the business requirements to connect to other systems.

Task 3: Decide Whether Scalable Shared Databases Will Be Used

Scalable shared databases can apply to both the relational database and the multi-dimensional databases (cubes). These can be used to scale out querying or reporting loads. With scalable shared OLAP databases, a read-only copy of the database on a shared drive is shared between several SSAS servers.

The benefits of using shared databases include an increase in the number of processors available and more memory for processing queries; however, proper disk configuration is important for the disk containing the database.

The restrictions of using shared databases include:
• The database must be on a read-only volume.
• The databases are supported by Windows Storage running only on Windows Server 2003 SP1 or later.
• Scalable shared databases do not support database snapshots.

Determine whether scalable shared databases will be used and record this in Table A-1 in Appendix A.

Refer to the “Additional Reading” section to find links to white papers on scalable shared databases.

Task 4: Determine Scaling Needs

If cube processing affects query performance or if processing can’t occur during times of reduced query load, consider moving 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” in SQL Server Books Online at http://msdn.microsoft.com/en-us/library/ms174928.aspx.

Processing can also be distributed 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” in SQL Server Books Online at http://msdn.microsoft.com/en-us/library/ms175604.aspx.

Determine whether to scale up or scale out for SSAS and record this information in Table A-1 in Appendix A.

Task 5: Decide Whether to Cluster

SSAS natively supports failover clusters (formerly known as server clusters or as MSCS) in Windows Server. Failover clustering enables recovery from a service failure in the shortest time possible. However, it is important to note the following considerations:

  • When an instance fails over to the next available node, all connections are lost, with the corresponding loss of uncommitted work. Most client applications should be able to handle this situation. Often, performing a refresh of the application will restore the results.
  • Administrative applications that process Analysis Services objects should be designed to handle failover situations.
  • SQL Server 2008 Enterprise enables up to 16 nodes in the failover clusters, while SQL Server 2008 Standard is limited to two nodes.

Record in Table A-1 in Appendix A whether clustering will be used for the SSAS server.

Task 6: Determine Placement

Factors such as politics, policy, network constraints, proximity to data sources, and geography can determine where to place the SSAS servers. In this task, it will be determined where to place each SSAS server.

SSAS can be run in a virtualized environment if the memory, disk, and network requirements do not exceed the throughput capabilities of the virtual machine.

SQL Server roles can coexist with other SQL Server roles on the same server. Decide whether the SSAS role will coexist with other roles on an existing server or if it will reside on a dedicated server.

Record in Table A-1 in Appendix A whether SSAS will be placed in a virtualized or physical environment and whether the SQL Server role will coexist with other SQL Server roles on the same server or reside on a dedicated server.

This task should be repeated for each additional instance required for SSAS.

Step Summary

In Step 5, the SSAS infrastructure was designed if it was determined that SSAS is required in the organization. The resource requirements, the SQL Server version, whether scalable shared databases will be used, and the scaling needs were determined. Additionally, whether to cluster and where to place the SSAS server and roles were determined. If it was determined that SSAS is not required, the reader was advised to go to the next step.

The following Step 5 outputs were recorded in Table A-1 in Appendix A:

  • The resource requirements.
  • The SQL Server version.
  • Whether scalable shared databases will be used, the scaling requirements, and whether clustering will be used to support fault tolerance.
  • The SSAS placement and whether the SQL Server role will coexist with other SQL Server roles on the same server or reside on a dedicated server.

Additional Reading

Tags: design, paper

Similar Articles

 

2007-2015 VidasSoft Systems Inc.