Realizar el mantenimiento de la base de datos

Es importante mantener las bases de datos ordenadas. Para ello, recomendamos:

• Utilizar la solución de mantenimiento de SQL Server
• Eliminar datos antiguos periódicamente
• Copia de seguridad de la base de datos

Utilizar la solución de mantenimiento de SQL Server

La solución de mantenimiento de SQL Server es un conjunto de scripts que te permiten ejecutar copias de seguridad, comprobaciones de integridad y mantenimiento de índices y estadísticas en todas las ediciones de Microsoft SQL Server a partir de la versión 2005. Para obtener más detalles, consulta este proyecto de GitHub.

Copia de seguridad de la base de datos

Recomendamos implementar copias de seguridad regulares de la base de datos de SQL Server, como copias de seguridad incrementales completas semanales o diarias.

Además, recomendamos utilizar el procedimiento almacenado en DatabaseBackup creado mediante el script en esta ubicación.

Eliminar datos antiguos periódicamente

Capacidades compartidas de la suite

Cree una base de datos independiente en la que guardar los elementos antes de eliminarlos. Esta base de datos actúa como un archivo para los elementos que puede necesitar almacenar por ciertas razones, como las auditorías.

1. Crear una nueva base de datos llamada, por ejemplo, UiPathArchives:

   create database UiPathArchives

2. Crear las siguientes tablas de copia de seguridad:
   1. ArchiveAuditEvent con la misma estructura que la tabla AuditEvent:

      SELECT * INTO [UiPathArchives].[dbo].[ArchiveAuditEvent] from [accountmanagementdb].[adt].[AuditEvent] where 1 = 2

3. Archivar los datos:
   1. Archivar registros de auditoría

      DECLARE @NumberOfDaysToKeep INT
      DECLARE @CurrentDate DATETIME
      
      -- Specify the number of days
      SET @NumberOfDaysToKeep = 60
      -- Archive the list of audit event records that you want to delete
      SET @CurrentDate = GetDate()
      BEGIN TRANSACTION
      INSERT INTO [UiPathArchives].[dbo].[ArchiveAuditEvent]
      SELECT
      [Id],[CreatedOn],[Version],[OrganizationId],[Source],[Category],[Action],[IsUserEvent],
      [UserId],[FullName],[Email],[DetailsVersion],[Details],[OperationId]
      FROM [adt].[AuditEvent]
      WHERE DateDiff(day, CreatedOn, @CurrentDate) > @NumberOfDaysToKeep
      -- Delete the audit events
      DELETE FROM [adt].[AuditEvent]
      WHERE EXISTS (SELECT 1 FROM [UiPathArchives].[dbo].[ArchiveAuditEvent] WHERE Id = [adt].[AuditEvent].[Id])
      COMMIT TRANSACTION

      Los datos antiguos se copian en estos archivos antes de eliminarse cuando se utiliza la siguiente consulta.

4. Eliminar datos de la tabla.
   Importante: Antes de ejecutar el siguiente script, asegúrate de adaptarlo a tu entorno.
   1. Eventos de auditoría

      declare @NumberOfDaysToKeep int
      declare @CurrentDate datetime
      
      -- Specify the number of days
      SET @NumberOfDaysToKeep = 60
      -- Create temporary table with the list of audit event records that you want to delete
      SET @CurrentDate = GetDate()
      SELECT
      [Id],[CreatedOn],[Version],[OrganizationId],[Source],[Category],[Action],[IsUserEvent],
      [UserId],[FullName],[Email],[DetailsVersion],[Details],[OperationId]
      INTO #TempAuditRecordsToDelete
      FROM [adt].[AuditEvent]
      WHERE DateDiff(day, CreatedOn, @CurrentDate) > @NumberOfDaysToKeep
      -- Review the audit event records to be deleted
      SELECT * FROM #TempAuditRecordsToDelete
      -- Delete the audit events
      BEGIN TRANSACTION
      DELETE FROM [adt].[AuditEvent]
      WHERE EXISTS (SELECT 1 FROM #TempAuditRecordsToDelete WHERE Id = [adt].[AuditEvent].[Id])
      DROP TABLE #TempAuditRecordsToDelete
      COMMIT TRANSACTION

Servidor de identidad

Cree una base de datos independiente en la que guardar los elementos antes de eliminarlos. Esta base de datos actúa como un archivo para los elementos que puede necesitar almacenar por ciertas razones, como las auditorías.

1. Crear una nueva base de datos llamada, por ejemplo, UiPathIdentityArchives:

   create database UiPathIdentityArchives

2. Crear las siguientes tablas de copia de seguridad:
   1. ArchiveLoginAttempts con la misma estructura que la tabla UserLoginAttempts:

      select * into [UiPathIdentityArchives].[dbo].[ArchiveUserLoginAttempts] from [UiPath].[dbo].[UserLoginAttempts] where 1=2

      Los datos antiguos se copian en estos archivos antes de eliminarse cuando se utiliza la siguiente consulta.

3. Eliminar datos de la tabla.
   Importante: Antes de ejecutar el siguiente script, asegúrate de adaptarlo a tu entorno.
   1. Intentos de inicio de sesión de usuario
      Para eliminar los intentos de inicio de sesión de más de 60 días, por ejemplo, usa la siguiente consulta. Esto puede ejecutarse de forma manual o programada como tarea en SQL Server.

      declare @NumberOfDaysToKeep int
      set @NumberOfDaysToKeep = 60
      if OBJECT_ID('[UiPathIdentityArchives].[dbo].[UserLoginAttemps]') = NULL 
        begin select * into [UiPathIdentityArchives].[dbo].[UserLoginAttemps] from [identity].UserLoginAttempts where 1=2 end
      begin transaction
        set identity_insert [UiPathIdentityArchives].[dbo].[UserLoginAttemps] on
        insert into [UiPathIdentityArchives].[dbo].[UserLoginAttemps] ([Id],[PartitionId],[UserId],[UserNameOrEmailAddress],[ClientIpAddress],[ClientName],[BrowserInfo],[Result],[CreationTime],[AuthenticationProvider],[PartitionName])
          select [Id],[PartitionId],[UserId],[UserNameOrEmailAddress],[ClientIpAddress],[ClientName],[BrowserInfo],[Result],[CreationTime],[AuthenticationProvider],[PartitionName]
            from [identity].UserLoginAttempts where DateDiff(day, CreationTime, GetDate()) > @NumberOfDaysToKeep
        delete from [identity].UserLoginAttempts where DateDiff(day, CreationTime, GetDate()) > @NumberOfDaysToKeep
      commit transaction

Orchestrator

Limpieza de la base de datos

Los siguientes scripts reemplazan a todos y cada uno de los scripts de limpieza de bases de datos anteriores. Cubren todas las tareas de mantenimiento necesarias y pueden configurarse según las necesidades.

Importante:

Puedes descargar los scripts de mantenimiento de la base de datos de Customer Portal.

Script SQL

Requisitos previos

Esto es lo que necesitas para poder ejecutar el script CreateOrchestratorCleanupObjects.sql:

• Debes tener acceso a la base de datos de archivo desde el contexto de tu base de datos de Orchestrator.

• Debes poder acceder al archivo utilizando nombres de 3 partes, como Archive.dbo.QueueItemsTableArchive.

Cómo usarla

1. Ejecuta el script SQL CreateOrchestratorCleanupObjects.sql para crear los siguientes objetos:

• La tabla dbo.__CleanupLog, que alberga los registros de ejecución.
• El procedimiento dbo.GetOrCreateArchiveTable, que crea o devuelve la tabla de archivo.
• El procedimiento dbo.RunOrchestratorCleanup, que realiza la eliminación y, opcionalmente, el archivo de los datos antiguos.

2. Ejecuta dbo.RunOrchestratorCleanup con el archivo de configuración XML de programación para realizar la limpieza.Para obtener más información sobre el archivo XML, consulta la sección Programar la limpieza a continuación.

Nota:

También puedes utilizar el agente de SQL Server para realizar la limpieza.

Ejemplo de script

DECLARE @cleanupConfigXml XML = 
'<CleanupConfig totalRunMaxMinutes="180">
	<Table name="QueueItems" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="CreationTime" additionalFilter="Status IN (2, 3, 4, 5, 6)" daysOld="180" batchSize="50" forceCascade="1" shouldArchive="1" />
</CleanupConfig>';

EXEC dbo.RunOrchestratorCleanup 
@cleanupConfigXml = @cleanupConfigXml,
@archiveDatabaseName = 'OrchestratorArchive';

secuencia de comandos de powershell

Esto es adecuado para situaciones en las que no puedas utilizar el script SQL.

Nota:

El script de PowerShell es compatible con Powershell 5.1 y se puede utilizar con los runbooks de automatización de Azure.

Requisitos previos

El script RunOrchestratorCleanup.ps1 debe poder comunicarse con estas dos cadenas de conexión:

• SourceConnectionString - esta es la cadena de conexión de base de datos de Orchestrator.
• DestinationConnectionString - esta es la cadena de conexión de base de datos de archivo. Ten en cuenta que la base de datos de archivo debe crearse y configurarse con antelación.

Objetos que crea

• La tabla dbo.__CleanupLog, que alberga los registros de ejecución.
• La tabla dbo.__CleanupIds, que almacena un lote temporal de ID que deben limpiarse.

Limitaciones

El script abre dos conexiones: una a la base de datos de Orchestrator (a través de SourceConnectionString) y una a la base de datos de archivo (a través de DestinationConnectionString). Por ello, no se trata de ninguna transacción real, ya que los datos se copian de Orchestrator al archivo, después de lo cual se eliminan de Orchestrator.

Esto significa que si se produce una excepción en cualquier lugar entre las dos acciones, lo que hace que el script se vuelva a ejecutar, los mismos datos podrían copiarse de nuevo, lo que provocaría duplicados en la base de datos de archivo.

Sin embargo, como la singularidad no se aplica en la base de datos de archivo, esto no genera ningún problema.

Ejemplo de script

.\RunOrchestratorCleanup 
-SourceConnectionString "Data Source=.;Initial Catalog=UiPath;User ID=sa;Password=******" 
-DestinationConnectionString "Data Source=.;Initial Catalog=<OrchestratorArchive>;User ID=sa;Password=******" 
-CleanupConfigXml '<CleanupConfig totalRunMaxMinutes="180"><Table name="QueueItems" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="CreationTime" additionalFilter="Status IN (2, 3, 4, 5, 6)" daysOld="180" batchSize="50" forceCascade="1" shouldArchive="1" /></CleanupConfig>'

Programación de la limpieza

Puedes elegir qué datos limpiar de tu base de datos, cuándo y durante cuánto tiempo se debe realizar la actividad de limpieza para cada ejecución, entre otros.

Para ello, utiliza el siguiente archivo XML con el SQL o el script de PowerShell. Está preconfigurado con nuestros parámetros recomendados, pero puedes editarlo si quieres características más avanzadas. Asegúrate de comprender lo que hace cada parámetro antes de editar el script, ya que esto puede tener consecuencias graves.

<CleanupConfig totalRunMaxMinutes="180">
    <Table name="QueueItems" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="CreationTime" additionalFilter="Status IN (2, 3, 4, 5, 6)" daysOld="180" batchSize="50" forceCascade="1" shouldArchive="1" />
    <Table name="Jobs" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="CreationTime" additionalFilter="State IN (4, 5, 6)" daysOld="180" batchSize="50" forceCascade="1" shouldArchive="1" />        
    <Table name="Logs" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="TimeStamp" additionalFilter="" daysOld="90" batchSize="1000" forceCascade="0" shouldArchive="1" />
    <Table name="AuditLogs" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="ExecutionTime" additionalFilter="" daysOld="365" batchSize="25" forceCascade="1" shouldArchive="1" />
    
    <Table name="Tasks" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="DeletionTime" additionalFilter="IsDeleted = 1" daysOld="180" batchSize="10" forceCascade="0" shouldArchive="1" />
    <Table name="QueueProcessingRecords" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="ProcessingTime" additionalFilter="ReportType != -1" daysOld="30" batchSize="1000" forceCascade="0" shouldArchive="0" />
    <Table name="Sessions" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="ReportingTime" additionalFilter="" daysOld="180" batchSize="50" forceCascade="1" shouldArchive="0" />    
    
    <Table name="RobotLicenseLogs" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="StartDate" additionalFilter="" daysOld="180" batchSize="1000" forceCascade="0" shouldArchive="0" />    
    <Table name="UserNotifications" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="CreationTime" additionalFilter="" daysOld="90" batchSize="1000" forceCascade="0" shouldArchive="0" />
    <Table name="TenantNotifications" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="CreationTime" additionalFilter="" daysOld="90" batchSize="1000" forceCascade="0" shouldArchive="0" />    
    <Table name="Ledger" runMaxMinutes="-1" idColumn="LedgerId" dateTimeColumn="CreationTime" additionalFilter="" daysOld="7" batchSize="1000" forceCascade="0" shouldArchive="0" />
    <Table name="LedgerDeliveries" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="LastUpdatedTime" additionalFilter="" daysOld="7" batchSize="1000" forceCascade="0" shouldArchive="0" />
    <Table name="__CleanupLog" runMaxMinutes="-1" idColumn="Id" dateTimeColumn="ExecutionTimeUtc" additionalFilter="" daysOld="7" batchSize="1000" forceCascade="0" shouldArchive="0" />
</CleanupConfig>

Incluso si una ejecución solo consigue limpiar parcialmente una o algunas tablas, las ejecuciones posteriores continuarán la limpieza, hasta que el script se ponga al día con todo el trabajo atrasado. Por lo tanto, es importante que no acumules un retraso mayor del que el script puede gestionar. Si esto sucede, valora la posibilidad de programar el script para que se ejecute con más frecuencia.

Parámetros del XML de limpieza

Parámetro	Explicación	Valores posibles
totalRunMaxMinutes	El número máximo de minutos que el script puede ejecutarse para todas las tablas durante una ejecución.	Debe ser mayor que 1.
name	El nombre de la tabla que contiene los datos que quieres eliminar.	Ejemplo: QueueItems
runMaxMinutes	El número máximo de minutos que el script puede ejecutarse para una tabla en particular durante una ejecución. Importante: La cantidad de tiempo que establezcas aquí a veces puede durar un par de minutos más. Además de eso, el valor totalRunMaxMinutes tiene prioridad y siempre se aplicará, incluso si estableces este parámetro en -1.	-1: el script se ejecutará durante un número ilimitado de minutos. 0: el script no se ejecutará para esa tabla en particular. >0 (es decir, un número elegido por ti): el número máximo de minutos que el script se ejecutará.
idColumn	El ID de la columna que contiene los datos que quieres eliminar.	Ejemplo: Id de la tabla QueueItems
dateTimeColumn	Esto difiere en función del tipo de datos. Se utiliza en combinación con daysOld.	Ejemplo: CreationTime para QueueItems
additionalFilter	Cualquier instrucción SQL válida para un filtro. Esto se puede dejar vacío.	Ejemplo: Status IN (2, 3, 4, 5, 6). Esto se incluye en el archivo XML recomendado por nosotros.
daysOld	Te permite mantener un cierto número de días de datos . Se utiliza en combinación con dateTimeColumn.	Esto debe establecerse en un mínimo de 2. Ejemplo: establecer este parámetro en 5 mantiene todos los datos que tienen 5 días de antigüedad.
batchSize	El número de filas de la tabla que se eliminarán en una iteración. Importante: Esto no se utiliza para las tablas donde se definen claves extranjeras.	Ejemplo: establecer este parámetro en 50 para la tabla QueueItems elimina 50 elementos de esa tabla en particular.
forceCascade	Te permite ejecutar el script para las tablas donde se definen claves extranjeras. Importante: El ejemplo ya usa forceCascade para las tablas donde es necesario. Por lo tanto, no necesitas cambiarlo.	0: no en cascada. 1: en cascada. Por ejemplo, establecer esta opción para la tabla QueueItems procesará tanto las tablas QueueItemEvents como las tablas QueueItemComments.
shouldArchive	Te permite elegir si quieres archivar los datos.	0: no archivar. 1: archivar.

Scripts de SQL y PowerShell comparados

Función	Script SQL	secuencia de comandos de powershell
XML de limpieza	Ambos utilizan la lógica descrita en la sección Programar la limpieza.
Registro de ejecución	Ambos crean una tabla dbo.__CleanupLogs para almacenar los registros de la ejecución. Puedes consultar los registros de una ejecución utilizando SELECT * FROM dbo.__CleanupLog WHERE ExecutionId = '<execution_id>' ORDER BY Id. Puedes comprobar si una ejecución contenía errores utilizando SELECT * FROM dbo.__CleanupLog WHERE ExecutionId = '<execution_id>' AND IsError = 1. El parámetro ExecutionId se genera con cada ejecución de cualquiera de estos scripts.
Tabla de archivo	La tabla de archivo no contiene ningún índice, claves extranjeras ni columnas de identidad. Las columnas de tipo de marca de tiempo no se archivan.
Nombre de la tabla de archivo	Los nombres siguen la misma lógica e incluyen una cadena basada en el esquema de la tabla.
Lote de ID que se archivarán/eliminarán	Almacenado en una tabla temporal.	Almacenado en la tabla dbo.__CleanupIds.
Transaction	Se realiza una sola transacción para cada lote, durante la cual los datos se archivan y se eliminan. El tamaño del lote, es decir, el número de filas de la tabla que se procesarán, se define en el archivo XML, a través del parámetro batchSize.	SqlBulkCopy se utiliza para copiar los datos. No existe una transacción única realizada para el archivo y la eliminación.