Tuesday, September 25, 2012

CRM 2011/SQL Server 2012 Business Intelligence Setup

SQL Server 2012 introduced some interesting new functionality in the area of business intelligence. I wanted to test some of those things out in conjunction with Dynamics CRM 2011 but realized that in order to do so a new virtual machine was in order. After spending numerous hours trying to get this to work correctly I finally came across this blog post that covers in great detail getting the SQL Server 2012 and SharePoint 2010 pieces installed in the correct order. You can reference the aforementioned post for the real details of the setup - I just wanted to show the high level overview along with the addition of CRM, Office and the couple other things I needed to do to get this all working.

The Steps

Installed Windows Server 2008 R2
Ran Windows Updates
Promoted server to a domain controller
Created domain user accounts for the various services
  • SQLSvc
  • SPAdmin - not sure this one got used, I was logged in as administrator during the process
  • SPFarm
  • SSAS
  • SSRS
Installed SharePoint 2010 Enterprise Edition prerequisites
Installed SharePoint Server 2010 Enterprise Edition - note do not run the SharePoint configuration wizard yet
Installed SharePoint Server 2010 SP1
Ran Windows Updates

Install SQL Server 2012 Business Intelligence Edition
  1. New SQL Server installation
  2. SQL Server Feature Installation - selected all features to be installed
  3. Configured service accounts
    1. SQL Server Agent, Database Engine, and Integration Services = SQLSvc account
    2. SQL Server Analysis Services = SSAS account
    3. SQL Server Reporting Services = SSRS account
  4. Configure Analysis Services - Multidimensional and Data Mining Mode
  5. Chose Install Only for Reporting Services Native Mode and Reporting Services SharePoint Integrated Mode
  6. Completed installation
  7. New SQL Server installation (Tabular)
  8. SQL Server Feature Installation - select only Analysis Services
  9. Name the instance
  10. Configure Analysis Services - Tabular Mode
  11. Configured service account
    1. SQL Server Analysis Services = SSAS account
  12. Completed installation
  13. New SQL Server installation (PowerPivot)
  14. SQL Server PowerPivot for SharePoint
  15. Name the instance
  16. Configured service account
    1. SQL Server Analysis Services = SSAS account
  17. Completed installation
From All Programs -> SQL Server 2012 -> Configuration Tools -> PowerPivot configuration Tool -  ran the PowerPivot Configuration Tool (Configure or repair PowerPivot for SharePoint)
Use the SPFarm account
Configured SharePoint from the command line:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN
PSConfig.exe -cmd upgrade -inplace b2b -force -cmd applicationcontent -install -cmd installfeatures
Restarted the Claims to Windows Token Service under Application Management -> Manage Services on the Server
Ran Windows Updates
Configured SharePoint to use SQL Server Reporting Services
From the SharePoint Management Shell run:
  • Install-SPRSService
  • Install-SPRSServiceProxy
  • get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance
Installed the SMTP feature on the server
Configured the SMTP Virtual Server to use my GMail account in the IIS 6.0 Manager
Created new SQL Server Reporting Services Application in SharePoint using SSRS account under System Settings -> Manage services on server
Selected the new SQL Server Reporting Services Application -> Provision Subscriptions and Alerts -> hit the Download script button and ran it in SQL Management Studio
Selected the new SQL Server Reporting Services Application -> E-mail Settings -> Checked Use SMTP Server, entered the FQDN name of the server and a From address
Installed CRM 2011 and Reporting Extensions
Installed and configured the CRM 2011 List Component for SharePoint
Ran Windows Updates
Installed Office 2010 Professional
Installed Visual Studio 2010 Tools for Office Runtime
Installed SQL 2012 PowerPivot for Excel 2010
Ran Windows Updates
Installed the latest version of Silverlight

And now a couple hours later... we should be all set!

Credit to:
http://blog.cloudshare.com/2012/05/22/step-by-step-guide-to-installing-sharepoint-with-sql-2012-powerpivot-powerview-and-reporting-services/

http://denglishbi.wordpress.com/2011/07/22/configuring-sql-server-denali-reporting-services-sharepoint-2010-integration/

Edit 9/27/2012: Added steps regarding adding/configuring SMTP and Email settings