...

Some planning and decision-making will be required before configuring and executing the database maintenance tasks described below. Consideration should be made on to how you consume and report on data in ResourceXpress and how troubleshooting could be affected when records are deleted.

Database maintenance requires a two-step approach. First, you configure the actions to take for each maintainable table and secondly second you configure and schedule the job to carry out the maintenance.

...

Available Maintenance Actions

There are four available actions for each of the above tables

Delete

The delete action will remove all records from the live target table. Records will not be archived and cannot be retrieved.

Deleting will improve performance and reduces the size of the database.

Archive

The archive option will move the records from the live table to an archive table. Records in an archive table cannot be viewed from the application UI but can be queried directly from the database.

When archiving, you specify the number of days worth of records to retain in the archive table.

Archiving will improve performance as the records do not interfere with application lookups however they still take up the same amount of storage space.

Take No Action

No records will be deleted or archived from the live table.

No performance or storage benefits apply to this option.

Configuring Maintenance Actions

Configuring the available maintenance actions is carried out in the ResourceXpress database and involves executing SQL queries to set your desired configuration.

Table 1.1 describes the available actions and their respective SQL values.

Table 1.2 shows the relevant SQL column names and the value configured by default.

Table 1.1 - SQL Values

...

Action Description

...

SQL Value

...

Delete

...

0

...

Archive

...

Any positive number i.e. 1, 2, 3 etc. The number configured will be the number of days records will be archived

...

Take No Action

...

NULL

Table 1.2 - Column Names and Default Values

...

Column Name

...

Default Value

...

ConnectivityLogDaysToKeep

...

0 = Delete all records

...

BookingSystemConnectivityLogDaysToKeep

...

0 = Delete all records

...

QubiActivityLogDaysToKeep

...

1 = Archive for 1 day

...

BookingDaysToKeep

...

NULL = Take no action

...

ResourceMessagesDaysToKeep

...

NULL = Take no action

...

EquipmentProblemsDaysToKeep

...

NULL = Take no action

...

ConnectivityLogArchiveDaysToKeep

...

0 = Delete all records

...

BookingSystemConnectivityLogArchiveDaysToKeep

...

0 = Delete all records

...

QubiActivityLogArchiveDaysToKeep

...

1 = Archive for 1 day

...

BookingArchiveDaysToKeep

...

NULL = Take no action

...

ResourceMessagesArchiveDaysToKeep

...

NULL = Take no action

...

EquipmentProblemsArchiveDaysToKeep

...

NULL = Take no action

To view the currently applied configuration execute the query

select * from dbo.ArchiveSetting

To change the maintenance action on any column you can run the below update query against the ResourceXpress database.

update dbo.ArchiveSetting set <column_name> = <value> where TenantId = 1

Example Queries

Archive Device Connectivity Log Records for 5 days

update dbo.ArchiveSetting set ConnectivityLogDaysToKeep = 5 where TenantId = 1

Keep all Qubi Activity Log records (not recommended)

update dbo.ArchiveSetting set QubiActivityLogDaysToKeep = NULL where TenantId = 1

Delete all Bookings records

update dbo.ArchiveSetting set BookingDaysToKeep = 0 where TenantId = 1

The maintenance actions configured in these columns will be carried out when the maintenance job executes. For example, if you configure the Booking option to 0 (delete) and the maintenance job runs at 2:00 am, all booking records will be deleted at that time.

Once you have configured your desired values you can move on to configuring the maintenance job itself.

Configuring the Maintenance Job

Option 1 - SQL Agent Job - MSSQL Server Only

If your ResourceXpress database is hosted on a dedicated SQL Server instance we recommend to carry out database maintenance as a SQL Agent Job.

Connect to the SQL Server using SQL Server Management Studio

Expand SQL Server Agent, right-click the Jobs folder, and select New Job…

...

On the General page populate the Name, Category, and Description fields then click OK

...

Select the Steps page and click New…

...

On the General page add a Step name, select the Type as Transact-SQL script (T-SQL) and ensure the correct Database is selected.

Add the below content into the Command window

...

...

Available Maintenance Actions

There are four available actions for each of the above tables

Delete

The delete action will remove all records from the live target table. Records will not be archived and cannot be retrieved.

Deleting will improve performance and reduces the size of the database.

Archive

The archive option will move the records from the live table to an archive table. Records in an archive table cannot be viewed from the application UI but can be queried directly from the database.

When archiving, you specify the number of days worth of records to retain in the archive table.

Archiving will improve performance as the records do not interfere with application lookups however they still take up the same amount of storage space.

Take No Action

No records will be deleted or archived from the live table.

No performance or storage benefits apply to this option.

Configuring Maintenance Actions

Configuring the available maintenance actions is carried out in the ResourceXpress database and involves executing SQL queries to set your desired configuration.

Table 1.1 describes the available actions and their respective SQL values.

Table 1.2 shows the relevant SQL column names and the value configured by default.

Table 1.1 - SQL Values

Table 1.2 - Column Names and Default Values

To view the currently applied configuration execute the query

select * from dbo.ArchiveSetting

To change the maintenance action on any column you can run the below update query against the ResourceXpress database.

update dbo.ArchiveSetting set <column_name> = <value> where TenantId = 1

Example Queries

Archive Device Connectivity Log Records for 5 days

update dbo.ArchiveSetting set ConnectivityLogDaysToKeep = 5 where TenantId = 1

Keep all Qubi Activity Log records (not recommended)

update dbo.ArchiveSetting set QubiActivityLogDaysToKeep = NULL where TenantId = 1

Delete all Bookings records

update dbo.ArchiveSetting set BookingDaysToKeep = 0 where TenantId = 1

The maintenance actions configured in these columns will be carried out when the maintenance job executes. For example, if you configure the Booking option to 0 (delete) and the maintenance job runs at 2:00 am, all booking records will be deleted at that time.

Once you have configured your desired values you can move on to configuring the maintenance job itself.

Configuring the Maintenance Job

Option 1 - SQL Agent Job - MSSQL Server Only

If your ResourceXpress database is hosted on a dedicated SQL Server instance we recommend to carry out database maintenance as a SQL Agent Job.

Connect to the SQL Server using SQL Server Management Studio

Expand SQL Server Agent, right-click the Jobs folder, and select New Job…

...

On the General page populate the Name, Category, and Description fields then click OK

...

Select the Steps page and click New…

...

On the General page add a Step name, select the Type as Transact-SQL script (T-SQL) and ensure the correct Database is selected.

Add the below content into the Command window

exec [dbo].ArchiveConnectivityLog 1
GO
exec [dbo].ArchiveBookingSystemConnectivityLog 1
GO
exec [dbo].ArchiveQubiActivityLog 1
GO
exec [dbo].ArchiveBookings 1
GO
exec [dbo].ArchiveResourceMessages 1
GO
exec [dbo].ArchiveEquipmentProblems 1
GO
exec [dbo].ArchiveGraphSubscription 1 
GO 
exec [dbo].ArchiveSchedulerInfo 1 
GO
exec [dbo].ArchiveAuditLog 1 
GO 
exec [dbo].ArchiveUserLoginHistory 1 
GO 
exec [dbo].DeleteBookingsArchive 1 
GO 
exec [dbo].DeleteAuditLogArchive 1 
GO 
exec [dbo].DeleteUserLoginHistoryArchive 1 
GO 
exec [dbo].DeleteGraphSubscription 1 
GO 
exec [dbo].DeleteSchedulerInfo 1 
GO 

...

On the Advanced page drop down the option On success action and select Go to the next step then click OK to save the step

...

• <SQL-SERVER\INSTANCE> = Your SQL Server instance name e.g. SQLSRV01\SQLEXPRESS

• <database_name> = your ResourceXpress database name e.g. rxpress

# Initial values, replace the {name} with proper values
$connectionString = "Data Source=<SQL-SERVER\INSTANCE-NAME>;Initial Catalog=<database_name>;Integrated Security=False; User ID=dbsupport1;Password=D8Supp0rt!;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"
$timeout = 300 # in seconds
$tenantId = 1

Write-Output('DB Maintenance - v0.1 started')
# Instantiate the connection to the SQL Database
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = $connectionString

# Define the SQL command to run
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = $timeout
$sqlCommand.Connection = $sqlConnection
$sqlCommand.Parameters.AddWithValue("@TenantId",$tenantId)

# Opening the SQL connection
try{
    $sqlConnection.Open()
}
catch{
    Write-Output('An error occurred in opening connection'+ $_.Exception.ToString())
    Exit 1
}

# Executing the stored procedures
$spList = 'ArchiveConnectivityLog','ArchiveBookingSystemConnectivityLog','ArchiveQubiActivityLog','ArchiveBookings','ArchiveResourceMessages','ArchiveEquipmentProblems','DeleteConnectivityLogArchive','DeleteBookingSystemConnectivityLogArchive','DeleteQubiActivityLogArchive','DeleteBookingsArchive','DeleteResourceMessagesArchive','DeleteEquipmentProblemsArchive','DeleteArchiveLog',‘ArchiveGraphSubscription’,’ArchiveSchedulerInfo’,‘ArchiveAuditLog’,’ArchiveUserLoginHistory’,’DeleteGraphSubscription’,‘DeleteSchedulerinfo’,‘DeleteAuditLogArchive’,‘DeleteUserLoginHistoryArchive’

foreach($sp in $spList){
     try{
        Write-Output('About to execute the sp ' + $sp)
        $sqlCommand.CommandText= 'exec [dbo].[' + $sp + '] @TenantId'
		$result = $sqlCommand.ExecuteNonQuery()
        Write-Output($sp + ' executed successfully')
    }
    catch{
        Write-Output('An error occurred in executing the sp ' + $sp + ' ' + $_.Exception.ToString())
    }
}

# Close the SQL connection
$sqlConnection.Close()
Write-Output('DB Maintenance is completed successfully')

Save and close the file.

Open Windows Task Scheduler

...