Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SQL Server 2012 and SSAS on same machine

Answered SQL Server 2012 and SSAS on same machine

  • Wednesday, December 19, 2012 7:08 AM
     
     
    Sign In to Vote
    0

    Hi,

    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.

    Option1:-

    VM1 contains SQL Server ,SSIS,SSAS

    VM2 contains Sharpoint and SSRS

    Option 2:-

    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.

    Vidya

    sagar

     

All Replies

  • Wednesday, December 19, 2012 7:01 PM
     
     Answered
    Sign In to Vote
    1

    Hello Vidya,

    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 Helper

    Blog Xing


     
  • Thursday, December 20, 2012 8:33 AM
     
     Answered
    Sign In to Vote
    0

    Hi Vidya,

    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.

    Regards,

    Vipin

     
  • Wednesday, January 02, 2013 5:44 PM
     
     
    Sign In to Vote
    0
    if 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
     
     
    Sign In to Vote
    0

    hi everyone,

    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.

    Thong