Surendra Sharma

Surendra Sharma

Search This Blog

Saturday, September 10, 2016

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 in Sitecore
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
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
SQL and Log file Output

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