locked
Load Balance 2 Analysis Servers RRS feed

  • Question

  • Hi,

    I would like to load balance 2 Analysis Servers using F5 or similar. Could somebody from Microsoft let me know if it's possible ?

    Also what ports would I need to open from F5. This being non clustered servers do they still run on port 2383 ? What's the best configuration to let the service run -  on a specific port or be dynamic ?

    Any input is appreciated.

    Thanks!

    Saturday, May 30, 2015 3:33 PM

Answers

  • It depends on what query tools you used and how you used them. Simple queries should be fine, but complex queries using Excel sometimes get broken into multiple statements. There are operations that generate session based sets, calculations and cubes, I'm not 100% sure what the trigger points are for all of these, but getting Excel to generate a session cube is not hard, but it definitely happens.

    If you create a pivot table in Excel (I tested on Excel 2013), put Product Lines on the rows in and Internet Sales on the Values, expand out any of the Product Lines and hightlight 4-5 products, right click and choose "Group" you will see a statement like the following in profiler:

    CREATE SESSION CUBE [Adventure Works_XL_GROUPING0] FROM [Adventure Works] ( MEASURE [Adventure Works].[Internet Sales Amount......

    Then subsequent queries will look like this and will be executed on the same SessionID

    SELECT NON EMPTY Hierarchize({DrilldownLevel(DrilldownMember({{DrilldownLevel({[Product].[Product Model Lines].[All Products]},,,INCLUDE_CALC_MEMBERS)}}, {[Product].[Product Model Lines].[Product Line].&[M]},,,INCLUDE_CALC_MEMBERS),[Product].[Product Model Lines].[Product Line1],INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Product].[Product Model Lines].[Model].[Product Line] ON COLUMNS  FROM (SELECT ({[Product].[Product Model Lines].[Model].&[Women's Mountain Shorts], [Product].[Product Model Lines].[Model].&[Mountain-500],

    ..... more products were listed here....

    [Product].[Product Model Lines].[Model].&[HL Mountain Seat/Saddle 2], [Product].[Product Model Lines].[Model].&[LL Mountain Seat/Saddle 2], [Product].[Product Model Lines].[Model].&[ML Mountain Seat/Saddle 2]}) ON COLUMNS  FROM (SELECT ({[Date].[Calendar].[Calendar Year].&[2008],[Date].[Calendar].[Calendar Year].&[2009],[Date].[Calendar].[Calendar Year].&[2010]}) ON COLUMNS  FROM [Adventure Works_XL_GROUPING0])) WHERE ([Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS


    http://darren.gosbell.com - please mark correct answers

    Wednesday, June 3, 2015 9:04 PM

All replies

  • I'm not from Microsoft, but a simple google search will bring you to the following 2 Microsoft articles

    https://www.microsoft.com/en-au/download/details.aspx?id=10556

    https://technet.microsoft.com/en-us/library/bb500217(v=sql.105).aspx

    Basically load balancing SSAS is possible, all the existing approaches involve configuring http access and then use a http load balancer or a custom solution. These articles should give you enough information for you to formulate how to implement load balancing in your environment. 


    http://darren.gosbell.com - please mark correct answers

    Sunday, May 31, 2015 10:07 AM
  • These are options for load balancing using http access. My application is already load balanced using VIP and http but my goal is here is to load balance the SSAS services. If I provide a named instance "A" on server A and "B" on server B can't a software load balancer like F5 be able to forward the VIP request  to  A or B?

    The only issue I could foresee is usage of dynamic ports or these named instances can run on static ports ?

    Monday, June 1, 2015 2:15 AM
  • The only issue I could foresee is usage of dynamic ports or these named instances can run on static ports ?

    If both servers have all the same databases you could probably do load balancing over the normal TCP connections (this is not something I've tried before). If you are using named instances I think you'd want to statically set the port. You'd also want to have some sort of "sticky session" technology. Some client tools (like Excel) will create temporary session scoped members and sets. And if a session set gets created on one server and then the query referencing that set gets executed on the other server you will get an error.

    http://darren.gosbell.com - please mark correct answers

    Monday, June 1, 2015 12:16 PM
  • SSAS, and SQL Server, do not support load balancing directly.

    You can setup 2 servers with the same data, and use F5 or something similar to direct users between the servers.  However, you must use "sticky sessions" which defeats a lot of the benefits of load balancing.

    Monday, June 1, 2015 12:30 PM
  • As far as I can see using the query tools it opens a new session every time executes the query and closes it. These look like one time use queries. Is there a way to confirm if AdoDb is coming for an existing session ?
    Wednesday, June 3, 2015 3:21 PM
  • It depends on what query tools you used and how you used them. Simple queries should be fine, but complex queries using Excel sometimes get broken into multiple statements. There are operations that generate session based sets, calculations and cubes, I'm not 100% sure what the trigger points are for all of these, but getting Excel to generate a session cube is not hard, but it definitely happens.

    If you create a pivot table in Excel (I tested on Excel 2013), put Product Lines on the rows in and Internet Sales on the Values, expand out any of the Product Lines and hightlight 4-5 products, right click and choose "Group" you will see a statement like the following in profiler:

    CREATE SESSION CUBE [Adventure Works_XL_GROUPING0] FROM [Adventure Works] ( MEASURE [Adventure Works].[Internet Sales Amount......

    Then subsequent queries will look like this and will be executed on the same SessionID

    SELECT NON EMPTY Hierarchize({DrilldownLevel(DrilldownMember({{DrilldownLevel({[Product].[Product Model Lines].[All Products]},,,INCLUDE_CALC_MEMBERS)}}, {[Product].[Product Model Lines].[Product Line].&[M]},,,INCLUDE_CALC_MEMBERS),[Product].[Product Model Lines].[Product Line1],INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Product].[Product Model Lines].[Model].[Product Line] ON COLUMNS  FROM (SELECT ({[Product].[Product Model Lines].[Model].&[Women's Mountain Shorts], [Product].[Product Model Lines].[Model].&[Mountain-500],

    ..... more products were listed here....

    [Product].[Product Model Lines].[Model].&[HL Mountain Seat/Saddle 2], [Product].[Product Model Lines].[Model].&[LL Mountain Seat/Saddle 2], [Product].[Product Model Lines].[Model].&[ML Mountain Seat/Saddle 2]}) ON COLUMNS  FROM (SELECT ({[Date].[Calendar].[Calendar Year].&[2008],[Date].[Calendar].[Calendar Year].&[2009],[Date].[Calendar].[Calendar Year].&[2010]}) ON COLUMNS  FROM [Adventure Works_XL_GROUPING0])) WHERE ([Measures].[Internet Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS


    http://darren.gosbell.com - please mark correct answers

    Wednesday, June 3, 2015 9:04 PM