Neuron ESB Database Changes

Neuron ESB 3.5 Workflow requires the configuration of the Neuron ESB Database. The Neuron ESB Database can be easily created using the Neuron ESB Explorer. However, we made several changes to support Workflow, the need to clean up tracking information and allow users to regularly purge and archive Neuron ESB Audit and Workflow Tracking data.

Database Schema Changes

In the Neuron ESB 3.5 release, we changed how we manage and update our database schemas. In the past, we released a single database script that would create the entire database, and with each release of Neuron ESB, we would release updates if necessary. For users that were several versions behind, it became confusing on how to upgrade your database from one version to the next.

With Neuron ESB 3.5, we have introduced a new database schema management scheme that will make it easier to create new databases and upgrade existing databases to stay in sync with Neuron releases. The new database scheme will also make it easier for the product team to release bug fixes and new features with future releases.

The new database scheme uses a series of database scripts based on a single version number. Each script is named using the following form:

<4-digit-version-number>_<description-of-change>.sql

With the Neuron ESB 3.5 release, we have walked back through our database history from prior releases and produced the following database scripts:

• 0001_CreateNeuronDB.sql
• 0002_UpdateTo2_6.sql
• 0003_UpdateTo3_0.sql
• 0004_UpdateTo3_1.sql
• 0005_UpdateTo3_2.sql
• 0006_UpdateTo3_3.sql
• 0007_UpdateTo3_1_0.sql
• 0008_UpdateTo3_1_405.sql
• 0009_UpdateTo3_5_0.sql

The current database version number for the Neuron ESB 3.5 release is version number 9. In the new database scheme, the first database script will create the basic Neuron ESB database corresponding to the Neuron ESB 2.5 release, and each additional script will apply the incremental changes that were made to the database with each Neuron software release. Moving forward with future releases, we will add new features and fix bugs to the database by releasing new incremental update scripts to the database, leaving the existing scripts in a stable, frozen state.

The advantages to this new version numbering scheme are:

• Less confusion on how to create or update a database. Users simply run the scripts in numerical order.
• We can make it easier to automatically upgrade your database for users.

Please note: Upgrading an existing database has risks including data loss of existing data. Only upgrade an existing database after backing up your existing data. This is important should any data loss or errors occur during the upgrade so that you can restore your database to a known operational state.

Upgrading an Existing Database

The upgrade process for databases is now much easier, and is also automated. There are two ways to create or upgrade a Neuron ESB database:

• Using Neuron ESB Explorer
• Using a new PowerShell script

Using Neuron ESB Explorer

The process for creating or updating a database is the same in Neuron Explorer for the 3.5 release as it has been in the past. However, there is a new feature that will allow the Neuron ESB Explorer to upgrade an existing database to the latest version.

If you configure a database that is incompatible with the installed version of Neuron ESB, the Neuron ESB Explorer will now prompt you to upgrade your database. If you choose to upgrade your database, the Neuron ESB Explorer will determine the current database version and will apply the incremental updates that are necessary to upgrade your database to the latest version.

Using PowerShell

The PowerShell script uses the sqlps PowerShell module installed by Microsoft SQL Server. This script will not work if the sqlps PowerShell module is not installed on the server.

You do not need to import sqlps prior to running the PowerShell script. The PowerShell script will detect if it is available, and if not, will import the sqlps module automatically.

The Neuron ESB 3.5 release includes a new PowerShell script that you can use to create or delete an existing Neuron ESB database. The PowerShell script will not create the actual database. The database must exist before running the PowerShell script. But the PowerShell script will create the database structures needed by Neuron ESB in an empty database. Given an empty database or an existing Neuron ESB database, follow these steps to create or update your database:

• Open a PowerShell console.
• Use the Set-Location cmdlet to change the directory to the installation directory for Neuron ESB.

> Set-Location “C:\Program Files\Neudesic\Neuron ESB v3\”

• Execute the PowerShell script, providing the correct values for the parameters (line breaks are for illustrative purposes and should not be entered into the PowerShell console):

> .\PowerShell\UpdateNeuronESBDatabase.ps1
–neuronEsbInstallPath “C:\Program Files\Neudesic\Neuron ESB v3”
-serverInstance “<server-name>”
-database “Test”

The serverInstance parameter can be a dot (“.”) for the local machine, or can be a machine name. If you have installed a named instance of SQL Server, you would specify that as well. For example, to install the database objects in a SQL Express instance, you would use .\SQLEXPRESS for the value of the serverInstance parameter.

The UpdateNeuronESBDatabase.ps1 PowerShell script will examine the database to determine whether the database is empty or contains an existing Neuron ESB database. If the database is an existing database, the UpdateNeuronESBDatabase.ps1 script will determine the version number for the database and will apply the update scripts necessary to bring your database up to date with the currently installed release of Neuron ESB.

Workflow Tracking Cleanup Job

Starting with the Neuron ESB 3.5 release, the Neuron ESB database needs a SQL Server Agent job to purge workflow tracking records marked for deletion. When creating a database through Neuron Explorer, an attempt will be made to create and schedule the job. If the user has insufficient database permissions for the creation of the job to succeed, then you will need to modify and run the script manually.

The job creation script is called CreateJob_PurgeWorkflowTracking.sql and can be found in the Sql folder under the default Neuron ESB installation folder (ex: C:\Program Files\Neudesic\Neuron ESB v3\Sql). Open the script file and replace the ${DatabaseName} placeholder. By default the script enables the job, sets the job’s owner as “sa” and schedules the job to execute every 10 minutes. Modify these values as needed then execute the script. You will see a new job in SQL Server Management Studio. Make sure to start the SQL Server Agent service. NOTE: If you are saving your script changes specify a different filename so that Neuron Explorer will have access to the unchanged CreateJob_PurgeWorkflowTracking.sql file.

Archive and Purge Neuron DB Job

Neuron ESB 3.5 introduces the ability to automate the purging and archiving of the Neuron ESB database. For example, when Neuron ESB Auditing is used, over time the auditing tables will grow, consuming more disk space. Additionally, when using Workflow, Workflow tracking will produce increasing amounts of data in their respective tables. Overtime, there will be a need to delete older data to maintain performance. Perhaps as well, based on an organization’s data retention policy, there will be a need to archive older data to an offline database or store.

There is a now a SQL Server Agent job, CreateJob_PurgeArchiveNeuron.sql, located in the Neuron ESB Sql folder under the default Neuron ESB installation folder (ex: “C:\Program Files\Neudesic\Neuron ESB v3\Sql\ “).

Open the script file and replace the following parameters with the values according to your organization’s data purge and archive policies:

• @DatabaseName – The Neuron ESB database which you want to purge and backup
• @LoginName – The username which has ability to run the Job
• @FolderName – The folder where you want to store Neuron ESB database backup. The backup database name will be in the format of
  “SERVERNAME_DATABASENAME_Mmm_dd_yyyy_hh_mm.bak”
• @NumberOfDays – This variable is set at a default of 7. when you run your job with this default setting, any data that is older than 7 days will be moved to the backup folder (for archiving) and then purged from the current Neuron ESB database

Once the modifications are done, the script can be executed in the SQL Server Management Studio to create the SQL Server Agent Job. Once created, users can schedule the job to run during off hours.