SharePoint & SQL Thoughts

After configuring user profile you get the correlation id and in event logs you get

The Forefront Identity Manager Service cannot connect to the SQL Database Server.

The SQL Server could not be contacted. The connection failure may be due to a network failure, firewall configuration error, or other connection issue. Additionally, the SQL Server connection information could be configured incorrectly.

Verify that the SQL Server is reachable from the Forefront Identity Manager Service computer. Ensure that SQL Server is running, that the network connection is active, and that the firewall is configured properly. Last, verify the connection information has been configured properly. This configuration is stored in the Windows Registry.

Open central administration à security à Configure Service Account (On the drop down pick Farm Account) and ensure that the select and account for the component is the same as the one you are using to configure FIM (User profiles) the OK.

That should fix that issue.

Happy FIMing

Here is the list of all the changes in SQL Server engine that has been removed.

http://technet.microsoft.com/en-us/library/ms143729.aspxv

This morning I decided to be a little adventurous and install SharePoint 2010 on SQL 2012 environment and the first thing I noticed is that the installation fails because in SQL 2012 Microsoft has removed [sys].[sp_dboption] system Stored Procedure. This has already been reported as a bug but the response from the MS team was that this is by design and they have deprecated it. No workaround has been provided as of yet but they are working to certify

I decided to take the [sp_dboption] from SQL 2008 R2 environment and I also noticed that the built-in functions like DATABASEPROPERTY are also gone. I made a few changes to the SPROC see code from the link below and was able to successfully install SharePoint 2010

https://skydrive.live.com/redir.aspx?cid=3a35c02c8765e840&resid=3A35C02C8765E840!261&parid=3A35C02C8765E840!152&authkey=!AKwXZrZvEi_O7Isv

** Disclaimer this has not been tested in a production environment.

One of the things that I’ve heard people complain over the years after the release of SharePoint 2010 is the disappearance of Drag & Drop web parts capability. This feature was useful because it allowed the page designer to add as many web parts on a page without having to click add and repeating the process all over again.

This feature is available on SharePoint 2010 with a little manipulation of the site URL of the page that you want to modify by adding the “?toolpaneview=2”

If your URL is http://sharepoint_portal/pages/default.aspx, replace it with http://sharepoint_portal/pages/?toolpaneview=2

By making this small change, the “Add Web Parts” zone is displayed on the right hand side of the page similar to what was available in SharePoint 2007 and WSS 3.0 and from here on you can happily drag and drop. v

Recently a colleague of mine encountered the error below and wanted to know how to get past itRecently a colleague of mine encountered the error below and wanted to know how to get past it.

When configuring SharePoint 2010 Publish Portal you encounter the following error

“The form cannot be rendered. This may be due to a misconfiguration of the Microsoft SharePoint Server State Service. For more information, contact your server administrator. “

This issue is caused by lack of Session State Service

To fix this issue power up your PowerShell and run the following commands

PS C:\Users\farmsvc> $serviceApp = New-SPStateServiceApplication -Name "State Service"

PS C:\Users\farmsvc> New-SPStateServiceDatabase -Name "StateServiceDatabase" -ServiceApplication $serviceApp

n  This command creates a database

Name                                      Id                                                                            Type

StateServiceDatabase         4d968d46-28f4-4306-b9ff-8e2908cb31fe           Microsoft.Office....

PS C:\Users\farmsvc> New-SPStateServiceApplicationProxy -Name "State Service" -ServiceApplication $serviceApp -DefultProxyGroup

DisplayName          TypeName             Id

State Service        State Service Proxy  8b155590-3be3-443a-9f10-4b303a01c450v

There has been this notion that breadcrumbs disappeared in SharePoint 2010 but that’s entirely not true. Microsoft replaced the traditional breadcrumbs with the folder in the ribbon. Most of the time, end-users have a difficult time finding this navigation thus preferring the traditional breadcrumb.

Here is a post from Erik Swenson on how to add or replace the folder breadcrumb, the only change I would recommend making, is changing your SiteMapProvider to CurrentNavSiteMapProviderNoEncode to prevent the navigation from bringing the child nodes.

Thus my provider would look something like this

<asp:SiteMapPath SiteMapProvider="CurrentNavSiteMapProviderNoEncode" id="ContentMap" runat="server"/>

Happy SharePointing...

Came across a good article regarding TFS upgrade from 2008 to 2010.

http://blogs.msdn.com/b/jjameson/archive/2010/05/04/upgrade-team-foundation-server-2008-to-tfs-2010-and-sharepoint-server-2010.aspx

I was having a conversation today with one of my clients about TempDB data files and how to spread them out to alleviate contention on SGMA pages, I came across a great article by Sunil Agarwal which I’d like to share.

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/04/managing-tempdb-in-sql-server-tempdb-configuration.aspx

Am back to SQL Server performance turning after a year of a lot of things, as always I love sharing what I am doing to help someone in need and to create a point of reference to my work.

Below is a SQL script that will allow SQL tables Index Rebuilding or Re-Organization, I added the Online flag to allow my tables to be accessible during the re-indexing process. You can reference BOL to see how the ONLINE flag works and the limitations that it brings along.

Remember for ONLINE feature to work you have to have enterprise, evaluation or the developer editions. For the purpose of my process, I needed to have the online flag enabled.

CREATE PROCEDURE [dbo].[usp_dbmaint_Reindexing_Statistics]
/*
This procedure determines the level of fragmentation on a database, based on fragmentation if your table is below 40%
the process reorganizes the indexes and if it’s over 40% the process rebuilds the indexes. At the end of the process,
I am updating statistics because Re-indexing will only update statistics regarding that specific Index but not the
Stats on the rest of your database.

Created by Leonard Mwangi www.etekglobalinc.com
*/
AS
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @i int, @n int

SET @i = 0
IF OBJECT_ID('tempdb..#Reindexing','u') IS NOT NULL
BEGIN
drop table #Reindexing
END

SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
,page_count
,0 as t_flag
INTO #Reindexing
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 20.0 AND index_id > 0;

SET @n = (SELECT COUNT(*) FROM #Reindexing)
WHILE @i < @n
BEGIN

SET @objectid = (SELECT TOP 1 objectid from #Reindexing where t_flag = 0);
SET @indexid = (SELECT TOP 1 indexid from #Reindexing where t_flag = 0);
SET @frag = (SELECT TOP 1 frag from #Reindexing where t_flag = 0);

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid

SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;

IF @frag > 20.0 and @frag < 40.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 40.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (ONLINE = ON)';
EXEC (@command);
PRINT N'Executed: ' + @command;

UPDATE top (1) #Reindexing
SET t_flag = 1
WHERE t_flag = 0
SET @i = @i + 1
END