vRealize Automation 7.x and Microsoft SQL server 2016 using 130 compatibility mode(SQL Server 2016(130)) is not supported

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:

• Using incorrect 130 compatibility mode generates these errors in the DynamicOps.Tracking.TrackingLogItems tables of the vRA Microsoft SQL database. These entries cause significant growth in the database.
  
  System.Data.OptimisticConcurrencyException: Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.
   
• In the DEM Orchestrator and Worker logs, you see entries similar to:
  
  <boolean>false</boolean>
  Error on Orchestrator Task Check DEMs
  System.Data.Services.Client.DataServiceRequestException: An error occurred while processing this request. ---> System.Data.Services.Client.DataServiceClientException: <?xml version="1.0" encoding="utf-8"?><m:error xmlns:m="</SPAN><?xml:namespace prefix = "m" /><m:code'> http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"><m:code</SPAN> /><m:message xml:lang="pl-PL">The etag value in the request header does not match with the current etag value of the object.</m:message><m:innererror><m:message>Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.</m:message><m:type>System.Data.OptimisticConcurrencyException</m:type><m:stacktrace> at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
  at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
  at System.Data.Services.Providers.ObjectContextServiceProvider.SaveChanges()</m:stacktrace></m:innererror></m:error>
  --
  at DynamicOps.Repository.WorkflowClaimHelpers.UnclaimWorkflowInstances(RepositoryModelEntities dataContext, IEnumerable`1 instances, String resultText)
  at DynamicOps.DWE.Activities.CheckDems.Execute(CodeActivityContext context)
  INNER EXCEPTION: System.Data.Services.Client.DataServiceClientException: <?xml version="1.0" encoding="utf-8"?><m:error xmlns:m="</SPAN><m:code'> http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"><m:code</SPAN> /><m:message xml:lang="pl-PL">The etag value in the request header does not match with the current etag value of the object.</m:message><m:innererror><m:message>Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.</m:message><m:type>System.Data.OptimisticConcurrencyException</m:type><m:stacktrace> at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
  at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
  at System.Data.Services.Providers.ObjectContextServiceProvider.SaveChanges()</m:stacktrace></m:innererror></m:error>

Environment

VMware vRealize Automation 7.x

Cause

This issue is caused by an upgrade of SQL server where multiple databases coexist, and you did not revert the vRA database to use 120 compatibility mode (SQL Server 2014(120)).

 

vRealize Automation 7.x does not support 130 compatibility mode. For more information, see the IaaS Database Server Requirements section of Installing vRealize Automation guide.

Resolution

To change the compatibility mode:

1. Launch the MS SQL studio.
2. Right-click on the vRA Database.
3. Select Properties > Options > Compatibility Level.
4. Change the mode to SQL Server 2014(120).

Note:

Alternatively, the compatibility mode can be changed by running the following SQL command:

ALTER DATABASE vra SET COMPATIBILITY_LEVEL = 120
 

The error messages should stop after making this change.

To truncate the DynamicOps.Tracking.TrackingLogItems table:

 

Caution: Take a backup of your database before running this script.

1. Log in to the vRealize Automation database as a user with sufficient privileges to alter the database.
2. Execute this SQL query:
   
   DECLARE @FromDateToPurge DATETIME = 'YYYY-MM-DD HH:MM:SS'
   DECLARE @RowCount INT = 0
   SELECT * INTO #tmpTrackingLogItems FROM [DynamicOps.Tracking].[TrackingLogItems] WHERE 1 = 0
   SET IDENTITY_INSERT #tmpTrackingLogItems ON INSERT INTO #tmpTrackingLogItems (Id, TrackingSourceId, TrackingCategoryId, InstanceName, LogDatetimeUTC, LogDatetimeLocal, LogDatetimeServer, UserName, Severity, MachineName, Message, StackTrace) SELECT Id, TrackingSourceId, TrackingCategoryId, InstanceName, LogDatetimeUTC, LogDatetimeLocal, LogDatetimeServer, UserName, Severity, MachineName, Message, StackTrace FROM [DynamicOps.Tracking].[TrackingLogItems]
   WHERE LogDatetimeLocal >= @FromDateToPurge SET IDENTITY_INSERT #tmpTrackingLogItems OFF
   SET @RowCount = 0
   SET @RowCount = (SELECT COUNT(1) FROM [DynamicOps.Tracking].[TrackingLogItems])
   TRUNCATE TABLE [DynamicOps.Tracking].[TrackingLogItems]
   SET IDENTITY_INSERT [DynamicOps.Tracking].[TrackingLogItems] ON INSERT INTO [DynamicOps.Tracking].[TrackingLogItems] (Id, TrackingSourceId, TrackingCategoryId, InstanceName, LogDatetimeUTC, LogDatetimeLocal, LogDatetimeServer, UserName, Severity, MachineName, Message, StackTrace) SELECT Id, TrackingSourceId, TrackingCategoryId, InstanceName, LogDatetimeUTC, LogDatetimeLocal, LogDatetimeServer, UserName, Severity, MachineName, Message, StackTrace FROM #tmpTrackingLogItems SET IDENTITY_INSERT [DynamicOps.Tracking].[TrackingLogItems] OFF
   SET @RowCount = @RowCount - (SELECT COUNT(1) FROM #tmpTrackingLogItems) PRINT 'Rows purged from table [DynamicOps.Tracking].[TrackingLogItems are:' + CAST(@RowCount AS NVARCHAR(50)) DROP TABLE #tmpTrackingLogItems
   
   Note: The date 'YYYY-MM-DD HH:MM:SS' in the script removes all log entries before the specified date.