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.

Returning a value from a dynamic SQL Statement

There have been times when I have needed to create a dynamic SQL Statement within a stored procedure.  You need to consider permissions of the process that is executing the dynamic sql statement but that will not be addressed here.  I had a need on a recent project to be able to execute a dynamic sql statement from a stored procedure and use its result within that same procedure.  Here is simple example of how you can do this.

This stored procedure accepts two integer parameters.  It converts them into a string values as it builds the SQL statement.  The key is that the data we desire to use is inserted into a temporary table within the dynamic sql statement.  This temporary table is created within this stored procedure and is accessible by the dynamic sql statement.  This allows us to query the data from the temporary table and use it later on within the stored procedure.


CREATE PROCEDURE dbo.Test
  @a int
  , @b int
AS
BEGIN
 DECLARE @return_status int
 DECLARE @sql varchar(8000)
   
 SET @sql = 'INSERT INTO #Temp(result_value)
             SELECT ' + convert(varchar(20), @a) + ' + ' + convert(varchar(20), @b);
   
 CREATE TABLE #Temp (
      result_value int null
 )

 EXEC (@sql);

 SELECT *
 FROM #Temp

 DROP TABLE #temp

END


This is the execution and results.

exec dbo.Test 1, 2

result_value
3
(1 row(s) affected)

SharePoint Online Development experiences

These are some things that I have learned while doing SharePoint Online development that are not explicitly stated on the SharePoint online developer site (http://msdn.microsoft.com/en-us/library/hh147180.aspx). Microsoft’s basic philosophy is that you can deploy sandbox solutions. Well … that is not completely true. Here are some things you can do in sandbox solutions that you cannot do SharePoint online

1.) You do not have access to some of the most useful objects in the Microsoft.SharePoint.Publishing assembly.  This make it difficult to make adjustments to the Global Navigation in code. For example, if you try to reference the PublishingWeb object, you get this error when you attempt to load the solution into the solution gallery.

2.) You do not have access to the SPLimitedWebPartManager. This rules out adding or modifying web parts in code. The funny thing is their site says that you can deploy web parts. However, you are not allowed to add your deployed web part to a page via code.

System.TypeLoadException: Could not load type 'Microsoft.SharePoint.WebPartPages.SPLimitedWebPartManager' from assembly 'Microsoft.SharePoint, Version=14.900.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'


3.) Calendar Overlays are not functional even though they appear to be available through the SharePoint Online UI. If you try to add the overlay via code, you get this message.

System.MissingMethodException: Method not found: 'Microsoft.SharePoint.SPForm Microsoft.SharePoint.SPFormCollection.get_Item(Microsoft.SharePoint.PAGETYPE)'.

This is a documented bug. It’s not specific to SharePoint Online.  The Calendar Overlay functionality only works in the Default Zone.


4.) At one point, I added a simple webpart to my Visual Studio 2010 SharePoint project.  The web part only printed into a label the text "Hello World". I was able to deploy the solution into the Solution Gallery. However, I received the following error when I tried to insert the web part into a page.

I got around this issue by separating the control out into a different project.