Upgrading SQL Server databases

For SQL Server, 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 stand-alone environment.

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

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

Note: Although the DBUpgrade utility updates the System Data toolkit to IBM Business Process Manager V8.5.6, it does not automatically update existing dependencies. The dependencies must be updated after migration.

For each deployment environment that you are creating, complete the following steps:

1. Copy the whole folder target_deployment_manager_profile\dbscripts\Upgrade\ to your database computer.
2. 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.
3. 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

   You might see warning messages when you run the scripts to upgrade the Business Space database telling you that the result of a query is an empty table or that no row was found for FETCH, UPDATE, or DELETE. These messages can safely be ignored.

   The result.log files are found in Upgrade_folder\cell_name or cell_name.de_name\database_type\database_name.schema_name.

4. 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.

   Ensure that all properties have been changed to the target (not source) environment. Ensure that the value of the target.config.property.file property is set to the full path of the configuration properties file that you used to create your target environment. You must also set the value of profile.name to the name of the new stand-alone profile.

5. If you are using SQL Server with Windows authentication enabled, copy the sqljdbc_auth.dll file from WAS_home\jdbcdrivers\SQLServer\auth\platform to WAS_home\java\jre\bin before you run the DBUpgrade utility.
6. To upgrade the databases to V8.5.6, run the DBUpgrade utility on the server 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 command-line utility reference 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.