Upgrading SQL Server databases

For SQL Server, initialize new databases and upgrade your existing schemas and data so that your databases work with the new version of IBM® Business Process Manager.

Figure 1. Sample environment after existing schemas and data are updated. The source environment is not running and the databases are not in use. The databases contain updated schemas and data. The target is not running but contains a deployment environment.

The details of the diagram are provided in the figure caption.

Run the DBUpgrade command to modify your existing database schemas and data for use with IBM Business Process Manager V8.5.7. The DBUpgrade utility updates the following items to V8.5.7:

System Data toolkit
Heritage Process Portal process application
Hiring Sample tutorial process application

Important: Although the DBUpgrade utility installs the System Data toolkit V8.5.7, the utility does not automatically update existing dependencies. Therefore, test your applications after migration. If they are not compatible with the new version of IBM BPM, you can update the dependencies so that your applications continue to use the previous version.

The DBUpgrade utility also converts encrypted passwords to work with the encryption algorithm used by IBM BPM V8.5.7. Encrypted passwords can be stored in the database for secure web service integrations users.

Note: Encrypted passwords can be stored in the database for Microsoft SharePoint integrations users.

Before you begin

Ensure that you have shut down the source environment before you proceed with the migration.

Verify that the users that are configured to access your SQL Server databases have the necessary privileges to upgrade the databases. The following database privileges are needed to modify existing SQL Server database schemas and data for use with IBM BPM V8.5.7.

The CONNECT and CREATETAB privileges are required on the database level.
The upgrade process accesses system tables. Grant the SELECT privilege to the user who performs the upgrade. These privileges are already granted to the public group by default; it is not necessary to grant them again unless they were revoked.
```
INFORMATION_SCHEMA.COLUMNS.TABLES
INFORMATION_SCHEMA.COLUMNS.COLUMNS
sys.indexes
sys.sysobjects
```
Do not grant the sysadmin server role to users. If a user has the sysadmin role, dbo will always be the default schema no matter what schema you assign. As a result, migration will use dbo to create new tables or update data.

For a list of supported database versions, refer to the system requirements.

Procedure

Based on your source environment and the location of the target environment, perform the following step:

Current® version of IBM BPM Standard or WebSphere® Lombardi Edition	File system where IBM BPM V8.5.7 is to be installed	Steps to take
WebSphere Lombardi Edition 7.2.0 that is previously upgraded from Teamworks® 7.0.0 or 7.0.1 to WebSphere Lombardi Edition 7.1.0 and then upgraded to WebSphere Lombardi Edition 7.2.0	Same file system as the existing WebSphere Lombardi Edition installation	In the `installation_root`/util/DBUpgrade directory, modify the `upgrade.properties` file to set the value of the `previous.lombardi.install.dir` property to be your source environment installation directory. Note: If this property is missing or not valid, the upgrade script is unable to migrate encrypted passwords, but can still perform other upgrade processing.
WebSphere Lombardi Edition 7.2.0 that is previously upgraded from Teamworks 7.0.0 or 7.0.1 to WebSphere Lombardi Edition 7.1.0 and then upgraded to WebSphere Lombardi Edition 7.2.0	Different file system from the existing WebSphere Lombardi Edition installation	If `[Lombardi_home_720]/AppServer/lib/ext/jcrypt.jar` is present in your installation, copy it to `installation_root/AppServer/lib/ext`

Copy the whole folder target_deployment_manager_profile\dbscripts\Upgrade\ to your database computer.
If you did not create a new messaging engine database and instead you plan to reuse your previous messaging engine database and schema, you must manually drop the existing messaging engine tables for each one.
Tip: The messaging engine table names use the SIB prefix.
On the database computer, upgrade all schemas. To see which schemas are upgraded, go to the directory where you copied the Upgrade folder and see the upgradeSchemaScriptsHelp_de_name.txt file.
Go to the directory where you copied the Upgrade folder and run the upgradeSchemaAll command. There is a different upgradeSchemaAll command for each deployment environment in the source.

Important: If you are using Windows Authentication, you cannot run upgradeSchemaAll and must run the SQL scripts directly using an SQL session.
```
upgradeSchemaAll_de_name.bat
```
You are prompted to enter the user name and password for each database connection. This command initializes the new database components and upgrades the schemas of all the existing databases except for the Process Server and Performance Data Warehouse databases. Those two databases are upgraded later by the DBUpgrade command.
Alternatively, if you want to run the SQL scripts manually, use an SQL session and run the scripts in the sequence listed in the Upgrade_folder\upgradeSchemaScriptsHelp_de_name.txt file and use the following parameters and commands.
```
osql -e -b -U username -P password -i script_name -o log name
```
where:
- -e specifies that the command is to be echoed on prompt
- -b specifies that the script is to exit when errors occur
- -U specifies the user name
- -P specifies the password
- -i specifies the input file
- -o specifies that all output is to be redirected to a file
The result.log files are found in Upgrade_folder\cell_name or cell_name.de_name\database_type\database_name.schema_name.
Copy the sample migration.properties file and rename it to target_migration.properties. Update the file with the configuration information for the target environment. Check all the target properties and edit them if required, following the instructions in the sample file. The sample file is in install_root\util\migration\resources\migration.properties.
Specify target environment values for the following properties:
- admin.username and admin.password: Use the cellAdmin user or the WebSphere primary administrative user name.
- bpm.home: The installation root of your target product. Make sure that the file separators are forward slashes (/). Use the full path. Do not use the tilde character (˜) to stand for the home directory. For example:
```
bpm.home=/opt/IBM/BPM85
bpm.home=C:/IBM/BPM
```
- profile.name: The target deployment manager profile.
- target.config.property.file: The full path of the configuration properties file that you used to create your target environment.
You must specify the source cluster names, not target cluster names, for the following properties:
- source.application.cluster.name
- source.messaging.cluster.name
- source.support.cluster.name
- source.web.cluster.name
To upgrade the databases to V8.5.7, run the DBUpgrade utility on the deployment manager computer in the target environment. The DBUpgrade command automatically upgrades the schema and data for Process Server and Performance Data Warehouse.
Tip: By default, DBUpgrade upgrades both the schema and data for Process Server and Performance Data Warehouse databases. For instructions for running the schema update separately, see the DBUpgrade troubleshooting topic.

Important: Ensure that your deployment manager and all the managed nodes in the source environment have been stopped before running this utility.
```
install_root\bin\DBUpgrade.bat -propertiesFile target_migration_properties_file
```
where:
- target_migration_properties_file is the full path to the migration properties file in which you specified the configuration information for the target environment.
For example:
```
install_root\bin\DBUpgrade.bat -propertiesFile "C:\bpm 85\util\migration\resources\target_migration.properties"
```
The command displays each database upgrade action as it runs. After all the upgrades are finished, you see a message similar to the following message:
```
All upgrade steps have been completed successfully.
```
The log location is listed in the output. If there are errors or exceptions, they appear in the log.
If you get an out-of-memory error indicating too many or too large data records, you can try to increase the heap size of the JVM for the DBUpgrade command. Open the DBUpgrade.bat file in install_root\bin and find -Xmx2048m in this file. It indicates that the maximum JVM heap size is 2048 megabytes. You can increase this value to update the heap size.
V8.5.7 no longer supports the Lombardi database user registry. Users that were maintained by the Lombardi database user registry are moved to the WebSphere Application Server file-based user registry. After migration, you can maintain these users in the WebSphere Application Server administrative console.

The utility updates existing schemas and migrates data. If you previously upgraded to WebSphere Lombardi Edition 7.2.0 from Teamworks 7.0.0 or 7.0.1, the script also converts encrypted passwords to work with IBM BPM V8.5.7.

For troubleshooting, the DBUpgrade command has new parameters in V8.5.7 CF2016.09. If necessary, you can run DBUpgrade -generateSQLOnly to generate the SQL files without running them. You can then edit the generated SQL files manually and run DBUpgrade -omitSQLGeneration to upgrade the database using the modified files.

What to do next

You might see warning messages similar to the following in the upgrade log: Couldn't load Resource META-INF*****. These messages can safely be ignored.