SSRS and SSAS

Question

SSRS and SSAS

Howard C. BAchtel-438731

SSCrazy

Points: 2384
More actions
June 22, 2010 at 8:14 am

#234228

We are planning to upgrade SQL Server from 2005 to 2008. With the upgrade we plan to also use SSAS and SSRS (not used for 2005). Should the two be installed on a separate instance than the DBMS? If so, should they be installed on separate instances individually (SSRS and SSAS)?
Can someone point me to where I can find the pertinent points of making this determination?
Thanks in advance.

Viewing 15 posts - 1 through 14 (of 14 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

It depends on your usage. Assuming you have more than tiny dbs and a decent number of users that need to do reporting and analysis (let's say 30+), then you most likely need to split on 3 servers.

SSAS AND SSRS need to be on a different system than the oltp system, that's for sure.

Now the problem with this is that you need to double or triple the amount of licenses and hardware so that part with depend on your budget.

Do you know any of those #s? That will help us slot you in the right setup.

Dbs size

# of reports and avg execution time

# of reports users

# of report executions per day

# of report executions during peak hour

# of analysts for ssas

# budget for licenses and hardware.

Howard C. BAchtel-438731 SSCrazy Points: 2384 More actions · Answer 2

Thanks for the input. Your response is very helpful and gives me what I want to know. Basically, we have not used SSRS or SSAS at all for version 2005, but there is some interest. The numbers you ask about are truly unknown at this time and I actually do not envision a large amount of individual users of the SSRS or SSAS, perhaps until the next upgrade of SQL Server, when ever that is.

We are a government organization (Veterans' Affairs) and I do not believe licensing is an issue.

I will request separate servers for each and see where that goes.

Thanks again.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Well just answer me this.

How big is the db right now?

If it's reallllly small (like under 15 GB), and that it doesn't plan to grow by much short term, you can always start with all 3 on the same machine. Or Oltp on one, then ssas and ssrs on the second one if budget permits.

I wouldn't go straight to 3 servers without being sure you need to.

Howard C. BAchtel-438731 SSCrazy Points: 2384 More actions · Answer 4

The current db is around 323 GB in size.

The data is purely batch update and used for reporting, but the vast majority (99%) is using MS Access by numerous users. I do not anticipate a large transformation since many of the user applications (Access) were developed centrally over the years (past six) and there are no new development resources.

I think SSRS and SSAS can reside on the same server with little difficulty, if for no other reason, the limited development resources. I have modified my request to address the use of a combined server for SSRS and SSAS.

Thanks again.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

0.3 TB of data for the veterans DB WOW.

With that scenario in mind there may be a way to use the same server for all 3.

Depending on how and who is doing the batch updates (assuming it can be done somewhat off hours), then you should be able to do all 3 installs on the same machine.

I'm only recommending this because you seem to have alsmot no transactions on a daily basis (like orders or inventory or stuff like that) and that all the changes are basically updates in the veterans adresses, phone # or stuff like that.

Again if everything is done in batches then there's likely a way to have only 1 server, which means only 1 set of DB which saves a ton of $ on HDs and sans and servers... and that you don't need to set up any or many intermediate workhorse servers to crunch the numbers. But then again 323 GB is no small DB by any means.

I'd strongly recommend that you get a second opinion other than mine on this topic because I'm not expert on the hardware side of things and I've never had to plan a install for such a huge DB.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 6

In general, especially for the size of systems you're talking about, I'd seperate these installs to seperate machines.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Grant Fritchey (6/22/2010)
In general, especially for the size of systems you're talking about, I'd seperate these installs to seperate machines.

2 or 3?

They're not even sure they need the technologies, this seems like a dry run and see what happens scenario.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 8

Ninja's_RGR'us (6/22/2010)
Grant Fritchey (6/22/2010)
In general, especially for the size of systems you're talking about, I'd seperate these installs to seperate machines.
2 or 3?
They're not even sure they need the technologies, this seems like a dry run and see what happens scenario.

I'm assuming you need the service, but 3. Each of these services gets nasty when it has to share very much, although I have seen SSAS & SSRS share systems, but only when one or the other was totally under-utilized.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Howard C. BAchtel-438731 SSCrazy Points: 2384 More actions · Answer 9

Thanks to all for your input. A couple of items:

Yes, the data is all updated via batch processes with all but a very few updated on off hours.

The data source is a hierarchical structure of what is basically an enterprise system (Veterans' Affairs Vista - not to be confused with Microsoft Operating System).

Perhaps you are correct in that it is somewhat an exploration of function in the use of SSRS and SSAS, but progress does (or should) take place and we need to start somewhere. The functionality can be very useful here and I am moving to make it work.

While the DBMS is heavily used for reporting and analysis, it seems a fit to use SSRS and SSAS as tools.

Your input is very much appreciated as we move on.

Thank you.

Greg Edwards-268690 SSC-Insane Points: 20667 More actions · Answer 10

Since you are just starting out, I would put SSAS and SSRS on the same machine.

SSAS and SQL on the same machine can be hard to keep stable. Both can have times when they want to consume memory.

And SSAS is not the easiest application to get to stick to memory limits.

You could start out small, see how it goes, and plan for scale up or out.

If you aren't already planning x64 bit, I would recommend it. It costs no more for the license, and adds some scalability in.

Greg E

george sibbald SSC Guru Points: 104210 More actions · Answer 11

Greg makes a good point about 64bit.

If you are already 64bit then that is a good argument for running the components together. If you have 64bit and plenty of memory installed (32Gb +) and cubes on separate drives then the engine and SSAS should sit happily together, and SSRS should work as well.

Also is this a clustered server? SSAS is cluster aware but SSRS is not so I would then move SSRS off.

For me if 64bit and cost not deciding factor I would have engine and SSAS together and SSRS separate, if 32 bit all 3 separate unless usage is low, even then drive separation and sufficient memory with use of AWE is important.

edit: cannot spell SSRS

---------------------------------------------------------------------

george sibbald SSC Guru Points: 104210 More actions · Answer 12

Other point I wanted to make is if you separate out the components you increase network traffic greatly, so factor that into your decisions.

---------------------------------------------------------------------

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

😀

Thanks for all the great input... I knew I didn't knew nearly enough about that one ;-).

Greg Edwards-268690 SSC-Insane Points: 20667 More actions · Answer 14

george sibbald (6/22/2010)
Greg makes a good point about 64bit.
If you are already 64bit then that is a good argument for running the components together. If you have 64bit and plenty of memory installed (32Gb +) and cubes on separate drives then the engine and SSAS should sit happily together, and SSRS should work as well.
Also is this a clustered server? SSAS is cluster aware but SSRS is not so I would then move SSRS off.
For me if 64bit and cost not deciding factor I would have engine and SSAS together and SSRS separate, if 32 bit all 3 separate unless usage is low, even then drive separation and sufficient memory with use of AWE is important.
edit: cannot spell SSRS

Be very careful about running SQL and SSAS on the same box, especially with a new cube and users. SSAS can be very hard to throttle should you have a large cross join query. My recommendation is to go with the separate box and treat is more as a Proff of Concept. This would have minimal impact on your current production environment.

Greg E