I am going to implement an environment where I need to configure Microsoft SQL Server 2012 (including SSRS, SSIS & SSAS) with Sharepoint 2013. I have developers that would use the SQL Server 2012 tools to build the Data Warehouse including cubes and models, and end users that would use Excel 2010 with Power Pivot as well as Powerview through Sharepoint 2013.
Could you please let me know if I need 64 bit infrastructure and tools to achieved required level of performance?
Absolutely you should use 64bit for the best performance on the servers. The reason for this is that majority of the SSRS report execution and SSIS/SSAS will be performed server-side.
Client side will not see a difference running 32bit vs 64bit in a majority of cases that do not use PowerPivot. When using PowerPivot in large datasets with complex joins a performance gain can be observed.
There are several reasons for this:
- The client PC consuming the report will utilize resources to display not process the data.
- Data processing will occur on the report, analysis, and database server typically.
However in PowerPivot:
Clients will see some benefit from x64 in Excel PowerPivot and this primarily depends on the volume of data users will be processing in memory in Excel. PowerPivot is an OLAP which uses in-memory processing and x64 allows for more memory. Thus, PowerPivot users will see a performance gain but primarily when the 2GB limit in 32bit systems is reached as x64 allows for more memory.
Horsepower has never been a replacement for best database design. I've huge databases which were built with the best indices and practices perform better than smaller ones with poor design.