Downgrading a SharePoint 2010 SQL Server

On one occasion, I came across an instance of a 2 server SharePoint farm.  One server was a SQL Server 2008 R2 database server and the other was the SharePoint 2010 Standard web front end.  The farm was initially setup using the Enterprise edition of SQL Server.  There is nothing wrong with using SQL Server 2008 Enterprise and SharePoint does take advantage of some of the Enterprise features.  The request came to add SQL Server Reporting Services (SSRS) into the Farm.  There are 3 different flavors of SharePoint to SSRS integration with this farm configuration.

1.) Install SSRS in native mode on the database server.  You then use the native mode SSRS web parts to render the reports within SharePoint.

2.) Install SSRS in integrated mode on the database server. The downside to this is that you need to perform a minimal SharePoint web front end installation on the database server. 

3.) Install SSRS in integrated mode on the web front-end server.  There are two things to be mindful of with this approach.  First, you will need a separate SQL license.  Second, Microsoft states that you must use the SAME VERSION of the SQL Server on the web front end as you have on the database server.

The company wanted to use the SSRS in integrated mode AND they did not want to perform a minimal SharePoint installation on the database server.  This only left option 3.  For the farm in question, they had already setup the farm using SQL Server 2008 R2 Enterprise.  This meant they would need to install SQL Server 2008 R2 Enterprise on the web front end server.  There is quite a difference in price between SQL Server 2008 R2 Standard and SQL Server 2008 R2 Enterprise.  Therefore, we decided to downgrade the existing database server.


There are plenty of posts that cover moving a farm from one SQL Server to another.  Here is a short list steps of that I found at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=158996 .  These are pure SQL server steps and do not account for setting up the SharePoint configuration databases.   In a nutshell, the SQL steps are:
  1.) Create a script of the custom server roles before downgrading the server
  2.) Detach the User Databases
  3.) Generate database script to create users
  4.) Physical files to backup system databases MASTER, MODEL, MSDB
  5.) Uninstall SQL Server 2008 Enterprise
  6.) Install SQL Server 2008 Standard
  7.) Attach the User Databases
  8.) Run the script to recreate the custom server roles (make sure Farm Admin account have securityadmin and dbcreator rights)

After installing SQL Server 2008 R2 STANDARD, we started re-attaching all of the SharePoint databases.  We encountered this error "Database ‘WebAnalyticsReportingDB’ already exists." as we tried to re-attach the database.  In typical Microsoft fashion, this error is not reflecting the actual problem.  In order to see the REAL error, you will need to open up SQL Server Management Studio (SSMS) and review the SQL Server logs.  You can see the SQL Server logs by
  1.) Open SQL Server Management Studio
  2.) In the object browser
  3.) Open the sql instance
  4.) Then management
  5.) Then sql server logs



You will see this when you review the log contents


The message "Database 'WebAnalyticsReportingDB' cannot be started in this edition of SQL Server because it contains a partition function 'DataIdPF'." makes sense because SQL Server 2008 R2 Enterprise supports partitioning and SQL Server 2008 R2 standard does not.

To resolve this problem
  1.) Make sure that both WebAnalyticsReportingDB and WebAnalyticsStagingDB are not on the SQL instance.
  2.) Re-provision the service in SharePoint using a powershell script from this technet site (http://technet.microsoft.com/en-us/library/gg266382.aspx)


I have also run into a similar with the Secure Store service.  If this happens to you, then you will need to recreate the Secure Store service in Central Administration.