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, and 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 database administrator 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, BMC Support shall provide different steps for you to follow.

Creating Oracle tablespaces

Installation worksheets for BMC Digital Workplace Catalog

Installation process overview for BMC Digital Workplace Catalog

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

Copy the following SQL statement boilerplate that will create the system/application database 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>";

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";

Select each statement block and click Execute to perform the query action against the database server.
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

You must create at least one tenant database before you can load the initial tenant data that enables users to log into BMC Digital Workplace Catalog to manage the service catalog. Repeat these steps for each of the tenants who plan to share the same BMC Digital Workplace Catalog application instance.

Copy the following SQL statement boilerplate that will create the tenant database 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>;

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;

Select each statement block and click Execute to perform the query action against the database server.
After you have 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, you must perform the following steps to prevent thread locking or the database hanging when running the postinstallation process to load the initial tenant data.

Copy the following SQL statement 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;

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;

Select each statement block and click Execute to perform the query action against the database server.
After you have 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 online documentation.

Where to go from here

Performing the BMC Digital Workplace Catalog installation