November 6, 2009 at 2:16 am
Our corporate currently has a medium sized (approx 500 GB) data warehouse in SQL SERVER 2005 and all SQL Server’s components database server, SSIS and SSAS are deployed to the single physical server. Considering the current performance and future growth management, we are in the process of re-architecting this BI infrastructure using SQL SERVER technologies.
Could you please share your ideas/experiences/expertise on selecting the following design choices for designing a BI infrastructure for an optimal performance:
1.System Configuration : Monolithic (run all SQL Servers components in single physical server) OR Distributed (run all SQL Servers components to different physical servers)
2.Data Storage: SAN or DAS
Thanks in Advance.
November 16, 2009 at 10:21 pm
1. SQL and SSIS on one box, plenty of memory and processors.
2. SSAS seperate
3. SSRS seperate but with ReportServer and ReportServerTempdb on the SQL box. No local SQL.
4. SAN.
Now, let me explain why. SQL and SSIS work pretty good together, just make sure you have enough memory and processors. Keep in mind that this box is used at two points(in many scenarios), ETL loading and cube processing. Otherwise it will be fairly quiet..
SSAS has some single threaded aspects that you may want to use partitions (enterprise edition) to manage. You can also build a cube on one and then restore it to several, while putting a load balancer in front of them.
SSRS has two methods for growth, scale up and scale out. Scale up is beefier hardware and such. Scale out is more servers. Having the ReportServer and ReportServerTempDb on a seperate box is a requirement for this..
Clear?
CEWII
November 18, 2009 at 6:41 pm
I agree you should put SSAS on a separate server, and I'd add that you should have plenty of memory there too.
This is based on what I see our customers doing time and again to achieve best performance.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply