Creating databases and users on SQL Server
You can use the create_bdssa_sqlserver_users.sql script to create databases and users for the SQL Server database. This script is available in the TSSA-DW<version>-external-files.zip file on the BMC Electronic Product Distribution (EPD) location.
SQL Server databases and users
By default, the script creates the following databases:
SQL Server databases
Database name | Purpose |
---|---|
TSSADW_DW_DB | To store the data warehouse tables. |
TSSADW_ETL_MASTER_DB | To store information required by Oracle Data Integrator (ODI) during extract, transform, and load (ETL) process. |
TSSADW_ETL_WORK_DB |
The following settings are recommended:
- Set the database recovery model 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 script creates the following users:
SQL Server users
User | Responsibilities |
---|---|
TSSADW_DW |
|
TSSADW_ETL_MASTER |
|
TSSADW_ETL_WORK |
|
Creating databases and users
Do the following:
- Ensure that the prerequisites are met.
- Log in to the SQL Server Management Studio.
- Navigate to the create_tssadw_sqlserver_users.sql script and edit it as follows:
If you do not want to create databases with default names, change the following database names. For example, TSSADW_DW_DATABASE and TSSADW_PR_DATABASE.
$TSSADW_DW_DB
$TSSADW_ETL_WORK_DB
$TSSADW_ETL_MASTER_DB
Note: '$' sign must not be retained
Do not retain the '$' sign in database names. For example, find and replace all the instances of $TSSADW_DW_DB with TSSADW_DW_DATABASE.
For each database, change the value of the
FILENAME
parameter for the .mdf and .ldf files.Note: '$' sign must not be retained
In the
FILENAME
parameter, retainN'
after the=
sign. Do not retain the '$' sign.If you do not want to create users with default names, change the following user names. For example, TSSADW_DW_USER and TSSADW_PR_USER.
- $TSSADW_DW
- $TSSADW_ETL_WORK
$TSSADW_ETL_MASTER
Note: '$' sign must not be retained
Do not retain the '$' sign in user names.
For each user, modify the
PASSWORD
parameter as required. When you replace instances of theLOGIN
parameter, thePASSWORD
parameter is also set to the same value asLOGIN
. After modifying theLOGIN
parameter, modify thePASSWORD
parameter.Note: '$' sign must not be retained
In the
PASSWORD
parameter, do not retain the '$' sign.Match the SQL Server collation sequence for the TrueSight Server Automation database with the collation sequence for the 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 TrueSight Server Automation database.
- Search for
- Ensure that adequate size is assigned to the .mdf and .ldf files. To know the size requirements, see Sizing tools .
- Open New Query.
- Copy the contents of the script file and paste the contents into the query window.
- Run the script.
After creating all databases, run the following commands. If you have changed the database names, use the new names in the following commands.
ALTER DATABASE TSSADW_DW_DB SET ALLOW_SNAPSHOT_ISOLATION ON; ALTER DATABASE TSSADW_DW_DB SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE TSSADW_ETL_WORK_DB SET ALLOW_SNAPSHOT_ISOLATION ON; ALTER DATABASE TSSADW_ETL_WORK_DB SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE TSSADW_ETL_MASTER_DB SET ALLOW_SNAPSHOT_ISOLATION ON; ALTER DATABASE TSSADW_ETL_MASTER_DB SET READ_COMMITTED_SNAPSHOT ON;
How to video
Creating databases and users on SQL Server for TrueSight Server Automation - Data Warehouse (5:42)
Comments
Log in or register to comment.