Install Analysis Services server on different box

[umeman]

Hi,

I need to install MS Analysis Services on a box other than
the database server (MS SQL 2000. Can anyone provide any guidence on how to do this ?

Thank you
-U

 

[MDXer]

Use the same process you would to install it on the same box except you don't instal the SQL component. Just remember that if you split MS SQL and MS AS onto seperate servers then you must license both of those servers. SO if ou SQL box is 4 proc and your OLAP box is 4 proc and you license by procs you require 8 licenses.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[umeman]

Thank you, I have one more question.

Are there any steps I need to follow up on after the install such as migrating the repository, setting any additional config settings etc ?

 

[MDXer]

you only need to migrate the repository if you want it in SQL. I typically place the rep in sql since I have experienced issues keeping it in Access if the AS implementation is a large one.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[dishakibria]

U -

I am curious what helped you decide you needed to install Anlysis Services on a separate box from the database server? I am trying to decide if I need to do that too?

I can imagine - you get performance improvement right off the bat as you are not competing with the database.

Would you need a separate license then? Also - at any point - do you need the IIS on the same machine as the Analysis Services SQL Server(somehow makes me uncomfortable).

Could I use the SQL Server Standard Edition for Analysis Services (if I installed on a separate box from our Enterprise edition db server)? Anything glaringly missing as far as Analysis Services is concerned in the Standard Edition?

Thank you,
D

 

[dishakibria]

Sorry - please accept my apologies on the license question - you do need separate license. Just verifying one point though - if I have a production server with 2 CPU Entrpeise Edition license then the Analysis Service License on a separate box will HAVE TO HAVE 2 CPU license for ENTERPRISE even if we want Standard AND on a box with one CPU???

Thank you,
Disha

 

[umeman]

The reason for having it on a separate box would be so that there is no performance impact on users when data is being loaded to the data warehouse,

Also better performance would be delivered when users are
performing drill down analysis from the cube to the
datawarehouse fact tables

That is my reasoning perhaps more experienced users
can shed more light

Thanks
-U

 

[MDXer]

Performance and security can be 2 main reasons for seperating the AS from the SQL typically this is only done in very large implementations. Another advantage to doing it is that it leaves a maintance window for the DW server if MOLAP storage is used. I would get into the nitty gritty if I had the time but it would probably cause our eyes to glaze over and put us all to sleep.

Yes you do need to license the server that AS is installed on for the number of procs on the machine. if you install it onto a 2 proc machine you need to proc licenses. you need not license the hyperthreaded v procs so a dual proc with HT results in 4 procs but only 2 require licenses. I have read that this license model carries over into multicore procs as well.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[dishakibria]

Thank you both for your VERY, VERY helpful tips. Do you stornlgy reccommend Enterprise edition over the Standard for Analysis Services?

I am asking because I am being asked to justify the extra cost for both the database server and the SA Server.


Thank you,
Disha

 

[MDXer]

Enterprise edition on AS has some added functionality that standard does not offer. You can find these differences on either the MS site or in SQL BOL. if you need or forsee the need for the added functionality then yes invest in it.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[dishakibria]

Thank you. That was helpful.

 

[wwwSQLOBSERVERcom]

Hi umeman (and/or MDXer/dishakibria),

Would you be able to give an indication of the magnitude of usage and whether running the two servers have helped improve response times?

The setup makes sense - I'm just interested to see if it's worth the trouble (and additional cost).

Cheers

http://www.SQLobSERVER.com

the Microsoft BI/DW community

 

[MDXer]

The enviroment I ran AS on seperate servers (Actually 3 servers) was a terabyte enviroment. The DW had 2 billion fact records with a 3 year history. Cubes were in the 50-60 region and ranged in size any where from 250MB to 35GB in size. We had custom apps to do parallel processing of the the cubes and made extensive use of partitioning.

Without a doubt the multiple seperate AS servers resulted in a performance gain.

The decision to seperate the AS from SQL seems like it would be a simple one but it is not always needed. Many factors such as Server size, data size, number of users, cube count dim count, security and of course cost can help determine if the seperation of components is warranted.


Shoot Me! Shoot Me NOW!!!
- Daffy Duck