Learning Lesson - Rebuild Sitecore index on real time basis from external system

If you are working on any large organization’s Sitecore project then certainly you have to integrate Sitecore with other existing external system. This is the real challenge where you have to study their external system and find the way to integrate it with Sitecore.

Recently I come across with one situation where we have to show search result both from Sitecore and external system. Getting search result is not a big deal but how to integrate it with Sitecore.

So we have created indexes of external system and merge with Sitecore indexes. This technic is working fine but now client want it should show search on real time basis.

Now this is the real technical challenge. Indexing is very costly process and scheduling in off- hour is fine but how to do it on real time?

After thinking a while on this situation, I was able to get the solution.

External system is referring SQL server database. So I decided to create trigger which call stored procedure which finally call MVC method and run the rebuild index code.

Real Time Indexing Technic Sequence

Write Rebuild index method in MVC controller action method

Here is a code for action method inside HomeController as

public class HomeController : Controller

{

    /// <summary>

    /// Rebuild Index in Sitecore

    /// </summary>

    /// <param name="TableName"></param>

    /// <param name="RecordId"></param>

    /// <param name="OperationType"></param>

    /// <param name="Token"></param>

    /// <returns></returns>

    public string RebuildIndex(string TableName, string RecordId, string OperationType, string Token)

    {

        string result = string.Empty;

        if (Token.Equals("dbtrigger"))

        {

            //Write code for rebuild the index

            string message = string.Format("Date={0}, Table Name={1}, RecordId={2}, OperationType={3}, Token={4}", DateTime.Now.ToString(),

                TableName, RecordId, OperationType, Token);

            System.IO.File.AppendAllText("d:\\webtest1.txt", message + Environment.NewLine);

            result = DateTime.Now.ToString() + " Index Rebuild";

        }

        else

        {

            result = "Token is incorrect";

            System.IO.File.AppendAllText("d:\\webtest1.txt", result);

        }

        return result;

    }

}

This is just a pseudo code. You can refer my article “How to rebuild index programmatically in Sitecore” for more detail on how to rebuild Sitecore indexes?

For implementing security check, I am passing token so that this rebuild indexes code will run only with matching passcode. 

For testing purpose, I am also logging all the request details in file which help us to understand that our method is running properly or not.

You must test this action method and note the URL as

MVC Action Method Result

SQL Table

Run below script for creating a demo table.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tbl_State](

       [Id] [int] IDENTITY(1,1) NOT NULL,

       [StateName] [nvarchar](50) NULL,

 CONSTRAINT [PK_tbl_State] PRIMARY KEY CLUSTERED

(

       [Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

How to call MVC controller action method from Stored procedure

--This Stored procedure call to MVC action method and do the index rebuild

CREATE PROCEDURE dbo.[usp_RebuildIndexViaMVCMethod]

       @OperationType VARCHAR(20) = null,

       @TableName VARCHAR(20) = null,

       @RecordId INT = null

AS

       DECLARE @obj int

       DECLARE @url VarChar(400)

       DECLARE @response VarChar(1000)

       SET @url= 'http://localhost:62364/home/RebuildIndex?TableName='+ @TableName + '&RecordId='+ CONVERT(VARCHAR(15),@RecordId) +'&OperationType='+@OperationType+'&Token=dbtrigger'

      

       EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT

       EXEC sp_OAMethod @obj, 'Open', NULL, 'GET', @url, false

       EXEC sp_OAMethod @obj, 'send'

       EXEC sp_OAGetProperty @obj, 'responseText', @response OUT

       --SELECT @obj

       SELECT @response [RESPONSE]

       --SELECT @url

       EXEC sp_OADestroy @obj

RETURN

         

This is the main step of this technical challenge. This stored procedure receive parameters like

• Operation type - Insert, update or delete flag
• Table Name – Name of the table
• RecordId - Record id for which received by trigger  

Specify the URL properly in SQL format.

Note: - You can also use web service URL. But I found more complication with web service. So my suggestion is to use MVC method than web service. 

You can test this stored procedure as 

--Run this stored procedure

EXEC [usp_RebuildIndexViaMVCMethod] 'INSERTED', 'tbl_State', 10

Note:-

You may get below “SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures'” error on executing above stored procedure as

Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1

SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.

Msg 15281, Level 16, State 1, Procedure sp_OAMethod, Line 1

SQL Server blocked access to procedure 'sys.sp_OAMethod' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.

Msg 15281, Level 16, State 1, Procedure sp_OAMethod, Line 1

SQL Server blocked access to procedure 'sys.sp_OAMethod' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.

Msg 15281, Level 16, State 1, Procedure sp_OAGetProperty, Line 1

SQL Server blocked access to procedure 'sys.sp_OAGetProperty' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.

Msg 15281, Level 16, State 1, Procedure sp_OADestroy, Line 1

SQL Server blocked access to procedure 'sys.sp_OADestroy' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.

Run below configuration statement which allow SQL to run Ole Automation Procedure to correc tthe above errors as

sp_configure 'show advanced options', 1

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1

GO

RECONFIGURE;

GO

sp_configure 'show advanced options', 1

GO

RECONFIGURE;

You should get below successful messages by executing above configuration statements.

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Configuration option 'Ole Automation Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install.

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Trigger

Below is a trigger which run on tbl_state table on insert / update / delete operation and call store procedure with all the parameters details as

-- Create Trigger on State table for insert, update and delete option

-- This trigger calls stored procedure to rebuild index through MVC action method

CREATE TRIGGER dbo.trig_State ON tbl_State AFTER INSERT, UPDATE, DELETE

AS

BEGIN

       DECLARE @Id INT

       IF EXISTS(SELECT * FROM INSERTED)

       BEGIN

              SELECT  @Id = INSERTED.ID FROM INSERTED

              EXEC dbo.[usp_RebuildIndexViaMVCMethod] 'Inserted', 'tbl_State', @Id

              PRINT 'Insert Operation';

       END

       IF EXISTS(SELECT * FROM DELETED)

       BEGIN

              SELECT @Id= DELETED.ID FROM DELETED

              EXEC dbo.[usp_RebuildIndexViaMVCMethod] 'Deleted', 'tbl_State', @Id

              PRINT 'Delete Operation';

       END

END

GO

Test the complete process

If I fire any insert/update/delete query on tbl_state table, it must fire trigger, stored procedure and finally call MVC rebuild index action. You should get response from MVC method in SQL query result as well as logging entry in our sample log file as

 

SQL and Log file Output

Please leave your comments or share this Sitecore lesson if it’s useful for you.