Preparing the tenant database

Before you can use the application, you must create a tenant database to store information required by the application. After you have created the database and tablespace, continue to the next step to run the scripts that install the initial seed data before you can use the application. The initial data seed includes the initial service categories and service type templates.

To create the tenant database, follow the specific instructions for the database you are using for the application platform.

These steps are also included in the README.txt file packaged with the installation files.

Oracle database preparation

Use a database client application to run the following SQL commands.

Edit the commands in a text editor to replace the <VARIABLE_NAME> placeholders by the parameter value assigned in the options.txt file used by the installer.

Create the application administrator role for managing the tenant tablespace

SQL statements
ALTER USER <BMC_DATABASE_LOGIN> IDENTIFIED BY "<BMC_DATABASE_PASSWORD>";
CREATE ROLE <BMC_DATABASE_LOGIN>ROLE;
GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE VIEW, QUERY REWRITE TO <BMC_DATABASE_LOGIN>ROLE;
GRANT <BMC_DATABASE_LOGIN>ROLE TO <BMC_DATABASE_LOGIN>;
COMMIT;
Example
ALTER USER ARADMIN_QA IDENTIFIED BY "SecretPassword1";
CREATE ROLE ARADMIN_QAROLE;
GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE VIEW, QUERY REWRITE TO ARADMIN_QAROLE;
GRANT ARADMIN_QAROLE TO ARADMIN_QA;

Create the tenant tablespace and assign the newly-created role as its administrator

SQL statements
CREATE TABLESPACE <BMC_AR_TENANT_TABLESPACE_NAME> DATAFILE '<path/to/file/<BMC_AR_TENANT_TABLESPACE_NAME>.DBF' SIZE 500M REUSE AUTOEXTEND ON;
CREATE USER <BMC_AR_TENANT_TABLESPACE_NAME> IDENTIFIED BY <create_a_password> DEFAULT TABLESPACE <BMC_AR_TENANT_TABLESPACE_NAME> TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON <BMC_AR_TENANT_TABLESPACE_NAME>;
ALTER USER <BMC_DATABASE_LOGIN> QUOTA UNLIMITED ON <BMC_AR_TENANT_TABLESPACE_NAME>;
GRANT <BMC_DATABASE_LOGIN>ROLE TO <BMC_AR_TENANT_TABLESPACE_NAME>;
COMMIT;
Example
CREATE TABLESPACE ARTENANTCALBRO DATAFILE '/usr/oracle/databases/ora112db/ARTENANTCALBRO.DBF' SIZE 500M REUSE AUTOEXTEND ON;
CREATE USER ARTENANTCALBRO IDENTIFIED BY SecretPassword2 DEFAULT TABLESPACE ARTENANTCALBRO TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON ARTENANTCALBRO;
ALTER USER ARADMIN_QA QUOTA UNLIMITED ON ARTENANTCALBRO;
GRANT ARADMIN_QAROLE TO ARTENANTCALBRO;
COMMIT;

MS SQL Server database preparation

Use SQL Server Management Studio to manage the database objects.

Replace the <VARIABLE_NAME> placeholders by the parameter value assigned in the options.txt file used by the installer.

Create the application administrator role for managing the tenant tablespace

  1. Under the database connection, expand the Security section.
  2. Expand Logins.
  3. If the <BMC_DATABASE_LOGIN> does not already exist, right-click Logins and select New Login.
    Otherwise, right-click <BMC_DATABASE_LOGIN> and select Properties.
  4. Enter or verify the following:
    Login name: <BMC_DATABASE_LOGIN>
    SQL Server authentication password: <BMC_DATABASE_PASSWORD>
  5. Select User Mapping.
  6. Ensure the user is mapped to the tenant database with the role of db_owner.
  7. Click OK.

Create the tenant tablespace and assign the newly-created role as its administrator

  1. From the menu bar, click New Query.
  2. Use the following command template for creating a tenant:

    SQL statements
    USE [<System Database Name>]
    GO
    CREATE USER [<System Database User>] FOR LOGIN [<System Database User>]
    GO
    USE [<System Database Name>]
    GO
    ALTER ROLE [db_owner] ADD MEMBER [<System Database User>]
    GO
    
    CREATE DATABASE <Tenant DB Name>
    ALTER DATABASE <Tenant DB Name> SET ALLOW_SNAPSHOT_ISOLATION ON
    ALTER DATABASE <Tenant DB Name> SET READ_COMMITTED_SNAPSHOT ON
    ALTER AUTHORIZATION ON DATABASE::<Tenant DB Name> TO <System Database User>
    Example
    USE [ARSystem]
    GO
    CREATE USER [ARAdmin] FOR LOGIN [ARAdmin]
    GO
    USE [ARSystem]
    GO
    ALTER ROLE [db_owner] ADD MEMBER [ARAdmin]
    GO
    
    CREATE DATABASE ARTenantCalbro
    ALTER DATABASE ARTenantCalbro SET ALLOW_SNAPSHOT_ISOLATION ON
    ALTER DATABASE ARTenantCalbro SET READ_COMMITTED_SNAPSHOT ON
    ALTER AUTHORIZATION ON DATABASE::ARTenantCalbro TO ARAdmin

Where to go from here

Seeding the initial tenant data

Was this page helpful? Yes No Submitting... Thank you

Comments

  1. Adrian Grancea

    Could you please confirm the step: "Create the application administrator role for managing the tenant tablespace" for Oracle DB, from installation experience this is happening during actual installation of SB master server and same db user is created, no need for new user.

    May 05, 2017 06:46
    1. Tony Chung

      Hi Adrian.

      These instructions were provided for users who wanted to create the databases in advance. In the 3.2 release, the installer set up the application and tenant databases if you supplied the sys user.

      This process changed as of the 3.3 release. For your installation, you should be looking at Creating Oracle tablespaces in the MyIT Service Broker 3.3 space.

      Thanks,

      -Tony

       

      May 05, 2017 11:47
  2. Daniel Soto

    Tony Chung 

     

    Hey Tony, can you please add the below steps for MSSQL Server Databases?

    https://communities.bmc.com/docs/DOC-61604


    Those are for pre-created ARServer (Service Broker ARServer, of course)



    use tempdb

    CREATE DATABASE "ARSystem_SB" ON (NAME = "ARSystem_SB_data", FILENAME = "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ARSys_SB.mdf", SIZE = 500MB) LOG ON (NAME = "ARSystem_SBpt_log", FILENAME = "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ARSysLog_SB.ldf", SIZE = 100MB)

    CREATE LOGIN "ARAdmin_SB" WITH PASSWORD = 'password', DEFAULT_DATABASE = ARSystem_SB
     
    use ARSystem_SB

    CREATE USER "ARAdmin_SB" FOR LOGIN "ARAdmin_SB"
    exec sp_addrolemember 'db_owner', 'ARAdmin_SB'
     
    alter database ARSystem_SB set recovery simple;
    alter database ARSystem_SB set single_user with Rollback immediate;
    alter database ARSystem_SB set multi_user;
    ALTER DATABASE ARSystem_SB SET PARAMETERIZATION SIMPLE
    ALTER DATABASE ARSystem_SB SET READ_COMMITTED_SNAPSHOT ON

    SELECT is_read_committed_snapshot_on FROM sys.databases where name =  'ARSystem_SB'

    create synonym trace_xe_action_map for sys.trace_xe_action_map;
    create synonym trace_xe_event_map for sys.trace_xe_event_map;



    I think they will be very helpful and will avoid getting 


    SEVERE,com.bmc.install.product.arsuitekit.platforms.arsystemservers.task.ARServerUpdateConfigurationEntry,
    THROWABLE EVENT {Description=[Failed to merge changes]},Throwable=[java.lang.NullPointerExceptioncom.bmc.smbu.install.common.rule.engine.ri 


    -Article Number: 000132470

    Oct 27, 2017 11:38
  3. Tony Chung

    You can refer to the same steps in the latest documentation for the BMC Digital Workplace Catalog 3.4 release: Creating Microsoft SQL Server databases.

    Oct 30, 2017 01:56