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.
Procedure
- To configure XA transactions for SQL Server, see Configuring XA Transactions.
Note that Windows Authentication is not
supported.
- 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
-
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
- Create the users and schemas for the SQL Server databases.
- 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
- 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
- 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
- Optional: To use your own JDBC driver, complete the
following steps.
What to do next
To protect the configuration data you're going to enter, see Creating secrets to protect sensitive configuration data.