Creating databases and users on SQL Server
This topic describes how to set up product-related databases and users for the Microsoft SQL Server database.
Before you start creating databases and users, ensure that the prerequisites are met.
You can create databases and users by using either of the following methods:
- Before starting configuration of the product by using the create_bdssa_sqlserver_users.sql script, which is located in the bdssa86<servicepackVersion>external-files.zip: You can download external files from the BMC Electronic Product Distribution (EPD) website.
- During configuration by using the create_bdssa_sqlserver_db_users.sql script, which you download by clicking the Download Database Scripts button in the postinstallation configuration wizard (see Configuring the product by using the console for details).
SQL Server databases and users
By default, the create_bdssa_sqlserver_users.sql or create_bdssa_sqlserver_db_users.sql script creates the databases shown in the following table. You can use different database names from those used in the table examples by modifying the script. Make a note of the database names for reference during the
- MultiExcerpt named 'Product_Name' was not found
SQL Server databases
Database name | Purpose |
---|---|
BDSSA_DW_DB | Storage of the reports data warehouse tables |
BDSSA_ETL_MASTER_DB | Storage of the extract, transform, and load (ETL) tool repository |
BDSSA_ETL_WORK_DB | Storage of the ETL tool repository |
BDSSA_PORTAL_DB | Storage of the portal content store |
The following settings are recommended:
- The database recovery model should be set to Simple.
- Set the server-level setting of
Maximum Server Memory
to 80% of the physical memory on the server. For example, if the server has 6 GB of memory, setMaximum Server Memory
to 4.7 GB.
By default, the create_bdssa_sqlserver_users.sql or create_bdssa_sqlserver_db_users.sql script creates the users shown in the following table. You can use different logon names from those used in the following table by modifying the script. Make a note of the user names and passwords for reference during the
- MultiExcerpt named 'Product_Name' was not found
SQL Server users
User | Responsibilities |
---|---|
BDSSA_DW |
|
BDSSA_ETL_MASTER |
|
BDSSA_ETL_WORK |
|
BDSSA_PORTAL |
|
To create databases and users
- On the reports data warehouse computer, navigate to the directory in which script is located.
- Log on to the SQL Server Management Studio. Ensure you have sysadmin privilege before you log on.
- Locate the create_bdssa_sqlserver_users.sql or create_bdssa_sqlserver_db_users.sql script and open it.
- Edit the script as follows:
Replace the following default database names with the desired names (for example, BDSSA_DW_DATABASE and BDSSA_PR_DATABASE) if you don't want to create databases with the default names specified in the SQL Server databases and users section.
$BDSSA_DW_DB
$BDSSA_ETL_WORK_DB
$BDSSA_ETL_MASTER_DB
$BDSSA_PORTAL_DB
Note
Do not retain the '$' sign in the database names. For example, you can find and replace all the instances of $BDSSA_DW_DB with BDSSA_DW_DATABASE.
For each of the databases, change the value of the
FILENAME
parameter for the .mdf and .ldf files.Note
In the parameter value, retain
N'
after the=
sign and do not retain the '$' sign.
The following example shows theFILENAME
parameter with a sample path for the .mdf and .ldf files of the warehouse database, BDSSA_DW_DB on Windows.CREATE DATABASE [BDSSA_DW_DB] ON PRIMARY (NAME = N'BDSSA_DW_DB', FILENAME = N'C:\Program Files\MYSQL\BDSSA\MSSQL\DATA\\BDSSA_DW_DB.mdf', SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)LOG ON (NAME = N'BDSSA_DW_DB_log', FILENAME = N'C:\Program Files\MYSQL\BDSSA\MSSQL\DATA\\BDSSA_DW_DB_log.ldf', SIZE = 1024KB, MAXSIZE = 2048GB, FILEGROWTH = 10%)
For each of the users, replace the following default user names with the desired names (for example, BDSSA_DW_USER and BDSSA_PR_USER) if you don't want to create users with the default names specified in the SQL Server databases and users section:
- $BDSSA_DW
- $BDSSA_ETL_WORK
- $BDSSA_ETL_MASTER
$BDSSA_PORTAL
Note
In values of this parameter, do not retain the '$' sign.
For each of these users, modify the
PASSWORD
parameter as required.Note
When you replace instances of the
LOGIN
parameter, thePASSWORD
parameter is also set to the same value asLOGIN
. After modifying theLOGIN
parameter, remember to modify thePASSWORD
parameter.In value of this parameter, do not retain the '$' sign.
The following example shows the
with sample values for the BDSSA_DW_USER user.LOGIN
andPASSWORD
parametersCREATE LOGIN [BDSSA_DW_USER] WITH PASSWORD='password', DEFAULT_DATABASE=[BDSSA_DW_DB], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO EXEC sys.sp_addsrvrolemember @loginame = N'BDSSA_DW', @rolename = N'dbcreator' GO EXEC sys.sp_addsrvrolemember @loginame = N'BDSSA_DW', @rolename = N’sysadmin'
- Match the SQL Server collation sequence for the BMC Server Automation database with the collation sequence for the reports data warehouse, as follows:
- Search for
COLLATE SQL_Latin1_General_CP1_CI_AS
and remove comments ( -- characters) from this line. - Replace
SQL_Latin1_General_CP1_CI_AS
with the collation sequence for the BMC Server Automation database.
- Search for
Ensure that adequate size is assigned to the .mdf and .ldf files. To know the size requirements, use the sizing tools described in Sizing for new product installations and new BMC Server Automation installations or Sizing for new product installations and existing BMC Server Automation installations, depending on your environment.
- Open New Query.
- Copy the contents of the script file and paste the contents into the query window.
- Run the script.
Comments