Database administrative tasks - MSSQL

Several scripts are provided to manage the database for the server components and the Dynamic Workload Console, performing tasks such as granting permissions and reorganizing databases.

The path and the available database tools vary depending on the component, as follows:
master domain manager and backup master domain manager
<MDM_installation_directory>/TWS/dbtools/<database_vendor>/<OS_type>/scripts
dynamic domain manager and backup dynamic domain manager
<DDM_installation_directory>/TDWB/dbtools/<database_vendor>/<OS_type>/scripts
Dynamic Workload Console
<DWC_installation_directory>/DWC/dbtools/<database_vendor>/<OS_type>/scripts
The following scripts are available:
dbgrant (available only for the master domain manager on Windows operating systems)
This script grants the user permissions for the views of the server components and the Dynamic Workload Console.
dbmaintenanceMssql
This script updates the database statistics (updateStat argument) and reduces index fragmentation by physically reordering pages and compacting space without fully rebuilding the index (reorg argument). These operations improve query performance and data read efficiency.
dbmove (available only for the master domain manager on Windows operating systems)
This script is to be run only if requested by Software Support.
Note: Do not run the dbmove script unless instructed to do so by Software Support. Running the script autonomously might damage or overwrite the data in your database.
To run the scripts, perform the following steps:
  1. Either copy the mssql folder to the database server, or launch the scripts from the workstation where you installed HCL Workload Automation. The mssql folder contains the scripts and the related .sql files.
  2. Check that the user who is going to run the procedure has the appropriate rights:
    • Database administrator permissions
    • Full access to and control of the HCL Workload Automation or Dynamic Workload Console installation directory
  3. Run the script with the -help parameter to display a list of supported parameters.

Syntax and arguments for the dbgrant script

This script is available only for the master domain manager on Windows operating systems.

Windows operating systems:
dbgrant.bat sql_server_instance  windows_auth_mode true|false
new_user database  [tws_user ] [tws_password ]
Supported parameters for the dbgrant script
sql_server_instance
The name or network address of the SQL Server instance.
windows_auth_mode
A boolean flag. Set to false if you use SQL Server authentication. Set to trueif you use Windows authentication.
new_user
The username of the account receiving read permissions.
database
The name of the target database.
tws_user
The administrative user performing the grant. This parameter is required only if you use SQL Server authentication.
tws_password
The password for the administrative user. This parameter is required only if you use SQL Server authentication.

Syntax and arguments for the dbmaintenanceMSSQL script

Windows operating systems:
dbmaintenanceMssql.exe -db database [-s server] [-u user] [-p password] [-a updateStat|reorg]
UNIX operating systems:
./dbmaintenanceMssql -db database [-s server] [-u user] [-p password] [-a updateStat|reorg]
Supported parameters for the dbmaintenanceMSSQL script are as follows:
-db database
The database name. This parameter is required.
-s server
The name of the database server, for instance localhost or localhost\SQLEXPRESS. The default value is localhost. This parameter is optional when you copy the mssql folder on the database server, otherwise it is required.
-u user
Name of the user accessing the database. This parameter is optional when you specify the user name in the shell where you run the command and when you use Windows authentication.
-p password
Database password. This parameter is optional when you specify the password in the shell where you run the command.
-a action
The action to be performed by the script. Supported values are updateStat and reorg. The default value is updateStat. If you choose the updateStat argument, the script updates the database statistics. If you choose the reorg, the script reduces index fragmentation by physically reordering pages and compacting space without fully rebuilding the index.