As you might know, Orchestrator never deleted anything from the database, instead it marks the object as deleted.
Ryan talks about it in this blog post about undeleting objects. Read more here
In my Lab environment I import and delete a lot of runbooks.
A couple of weeks ago i hit a point where the web service did not show all objects. After a thorough investigation i found out that the authorization cache was too big to be generated within the giving time. Therefore the web service would timeout before the cache was complete.
I tried to delete objects, but it did not help. By checking the database i found out that the authorization cache was generating entries for all objects, even the ones that were already deleted!
so i checked the stored procedures in the data store, and found out that it was in fact using a query that included all objects.
To fix this problem i have changed the stored procedures inside the data store:
Here is my new version of the procedures:
[Microsoft.SystemCenter.Orchestrator.Internal].[ComputeRunbookAuthorizationCache]:
USE [Orchestrator] GO /****** Object: StoredProcedure [Microsoft.SystemCenter.Orchestrator.Internal].[ComputeRunbookAuthorizationCache] Script Date: 4/23/2013 6:06:09 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [Microsoft.SystemCenter.Orchestrator.Internal].[ComputeRunbookAuthorizationCache] @TokenId bigint AS BEGIN SET NOCOUNT ON; DECLARE @cursor CURSOR DECLARE @uniqueId uniqueidentifier DECLARE @readRights bigint DECLARE @readAndPublishRights bigint SET @readRights = 16 SET @readAndPublishRights = 1048592 SET @cursor = CURSOR LOCAL FAST_FORWARD FOR SELECT [UniqueID] FROM dbo.POLICIES WHERE Deleted = 0 OPEN @cursor FETCH NEXT FROM @cursor INTO @uniqueId WHILE @@FETCH_STATUS = 0 BEGIN exec [Microsoft.SystemCenter.Orchestrator.Internal].PopulateAuthorizationCache @TokenId, @uniqueId, @readRights exec [Microsoft.SystemCenter.Orchestrator.Internal].PopulateAuthorizationCache @TokenId, @uniqueId, @readAndPublishRights FETCH NEXT FROM @cursor INTO @uniqueId END CLOSE @cursor DEALLOCATE @cursor END
and the other procedure:
[Microsoft.SystemCenter.Orchestrator.Internal].[ComputeFolderAuthorizationCache]:
USE [Orchestrator] GO /****** Object: StoredProcedure [Microsoft.SystemCenter.Orchestrator.Internal].[ComputeFolderAuthorizationCache] Script Date: 4/23/2013 6:06:46 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [Microsoft.SystemCenter.Orchestrator.Internal].[ComputeFolderAuthorizationCache] @TokenId bigint AS BEGIN SET NOCOUNT ON; DECLARE @cursor CURSOR DECLARE @uniqueId uniqueidentifier DECLARE @rights bigint SET @rights = 16 SET @cursor = CURSOR LOCAL FAST_FORWARD FOR SELECT [UniqueID] FROM dbo.FOLDERS WHERE Deleted = 0 OPEN @cursor FETCH NEXT FROM @cursor INTO @uniqueId WHILE @@FETCH_STATUS = 0 BEGIN exec [Microsoft.SystemCenter.Orchestrator.Internal].PopulateAuthorizationCache @TokenId, @uniqueId, @rights FETCH NEXT FROM @cursor INTO @uniqueId END CLOSE @cursor DEALLOCATE @cursor END
All i have added to the procedure queries is
WHERE Deleted = 0
after you have changed the procedures, wait 10+ minutes for the authorization cache to update or force the update by using this guide:
Now it works and i have no problems.
But please remember
Changing the data store is NOT supported, and this solution should only be used in Lab environments.
Please do not hesitate to post any comments about the solution 🙂
I really like your writing style, fantastic information, thanks for putting up ebafdkbkebdf