Description

The Spotfire Server Maintenance Stored Procedures are a collection of handy maintenance scripts that maintain your Spotfire Database, permissions and licenses.

Spotfire Server Maintenance

The TIBCO Spotfire Database Server Maintenance custom script ensures that the database server is using the SQL Server settings for optimal Spotfire Server performance. It is recommended to run this procedure after any major hardware or software changes. The procedure can also be placed on a schedule via SQL Server Agent.

The script goes through the following process:

  • Enable advanced options
  • Determine if ad-hoc optimisation can improve performance, if so, enable it for the spotfire database server, if not disable it.
  • If you have installed the Spotfire Server on the same server instance - The stored procedure variable SpotfireServerSame
    • If Yes (SpotfireServerSame=1) DEFAULT, reconfigure the database server memory settings to have a spare 8GB free
    • If No (SpotfireServerSame=0), reconfigure the database server memory settings to have a spare 4GB free
  • Reconfigure and set the database recovery model to simple for the Spotfire Server and Action Log
  • Reconfigure the database compatibility level to that of the server version you are using
  • Reconfigure the database backup compression settings so that any backups made will be compressed by default
  • Reconfigure the spotfire server databases collation (if not already set to the default Latin1_General_CI_AS)
  • Reconfigure the owner of the spotfire server databases to a sql login system administrator
  • Ensure that the Spotfire_Server and Spotfire_ActionLog Users are in the db_owner role of their respective databases

Spotfire Action Log Maintenance

The TIBCO Spotfire Action Log Maintenance custom script ensures that the Action Log Database is cleaned to within a specified period of time. It is recommended to run this procedure on a schedule via SQL Server Agent.

The script goes through the following process:

  • Clear action log (table: dbo.ACTIONLOG) from before date limit (Default 3 month limit, can be changed by declaring the stored procedure variable)
  • Clear Action Log database maintenance log (table: CustomScripts.MaintenanceLog) from before date limit (Default 3 month limit, can be changed by declaring the stored procedure variable)
  • Clear Spotfire Server database maintenance log (table: CustomScripts.MaintenanceLog) from before date limit (Default 3 month limit, can be changed by declaring the stored procedure variable)

Spotfire User Maintenance

The TIBCO Spotfire User Maintenance custom script ensures that the users on the server are current and have accessed the server within a specified period of time. It is recommended to run this procedure on a schedule via SQL Server Agent.

The script goes through the following process:

  • Remove licenses for disabled users
  • Remove licenses for inactive users (default is 12 months of inactivity on the Spotfire Server, can be changed by declaring the stored procedure variable)
  • Remove users that have been deleted from the active directory

Spotfire Library Maintenance

The TIBCO Spotfire Library Maintenance custom script ensures that the Library Permissions are cleaned to remove duplicate permission structures and remove users that have been disabled. It is recommended to run this procedure on a schedule via SQL Server Agent.

The script goes through the following process:

  • Find and remove individual users that are part of domain groups or spotfire groups that already have the same permissions to the library folders
  • Remove the permissions for disabled users from library folders

SQL Server Agent Setup Script

This script finds and detects which Spotfire Server maintenance stored procedures you have installed on your Spotfire Server Database and adds them to a scheduled job to be run on the first day of each month at 12:00am (System Time).

Download

Download the latest powershell script on the following page: Spotfire Server Tools and Scripts

How to add or update the stored procedures

Log into the database server via remote desktop or directly.

Open "SQL Server Management Studio" and log into the Spotfire Server database

Go to "File" > "Open" > "File..."

Browse to and select the required sql file in the following order and click "Open":

  1. Spotfire_Server-CustomScripts-ServerMaintenance
  2. Spotfire_ActionLog-CustomScripts-ActionLogMaintenance (if you have the Spotfire Action Log Database and process set up)
  3. Spotfire_Server-CustomScripts-LibraryMaintenance
  4. Spotfire_Server-CustomScripts-UserMaintenance
  5. Spotfire_Server-SQLServerAgent-JobSetup


The script will open and display, double check that the spotfire server database line (shown below) is named correctly according to your own environment.

Click "Execute", this will proceed to create or update the stored procedure.

If desired, go through the process again, selecting your next desired script.

Action Log Maintenance Additional Task

If you are intending to use the action log stored procedure for maintenance, you will need to make changes to the Spotfire configuration to ensure that Spotfire itself is no longer maintaining the action log database.

Browse to the Spotfire Server file directory and run the "UIConfig" tool

Navigate to the "User Action Log" page and set "Pruning Period (hrs)" to 0 to disable Spotfire's own action log maintenance process.

Click "Save configuration"

You will also need to restart the Spotfire Server service

How to run the stored procedures

To run the stored procedures to perform the maintenance, browse to "Programmability" > "Stored Procedures" and right-click on the desired "CustomScripts" script, click "Execute Stored Procedure.,."

Confirm any additional parameters or settings if required (defaults will be used if none are selected) and click "OK"

The maintenance process will now execute.

Maintenance Log

The stored procedures also maintain a log of actions and changes, to view this log, browse to "Tables" > "CustomScripts.MaintenanceLog", right-click on the table and select "Select Top 1000 Rows" (this applies for both the Spotfire Server and Action Log databases)

From this point you can modify the select script how you like, it will generally appear like:

  • No labels