Deploying and Managing Microsoft SQL Server PowerPivot for SharePoint

1. DBI406 Deploying and Managing Microsoft SQL Server PowerPivot for SharePoint Dave Wickert Principal Program Manager Microsoft Corporation

2. Session Objectives and Takeaways • Session Objective(s): • Understand the various PowerPivot for SharePoint configurations • Understand the high level internals behind PowerPivot for SharePoint • When is the BI Appliance a good way to build your PowerPivot infrastructure • Learn what is coming in SQL Server Denali PowerPivot . . .

3. PowerPivot Infrastructure

4. How are we going to build this? Implementations

5. ImplementationsInstallation • End-to-End Installation Guides • PowerPivot for SharePoint: Single Server Installation • PowerPivot for SharePoint: Existing Farm Installation • Microsoft SQL Server 2008 R2 PowerPivot Planning and Deployment • PowerPivot for SharePoint - Transitioning from a Single Server to SharePoint Farm • Architecture Diagrams • PowerPivot Technical Diagram: PowerPivot Client/Server Architecture • PowerPivot Technical Diagram: PowerPivot Security Architecture • There are a lot of steps and a lot components integrated together: • PowerPivot System Service, AS Engine, SharePoint, Excel Services, Reporting Services, Excel Client, Office Workspace, IIS, Windows, etc.) • Examples: • To change password for SharePoint Farm takes seven separate steps in the farm, services, and IIS. • Multiple steps to handle larger file sizes • Quick Tips • Requires domain accounts • Upgrade is tricky! May want to build out new server vs. upgrading existing

6. ImplementationsPowerPivot “New Server” PowerPivot“New Server” Web Services Excel Services PowerPivot SQL databases

7. ImplementationsMove SQL databases out SharePoint Databases SharePointWeb/App Server SP Config Web Services SP Content(s) PPvt App Db(s) Excel Services PowerPivot

8. ImplementationsScale-out LB SharePoint Databases SharePointWeb/App Server SharePointWeb/App Server SharePointWeb/App Server SharePointWeb/App Server SP Config Web Services Web Services Web Services Web Services SP Content(s) PPvt App Db(s) Excel Services Excel Services Excel Services Excel Services PowerPivot PowerPivot PowerPivot PowerPivot

9. ImplementationsDedicated servers w/ high availability LB . . . SharePoint Databases SharePointWeb Server SharePointWeb Server SP Config SharePointApp Server SharePointApp Server SharePointApp Server . . . Web Services Web Services SP Content(s) Excel Services Excel Services PowerPivot PowerPivot Other Services PPvt App Db(s)

10. ImplementationsCapacity planning (long term) (1) http://bit.ly/cZOHe0 “New Server” ‘alias’ the SharePoint RDBMS physically moves the database, but the SharePoint configuration remains intact (2) Single Server ‘migrate’ the SharePoint RDBMS so that the SharePoint configuration sees the database server in its true location (3) Single Server Single Server Scale-out the farm by adding NLB servers; all servers providing all services (4) Single Server Multiple Servers (5) WFE AppServer AppServer AppServer Specialize the farm; add app servers which are tied to various services (size, scale and configure)

11. ImplementationsDelegation, Claims, Active Directory • Issue: Clicking on slicers on a PowerPivot workbook in SharePoint only worked as a domain administrator • Tried: The data connection uses Windows Authentication and user credentials could not be delegated • Solution: Occurs when AD environment upgraded from NT 4.0. Ensure users are: • Members of the “Pre-Windows 2000” NT group (i.e. have Pre-Windows 2000 Compatible Access” • Part of the “Authenticated Users” group and have “Read Permissions” • References • Delegation, Claims, Active Directory…Oh My!…Aw Crap! • The data connection uses Windows Authentication and user credentials could not be delegated • Testing the Claims To Windows Token Service for different identities • Delegation, Claims, Active Directory….Again?! Frak! • BIN308 SQLCAT: Configuring and Securing BI Applications in a SharePoint 2010 Environment

12. a peek inside PowerPivot Infrastructure

13. PowerPivot for SharePointServer, Services, and Components c2wts/proxy SharePoint Farm c2wts App Client WFE Content Excel Calculation Services Excel Web Access SQL Excel Services Application Proxy MSOLAP TCP Excel Web Service MSOLAP Driver HTTP Excel Calc Services Channel Data Sources PowerPivot Web Service PowerPivot Service Application Proxy PowerPivot System Service Excel Services Analysis Services in VertiPaq Mode PowerPivot Services

14. PowerPivot for SharePointRendering the PowerPivot Workbook SharePoint Farm c2wts App Client WFE Content Excel Calculation Services SQL Excel Web Access Excel Services Application Proxy Excel Web Service MSOLAP Excel Calc Services Channel Data Sources PowerPivot Web Service PowerPivot Service Application Proxy PowerPivot System Service Analysis Services in VertiPaq Mode

15. PowerPivot for SharePointRendering the PowerPivot Workbook SharePoint Farm c2wts App Client WFE Content Excel CalculationServices SQL Excel Web Access Excel Services Application Proxy c:\Program Files\...\OLAP\Backup Excel Web Service MSOLAP Excel Calc Services Channel Data Sources PowerPivot Web Service PowerPivot Service Application Proxy PowerPivot System Service Analysis Services in VertiPaq Mode

16. PowerPivot for SharePointPowerPivot does NOT require Kerberos SharePoint Farm c2wts App Client WFE Content Excel Calculation Services SQL Excel Web Access Excel Services Application Proxy Excel Web Service MSOLAP Excel Calc Services Channel Data Sources PowerPivot Web Service PowerPivot Service Application Proxy PowerPivot System Service Analysis Services in VertiPaq Mode

17. PowerPivot for SharePointServer Action SharePoint Farm c2wts App Client WFE Content Excel Calculation Services SQL Excel Web Access Excel Services Application Proxy Excel Web Service MSOLAP Excel Calc Services Channel Data Sources PowerPivot Web Service PowerPivot Service Application Proxy PowerPivot System Service Analysis Services in VertiPaq Mode

18. PowerPivot for SharePointServer Action > c2wts

19. PowerPivot for SharePointServer Action > c2wts SharePoint Farm c2wts App Client WFE Content Excel Calculation Services SQL Excel Web Access Excel Services Application Proxy Excel Web Service MSOLAP Excel Calc Services Channel Data Sources PowerPivot Web Service PowerPivot Service Application Proxy PowerPivot System Service Analysis Services in VertiPaq Mode

20. PowerPivot for SharePointServer Action SharePoint Farm c2wts App Client WFE Content Excel Services Application Proxy Excel Calculation Services SQL Excel Web Access c:\Program Files\...\OLAP\Backup Excel Web Service MSOLAP Excel Calc Services Channel Data Sources ImageLoad PowerPivot Web Service PowerPivot Service Application Proxy PowerPivot System Service Analysis Services in VertiPaq Mode

21. PowerPivot for SharePointLoad Balancing SharePoint Farm c2wts • By default, set to round-robin health allocation • Testing notes that BP to set to health-based allocation • Uses more resources than round-robin • BUT, pro-actively evicts unnecessary VertiPaq dBs when under memory pressure App1 App2 Excel Calculation Services Excel Calculation Services MSOLAP MSOLAP Channel Channel PowerPivot Service Application Proxy PowerPivot Service Application Proxy PowerPivot System Service PowerPivot System Service Analysis Services in VertiPaq Mode Analysis Services in VertiPaq Mode

22. Capacity PlanningImpact of slicers, charts, tables, worksheets ~20 ~530 ECS fires a lot of statements; this will be reduced in SharePoint SP1

23. Using the BI Appliance to jumpstart building PowerPivotinfrastructure

24. Microsoft Business Intelligence Appliance End-to-end, pre-configured stack quickly enables BI for Excel power users • Rich insight: Empower users to easily create PowerPivot workbooks from real-time business data for faster, more accurate insights • Reduced complexity: Overcome cost and complexity of BI; shift IT resources from running ad-hoc reports to innovation initiatives • Improved deployment & manageability : Appliance management dashboard and BI Center Getting Started Guide streamline deployment and simplify administration.

25. BI Appliance – Best Practices • Onboard Backup/Restore - Only an interim solution • Better options: Use Windows Backup via a network backup (for off Appliance backup) • Use comprehensive integrated SharePoint and SQL backup solution such as Data Protection Manager • Data partition (D: ) 683 GB • For more space: Move to an off-appliance backup solution (then remove the E: backup partition and extend the data partition D: to include rest of drive • SQL Max Server Memory set to 8192 • Raise or lower based on your needs • SharePoint max upload size and Excel Services max workload size set to 2GB • Lower the values to have more control over users workbook sizes

26. BI Appliance – Best Practices (cont.) • Based on PowerPivot “New Server” All-in-1 installation • Designed as standalone machine • Don’t combine with other systems – should not just ‘add’ to an existing farm • Should not combine appliances to get more capacity • If you need more capacity, get a regular server(s) and install components • Goal was to size the hardware for 80 concurrent users • But we have published performance info for more, see HP sizing white paper (http://h20195.www2.hp.com/V2/GetPDF.aspx/4AA3-3086ENW.pdf)

27. What’s coming in SQL Server Denali PowerPivot

28. SQL Server Denali PowerPivot • Builds upon our V1 release in SQL Server 2008 R2 • New administrative capabilities • Lots of bug fixing, failover capabilities and scalability enhancements • Default allocation algorithm changed to health-based rather than round-robin (means we are more responsive to low memory situations • More ‘knobs’ to control: • Amount of database caching done on the app servers • Cleanup of database caching (based on inactivity) • Disable data refresh jobs if they are constantly failing, or if they succeed but are not being viewed

29. SQL Server Denali PowerPivot (cont.) • New administrative capabilities (more): • New capacity planning health rules for CPU, memory and disk space (you will start to see warnings if you are running out of resources) • Support for transparent connectivity (.bism files) • New midtierminidump facility

30. SQL Server Denali PowerPivotAdministrative improvements demo

31. SQL Server Denali PowerPivot (cont.) • A new setup experience • Rather than installing and configuring SharePoint directly, setup now just lays down the bits and there is a new standalone configuration wizard • Capabilities: • Wizard has both a one-click “GO” option as well as astep-by-step experience • Existing farm steps integrated with the wizard • Individual steps can be scripted • History is directly integrated into the tool • Able to uninstall, upgrade, repair and patch separately

41. SQL Server Denali PowerPivot (cont.) • Important note:SharePoint SP1 is required for CTP3 (and RTM) • SQL Setup will issue a warning if SharePoint SP1 has not been installed when the PowerPivot bits are laid down. This gives you some time to upgrade to SP SP1. • However, if you still haven’t upgraded to SP1, by the time you get to run the PowerPivot configuration wizard, we will throw a fatal error and you will be blocked from configuring your SharePoint farm until you upgrade to SP1.

42. SQL Server Denali PowerPivotSetup Experience demo

43. TroubleshootingResources • SQL Server PowerPivot for SharePoint Newsgroup • Personal / MVP blogs like: • powerpivotgeek.com • dennyglee.com • powerpivotpro.com • powerpivot-info.com • powerpivotblog.nl • Twitter > #PowerPivot • Professional Microsoft PowerPivot for Excel and SharePoint > Chapter 9

44. Related Content • Breakout Sessions:“DBI206 – What’s New in Microsoft SQL Server Code-Named “Denali” for SQL Server Analysis Services and PowerPivot”, Tues 5/17 @10:15am • Interactive Sessions:“DBI471-INT – Tips and Tricks for Troubleshooting a Microsoft SQL Server PowerPivot for SharePoint Installation”, Tues 5/17 @3:15pm • Hands-on Labs:“DBI372-HOL – Managing Microsoft PowerPivot for Excel 2010 Solutions in Microsoft SharePoint Server 2010” • Find Me Later At…the Microsoft product booth today Thurs (10:30am-noon) – Stop by for a chat!

45. What’s Next? • Follow, Tweet and Enter to win an Xbox Kinect Bundle • GAME ON! Join us at the top of every hour at the BI booth to compete in the Crescent Puzzle Challenge and Win Prizes • Sign up to be notified when the next CTP is available at: microsoft.com/sqlserver Join the Conversation @MicrosoftBI /MicrosoftBI

46. Resources • Connect. Share. Discuss. http://northamerica.msteched.com Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers http://microsoft.com/technet http://microsoft.com/msdn

47. Complete an evaluation on CommNet and enter to win!

49. More info slides . . .

50. BI Appliance – Installation steps • Install Windows 2008 R2 (and apply all critsit patches) • Add user to appliance Local Admin • Lay down SharePoint 2010 w/ all prerequisites • Install SQL Server 2008 R2 PowerPivot “New Server” • Install KB979917 (SP issues) • Install KB976127 (updated ADO.NET Data Services)