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.

Before you begin

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

To install BMC Helix Digital Workplace Catalog, you need to create a database first. Before installing BMC Helix Digital Workplace Catalog, take into account the following considerations:

• For the BMC Helix Digital Workplace Catalog fresh deployment, the Application Database Dump is provided for setting up your system database. 
• The login has a default user name / password (Demo / password).
• Schema creation is no longer required as part of the post-installation process because the database dump has a pre-created schema. 

To set up the system/application database and user

1. Download the DWPCatalog2002SQLServerGoldenDB.bak file from BMC Electronic Product Distribution site. The default collation of this bak file is SQL_Latin1_General_CP1_CI_AS. If your database has a different collation, see  DOC-128064  for the procedure to update the collation. For more information, see Downloading the BMC Digital Workplace Catalog installation files.

2. Execute the following commands for creating the database and restoring the dump from the previous step.

   Use master;
   
   go
   /* Restore the database dump */
   RESTORE DATABASE DWPCatalog
   FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\DWPCatalog2002SQLServerGoldenDB.bak' 
   WITH MOVE 'ARSystem_data' 
   TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DWPCatalog.mdf', 
   MOVE 'ARSystem_log' 
   TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DWPCatalog_Log.ldf';
   
   ALTER DATABASE DWPCatalog
      SET ALLOW_SNAPSHOT_ISOLATION ON;
   
   ALTER DATABASE DWPCatalog
      SET READ_COMMITTED_SNAPSHOT ON;
   
   /* Create the login and assign it to the database */
   CREATE LOGIN "DWPCAdmin"
      WITH PASSWORD = 'Passw0rd!',
      DEFAULT_DATABASE = "DWPCatalog";
   
   USE DWPCatalog;
   
   /* Create a user for the login */
   CREATE USER "DWPCAdmin"
      FOR LOGIN "DWPCAdmin";
   
   /* Apply the admin role permissions to the application database admin user */
   ALTER ROLE "db_owner"
      ADD MEMBER "DWPCAdmin";
   
   ALTER AUTHORIZATION ON DATABASE::DWPCatalog TO DWPCAdmin;

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

   use master
   
   /* Create tenant database */
   CREATE DATABASE "DWPCTenant" 
   ON (NAME = "dwpctenant_data", 
   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DWPCTenant.mdf', SIZE = 2000MB)
   LOG ON (NAME = "dwpctenant_log", 
   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DWPCTenant.ldf', SIZE = 1000MB);
   
   ALTER DATABASE DWPCTenant
      SET ALLOW_SNAPSHOT_ISOLATION ON;
   
   ALTER DATABASE DWPCTenant
      SET READ_COMMITTED_SNAPSHOT ON;
   
   /* Enable the application database admin user to manage the tenant database */
   ALTER AUTHORIZATION 
   ON DATABASE::DWPCTenant 
   TO DWPCAdmin;
   

2. Select each statement block and click Execute to perform the query action against the database server.

3. 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 post-installation process to load the initial tenant data.

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

   SQL statement boilerplate to reduce errors

   USE DWPCatalog;
   
   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 DWPCatalog SET RECOVERY FULL;
   ALTER DATABASE DWPCatalog SET MULTI_USER WITH ROLLBACK IMMEDIATE;
   ALTER DATABASE DWPCatalog SET PARAMETERIZATION SIMPLE;
   ALTER DATABASE DWPCatalog SET READ_COMMITTED_SNAPSHOT ON;
   
   
   USE DWPCTenant;
   
   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 DWPCTenant SET RECOVERY FULL;
   ALTER DATABASE DWPCTenant SET MULTI_USER WITH ROLLBACK IMMEDIATE;
   ALTER DATABASE DWPCTenant SET PARAMETERIZATION SIMPLE;
   ALTER DATABASE DWPCTenant SET READ_COMMITTED_SNAPSHOT ON;

2. Select each statement block and click Execute to perform the query action against the database server.

3. After you run all the queries, repeat the same steps for any additional tenant database that you want to set up.

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

Where to go from here

Performing the BMC Digital Workplace Catalog installation