Upgrading the Neuron Database

Database Schema Enhancements in Neuron ESB 3.6

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 us 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.6 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
• 0010_UpdateTo3_5_0_34.sql
• 0011_UpdateTo3_5_2.sql
• 0012_UpdateTo3_5_3.sql
• 0013_UpdateTo3_5_4.sql
• 0014_UpdateTo3_5_4.sql
• 0015_UpdateTo3_5_4.sql
• 0016_UpdateTo3_5_4.sql
• 0017_UpdateTo3_5_4.sql
• 0018_UpdateTo3_5_4.sql
• 0019_UpdateTo3_5_4.sql
• 0020_UpdateTo3_5_4.sql
• 0021_UpdateTo3_5_4.sql
• 0022_UpdateTo3_5_4.sql
• 0023_UpdateTo3_5_4.sql
• 0024_UpdateTo3_5_4.sql
• 0025_UpdateTo3_6.sql

The current database version number for the Neuron ESB 3.6 release is version number 25. 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:

1. Less confusion on how to create or update a database. You simply run the scripts in numerical order.
2. We can make it easier to automatically upgrade your database for you.

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 Explorer, or using a new PowerShell script.

Create or Update a Database Using Neuron Explorer

The process for creating or updating a database is the same in Neuron Explorer for the 3.6 release as it has been in the past. However, there is a new feature that will allow Neuron 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, Neuron Explorer will now prompt you to upgrade your database. If you choose to upgrade your database, Neuron Explorer will determine the current database version and will apply the incremental updates that are necessary to upgrade your database to the latest version.

Create or Update a Database Using PowerShell

The Neuron ESB 3.6 release includes a 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.
• 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
  -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.