SQL Server, SSIS, SSAS and SSRS on ONE Server

Understanding the difference between SSIS - SQL Server Intigration Service, SSAS-SQL Server Analysis Services and SSRS -SQL Server Reporting Services.

SQL Server 2008 R2 Services Availability in Editions

One key consideration and difference is that not all services come with the different editions of Microsoft SQL Server 2008 R2. The six SQL Server Editions for SQL Server 2008 R2 are Datacenter, Enterprise, Standard, Web, Workgroup and Express. Integration Services is included in the Datacenter and Enterprise editions. Reporting Services is included in all editions of SQL Server 2008 R2. However, full reporting services are limited in the Standard, Web Workgroup and Express editions. Lastly, Analysis Services is available in the Datacenter, Enterprise and Standard editions with advanced analytical functions in the Enterprise and Datacenter editions only. The Standard edition does not include SQL Server PowerPivot for SharePoint that is part of the basic Analysis Services package.

SQL Server Integration Services

The SQL Server Integration Services (SSIS) is the data-warehousing arm of the SQL Server 2008 R2 suite -- equipped with superior Extract, Transform and Load (ETL) capabilities. It provides the vehicle for moving data from different data sources to another and changing the data, if necessary. The three components in the SSIS platform for starting the data integration process are the Import and Export Wizard, SSIS Designer and SSIS API Programming. The Import and Export Wizard simply transfers data from source to destination but does not include data transformation capabilities. The SSIS Designer is an integrated component of the Business Intelligence Development Studio used for the development and maintenance of integration services packages. SSIS API Programming module allows you to code SSIS packages using any number of programming languages.

SQL Server Reporting Services

The SQL Server Reporting Services (SSRS) is a framework of reporting mechanisms such as the Report Builder, Report Designer, Report Manager and Report Server that work together through a Web interface to enable the development of concise interactive reporting solutions in print or Web format. Report Builder and Report Designer are two SSRS components for generating reports. Report Builder is a simple solution for the information worker or business user to create quick reports without the need to understand the core structure of the data. The Report Designer is a tool for developers because it adds complexity to custom reports development. Using this tool requires an understanding of the Business Intelligence Development Studio shell in Visual Studio and the underlying structure of the data. According to Microsoft, the Report Server is the core process engine in SSRS that manages the processing and delivery of reports using processors. The Report Manager is an administrative tool that controls reporting services through a Web interface.

SQL Server Analysis Services

The SQL Server Analysis Services, or SSAS, is a multidimensional analysis tool that features Online Analytical Processing, powerful data mining capabilities, and deeper dimensions to business information within a relational database. Multidimensional analysis is an OLAP technique that produces the ability to analyze large quantities of data by storing data in axes and cells instead of the traditional relational two-dimensional view in rows and columns. SSAS places predictive analytic capabilities in the hands of information workers by creating an instant connection to backend data using familiar applications such as Microsoft Excel and SharePoint for analysis, visual presentation and collaboration.

When SQL Server, SSIS, SSAS and SSRS are on ONE Server:

Best practice dictates that we use a separate server for each of these SQL Server Services. And this seems logical because otherwise these services will compete over server resources. In IT we call this competition: contention.

However there are some great reasons to put these services on one box:

• Licensing: SQL Server licensing can be expensive. And you need licenses for every server on which a services runs.
• Better resource utilization: Less servers, less power usage, less maintenance and -monitoring cost.
• Sometimes the network is the problem as a lot of data moves from the SQL Engine to SSIS or SSAS resulting in network congesting. If services run on the same machine, SQL Server uses the Shared Memory Protocol which is faster and leads to less network congestion.

In a scenario with all SQL/BI Services on one server we need some strategies to diminish the described contention. This is of course a big subject and I can only touch the basics in this post and give you some tips.

TIP 1: Limit the software and services on the server

This seems logical but I have been to several sites where developers remote desktop into the production server to do maintenance- and other jobs. This is certainly not a best practice. Better is to not install any client tools on the server and use a separate developer/test server to do maintenance.

TIP 2: Get an overview of your typical daily workload

In this image I have mapped a typical daily workload to the services needed to perform the workload:

• From 07:00 AM to 23:00 is the extended working day window. Some users start early, others end late: all are extensively opening SSRS reports and querying SSAS cubes.
• From 23:00 to 01:00 AM backups are running for all (source) systems. This is the period we do our own SSAS and SQL Server backups.
• From 01:00 AM tot 07:00 AM is our process window

In this period:

• we use SSIS to land data from our source systems into the staging database
• we use SSIS to load data from our staging database into the data warehouse
• we’ll process the dimensions and cubes of our SSAS databases.
• we warm the cache of our SSAS database and start distributing reports using SSRS subscriptions and SSIS for large Excel reports.

With this knowledge we have a clear understanding of potential contention issues. Furthermore we can schedule to stop and start services on a need to use basis. We can use the operating system commands NET START and NET STOP for this purpose.

TIP 3 Set Maximum Memory Setting for SQL Server

With the max server memory setting you can place a limit to the amount of buffer pool memory used by SQL Server. Which leads to the question to what amount should we limit the buffer pool memory?

During our extended working day window we will probably not benefit from a large buffer pool memory: Most queries will probably be answered by Analysis Services. However in the process windows we will benefit from a larger buffer pool memory. We can change this property during the day and schedule these changes by using the sp_configure system stored procedure. Based on 24GB of RAM, reserve 4 GB for the operating system and:

• During working day window set max server memory to 4 GB, reserve 8 GB for SSAS and 8 GB for SSRS.
• During the process window set max server memory to 10 GB, reserving 10GB for SSIS and 8GB for SSAS.

TIP 4 Set SSAS Memory Settings

The SSAS memory settings are available in the msmdsrv.ini file and in properties window of the server. If the value is between 0 and 100 than this means a percentage of total available physical memory. Above 100 means bytes. Change these settings to:

• Memory\HardMemoryLimit: from the default of 0 to 32
• Memory\TotalMemoryLimit: from the default of 80 to 28
• Memory\LowMemoryLimit:from the default of 65 to 24

This means that Analysis Services will start freeing up memory once its has reached the LowMemoryLimit threshold of 24% of physical memory. This process will get more aggressive if it reaches the other thresholds.
While you’re at it change some other memory properties:

• OLAP\Process\BufferMemoryLimit from 60 to 20
• OLAP\Process\AggregationMemoryLimitMax from 80 to 14
• OLAP\Process\AggregationMemoryLimitMin from 10 to 4

TIP 5 Set SSRS Memory Settings

In the RSReportServer.config file add the WorkingSetMaximum property and set it to 8GB (for our 24GB example):
<WorkingSetMaximum>8000000</WorkingSetMaximum>
Restart the reporting services service, so other memory related properties that are based on WorkingSetMaximum get set.

TIP 6 Monitor and adapt the memory setting

Use the settings in tip 3, 4 en 5 as a starting point but monitor memory usage and change these properties to map them to your workload.

TIP 7 Use Windows System Resource Manager to restrict CPU utilization

With Windows System Resource Manager (WSRM) you can create custom resource allocation policies to restrict resources (CPU, RAM) to an application. You can map your workload windows to policies and define these policies e.g.:

• policy ProcessData which runs when Analysis Services is processing data, set the CPU percentage for SQL to 45% and 45% for SSAS
• policy ProcessIndex which runs when Analysis Services is processing aggregations, set the CPU percentage for SQL to 10% and 80% for SSAS

Next you can setup calendar rules to apply the different policies to the correct time frames.

However this has the drawback that you have two calendar schemes: one in SQL agent and one in WSRM that you have to keep in sync. Furthermore it’s possible that there aren’t any CPU contention issues. My advice would be to monitor CPU usage and plan resource allocation policies if necessary based on the monitoring results.

TIP 8 Don’t write to the same disk you’re reading from

In the daily process window we will typically load large amount of data in our data warehouse:

• We load data from the source system in a staging database.
• From the staging database we load the data in the data warehouse.
• From the data warehouse we load the Analysis Services cubes.

In this scenario put the data warehouse database files on another drive as the staging database and the Analysis Services cubes.

Conclusion

In this post I focused on diminishing contention issues when using one server for your BI Solution running SQL Server, Analysis Services, Integration Services and Reporting Services. Key aspect is understanding your workload and the role the different applications play as well as the properties you can tweak to diminish contention.