Post reply

OLAP and MS SQL on the same machine...

  • September 16, 2003 at 8:48 am

    #64541

    Hello,

    I would like to know advantages and disadvantages to install on the same server machine an OLAP server and MS SQL-Server.

    We have many Datwarehouses on SQL-Server, and many other OLAP db on OLAP Servers: We generally put each of them on a dedicated server. Currently, we are thinking to put in the same server machine our Datawarehouse (about 500 GB) AND our OLAP db/cubes. We are wondering what could be the advantes and disadvantages of proceeding like this ? is there any particular point we should keep attention ??

    Many thanks for your help

    Abdou

  • September 16, 2003 at 10:25 am

    #474469

    OLAP tends to be larger data sets since it precaclulates values for the cubes. This depends on how you define the cube and how much data in your warehouse is not being used, but in general, the cubes can get large.

    Not sure about the resource requirements of OLAP v OLTP. I'm sure you'd have some competition for resources, might need to prefefine memory for both, but I haven't done enough work to really point you in the right direction. Hopefully someone else has.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

    http://www.dkranch.net

  • September 17, 2003 at 1:45 am

    #474569

    I assume that by "MS SQL-Server" you mean an OLTP system?

    The http://www.sql-server-performance.com site has some useful tips here, mainly around avoiding OLAP and OLTP sharing a database.

    It used to be the case that the tuning optimizations made for OLTP would affect any OLAP services on the same server, and vice versa. I assume this is still true.

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • September 17, 2003 at 2:17 am

    #474573

    You could do this and there can be good reasons for, but you would have to carefully plan your resources

    MOLAP Cubes don't take up much space compared to databases, HOLAP and ROLAP can take up a lot more.

    Querying cubes is quick and relatively low resource hogging and runs okay alongside quite busy SQL. Cube rollups however, particularly with large numbers of aggregations, smack the server back into the 80s.

    We run MSSQL and MSOLAP services together on our main reporting server and the reason we are able to do this is careful timing. During the day no cubes are rolled up, and then they are processed between 10pm and 5am when nobody is querying SQL.

    Couple of things to be careful of:

    • Empty cells take up the same amount of space as populated cells. If you have very sparse dimensions the cubes can get very large
    • Keep cube files off the arrays used by MSSQL and vice versa
    • While optimised MOLAP cubes can be very quick and low resource to query, very sparse or ROLAP cubes can take a lot of work
    • You lose the ability to reboot the servers seperately, which can be a big problem
    • If you run a clustered MSSQL server you should be aware that clustering Analysis services is a 'mare. You are also stuck with Active|Passive only

    If you can afford two then I would go for that.

    Keith Henry




    Keith Henry


    According to everyone I know I "do something with computers?" for a living, so there you go.

  • September 17, 2003 at 8:03 am

    #474641

    I have Warehouses and OLTP dbs on the same production machines. The only time I run into resource issues is reprocessing cubes. I have 3 cubes with 50 or so dimensions, and 10 measures and 4 million records or so and it takes 3-4 hrs and 100% CPU to process the cube, so this can only be done at night. Otherwise no problems whatesoever.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply