Creating and configuring a Microsoft SQL Server database for recording data

To record data to a Microsoft SQL Server database, create the database, and configure an ODBC definition to it. Configure your integration server so that it can connect to the database.

Before you begin

Read the following topics:

Note: Choosing V2 or legacy schema for the DataCaptureStore tables.

Most users of IBM® App Connect Enterprise must use the schemas that are defined in DataCaptureSchema_V2.sql file.

It contains up-to-date schemas that use the integration server hostname and integration node hostname to distinguish records inserted on behalf of other processes. In IBM Integration Bus 10.0 and earlier, integration nodes and integration servers use universally unique identifiers (UUIDs) to identify other processes and the DataCaptureSchema.sql file contains table schemas that include these elements for compatibility reasons.

Since IBM App Connect Enterprise does not use UUIDs, records originating from IBM App Connect Enterprise contain null values for these fields.

About this task

The following steps describe how to create the SQL Server database, create an ODBC definition, and set a user ID and password for the database.

You can use Integrated Windows Authentication to specify a Windows user account to be used for authentication when using Microsoft SQL Server to record and replay data. For more information, see Using Integrated Windows Authentication when recording data with a Microsoft SQL Server database.

Procedure

Use the script that is provided with IBM App Connect Enterprise to create and configure an SQL Server database to store your recorded data. Note that the script creates some tables that are not currently used, and are reserved for future use, such as WMB_EVENT_FIELDS and WMB_EVENT_TYPES.
1. Locate the script at install_dir\server\ddl\sqlServer\DataCaptureSchema_V2.sql, where install_dir is the location of your IBM App Connect Enterprise installation.
2. Optional: Customize the provided DataCaptureSchema script.
  
  If you modify the SQL to specify a particular schema, you must also set the same schema name in the server.conf.yaml file. See Configuring data recording.
3. To run the script, at a command line, navigate to the script location and enter the following command:
```
sqlcmd -d databaseName -i DataCaptureSchema_V2.sql
```
Create an ODBC definition for the database.

If you used the supplied script to create your database without modifications, create an ODBC definition for the database that is called MBRECORD, with MBRECORD as the data source name (DSN). For more information, see Enabling ODBC connections to the databases.
Use the mqsicredentials or ibmint command to set a user identifier and password for the integration server for connecting to the database; for example:
```
mqsicredentials --create --work-dir workDir --vault-key vaultPassword --credential-type odbc --credential-name dataSourceName --username userID --password password
```
```
ibmint set credential --work-dir workDir --vault-key vaultPassword --credential-type odbc --credential-name dataSourceName --username userID --password password
```
- workDir is the name of the work directory for your integration server.
- vaultPassword is the vault key that is used to access the vault where the credential is stored.
- dataSourceName identifies the database to which you want to record data.
- userID and password specify the user identifier and the password that the integration server uses to connect to the database.
If Integrated Windows Authentication is being used for SQL Server database access, then the service user ID under which the process runs is used by Windows to access the SQL Server database. That is, it ignores any user ID and password credentials that are set by using the mqsicredentials or ibmint command.
To ensure that the changes to the credentials take effect, restart the integration server.
For more information, see Starting an integration server.
Test the connection to your database by using the mqsicvp command.
For more information, see mqsicvp command.

What to do next

Follow the steps for recording data. See Recording data.