Note: This is not a mandatory process
Introduction
Intuitive V6.0 introduces the option to install Intuitive dashboards under SQLite rather than SQL Server.
For fresh implementations, it is just a matter of selecting the preferred database at install time.
For upgrades, where an existing implementation uses SQL Server most sites will continue to use SQL Server as their underlying dashboard database when they upgrade to V6.0.
Some sites may choose to migrate their installation from SQL Server to SQLite. This process uses the intuitive ETL Service to read the existing data from the existing SQL Server dashboard database and write the data to the SQLite dashboard database.
This article details the migration process that needs to be followed to achieve this.
Note: This work should only be undertaken by your intuitive dashboard server system administrator. The process is not destructive, so your original SQL Server dashboard database will not be removed or altered in any way, but you are still advised to take system backups prior to performing this process.
Migration process:
- Firstly, take a backup of your existing intuitive SQL Server implementation.
- Now upgrade your existing SQL Server installation to V6.0. in the standard way (Note: do not choose the SQLite installation option).
- Now run the V6.0 installation again, but this time select SQLite as the installation database, which will create a 'clean' SQLite database. Call the SQLite database ‘dashboard.db’ and ensure that it’s located in the intuitive dashboards folder (normally C:\Program Files\Intuitive Business Intelligence\Intuitive Dashboards). Note: If another location/name is chosen then the ETL configuration file provided (See below: ETLService.Migrate.config) will need to be edited to reflect this.
- Setup a System ODBC connection to the existing SQL Server dashboard database called ‘dashboard_odbc’. This will enable the ETL process to read the existing SQL Server tables.
- The V6.0 installation includes an ETL configuration file 'ETLService.Migrate.config' found in the ETL service folder (Normally C:\Program Files\Intuitive Business Intelligence\Intuitive Dashboards\Tools\ETL Service)
This contains the full list of tables to 'copy' from the SQL Server database to the target SQLite database. - Make a backup copy of the standard 'ETLService.config' file calling it 'ETLService - Copy.config' so you can restore this file later if required.
- Now copy/rename the ETLService.Migrate.config to ETLService.config
- Now run the ETL Service executable to perform the data migration.
To do this, in the 'C:\Program Files\Intuitive Business Intelligence\Intuitive Dashboards\Tools\ETL Service' folder right click on ‘ETLService.Console.exe’ and select run as Administrator. - This will now perform the migration of the data from SQL Server to SQLite. You should observe similar in the ETL Service console window:
Note: A logfile of this process 'scheduler.YYYYMMDD.txt' will be created in the 'C:\Intuitive_WorkFiles\Logfiles' folder. - Now restart the Intuitive Dashboard Service
- The intuitive dashboard system is now running under SQLite and you can now login to the dashboard system as normal. Note: You may need to clear the browser cache (Ctrl-F5) at this point so the system browser picks up the new client.
- If for any reason the migration needs to be performed again, to enable this you will need to delete the file '.dashboard-import-done' from the initialised folder: 'C:\Program Files\Intuitive Business Intelligence\Intuitive Dashboards\Tools\ETL Service\initialised\'
- If required, now restore your original 'ETLService.config' file which was backed-up in step 6.