- Getting Started
- Requirements
- Best Practices
- Installation
- Updating
- Identity Server
- Troubleshooting startup errors
Maintenance Considerations
It is important to keep your Identity Server database free from clutter. To do this, we recommend:
SQL Server Maintenance Solution is a set of scripts that enable you to run backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server, starting with the 2005 version. Please see this GitHub project for more information.
It is recommended that you create a separate database in which to save items before you delete them. This database acts as an archive for the items you may need to store for certain reasons, such as audits.
-
Create a new database called, for example,
UiPathIdentityArchives
:create database UiPathIdentityArchives
create database UiPathIdentityArchives -
Create the following backup tables:
-
ArchiveLoginAttempts
with the same structure as theUserLoginAttempts
table:select * into [UiPathIdentityArchives].[dbo].[ArchiveUserLoginAttempts] from [UiPath].[dbo].[UserLoginAttempts] where 1=2
select * into [UiPathIdentityArchives].[dbo].[ArchiveUserLoginAttempts] from [UiPath].[dbo].[UserLoginAttempts] where 1=2
-
Old data is copied to these archives prior to being deleted when using the query below.
To delete login attempts older than 60 days, for example, use the query below. It can be executed manually or scheduled in an SQL Server Job.
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
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
We recommend implementing regular backups of the SQL Server database, such as full weekly or daily incremental backups.
Additionally, we recommend using the DatabaseBackup stored procedure that is created using the script at this location.