Development, Staging, Production - best practice

Hi,
I would appreciate your advice in deploying enterprise BI solution. The task
at hand is this: I need to get the data from DW (SQL 2005), process into SSAS
and present with SSRS. I may also need to offer a third party OLAP browser as
a part this of solution. My initial thoughts about the setup for this are:
- SRV1: Development server, with all SQL 2005 components, IIS, and any third
party OLAP tools that we might pick.
-SRV2: Staging/Processing. Schedule and run SSIS to get data from DW, and
refresh and process SSAS cubes.
-SRV3: Production Reporting/Web server: This server would hold processed
cubes from SRV2 (archived and restored on SRV3), SSRS, IIS any any third
party OLAP browser that we may pick.
Initially there will be 2-3 developers, 15-20 cubes and 30-40 reports. We
are targeting to server 20-30 Report users.
Does the above setup make sense? Do I need dedicated web server with or
without SSRS? Please share thoughts or advise of any best practice articles.
Thank you.
ZoranHello Zoran,
The server setup need to be decide with that how large your cube is and how
many data your cube stored.
Yes, you need to seperate a stand alone server to process all the Cube data
and use the Reporting Services as the Front End Server.
I don't think you need to dedicate the web server with Reporting Services
because the Reporting Services is also a web application in the web front
end. Also, considering your report users, your report web quest will not at
a high level.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you Wei,
Currently all our cubes are MOLAP, with database sizes ranging from 30 MB to
300 MB. I read that one of the most efficient ways to move from Staging to
Production is backup and retore. But, is it good practice to keep production
cubes on the same server as SSRS? Technically, there would be no processing
on that server, cubes would only sit there for user's queries...
Zoran
"Wei Lu [MSFT]" wrote:
> Hello Zoran,
> The server setup need to be decide with that how large your cube is and how
> many data your cube stored.
> Yes, you need to seperate a stand alone server to process all the Cube data
> and use the Reporting Services as the Front End Server.
> I don't think you need to dedicate the web server with Reporting Services
> because the Reporting Services is also a web application in the web front
> end. Also, considering your report users, your report web quest will not at
> a high level.
> Hope this helps.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Zoran,
Whether putting the SSRS together with the Cube depends on the size and how
system resource used by the SSAS.
In your scenario, I think it is OK for you to put the SSRS on the same
server at this time. But if your cube grows, you may need to seperate the
SSAS with SSRS.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you Wei.
In your previous post you you mentioned "depends how system resources
(are)used by SSAS". In my scenario, they would be used for user queries
(direct and via SSRS). There would be no processing done on this server.
I can certainly see the number of cubes grow in near future. Is one server
for SSRS and SSAS going to work, or am I setting myself up for upgrade in
near future?
Regards,
Zoran Knezic
"Wei Lu [MSFT]" wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||RS 2005 does all rendering in RAM. As long as you have enough RAM I think
you will be OK. I would suggest starting off on one server. Perhaps let
management know that you will need to monitor the usage and there is a
chance they will need to be split onto separate servers. RS 2008 is going to
be much smarter and with how it renders.
You said the following: Initially there will be 2-3 developers, 15-20 cubes
and 30-40 reports. We
are targeting to server 20-30 Report users.
This is not that many users. I do not have cubes but I have a datamart which
includes a table with 150 million rows (small rows admittedly) plus several
tables that have several million rows. RS 2005 and the datamart are on the
same server without difficulty. Similar number of users (more reports). My
server is old (4 years old) and ready for replacement. I have 4
processors(2.4 GHz), 4 Gigs of Ram (so this is not a huge powerful box by
any means). Performance is very very good for me. Running SQL 2005 Standard
edition.
Since you are just getting started. If you are able to plan on using RS 2008
I would do so. RS 2008 should be able to use a SQL 2005 db for its
metadata/object caching so it is a licensing issue.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"zk_" <zk_@.newsgroup.nospam> wrote in message
news:EB03F7C3-13D3-40D2-8991-7FAF755EEFCE@.microsoft.com...
> Thank you Wei.
> In your previous post you you mentioned "depends how system resources
> (are)used by SSAS". In my scenario, they would be used for user queries
> (direct and via SSRS). There would be no processing done on this server.
> I can certainly see the number of cubes grow in near future. Is one server
> for SSRS and SSAS going to work, or am I setting myself up for upgrade in
> near future?
> Regards,
> Zoran Knezic
> "Wei Lu [MSFT]" wrote:
>> Hi ,
>> How is everything going? Please feel free to let me know if you need any
>> assistance.
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Thank you Bruce.
It is good to hear a first hand experience with DB and SSRS on the same
server.
I am sure that SSAS creates bigger overhead then DB, but judging by your
experience, I might be OK.
My IMIT colleagues came up with 2x 2.8 GHz server, with 16 Gb of RAM.
Alternatively I might be able to suggest two servers, 2x2.8 GHz 8GB of RAM
each.
Once again, thank you for your time.
Zoran Knezic
"Bruce L-C [MVP]" wrote:
> RS 2005 does all rendering in RAM. As long as you have enough RAM I think
> you will be OK. I would suggest starting off on one server. Perhaps let
> management know that you will need to monitor the usage and there is a
> chance they will need to be split onto separate servers. RS 2008 is going to
> be much smarter and with how it renders.
> You said the following: Initially there will be 2-3 developers, 15-20 cubes
> and 30-40 reports. We
> are targeting to server 20-30 Report users.
> This is not that many users. I do not have cubes but I have a datamart which
> includes a table with 150 million rows (small rows admittedly) plus several
> tables that have several million rows. RS 2005 and the datamart are on the
> same server without difficulty. Similar number of users (more reports). My
> server is old (4 years old) and ready for replacement. I have 4
> processors(2.4 GHz), 4 Gigs of Ram (so this is not a huge powerful box by
> any means). Performance is very very good for me. Running SQL 2005 Standard
> edition.
> Since you are just getting started. If you are able to plan on using RS 2008
> I would do so. RS 2008 should be able to use a SQL 2005 db for its
> metadata/object caching so it is a licensing issue.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "zk_" <zk_@.newsgroup.nospam> wrote in message
> news:EB03F7C3-13D3-40D2-8991-7FAF755EEFCE@.microsoft.com...
> > Thank you Wei.
> > In your previous post you you mentioned "depends how system resources
> > (are)used by SSAS". In my scenario, they would be used for user queries
> > (direct and via SSRS). There would be no processing done on this server.
> > I can certainly see the number of cubes grow in near future. Is one server
> > for SSRS and SSAS going to work, or am I setting myself up for upgrade in
> > near future?
> >
> > Regards,
> > Zoran Knezic
> >
> > "Wei Lu [MSFT]" wrote:
> >
> >> Hi ,
> >>
> >> How is everything going? Please feel free to let me know if you need any
> >> assistance.
> >>
> >> Sincerely,
> >>
> >> Wei Lu
> >> Microsoft Online Community Support
> >>
> >> ==================================================> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >>
> >> ==================================================> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
>
>