Built in data rollover feature in vRA does not function as expected
search cancel

Built in data rollover feature in vRA does not function as expected

book

Article ID: 344335

calendar_today

Updated On:

Products

VMware Aria Suite

Issue/Introduction

  • When you enable the rollover feature after the vRA environment has been up and running for a while, the built in data rollover function is unable to archive old log events stored in the IaaS SQL database.
  • You see below information mentioned in Note section under Customize Data Rollover Settings topic in vRealize Automation Documentation.
Consider existing system data and the potential impact on system performance before enabling data rollover. For example, if you enable this feature one year after vRealize Automation began running in your environment, verify that you have set the value of DataRollover MaximumAgeInDays to 300 or greater to ensure that enabling data rollover feature does not impact system performance.
  • This note states to only perform archiving on 65 days of data at a time. If you have a large number of events, even this period may be too large to handle in one pass.

Environment

VMware vRealize Automation 7.x

Resolution

Resolution

Note:

  • Take a backup of the IaaS SQL database before proceeding.
  • You can stop the DataRollover process by changing the DataRollover Status setting of Running to Disabled or Enabled. This causes the currently running process to quit gracefully. No work is lost. All data archived or deleted up to the point of stopping the process is saved.

Procedure

  1. Take a full IaaS SQL Server Database backup.
  2. Open the attached 53063_DataRollover_UPDATED_v1.sql script in SQL Server Management Studio and execute the entire contents of the script. This script will create/modify the following:
    1. Create the [dbo].[DatarolloverStatus] table - This table holds execution information that can be monitored while the DataRollover process [usp_DataRollover] stored procedure is running.
    2. Drop and recreate the [dbo].[usp_DataRollover] stored procedure - This is the DataRollover process. This stored procedure will be entirely replaced with the V4 version.
    3. Three records will be added to the [DynamicOps.Core].[GlobalPropertyItem] table. These new records are settings that can be set on the Infrastructure-Administration-Global Settings page in the vRA portal. The other DataRollover settings are described here .
      1. DataRollover BatchSize - This is defaulted to 2000 and probably does not need to be changed. However, if there seem to be some performance impacts, then a smaller BatchSize may help. A larger BatchSize may get the job done faster, but will put more pressure on concurrent processing. Valid range is 100 to 20000.
      2. DataRollover UpdateStatistics - The UpdateStatistics is off by default, but is highly recommended to be turned on (set to 1) as updated statistics is good for query performance. This causes the [dbo].[usp_DataRollover] stored procedure to perform update statistics command on the tables after the archival process has run.
      3. DataRollover VirtualMachineHistory BatchSize - Specifies batch size in the VirtualMachineHistory table in the range of 1 - 1000 records. The default is 200.
  3. Increase sql command timeout in the repository:
    1. Edit  C:\Program Files (x86)\VMware\vCAC\Server\Model Manager Web\Web.config file
    2. Add the following entry in "<appSettings>" section of web.config of web repository to increase timeout to 5 minutes:

      <add key="sqlCommandTimeout" value="300" />
       
    3. Run iisreset and restart all IAAS related services.
    4. Repeat this on all web repository nodes
  4. Once all of the above is done, let the DataRollover process catch up to current by executing [dbo].[usp_DataRollover] directly in SQL Server Management Studio using this syntax below.
    Note: This is optional, but HIGHLY RECOMMENDED. This may run for a very long time. But this is to be expected if there is massive amounts of data to archive/delete. Before executing this, ensure that the DataRollover process status is set to Enabled by going to Infrastructure-Administration-Global Settings page in the vRA portal.
    EXEC [dbo].[usp_DataRollover]

Recovering Disk Space

  • If the goal is to recover disk space, this should be set to False. The default setting is True, which will not free up space, because it merely moves (archives) all the records from the "real" table to the "archive" table and thus will still consume the space. Setting this to False will DELETE the data.
    DataRollover IsArchiveEnabled
  • Also this setting is defaulted to 0, which means UPDATE STATISTICS will not be performed as part of the data rollover process. It is HIGHLY RECOMMENDED to make this setting 1. Setting it to 1 will allow the data rollover process to perform MS SQL Server "UPDATE STATISTICS" command on the table that have had data removed. This is very important for the MS SQL Server optimization engine to correctly access data. This will generally increase query performance.
    DataRollover UpdateStatistics



Additional Information

  • Run this query to see DataRollover progress while it is running.
  • You should be able to see the count of records archived/deleted changing.
SELECT *, DATEDIFF(s, RecUpdate, GETDATE()) AS [seconds_since_update]
FROM [dbo].[DatarolloverStatus]
ORDER BY [Id] DESC

*********************************************************************************************
You can also go to Intrastructure-Monitoring-Log to see the status of the DataRollover process. The [usp_DataRollover] procedure will log completion information here.

*********************************************************************************************
Note that you may see this timeout, but this does *NOT* kill the actual DataRollover process.

DataRollover Workflow failed : System.Net.WebException: The operation has timed out
at System.Net.HttpWebRequest.GetResponse()
at DynamicOps.Repository.DataServiceContextExtensions.ExecuteInvoke(DataServiceContext context, Uri requestUri, String serializedRequest)
at DynamicOps.Repository.DataServiceContextExtensions.ExecuteInvoke[TRequest,TResponse](DataServiceContext context, Uri requestUri, TRequest requestObject, Boolean useDataContractSerializer)
at DynamicOps.Repository.DataServiceContextExtensions.ExecuteInvoke[TRequest,TResponse](DataServiceContext context, Uri requestUri, TRequest requestObject)
at DynamicOps.ManagementModel.Activities.DataRollover.Execute(CodeActivityContext context)
at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)
2018-02-03T03:01:41.993Z DCWIDVMVRA030 vcac: [component="iaas:DynamicOps.DEM.exe" priority="Error" thread="40272"] [sub-thread-Id="21"  context=""  token=""] DataRollover Workflow failed : System.InvalidOperationException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Net.WebException: The remote server returned an error: (400) Bad Request.
at System.Net.HttpWebRequest.GetResponse()
at DynamicOps.Repository.DataServiceContextExtensions.ExecuteInvoke(DataServiceContext context, Uri requestUri, String serializedRequest)
--- End of inner exception stack trace ---
at DynamicOps.Repository.DataServiceContextExtensions.ExecuteInvoke(DataServiceContext context, Uri requestUri, String serializedRequest)
at DynamicOps.Repository.DataServiceContextExtensions.ExecuteInvoke[TRequest,TResponse](DataServiceContext context, Uri requestUri, TRequest requestObject, Boolean useDataContractSerializer)
at DynamicOps.ManagementModel.ManagementModelEntities.SubmitDataRollover()
at DynamicOps.ManagementModel.Activities.DataRollover.Execute(CodeActivityContext context)
at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

*********************************************************************************************
If you see this timeout, then the DataRollover process will be kill, but all progress made archiving and deleting data will be saved. However if you do see this error, go to the Infrastructure-Administration-Global Settings page in the vRA portal and set the DataRollover Status back to Enabled. It may be left as Running when the sql command times out.

Exception has been thrown by the target of an invocation.
Inner exception: An error occurred while executing the command definition. See the inner exception for details.
Inner exception: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Inner exception: The wait operation timed out
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
--- End of inner exception stack trace ---
at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
at System.Data.Objects.ObjectContext.CreateFunctionObjectResult[TElement](EntityCommand entityCommand, ReadOnlyMetadataCollection`1 entitySets, EdmType[] edmTypes, MergeOption mergeOption)
at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, MergeOption mergeOption, ObjectParameter[] parameters)
at DynamicOps.ManagementModel.ManagementModelOperations.<>c__DisplayClass32.<SubmitDataRolloverRequest>b__31(String r, ManagementModelEntities d)
at DynamicOps.Common.Entity.OperationInvoke`1.Execute[TRequest](Func`3 operation)
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.Data.Services.Providers.BaseServiceProvider.InvokeServiceOperation(ServiceOperation serviceOperation, Object[] parameters)
at System.Data.Services.Providers.DataServiceProviderWrapper.InvokeServiceOperation(OperationWrapper serviceOperation, Object[] parameters)
at System.Data.Services.RequestUriProcessor.ComposeExpressionForServiceOperation(SegmentInfo segment, IDataService service, Boolean checkRights, SegmentInfo lastSegment)
at System.Data.Services.RequestUriProcessor.ComposeExpressionForSegments(IList`1 segments, IDataService service, Boolean isCrossReferencingUri)
at System.Data.Services.RequestUriProcessor.ProcessRequestUri(Uri absoluteRequestUri, IDataService service, Boolean internalQuery)
at System.Data.Services.DataService`1.HandleRequest()


Attachments

53063_DataRollover_UPDATED_v1.sql get_app
53063_DataRollover_UPDATED_v1.sql get_app
Powered by Wolken Software