Creating SQL Server databases

Create Microsoft SQL Server databases before installing IBM Business Automation Studio.

About this task

You must create a database for Application Engine playback server as well as for IBM Business Automation Studio before you can run Business Automation Studio.

Tip: If you need to know which database versions are supported for your installation, use this IBM® Support page to generate a software compatibility report.

1. To configure XA transactions for SQL Server, see Configuring XA Transactions .
   Note that Windows Authentication is not supported.
2. To create the Application Engine playback server database, run the following command on your local or remote database server:

   CREATE DATABASE APP_ENGINE_DB_NAME;

   where APP_ENGINE_DB_NAME is the database name for the Application Engine playback server database, for example, APPDB
3. To create the database for Business Automation Studio, run the following commands:

   CREATE DATABASE STUDIO_DB_NAME COLLATE SQL_Latin1_General_CP1_CI_AS;
   ALTER DATABASE STUDIO_DB_NAME SET READ_COMMITTED_SNAPSHOT ON;

   where STUDIO_DB_NAME is the database name for the Business Automation Studio database, for example BASDB
4. Create the users and schemas for the SQL Server databases.
   1. If you haven't already done it, create an SQL Server login account for the database user of your database.
      Note: The SQL Server login that is created must not have SYSADMIN privileges. If the login user has SYSADMIN privileges, the specified schema value is ignored for database connections by SQL Server because the sysadmin user's default schema is always dbo.
      For example, if login_account is the login user and secret1 is the password, run the following command:

      USE MASTER
      GO
      CREATE LOGIN login_account WITH PASSWORD='secret1'
      GO
      

   2. If you haven't already done it, update the master database to grant permission for XA transactions for the login account.
      For example, if sqluser is the database username, run the following command to update the master database (where login_account is the same login account that you used in the previous step):

      USE MASTER
      GO
      CREATE USER sqluser FOR LOGIN login_account WITH DEFAULT_SCHEMA=sqluser
      GO
      EXEC sp_addrolemember N'SqlJDBCXAUser', N'sqluser';
      GO

   3. For the database that you created, set the default schema for the SQL Server login and assign the required rights to the user for Business Automation Studio and Application Engine playback server.
      For example, if studio_user is the database username, run the following command:

      USE STUDIO_DB_NAME
      GO
      CREATE USER studio_user FOR LOGIN login_account WITH DEFAULT_SCHEMA=studio_user
      GO
      CREATE SCHEMA studio_user AUTHORIZATION studio_user
      GO
      EXEC sp_addrolemember 'db_ddladmin', 'studio_user';
      EXEC sp_addrolemember 'db_datareader', 'studio_user';
      EXEC sp_addrolemember 'db_datawriter', 'studio_user';
      GO
      

      If app_engine_user is the database username, run the following command:

      USE APP_ENGINE_DB_NAME
      GO
      CREATE USER app_engine_user FOR LOGIN login_account WITH DEFAULT_SCHEMA=app_engine_user
      GO
      EXEC sp_addrolemember 'db_ddladmin', 'app_engine_user';
      EXEC sp_addrolemember 'db_datareader', 'app_engine_user';
      EXEC sp_addrolemember 'db_datawriter', 'app_engine_user';
      GO

5. Optional: To use your own JDBC driver, complete the following steps.
   1. Package your JDBC files into a compressed file and use the sc_drivers_url configuration parameter to download them from an accessible web server. Follow the steps in Optional: Preparing customized versions of JDBC drivers and ICCSAP libraries.
   2. Add the customized JDBC driver information to the configuration parameters.
      • Set bastudio_configuration.database.use_custom_jdbc_drivers to true and insert your own JDBC driver files into bastudio_configuration.database.jdbc_driver_files in the configuration parameters. See Business Automation Studio configuration parameters.
      • Set application_engine_configuration.use_custom_jdbc_drivers to true in the configuration parameters. See Application Engine configuration parameters.
        Note: For the Application Engine database, you can use your own JDBC driver only to initialize the database. The JDBC driver is not used by Application Engine at runtime. Application Engine uses a Node.js library to connect to the database, and this library is not customizable.