dedicated server for analysis services

Question

dedicated server for analysis services

mstephens1754

SSCommitted

Points: 1650
More actions
January 20, 2014 at 4:23 am

#287391

I have one business group that creates a ton of cubes via analysis services right now that are on SQL 2008. However, I've recently started moving everything over to SQL2012 so with that said. Do you all run Analysis Services on the same server as all your other SQL databases or do you run this on a separate server? I really don't see the benefit to running it on a separate server than again I'm new to SQL server administration.

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

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

PB_BI SSCoach Points: 17463 More actions · Answer 1

From personal experience it depends but generally I would recommend that it is separate. What else is happening on the box? If you have all of your SQL databases on the same box in may be wise to consider a separate server for SSAS. If SSAS is used heavily and you have large cubes then it would be best on a separate server. There are many factors to consider - volume and velocity of data, query types, concurrency, cost etc.

Have a read of these whitepapers and see what you think:

http://msdn.microsoft.com/en-us/library/hh226085.aspx

http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=17303

Plus there is a good forum post here:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/a5e37957-3e35-4ffc-96aa-80c9467c2255/put-sql-server-ssas-on-the-same-server-or-different-server?forum=sqlanalysisservices

Good luck.

I'm on LinkedIn

notarian@gmail.com SSCommitted Points: 1950 More actions · Answer 2

You might be able to get away with having them both on the same server. It depends on what else is going on with the server and the size of your cubes. If your data warehouse is 10 GB and your cube is 5 GB that's one thing. If the data warehouse is 1TB and the cube 500GB then you're probably going to run into issues with resource contention as the SSAS process wants all the CPU/RAM but the Database Engine needs quite a bit as well just to respond to queries SSAS is generating. Capture PerfMon stats and see what's going on during processing.

simmonssql Old Hand Points: 301 More actions · Answer 3

I have a cluster with both database engine and Analysis Services running on them with no problem, but as people have already said it depends on the scale of your operation, resources, disk subsystem etc...

Also to consider is that you'll need to ensure that the Analysis Services and database engine resource allocations are correctly configured. If you don't, you'll start to see contention issues. The SQL max and min server memory settings for the database engine are quite straightforward, however the Analysis Services settings are not so much. See the link for details:

sneumersky SSCertifiable Points: 7667 More actions · Answer 4

SSRS is usually the first to be scaled out, THEN SSIS, THEN SSAS. Use the Analysis Services Performance and Operations guide to make your final decision.