Effectuer la maintenance de la base de données

Il est important de ne pas encombrer vos bases de données. Pour ce faire, nous vous recommandons :

• Utilisation de la Solution de maintenance de SQL Server
• Suppression périodique des anciennes données
• Sauvegarde de la base de données

Utilisation de la Solution de maintenance de SQL Server

La Solution de maintenance de SQL Server est un ensemble de scripts qui permettent d’exécuter des sauvegardes, des contrôles d’intégrité et la maintenance de l’index et des statistiques dans toutes les éditions de Microsoft SQL Server, à partir de la version 2005. Pour plus de détails, consultez ce projet GitHub.

Sauvegarde de la base de données

Nous vous recommandons d'implémenter des sauvegardes régulières de la base de données SQL Server, telles que les sauvegardes hebdomadaires complètes ou incrémentielles journalières.

En outre, nous vous recommandons d'utiliser la procédure stockée DatabaseBackup créée à l'aide du script à cet emplacement.

Suppression périodique des anciennes données

Capacités partagées de Suite

Créez une base de données distincte dans laquelle enregistrer les éléments avant de les supprimer. Cette base de données sert d'archive pour les éléments que vous devrez peut-être stocker pour certaines raisons, tels que les audits.

1. Créez une base de données appelée, par exemple, UiPathArchives.

   create database UiPathArchives

2. Créez les tables de sauvegarde suivantes :
   1. ArchiveAuditEvent avec la même structure que le table AuditEvent :

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

3. Archivez les données :
   1. Archiver les enregistrements d'audit

      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

      Les anciennes données sont copiées sur ces archives avant d’être supprimées lors de l’utilisation de la requête ci-dessous.

4. Supprimez les données du tableau.
   Attention : Avant d'exécuter les scripts suivants, assurez-vous de les adapter à votre environnement.
   1. Événements d'audit

      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

Serveur d'identité

Créez une base de données distincte dans laquelle enregistrer les éléments avant de les supprimer. Cette base de données sert d'archive pour les éléments que vous devrez peut-être stocker pour certaines raisons, tels que les audits.

1. Créez une base de données appelée, par exemple, UiPathIdentityArchives.

   create database UiPathIdentityArchives

2. Créez les tables de sauvegarde suivantes :
   1. ArchiveLoginAttempts avec la même structure que le table UserLoginAttempts :

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

      Les anciennes données sont copiées sur ces archives avant d’être supprimées lors de l’utilisation de la requête ci-dessous.

3. Supprimez les données du tableau.
   Attention : Avant d'exécuter les scripts suivants, assurez-vous de les adapter à votre environnement.
   1. Tentatives de connexion utilisateur
      Pour supprimer les tentatives de connexion antérieures à 60 jours, par exemple, utilisez la requête ci-dessous. Vous pouvez l'exécuter manuellement ou la planifier dans une tâche de 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

Nettoyage de la base de données

Les scripts suivants remplacent l'ensemble des scripts de nettoyage de la base de données antérieurs. Ils couvrent toutes les tâches de maintenance nécessaires et peuvent être configurés en fonction des besoins.

Important :

Vous pouvez télécharger les scripts de maintenance de la base de données Orchestrator à partir du Customer Portal.

Script SQL

Prérequis

Voici ce qu'il faut savoir pour exécuter le script CreateOrchestratorCleanupObjects.sql :

• Vous devez avoir accès à la base de données d'archives dans le contexte de votre base de données Orchestrator.

• Vous devez pouvoir accéder à l'archive à l'aide de noms en 3 parties, tels que Archive.dbo.QueueItemsTableArchive.

Comment l’utiliser

1. Exécutez le script SQL CreateOrchestratorCleanupObjects.sql pour créer les objets suivants :

• La table dbo.__CleanupLog, qui héberge les journaux d'exécution.
• La procédure dbo.GetOrCreateArchiveTable, qui crée ou renvoie la table d'archives.
• La procédure dbo.RunOrchestratorCleanup, qui effectue la suppression et, éventuellement, l'archivage des anciennes données.

2. Exécutez dbo.RunOrchestratorCleanup avec le fichier de configuration XML de planification pour effectuer le nettoyage. Pour plus de détails sur le fichier XML, consultez la section Planification du nettoyage ci-dessous.

Remarque :

Vous pouvez également utiliser SQL Server Agent pour effectuer le nettoyage.

Exemple 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';

script PowerShell

Ce scénario est adapté aux situations où vous ne pouvez pas utiliser le script SQL.

Remarque :

Le script PowerShell est compatible avec Powershell 5.1 et peut être utilisé avec les runbooks d'Azure Automation.

Prérequis

Le script RunOrchestratorCleanup.ps1 doit être capable de communiquer avec ces deux chaînes de connexion:

• SourceConnectionString : il s(agit de la chaîne de connexion de votre base de données Orchestrator.
• DestinationConnectionString : il s'agit de la chaîne de connexion de la base de données d'archives. Notez que la base de données d'archives doit être créée et configurée à l'avance.

Objets créés

• La table dbo.__CleanupLog, qui héberge les journaux d'exécution.
• La table dbo.__CleanupIds, qui stocke un lot temporaire d'ID qui doivent être nettoyés.

Limitations

Le script ouvre deux connexions : l'une à la base de données Orchestrator (via SourceConnectionString), et l'autre à la base de données d'archives (via DestinationConnectionString). Ainsi, aucune transaction réelle n'est impliquée, puisque les données sont copiées depuis Orchestrator dans l'archive, puis supprimées d'Orchestrator.

Cela signifie que, si une exception est levée quelque part entre les deux actions, entraînant la réexécution du script, les mêmes données peuvent être à nouveau copiées, ce qui entraîne des doublons dans la base de données d'archives.

Cependant, comme le caractère unique n'est pas appliquée à la base de données d'archives, cela ne génère aucun problème.

Exemple 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>'

Planification du nettoyage

Vous pouvez choisir les données à nettoyer à partir de votre base de données, le moment et la durée de l'activité de nettoyage pour chaque exécution, entre autres paramètres.

Pour ce faire, utilisez le fichier XML suivant avec le script SQL ou PowerShell. Il est préconfiguré avec nos paramètres recommandés, mais vous pouvez le modifier si vous souhaitez des fonctionnalités plus avancées. Assurez-vous de bien comprendre le rôle de chaque paramètre avant de modifier le script, car cela peut avoir de graves conséquences.

<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>

Même si une exécution ne parvient à nettoyer que partiellement une ou quelques tables, les exécutions suivantes poursuivront le nettoyage, jusqu'à ce que le script rattrape son retard. Il est donc important que vous n'accumuliez pas de retard plus important que le script ne peut gérer. Si cela se produit, envisagez de planifier le script pour qu'il s'exécute plus fréquemment.

Nettoyer les paramètres XML

Paramètre	Explication	Valeurs possibles
totalRunMaxMinutes	Le nombre maximal de minutes pendant lesquelles le script est autorisé à s'exécuter pour toutes les tables lors d'une exécution.	Doit être supérieure à 1.
name	Le nom de la table contenant les données à supprimer.	Exemple : QueueItems
runMaxMinutes	Le nombre maximal de minutes pendant lesquelles le script est autorisé à s'exécuter pour une table particulière lors d'une seule exécution. Important : Le délai défini ici peut parfois être dépassé de quelques minutes. De plus, le paramètre totalRunMaxMinutes a priorité et sera toujours appliqué, même si vous le définissez sur -1.	-1 : le script sera exécuté pendant un nombre illimité de minutes. 0 : le script ne s'exécutera pas pour cette table en particulier. >0 (autrement dit, un nombre choisi par vous) : le nombre maximum de minutes pendant lesquelles le script s'exécutera.
idColumn	L'ID de la colonne qui contient les données que vous souhaitez supprimer.	Exemple : Id de la table QueueItems
dateTimeColumn	Cela diffère selon le type de données. Ce paramètre est utilisé en combinaison avec daysOld.	Exemple : CreationTime pour QueueItems
additionalFilter	Toute instruction SQL valide pour un filtre. Ce paramètre peut être laissé vide.	Exemple : Status IN (2, 3, 4, 5, 6). Cette valeur est incluse dans le fichier XML recommandé par nos soins.
daysOld	Vous permet de conserver l'équivalent d'un certain nombre de jours de données. Ce paramètre est utilisé en combinaison avec dateTimeColumn.	Ce paramètre doit être défini sur 2 au minimum. Exemple : si vous définissez ce paramètre sur 5, toutes les données datant de 5 jours seront conservées.
batchSize	Le nombre de lignes de la table à supprimer dans une itération. Important : Ce paramètre n'est pas utilisé pour les tables où des clés externes sont définies.	Exemple : la définition de ce paramètre sur 50 pour la table QueueItems supprime 50 éléments de cette table spécifique.
forceCascade	Permet d'exécuter le script pour les tables où des clés externes sont définies. Important : L'exemple utilise déjà forceCascade pour les tables qui le requièrent. Vous n'avez donc pas besoin de le modifier.	0 : pas de cascade. 1 : cascade. Par exemple, la définition de cette option pour la table QueueItems la table traitera les tables QueueItemEvents et QueueItemComments.
shouldArchive	Permet de choisir si vous souhaitez archiver les données.	0 : ne pas archiver. 1 : archiver.

Comparaison des scripts SQL et PowerShell

Fonctionnalités	Script SQL	script PowerShell
Nettoyage XML	Ils utilisent tous les deux la logique décrite dans la section Planification du nettoyage.
Journal d'exécution	Ils créent tous les deux une table dbo.__CleanupLogs pour stocker les journaux d'exécution. Vous pouvez interroger les journaux d'une exécution en utilisant SELECT * FROM dbo.__CleanupLog WHERE ExecutionId = '<execution_id>' ORDER BY Id. Vous pouvez vérifier si une exécution contenait des erreurs en utilisant SELECT * FROM dbo.__CleanupLog WHERE ExecutionId = '<execution_id>' AND IsError = 1. Le paramètre ExecutionId est généré à chaque exécution de l'un de ces scripts.
Archivage de table	La table d'archives ne contient pas d'index, de clés externes et de colonnes d'identité. Les colonnes de type Horodatage (Timestamp) ne sont pas archivées.
Archivage de nom de table	Les noms suivent la même logique et incluent une chaîne basée sur le schéma de table.
Lot d'ID à archiver/supprimer	Stocké dans une table temporaire.	Stocké dans la table dbo.__CleanupIds.
Transaction	Une seule transaction est effectuée pour chaque lot, au cours de laquelle les données sont à la fois archivées et supprimées. La taille du lot, c'est-à-dire le nombre de lignes de la table à traiter, est définie dans le fichier XML, via le paramètre batchSize.	SqlBulkCopy est utilisé pour copier les données. Aucune transaction unique n'est effectuée pour l'archivage et la suppression.