Wednesday, December 19, 2012 7:08 AM
We are using SQL Server 2012 EE and are expecting a max of 3TB of Data for DW.
SSRS Reports (in SP integrated mode ) are built on SSAS.
In a Virtualized environment , what would be the recommended architecture to deploy different components of BI.
VM1 contains SQL Server ,SSIS,SSAS
VM2 contains Sharpoint and SSRS
VM1 contains SQL Server,SSIS
VM2 contains SSAS
VM3 contains SSRS and Sharepoint
Option1 might give processing advantages and might use shared memory but is it the recommended practice ?
Please suggest if we have any other options.
Wednesday, December 19, 2012 7:01 PM
SQL Server and SSAS have a different strategy in utilizing the memory (RAM) and depending on the current workload it can happen, that e.g. SSAS requires the memory used by SQL Server and this can cause performance problem. If you use SQL Server as data warehouse for SSAS, then you will see this effect extremly during cube processing.
So option 2 will be a better solution regarding performance.
Olaf HelperBlog Xing
Thursday, December 20, 2012 8:33 AM
1) Setting the minimum and maximum memory limit for SQL server and SSAS is also a best option(with option 1), in this case maximum resource utilization is the advantage, and better performance, provided sufficient memory is allocated for SSAS. If memory is not sufficient for SSAS also SQL server is utilizing the full memory then performance will go down
2) If SSAS is configuring in a separate VM, the consistent performance would be there for cube processing and reading the cube.
conclusion: Separate VM for SQL sqrver and SSAS would be best option if the CUBE and SQL server need to run parallel.
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Thursday, December 27, 2012 4:35 AM
Wednesday, January 02, 2013 5:44 PMif your disk requirement wasnt 3 tb you could have still gotten away with option 1. For a 3 TB system you want to make sure that each VM has enough resource allocated to it. When processing a cube you dont want SSAS and the SQL engine in the same hardware. Vm also is not known to be foolproof and meeting any kind of SLA with all your environments hosted on the same machine is almost impossible. with SSRS and Sp you may want scale out deployment in future in which case option 2 is better suited. Also the cube has different IO needs compared to DB engine so the behaviour of the system is going to be unpredictable and not easy to troubleshoot if everything is hosted in the same system.
Jayanth Kurup - www.enabledbusiness.com
Wednesday, January 09, 2013 4:33 AM
we just completed our BI project but w/o using sharepoint. We initially opted for option 2 but had to settle for option 1 - lets just say for political reasons. We had inconsistent performance throughout with our all in box solution ( database engine, ssis, ssas, ssrs ) but we finally solved it. If your are using VMware, make sure, you select 'unlimited' for the VM memory settings. Our infra guys have a VM template which caps the VMs memory to 4GB physical RAM, which caused us months of agony.
There are also other considerations, how many users will be querying the cube. Also, SQL server process doesn't release the memory after it reaches its high water mark.