Writing SEO friendly URL’s in Sharepoint 2007 / 2010 (Part 2)

In my previous post I have discussed how to use URL rewrite module to create SEO friendly urls for Sharepoint websites. In this post, I will focus on how to improve this functionality by using DB Provider in IIS Rewrite Module 2.0 and IIS Rewrite module extensibility samples then bring it all together into SharePoint as a custom feature that will be linked from the Site Actions to allow content authors to manage url rewriting from within SharePoint

Pre requisites:

1. MOSS 2007 or 201

2. IIS 7.0 / 7.5

3. SQL Server 2005 or 2008

4. IIS Rewrite module 2.0,

5. IIS Rewrite modle extensibility samples


STEP 1



1. Download and install IIS URL rewrite module 2.0 http://www.iis.net/download/urlrewrite



2. Download and install Extensibility samples http://archive.msdn.microsoft.com/Project/Download/FileDownload.aspx?ProjectName=rewriteextensibility&DownloadId=9257


During the installation make sure to select the "Runtime" option in the custom setup. This will register the sample providers in .NET Global Assembly Cache (GAC) so that they can be used by URL Rewrite Module

Once we have downloaded and installed these two components you can then move on to the configuration part






STEP

1. Open URL Rewrite feature in IIS Manager then select “View Providers”

2. Select “Add Provider”

There are three rewrite providers included in the installation package :



DbProvider - this provider can be used to retrieve rewrite mappings from a SQL Server database table by executing a stored procedure

FileMapProvider - this provider can be used to retrieve rewrite mappings stored in a text file

FileContainsProvider - this provider can be used to check if any string in a text file is a substring of the provider's input string.




In this example I will only be using DbProvider.




STEP 3
To use the DbProvider, you will need access to your SQL server. The provider will connect to SQL Server database and execute a stured procedure that takes a NVARCHAR input parameter containing the input URL string and returns a one row one column result set containing the output URL string on the NVARCHAR string






I am creating two seaprate DB providers 1. DB_Rewrite and 2. DB_Redirect here which will refrence to the separate stored procedures GetRewrittenUrl and GetRedirectedUrl



Open a SQL Server Management Studio, open a new query window and execute the following SQL script:


USE [master]

CREATE LOGIN [IIS APPPOOL\DefaultAppPool] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

CREATE DATABASE [RewriteDB]
GO



USE [RewriteDB]

GO

CREATE TABLE [dbo].[RewriteTable](

[OriginalUrl] [nvarchar](256) NOT NULL,

[NewUrl] [nvarchar](256) NOT NULL

) ON [PRIMARY]

GO

CREATE PROCEDURE [dbo].[GetRewrittenUrl]
@input nvarchar(256)
AS
SELECT rt.NewUrl
FROM dbo.RewriteTable rt
WHERE rt.OriginalUrl = @input

GO

CREATE PROCEDURE [dbo].[GetRedirectedUrl]

@input nvarchar(256)

AS

SELECT rt.OriginalUrl

FROM dbo.RewriteTable rt

WHERE rt.NewUrl = @input

GO


STEP 4

Configuring DbProvider settings

In IIS Manager in the URL Rewrite feature view select "View Providers..." in the action pane.

Select "Add Provider" and choose DbProvider. Name it DB_Rewrite; that will be the name by which you will refer to the provider from a rewrite rule



Select the DbProvider instance called DB_Rewrite and click "Add Provider setting..." action.


Use the "Edit Provider Setting" dialog to configure the provider:



Use the following values for the provider settings:


SQL Server connection string: provide a SQL Server connection string, for example:


"Data Source=servername\sqlexpress;Initial Catalog=RewriteDB;Integrated Security=True"


Stored procedure name: GetRewrittenUrl


Cache minutes interval: set to 0 if values in the SQL table do not change, or set to a positive integer so that provider periodically refreshes the module's internal rewrite cache. If not specified the the value of 0 is assumed.




Repeat this process to create a second DbProvider instance


Select "Add Provider" and choose DbProvider. Name it DB_Redirect; that will be the name by which you will refer to the provider from a redirect rule

Select the DbProvider instance called DB_Redirect and click "Add Provider setting..." action.


Use the "Edit Provider Setting" dialog to configure the provider:



Use the following values for the provider settings



SQL Server connection string: provide a SQL Server connection string, for example:


"Data Source=servername\sqlexpress;Initial Catalog=RewriteDB;Integrated Security=True"


Stored procedure name: GetRedirectedUrl



Cache minutes interval: set to 0 if values in the SQL table do not change, or set to a positive integer so that provider periodically refreshes the module's internal rewrite cache. If not specified the the value of 0 is assumed.






STEP 5

Calling DbProvider from a Rewrite Rule:

In the root directory of your sharepoint site open web.config file.

add the following lines inside <configuration>/<system.webServer>/<rewrite>/<rules> element:

<configuration>
<system.webServer>
<rewrite>

<providers>

<provider name="DB_Rewrite" type="DbProvider, Microsoft.Web.Iis.Rewrite.Providers, Version=7.1.761.0, Culture=neutral, PublicKeyToken=0545b0627da60a5f">

<settings>

<add key="ConnectionString" value="Data Source=DatabaseName;Initial Catalog=RewriteDB;User=user_name; Password=password />

<add key="StoredProcedure" value="GetRewrittenUrl" />

<add key="CacheMinutesInterval" value="0" />

</settings>

</provider>

<provider name="DB_Redirect" type="DbProvider, Microsoft.Web.Iis.Rewrite.Providers, Version=7.1.761.0, Culture=neutral, PublicKeyToken=0545b0627da60a5f">

<settings>

<add key="ConnectionString" value="Data Source=DatabaseName;Initial Catalog=RewriteDB;User=user_name; Password=password” />

<add key="StoredProcedure" value="GetRedirectedUrl" />

<add key="CacheMinutesInterval" value="0" />

</settings>

</provider>

</providers>

<rules>

<rule name="DbProvider2" enabled="true" stopProcessing="true">

<match url="(.*)" />

<conditions logicalGrouping="MatchAll" trackAllCaptures="false">

<add input="{DB2:{R:1}}" pattern="(.+)" />

</conditions>

<action type="Redirect" url="{C:1}" />

</rule>

<rule name="DbProviderTest" enabled="true" stopProcessing="true">

<match url="(.*)" />

<conditions logicalGrouping="MatchAll" trackAllCaptures="false">

<add input="{DB:{R:1}}" pattern="(.+)" />

</conditions>

<action type="Rewrite" url="{C:1}" />

</rule>
</rules>
</rewrite>

This rule performs HTTP redirect to a URL that is obtained from a SQL Server database via DbProvider. The DbProvider instance named "DB" is invoked from the rule's condition and if the result returned from the provider is not empty then the HTTP redirection is performed.



At this stage you should be able to add Original and New Urls (friendly urls) to you database table and test is they are working correctly. If you are having trouble, remove the second Db_Provider (DB_Redirect) and test it saperately until you get it working correctly then add the redirect part. The reason I am using two separate providers is because I display a friendly url even when uses navigate to the site pages through the website navigation menu (not just when they type in the short url)



For example

If we have an Original sharepoint url : http://www.mysite.com.au/AboutUs/Pages/default.aspx

And a NewUrl(friendly url) http://www.mysite.com.au/about-us  

The rewriting will work fine if user manually types http://www.mysite.com.au/about-us
 But if they use the navigation menu to get to this page they will get http://www.mysite.com.au/AboutUs/Pages/default.aspx


To fix this, I am doing a permanent (301) redirection from old URL back to new URL
STEP 6
Once we have the functionality working correctly, the next step is to create an administration page where content authors can add / update / modify old and new urls to RewriteDB

STEP 7

Create a sharepoint Feature and add a link to it form the site actions menu




Step6 and Step 7 should be very easy to implement if you have any .net forms devepment experience and basic understanding on setting up sjharepoint features. so I am not posting specific details on these steps but if you would like to get more information or sample source code please send me an email on srirajc@hotmail.com






To see  this in action  please visit >> http://www.hamiltonisland.com.au/