SharePoint & SQL Thoughts

Traditional breadcrumb in SharePoint

2012-02-10T15:07:31-06:00:00

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...

TFS Upgrade and SharePoint intergration

2011-08-19T10:15:31-05:00:00

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

TempDB - some things to know

2011-08-02T15:30:35-05:00:00

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

All databases storage information

2011-06-08T23:02:42-05:00:00

SQL Tidbits

Have you ever had a need to see storage information for all your databases in a SQL Server? As they say there are many ways to skin a cat all based on your need. Back in the day I was taught to use SYS.SYSFILES, its handy and it does give you what you need but suppose you wanted a picture of your entire SQL Server? Then this solution is not viable that’s when we divert to SYS.MASTER_FILES according to MSDN http://msdn.microsoft.com/en-us/library/ms186782.aspx this system view contain a row per file of a database as stored in the Master database simply put as contains all the information you need to know regarding your database(s) storage questions.

Here is a query that sums it all up excluding the system databases.

SELECT DB_NAME([database_id]) AS N'database_name'

, [name] AS N'logical_name'

, [Physical_Name]

,(size*8)/1024 as 'Physical_File_Size (MB)'

, SUM((size*8)/1024) Over(PARTITION by [database_id]) as 'DatabaseSize (MB)'

, [growth]

, [is_percent_growth]

FROM SYS.MASTER_FILES WHERE [database_id] > 4;

I hope this saves someone a headache.

Performance Turning

2011-04-22T12:29:21-05:00:00

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

Thank you South Florida for a successful SPSouthFLA

2010-12-06T16:45:48-06:00:00

I wanted to officially thank the organizers, speakers, volunteers and the attendees of SharePoint Saturday South Florida. Being the first event in South Florida the reception was phenomenon and the group of speakers from keynote by Joel Oleson to session’s speakers from well renowned speakers like John Holliday, Randy Disgrill, Richard Harbridge, Ameet Phadnis, Fabian Williams, Chris McNulty, Jaime Velez to organizers like Michael Hinckley amongst others.

With my Business Intelligence (BI) presentation being on the last track of the day, I spent very quality time networking with these great guys and getting the insider scope on International SharePoint Community from Joel and his son which was mesmerizing.

I had a very active audience to a point where we couldn’t accommodate all the contents within the 1hr allocated time because they were very engaged and wanted a deep dive session on news features like PowerPivot, enhancements on PerformancePoint, Excel Services amongst others in order to understand the business value and how SharePoint 2010 is making the self-service BI become a reality.

These community events allows the attendees experience technology first hand and network with MVPs, authors, experts providing high quality educational sessions usually for free which is a reason to attend.

I have made the slides for my session available for download for those interested http://goo.gl/VaH5x

Thinking outside the box FBA & Search

2010-10-21T08:50:10-05:00:00

Form Based Authentication (FBA) is great when exposing SharePoint on the internet or extranet, users don’t have to know the domain they are authenticating to, you can manage the authentication using LDAP or SQL database amongst other cool stuff. But during the configuration process you end-up disabling the Integrated Windows Authentication (IWA) because you want your users to be provided with an FBA page. Once you disable the IWA you get the following notification

If Windows authentication is not selected on any Zone of this Web application, crawling for this Web application will be disabled.

Or search an error in event logs

Access is denied. Verify that either the Default Content Access Account has access to this repository, or add a crawl rule to crawl this repository.

Great, the dilemma starts because you want FBA and Search but SharePoint is screaming that you need IWA for crawling purposes.

Here is a nifty way to get around this,

1. Extend your FBA based portal to have a different zone (intranet, extranet custom…) whatever you want.

2. Once you extend your portal, it will inherit the configuration of the default portal but it will be a different site on IIS.

3. Back to Authentication provider, make sure that your new site has IWA checked at this point FBA is optional since the site will only be used for content crawling.

4. Modify your Search Service Application Content Source, delete FBA site and add the extended site in your Start Addresses.

5. Save and start a full crawl. Once the crawl is complete, browse to your FBA site and try the search. And Walla Search should be working now.

#SQLSat53 was a succcess

2010-10-04T04:29:10-05:00:00

Thanks everyone who attended my sessions at SQL Saturday KC, I have uploaded the slides for anyone who wants to take a look, if anyone wants the scripts for automation please let me know.

http://www.slideshare.net/lmundia/power-shell-for-sql-server

Thanks again for attending

KC SharePoint Users Group presentation slides

2010-09-16T02:40:13-05:00:00

Below is a link for the SharePoint 2003/2007 to SharePoint 2010 upgrade slides from KC SharePoint users group presentation.

http://www.slideshare.net/lmundia/upgrade-sharepoint-20032007-to-sharepoint-2010

SharePoint 2007 "Fabulous 40 Templates" upgrade

2010-09-10T19:11:32-05:00:00

Here are the steps to upgrade your SharePoint 2007 "Fabulous 40 Templates" to SharePoint 2010

http://blogs.technet.com/b/tothesharepoint/archive/2010/08/18/sharepoint-2010-products-upgrade-and-the-fabulous-40-application-templates.aspx