This documentation supports the 19.05 version of BMC Digital Workplace Advanced.

To view an earlier version, select the version from the Product Version menu.

Creating Microsoft SQL Server databases

If you are using Microsoft SQL Server as the database engine, a database administrator (DBA) must create two databases before you install BMC Digital Workplace Catalog:

  • The system database to store the shared application data
  • The tenant database to store the organization-specific data related to the service catalog items

The DBA must also create a single database user who can perform administrative operations on both databases.

A DBA who can operate as the sa user can copy the SQL statements from this topic into SQL Server Management Studio to create the databases and database users.

Note

Perform the following tasks to create databases for a fresh installation of BMC Digital Workplace Catalog. If BMC is providing technical support to migrate your data from MyIT Service Broker 3.2 to a fresh installation of MyIT Service Broker 3.3 or later, BMC Support will provide different steps for you to follow.

Before you begin

Complete the "Database server and data storage creation parameters" section of the Installation worksheets for BMC Digital Workplace Catalog.

To create the system/application database and database user

  1. Copy the following SQL statement boilerplate that will create the system/application database, and paste the boilerplate into a SQL Server Management Studio query editor.

    SQL statement boilerplate to create the system/application database and user
    /* MSSQL databases require unique logical names and full paths for the data file and log file.
       Remember to replace the database name and path with values that match your system */
    
    CREATE DATABASE "<BMC_DATABASE_TABLESPACE>"
       ON (NAME = "<BMC_DATABASE_TABLESPACE>_data",
       FILENAME = 'd:\DATA\<BMC_DATABASE_TABLESPACE>.mdf', SIZE = 2000MB)
       LOG ON (NAME = "<BMC_DATABASE_TABLESPACE>_log",
       FILENAME = 'd:\DATA\<BMC_DATABASE_TABLESPACE>.ldf', SIZE = 1000MB);
    
    ALTER DATABASE <BMC_DATABASE_TABLESPACE>
       SET ALLOW_SNAPSHOT_ISOLATION ON;
    
    ALTER DATABASE <BMC_DATABASE_TABLESPACE>
       SET READ_COMMITTED_SNAPSHOT ON;
    
    CREATE LOGIN "<BMC_DATABASE_LOGIN>"
       WITH PASSWORD = '<BMC_DATABASE_PASSWORD>',
       DEFAULT_DATABASE = "<BMC_DATABASE_TABLESPACE>";
    
    USE <BMC_DATABASE_TABLESPACE>;
    
    CREATE USER "<BMC_DATABASE_LOGIN>"
       FOR LOGIN "<BMC_DATABASE_LOGIN>";
    
    ALTER ROLE "db_owner"
       ADD MEMBER "<BMC_DATABASE_LOGIN>";
  2. Replace the <VARIABLE_NAME> placeholders with the values collected in the installation worksheets.

    Example SQL statements to create the system/application database and user
    CREATE DATABASE "dwp_db_app"
       ON (NAME = "dwp_db_app_data",
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\dwp_db_app.mdf', SIZE = 2000MB)
       LOG ON (NAME = "dwp_db_app_log",
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\dwp_db_app.ldf', SIZE = 1000MB);
    
    
    ALTER DATABASE dwp_db_app
       SET ALLOW_SNAPSHOT_ISOLATION ON;
    
    ALTER DATABASE dwp_db_app
       SET READ_COMMITTED_SNAPSHOT ON;
    
    CREATE LOGIN "dwp_admin_app"
       WITH PASSWORD = 'Passw0rd!',
       DEFAULT_DATABASE = "dwp_db_app";
    
    USE dwp_db_app;
    
    CREATE USER "dwp_admin_app"
       FOR LOGIN "dwp_admin_app";
    
    ALTER ROLE "db_owner"
       ADD MEMBER "dwp_admin_app";
    
  3. Select each statement block and click Execute to perform the query action against the database server.
  4. After you have run all the queries, refresh the database list to view the new database that you created.

To create the tenant database and database user

Before you can load the initial tenant data that enables users to log in to BMC Digital Workplace Catalog to manage the service catalog, you must create at least one tenant database. Repeat these steps for each tenant who plans to share the same BMC Digital Workplace Catalog application instance. 

  1. Copy the following SQL statement boilerplate that will create the tenant database, and paste the boilerplate into a SQL Server Management Studio query editor.

    SQL statement boilerplate to create the tenant database
    /* MSSQL databases require unique logical names and full paths for the data file and log file.
       Remember to replace the database name and path with values that match your system */
    
    CREATE DATABASE "<BMC_DATABASE_TENANT_TABLESPACE>"
       ON (NAME = "<BMC_DATABASE_TENANT_TABLESPACE>_data",
       FILENAME = 'd:\DATA\<BMC_DATABASE_TENANT_TABLESPACE>.mdf', SIZE = 2000MB)
       LOG ON (NAME = "<BMC_DATABASE_TENANT_TABLESPACE>_log",
       FILENAME = 'd:\DATA\<BMC_DATABASE_TENANT_TABLESPACE>.ldf', SIZE = 1000MB);
    
    ALTER DATABASE <BMC_DATABASE_TENANT_TABLESPACE>
       SET ALLOW_SNAPSHOT_ISOLATION ON;
    
    ALTER DATABASE <BMC_DATABASE_TENANT_TABLESPACE>
       SET READ_COMMITTED_SNAPSHOT ON;
    
    /* Enable the db owner to manage the tenant database */
    ALTER AUTHORIZATION
       ON DATABASE::<BMC_DATABASE_TENANT_TABLESPACE>
       TO <BMC_DATABASE_LOGIN>;
    
  2. Replace the <VARIABLE_NAME> placeholders with the values collected in the installation worksheets. The final SQL statement grants the system database user full administrative rights to the tenant database.

    Example SQL statements to create the tenant database and user
    CREATE DATABASE "dwp_db_tenant"
       ON (NAME = "dwp_db_tenant_data",
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\dwp_db_tenant.mdf', SIZE = 2000MB)
       LOG ON (NAME = "dwp_db_tenant_log",
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\dwp_db_tenant.ldf', SIZE = 1000MB);
    
    ALTER DATABASE dwp_db_tenant
       SET ALLOW_SNAPSHOT_ISOLATION ON;
    
    ALTER DATABASE dwp_db_tenant
       SET READ_COMMITTED_SNAPSHOT ON;
    
    /* Enable the db owner to manage the tenant database */
    ALTER AUTHORIZATION
       ON DATABASE::dwp_db_tenant
       TO dwp_admin_app;
    
  3. Select each statement block and click Execute to perform the query action against the database server.

  4. After you run all the queries, refresh the database list to view the new database that you created.

To add required SQL Server features to both databases

If your database server is Microsoft SQL Server 2012 or later, perform the following steps to prevent thread locking or the database from hanging when running the postinstallation process to load the initial tenant data.

  1. Copy the following SQL statement boilerplate, and paste the boilerplate into a SQL Server Management Studio query editor.

    SQL statement boilerplate to reduce errors
    USE <replace_with_database_name>;
    
    CREATE SYNONYM trace_xe_action_map FOR sys.trace_xe_action_map;
    CREATE SYNONYM trace_xe_event_map FOR sys.trace_xe_event_map;
    
    ALTER DATABASE <replace_with_database_name> SET RECOVERY FULL;
    ALTER DATABASE <replace_with_database_name> SET MULTI_USER WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE <replace_with_database_name> SET PARAMETERIZATION SIMPLE;
    ALTER DATABASE <replace_with_database_name> SET READ_COMMITTED_SNAPSHOT ON;
  2. Replace all occurrences of <replace_with_database_name> with the name of the database to modify, such as the database referenced by the variables <BMC_DATABASE_TABLESPACE> and <BMC_DATABASE_TENANT_TABLESPACE>.

    Example SQL statements to reduce errors on the system/application database
    USE dwp_db_app;
    
    CREATE SYNONYM trace_xe_action_map FOR sys.trace_xe_action_map;
    CREATE SYNONYM trace_xe_event_map FOR sys.trace_xe_event_map;
    
    ALTER DATABASE dwp_db_app SET RECOVERY FULL;
    ALTER DATABASE dwp_db_app SET MULTI_USER WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE dwp_db_app SET PARAMETERIZATION SIMPLE;
    ALTER DATABASE dwp_db_app SET READ_COMMITTED_SNAPSHOT ON;
  3. Select each statement block and click Execute to perform the query action against the database server.

  4. After you run all the queries, repeat the same steps for each database.

    For more information about the issues that these SQL statements prevent, see Configuring the Microsoft SQL Server in the Remedy ITSM Deployment documentation.

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

Comments

  1. Thad Esser

    This page is confusing. The title indicates it is for SQL Server, but the boilerplate scripts look like they are for Oracle.

    Jun 12, 2019 03:26
    1. Ravee Panjwani

      Thanks for your comment, Thad.

      The content on this topic applies to SQL, please clarify which section you are referring to... Also, here's the topic that covers the steps for Oracle → Creating Oracle tablespaces.

      Thanks,
      Ravee

      Jun 12, 2019 04:11
      1. Thad Esser

        From what I understand, and after talking to one of our DBAs who was also confused by this page, a "tablespace" is an Oracle thing, not applicable to SQL Server.

        Jun 12, 2019 04:24
        1. Ravee Panjwani

          Thanks again, Thad.

          I now see that you are referring to the placeholder names used above. Please replace those with values that apply to your environment.

          Thanks,
          Ravee

          Jun 12, 2019 04:44