What should be Server Architecture for a BI layer with OLTP Source , Data Warehouse , SSIS and SSAS ? RRS feed

  • Question

  • Hi,

    With the current design model of the BI project that I am currently working on , OLTP database , Data Warehouse , SSIS and SSAS instance reside on the same server. This current architecture is degrading the performance of the Server.

    Please help me with a document from Microsoft or any reference with the recommended architecture it should be for the current BI Project that I am currently working on.


    Neeraj Dubey

    Friday, March 29, 2019 12:33 PM

All replies

  • You will need to determine what is the largest resource consumer and move that to a different server. OLTP and OLAP/Reporting workloads are radically different. OLTP tends to have far more reads than writes, but both reads and writes tend to affect small data sets. OLAP/DW/Reporting workloads tend to involve large scans and low writes. 

    SSIS can be run on a different server, sometimes on a stand alone server which just runs SSIS. Same for SSAS and your DW instances.  I can't find any documentation for Microsoft explicitly stating the optimal topology for these servers. 
    Friday, March 29, 2019 2:06 PM
  • Thanks for your response

    Did you mean the optimal topology of following servers should be as mentioned below : 

    1. Stand alone server for OLTP

    2. Stand alone server for SSIS

    3. Stand Alone Server for DW

    4. Stand Alone Server for SSAS

    Monday, April 1, 2019 12:09 PM
  • Can any one help me with standard documentation for this topology of Server architecture for such requirement ?

    Monday, April 8, 2019 6:08 AM
  • Hi Neeraj,

    Your profile says you work for Tata.

    Tata has lots of experience in data warehousing. You should talk to some of the people inside Tata.

    If Tata would like me to help them across their project I am available. Ok?

    I am one of the worlds top experts on data warehousing etc and I would be happy to help Tata or the other indian outsourcers. 

    Best Regards 


    Peter Nolan

    Sunday, June 30, 2019 3:03 PM