Automate - Convert SQLite to MSSQL

Owned by Ryan Best (Unlicensed)
Aug 27, 2021
3 min read

 

The material in this document is for informational purposes only. This guide assumes that the most recent version of Rampiva Automate is in use unless otherwise noted in the prerequisites. The products it describes are subject to change without prior notice, due to the manufacturer’s continuous development program. Rampiva makes no representations or warranties with respect to this document or with respect to the products described herein. Rampiva shall not be liable for any damages, losses, costs or expenses, direct, indirect or incidental, consequential or special, arising out of, or related to the use of this material or the products described herein.

Introduction

Rampiva Automate allows you to redirect the existing SQLite database into a Microsoft SQL Server database. This can provide a number of advantages relating to reporting. Numerous reporting systems such as PowerBi or Tableau can access the MSSQL tables to create rich dashboards and reports from the Automate environment.

Prerequisites 

  • MSSQL Administrative Privileges and Logon credentials

  • MSSQL Server IP/Machine Name

  • DB Browser for SQLite

  • SQL Server Management Studio

Instructions

A. Migration Preparation - MSSQL

1

In Microsoft SQL Server, create two new databases. Rampiva_Master and Rampiva_UR.

Each database should be set to unlimited growth.

When possible, assigning the database owner as the Rampiva Network Service account is preferable.

 

B. DB Browser for SQLite

1

Using the Services control panel, stop the Rampiva Scheduler and Rampiva Engine Services.

2

Copy the database files from their default location, C:\ProgramData\Rampiva\Scheduler for Nuix\Scheduler\stores\, to a location of your choice.

3

Launch DB Browser for SQLite and open each of the .DB files from the File menu.

Ensure that you are working with the copied database files and not the original versions.

 

4

Select File > Export > Database to SQL File.

 

5

Check the box to Keep column names in INSERT INTO.

 

6

Select Export Data Only from the available dropdown menu.

 

7

Select the location to save the SQL query. Repeat for each database in the stores folder.

 

C. Configure Scheduler

1

Open the Rampiva Scheduler Configuration file. By default, the file is stored here: C:\ProgramData\Rampiva\Scheduler for Nuix\Scheduler\config\config.yml.

 

2

The settings that control Microsoft SQL redirection require you to un-comment and provide specific MSSQL server connection information.

Populate the MSSQL connection information for each Rampiva Scheduler data store:

  • auditStore

  • clientMatterStore

  • configurationStore

  • jobsStore

  • jobsArchiveStore

  • libraryStore

  • reportingStore

  • securityStore

  • userSettingsStore

  • utilizationStore

3

After the migration is complete, start the Rampiva Scheduler Service.

D. Migration

1

Stop the Rampiva Scheduler Service.

2

Launch Microsoft SQL Server Management Studio.

3

Open and execute the saved SQL queries from section B. DB Browser for SQLite steps. The SQL queries import records into both SQL databases. Execute the SQL queries for each table based on the values below.

Rampiva_Master.mdf:

  • audit.sql

  • clientMatter.sql

  • configuration.sql

  • jobs.sql

  • jobs_archive.sql

  • security.sql

  • userSettins.sql

Rampiva_UR.mdf:

  • reportline.sql

  • utilization.sql

4

Start the Rampiva Scheduler Service.

5

Monitor the service to ensure it runs. If configuration errors are detected, the service will not run.