Creating Microsoft SQL Server databases
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 userCREATE 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 userCREATE 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 errorsUSE <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 databaseUSE 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