How to migrate TIBCO Spotfire Server application database from Oracle to Microsoft SQL Server and vice versa

Products	Versions
Spotfire Server	11.3 and lower

Description

This article describes how to migrate the TIBCO Spotfire Server application database from Oracle to Microsoft SQL Server or vice versa. There is no automated method for this, so this is a very manual process that requires multiple manual steps. Additionally, there are some important caveats that must be considered:

Important:

Any external user accounts (ref: External user accounts) will be given new GUIDs when initially synced in the new database which means all library permissions will be lost and will need to be set again. Library content which is owned by user who doesn't exist now with the same GUID will be updated with TIBCO Spotfire Administrator as owner.
All Nodes will need to be manually added again after migration

This technique is for versions 11.3 and lower. Starting with version 11.4.0 of Spotfire Server, you can use command-line commands to migrate a Spotfire database instance or an action database instance from one to another. See Migrating a database instance for more details.

Issue/Introduction

How to migrate TIBCO Spotfire Server application database from Oracle to Microsoft SQL Server and vice versa

Environment

All

Resolution

There are some differences in data table names and column names between the TIBCO Spotfire Server application's Oracle database and Microsoft SQL database versions. Because of that in order to move the database to a different server type, the new database should be created and then the Library Export/Import functionality should be used to move the content to the destination database.

To perform, please follow these steps:

Create the new TIBCO Spotfire Server database on the new data base server:
- Oracle: Reference: Setting up the Spotfire database (Oracle)
- Microsoft SQL Server: Reference: Setting up the Spotfire database (SQL Server)
Export the library content from the original database. Reference: export-library-content
Export the server configuration.xml file from the original database before creating the new bootstrap file. Reference: export-config
If using database user directory (ref: Local user accounts), then export the users and groups from the original environment. Reference: export-users and export-groups
Export distribution from original database. Reference: export distribution
Export rules and schedules from the original database. Reference: export-rules
Create a new bootstrap file with the new database details. Reference: Creating the bootstrap.xml file
If using database user directory, import the users and groups exported in Step 4. Reference: import-users and import-groups
To replicate the same configuration, import the configuration file which was exported in Step 3. Reference: import-config
Import the library content which is exported in Step 2. Reference: import-library-content
Import rules and schedules which is exported in step 6. Reference: import-rules
Start TIBCO Spotfire Server.
Add distribution to new database which is exported in step 5. Reference: Add package to deployment area