Skip to main content

psaDBAdmin Installation, Configuration and User Guide

PSA Support
  • Updated

Table of Contents

Overview

psaDBAdmin provides each project with extra functionality to assist ADM success. There is a Database, Database Table, Current SQL Processes, Tech Lead, DB Add File, Custom Change, Collect Admin, Database Monitoring, Server Audit and Configuration tab.

Key Features:

  • Ability to create, backup, take offline and detach database.
  • Ability to view databases columns and indexes.
  • Ability to end an SQL session.
  • Ability to monitor drive space and jobs.
  • Ability to set default size and add file on a database.
  • Ability to do custom changes using SQL script.
  • Ability to load bulk tables and copy tables from collect.
  • Ability to monitor databases different logs and missing index.
  • Ability to view all necessary server audit information.
  • Ability to set a recipient email for disk space alert.

Install psaDBAdmin

The application can be installed on Syniti Solutions DSP versions 7.0.6 and above.

Download the Application License

The psaDBAdmin application and/or license are obtained by opening a support ticket at support.syniti.com.

Perform the following steps to retrieve the necessary information for a license request:

  1. On the DSP application server, locate the Hardware Identifier program (called “HardwareIdentifier.exe") included in a zip file along with the DSP installation software and documentation previously downloaded from Syniti.
  2. Open the program.
  3. Click Generate.
  4. Copy the automatically generated ID and collect the following additional information. All information below pertains to the application server running DSP; no information is needed regarding the database server:
    1. Hardware ID (as mentioned above)
    2. Windows computer name
    3. Number of processor cores (as shown in the Task Manager CPU tab)
    4. Usage of the DSP instance, as in, DEV, TEST (or QA) or PROD
  5. Syniti Licensing will deliver the license file via the support ticket.

Install the License

Perform the following steps to install the license:

  1. Log in to the DSP site as an Administrator.
  2. Select Admin > Configuration > Product Licenses in the Navigation pane.
  3. Click the Upload a file icon in the FILE NAME column next to the Upload a New Product License link.
  4. Locate the license file that was provided by Syniti Licensing.
  5. Click Open
  6. Verify the license is uploaded.

    NOTE: If the Navigation pane does not display all the licensed components as expected, use the browser refresh button or the F5 key to refresh the screen. At this point the full vertical menu will appear.

Install the Application

Perform the following steps to install the application:

  1. Right click on psaDBAdmin.zip and go to Properties. Ensure to unblock the file if it is blocked.
  2. Unzip the file.
  3. Navigate to the DSP Installation folder (e.g. D:\BOA\DSP or C:\Program Files (x86)\BOA\DSP)
  4. Back up the DSP Install\BOA\DSP folder to a compressed zip file.
  5. Back up all Syniti-supplied SQL Server databases or verify that a complete recent backup already exists.

    Supplied databases: AutoGen, cMap, cMap_Data, cMass, cMass_Data, Console, CranPort, CranSoft, DataConstructionServer, DataDialysis, DataGarage, DBMoto_Client, DGE, DGE_Data, dgReports, dgSAP, dspAddOn, DSPCommon, dspMonitor_AccPak, dspMonitorConfig, DSW, IGC, Integrate, IntegrateStaging, InterfaceServer, MC, & RADToolkit

  6. Stop IIS.

    This process disconnects all active DSP users, so it is highly recommended to perform the install when no users are on the system. This process stops IIS on the web server.

    1. Open Windows Start Menu.
    2. Open the Command Prompt (run as an administrator).
    3. Type: IISReset –stop
    4. Click the Enter key.
    5. Leave the Command Prompt window open for later use.
  7. Stop all services that start with “Cransoft Service …”

    This process stops all DSP background jobs, so it is highly recommended to perform the install when no scheduled operations are running on the system.

    1. Open Windows Start Menu.
    2. Select Administrative Tools.
    3. Run Services.
    4. Right-click the DSP service.
    5. Select Stop.
    6. Repeat the previous two steps for any additional DSP services.
  8. Copy the Web folder from the zip file to your existing DSP install\Web folder.  If prompted, replace the files in the destination.
  9. Copy the Databases folder from the zip file to your existing DSP install\Databases folder.  If prompted, replace the files in the destination.
  10. Navigate to DSP install\Databases and execute file psaDBAdmin_Install.bat (run as an administrator).
  11. Start all services that start with “Cransoft Service …”
    1. Open Windows Start Menu.
    2. Select Administrative Tools.
    3. Run Services
    4. Locate the DSP service.
    5. Right-click the DSP service.
    6. Select Start
    7. Repeat the previous two steps for any additional DSP services.
  12. Start IIS.
    1. Open Windows Start Menu.
    2. Open the Command Prompt (run as an administrator).
    3. Type: IISReset –start.
    4. Click the Enter key.

If the Navigation pane in DSP doesn’t show psaDBAdmin, then try these steps:

  1. Log in to the DSP site as an Administrator.
  2. Select Admin > Configuration > Product License in the Navigation pane.

    Validate that psaDBAdmin displays.

  3. Select Admin > Configuration > Site Menu in the Navigation pane.

    Validate that psaDBAdmin display, otherwise:

    1. Click Add.
    2. Enter a priority in the PRIORITY field.
    3. Enter psaDBAdmin as the label for the site menu option in the LABEL field.
    4. Select the psaDBAdmin :psaDBAdmin page from the LINK TO PAGE ID list box.
  4. Select Admin > Configuration > Parameters in the Navigation pane.
    1. Click Clear Cache.
    2. Reload the browser tab.

    NOTE: Command time out for psaDBAdmin datasource at the vertical view of Common > Configuration > Data Source Registry must be set to at least 6000.

If you still cannot access the psaDBAdmin application, then review the “Define Security Roles” article in the DSP Online Help to ensure that thye DSP user has access to psaDBAdmin. The DSP Online Help is accessible from the question mark icon in the top-right corner of all DSP pages. You can also open a ticket at support.syniti.com for assistance.

User Guide

Quick Checklist to Add Database

  1. Open Database page to view existing databases.
  2. Click Add to create a new database then save.
  3. Go to the vertical view of the created database.
  4. Click Create and then click Edit and populate the mandatory fields.
  5. Click Save and click Create to complete the database creation.
  6. Open SSMS refresh and verify that the database exists.

Quick Checklist to Backup Database

  1. Open Database page to view existing databases.
  2. Select the database needs to backup.
  3. Go to the vertical view of the selected database.
  4. Click Backup and then click Edit to make the desired changes.
  5. Click Backup Database to complete the process.

Quick Checklist to Take Offline Database

  1. Open Database page to view existing databases.
  2. Select the database needs to take offline.
  3. Go to the vertical view of the selected database.
  4. Click Take Offline, read through the message and click Ok.

Quick Checklist to Detach Database

  1. Open Database page to view existing databases.
  2. Select the database needs to detach.
  3. Go to the vertical view of the selected database.
  4. Click Detach, read through the message and click Ok.

Quick Checklist for DatabaseTable

  1. Open Database Table page to view existing tables present in databases.
  2. Select database and click Columns to see the columns list and details.
  3. Select database and click Indexes to see the indexes list and details.
  4. Select database and click Foreign Keys to see the foreign key list and details.

Quick Checklist for Current SQL Processes

  1. Open Current SQL Process page to view processes.
  2. Select Process by Session_ID that needs to stop.
  3. Click Kill Session on the right-hand side.

Quick Checklist for Tech Lead

  1. Open Tech Lead page andselect Drive Space to see all the drives, click Drive Details icon to see the size summary of all the Database available in the drive. Click refresh icon to update the drive information.
  2. Click Database Details, select a database name and click the database details icon to see the file details in summary. Click refresh icon to update the database information.
  3. Click the Database Dashboard under Tech Lead to see visual on the size logs.
  4. Click TechLog and click Populate Log icon to have the updated TechLog info. Click Clear Log icon to delete the TechLog records.

Quick Checklist for Tech Lead-Maintenance

  1. Open Tech Lead page and selectJobs under Maintenance, select Web App and click job icon to see the job details. Click Delete icon to remove the Web App/Jobs information.
  2. Click Job – Tasks to view existing jobs, select Job ID and click Tasks icon to show the detailed task information.
  3. Click Log Counts to view log count record, click refresh icon to update the log count. Click Run Clean Up icon to remove unnecessary jobs.

Quick Checklist to DB Add File

There are two options:

  • Option 1 -
    1. Click the DB Add File page select Database File Create.
    2. Click Edit and select AllDatabases on the Build Option to fill in the required fields.
    3. Click the Build Add File icon to process.
    4. Changes will take effect only on the active databases on the child pane.

    OR

  • Option 2 -
    1. Click the DB Add File page and select Database File Create.
    2. Click Edit and select DefaultOnly for the Build Option.

      NOTE: Settings on the Database Default File Size page are used for the active database on the child pane.

  • Click File Size to view existing record, and then make changes.
  • Click Database Default File Size to view existing record, and then make changes.

Quick Checklist Custom Change

  1. Open Custom Change page and select Custom Changes click Apply icon or Remove icon for the selected changes to apply. Click the Logs icon to see the Logs list and click the SQL icon for the SQL script and then make changes.
  2. Click Custom Change Logs to view the Logs list.
  3. Click Custom Change Client and make changes.

Quick Checklist for Collect Admin

  1. Open Collect Admin page and click Collect Copy Tables.
  2. Click Edit and apply desired changes.
  3. Click Copy Tables icon to start the copy process.
  4. Click Copy Table Logs to view log list.
  5. Click Collect Upload to edit and apply desired changes.

    NOTE: An option to add and edit tables is available.

  6. Click Load Tables icon to start the bulk load process.

Quick Checklist for Database Monitoring

  1. Open Database Monitoring page and select Missing Index Details to view information. The Delete All icon is available to clear the list.
  2. Click Session Data Log to view records. The Delete All icon is available to clear the list. The Process icon displays the updated logs.
  3. Click Temp Database Log to view records.
    • The Delete All icon is available to clear the list.
    • The Process icon displays the updated logs.
    • The Delete icon removes the selected session.
  4. Click CPU & Memory Log to view records. The Delete All icon is available to clear the list and the Process icon displays the updated logs.
  5. Click Disk Read & WriteLog to view records. The Delete All icon is available to clear the list and the Process icon displays the updated logs.

Quick Checklist for Server Audit

  1. Open Server Auditpage and select Audit Process, options available are Jobs to view the jobs list, Run Audit to start the audit process and Clear Results to remove all jobs list.
  2. Click Audit Hor View Times to view duration details.
  3. Click Web Apps to view web app list and information.
  4. Click Audit DB Settings to view database list settings and details.
  5. Click Audit DB Files to view all files on databases.
  6. Click Audit Index Fragmentation to view index fragmentation information.
  7. Click Audit Missing Index Stats to view missing index stats.
  8. Click Audit Table Counts to view all tables on databases.
  9. Click Audit Table Missing PK to view tables that are missing primary key.
  10. Click Audit Job & Error to view job queue and job task message.
  11. Click Audit Job Queue to view job queue list and information.
  12. Click Audit Job Tasks to view job tasks list and details.
  13. Click Audit Cransoft Queue to view the cransoft queue information.
  14. Click Audit Cransoft Service to view the cransoft services list and details.
  15. Click Audit Cransoft Service Queue to view the cransoft services queue information.
  16. Click Audit DB Configure List to view the database configuration list.
  17. Click Audit Product License to view the existing products and license on the App server.
  18. Click Audit File DataSource to viewfile data sources information.
  19. Click Audit Collect Target Source to viewcollect target data sources details.
  20. Click Audit Collect Table to view all collect tables info.
  21. Click Audit Collect Package Type to view all available packages type in collect.
  22. Click Audit DSW Target to view a list of targets and details.
  23. Click Audit DD Reports to view a list of DD reports.
  24. Click Audit Database Version to view a list of database versions.
  25. Click Audit Patch Log to view patches log.

Quick Checklist for Configuration

Open Configuration Page and select Disk Space Alert. Make changes as necessary. Click Mail Settings icon and enter an email address for notification when the threshold is met.

Database

Database will allow users to add databases, build a profile for a database, view tables, views, procedures, functions, and triggers.

Add a Database and then build the profile for the Database

psaDBAdmin_1.png

Image Definitions

mceclip1.png

Database Table

Database Tables will display all tables inside each database registered under SQL Server Instance.

psaDBAdmin_2.png

Image Definitions

mceclip3.png

Current SQL Processes

SQL processes allows users to view current running processes, the query being executed and status, we then have the ability to kill any session.

Find your Session_ID and "Kill Session."

psaDBAdmin_3.png

Image Definitions

mceclip5.png

Tech Lead–Drive Space

Drive Space display the summary of free size, used size, total size and percentage available for each drive.

psaDBAdmin_4.png

psaDBAdmin_5.png

Image Definitions

mceclip8.png

Tech Lead–Database Details

In this page Database Details provide the breakdown of rows and log type description. A refresh button is available to get the most current information.

psaDBAdmin_6.png

Image Definitions

mceclip10.png

Tech Lead–Database Dashboard

Database Dashboard gives a quick visual overview of the current SDB, DG, DSW and overall Database Size Log.

psaDBAdmin_7.png

Tech Lead–Tech Log

Tech Log page provides the execute details of JobErrorCount, DiskSpace available and PrimaryKeyCheck.

psaDBAdmin_8.png

Image Definitions

mceclip14.png

Tech Lead–Primary Key Check

Primary Key Check display the list of tables in database that has no primary key.

psaDBAdmin_9.png

Maintenance–Jobs

Jobs display the summary of job failed group byWEB APP ID and with count information.

psaDBAdmin_10.png

Image Definitions

mceclip17.png

Maintenance–Job – Tasks

Job Tasks display the jobs that failed with detailed description.

psaDBAdmin_11.png

Image Definitions

mceclip19.png

Maintenance–Log Counts

Log counts display automate log, data quality log, Cransoft and Cransoft workflow log count details.

psaDBAdmin_12.png

Image Definitions

mceclip21.png

DB Add File–Database File Create

Database File Create page allow you to create data files on All databases and can set the file count for sdb, dsw, dg, file size,file growth size, app file size and app growth size. Another build option is Default only that will capture the setup registry from Database Default File Size page.

psaDBAdmin_13.png

Image Definitions:

mceclip0.png

DB Add File–File Size

File Size is a registry page of memory that can be use or set in Database Default File Size.

psaDBAdmin_14.png

DB Add File–Database Default File Size

This page allows to set up a default Database Size and File Growth.

psaDBAdmin_15.png

Custom Change–Custom Changes

Custom Changes page allows to change an existing process and replace with a new SQL script.

psaDBAdmin_16.png

Image Definitions:

mceclip1.png

Custom Change–Custom Change Logs

Custom Changes Logs display the new SQL script and to which database it has been applied.

psaDBAdmin_17.png

Custom Change–Custom Change Client

Custom Change Client is where to register the client name that will be needing custom changes.

psaDBAdmin_18.png

Collect Admin–Collect Copy Tables

Collect Copy Tables is additional functionality in psaDBAdmin that allows you copy tables in collect under a specific target source.

psaDBAdmin_19.png

Image Definitions:

mceclip2.png

Collect Admin– Copy Table Logs

Display the Copy Table Logs information.

psaDBAdmin_20.png

Collect Admin– Collect Upload

Copy Bulk Upload allows the user to do bulk upload and set target, source, package type, and the tables to load.

psaDBAdmin_21.png

Image Definitions:

mceclip3.png

Database Monitoring– Missing Index Details

Display the Missing Index Details information and allows to delete all logs.

psaDBAdmin_22.png

Image Definitions:

mceclip4.png

Database Monitoring– Session Data Log

Display the Session Data Logs information and allows to clear all logs and processes.

psaDBAdmin_23.png

Image Definitions:

mceclip5.png

Database Monitoring–Temp Database Log

Display the Temp Database Logs information and allows to clear all logs and processes.

psaDBAdmin_24.png

Image Definitions:

mceclip6.png

Database Monitoring–CPU & Memory Log

Display the CPU & Memory Logs information and allows to clear all logs and processes.

psaDBAdmin_25.png

Image Definitions:

mceclip7.png

Database Monitoring–Disk Read & Write Log

Display the Disk Read & Write Logs information and allows to clear all logs and processes.

psaDBAdmin_26.png

Image Definitions:

mceclip8.png

Server Audit– Audit Process

Audit Process page allows you to run all the audit processes at once, providing the list of active jobs and has an option to run a DB snapshot and View snapshot.

psaDBAdmin_27.png

Image Definitions:

mceclip9.png

Server Audit– Audit Hor View Times

This page displays Horizontal view duration and details.

psaDBAdmin_28.png

Server Audit – Audit Web Apps

Audit Web Apps provides the list of all available webapps with data source ID and version.

psaDBAdmin_29.png

Server Audit – Audit DB Settings

Audit DB Settings display the database collation settings information.

psaDBAdmin_30.png

Server Audit – Audit DB Files

Audit DB Files deliver database files and information.

psaDBAdmin_31.png

Server Audit – Audit Index Fragmentation

This page provides the thorough Index Fragmentation records.

psaDBAdmin_32.png

Server Audit – Audit Missing Index Stats

This page provides the list of missing index stats.

psaDBAdmin_33.png

Server Audit – Audit Table Counts

Audit Table Counts display counts of tables with DB name information.

psaDBAdmin_34.png

Server Audit – Audit Table Missing PK

Audit Table Missing PK displays list of tables with DB name that are missing Primary Key.

psaDBAdmin_35.png

Server Audit – Audit Job & Error

This page delivers failed list of job queues with detailed task message.

psaDBAdmin_36.png

Server Audit – Audit Job Queue

This page provides list of failed job queues.

psaDBAdmin_37.png

Server Audit – Audit Job Queue Tasks

This page shows information of job queue tasks with detailed message.

psaDBAdmin_38.png

Server Audit – Audit Cransoft Queue

This page views the list of Cransoft queue available.

psaDBAdmin_39.png

Server Audit – Audit Cransoft Service

This page delivers the list of Cransoft services available.

psaDBAdmin_40.png

Server Audit – Audit Cransoft Service Queue

The page displays the active Cransoft Service Queue with Que ID, priority, threads, and date.

psaDBAdmin_41.png

Server Audit – AuditDB Configure List

This page provides details of the database configuration list.

psaDBAdmin_42.png

Server Audit – Audit Product License

This page provides the product license information that has been applied.

psaDBAdmin_43.png

Server Audit – Audit File DataSource

This page displays the data source ID, source name and path.

psaDBAdmin_44.png

Server Audit – Audit Collect Target Source

This page provides information of the target, source, connection type schema owner ran in Collect.

psaDBAdmin_45.png

Server Audit – Audit Collect Table

This page shows the tables run through collect with source, target, package type & date information.

psaDBAdmin_46.png

Server Audit – Audit Collect Package Type

This page displays the lists of package type with package count and creation date.

psaDBAdmin_47.png

Server Audit – Audit DSW Target

This page contains detailed information for data staging warehouse target.

psaDBAdmin_48.png

Server Audit – Audit DD Reports

This page displays random information as part of a due diligence report.

psaDBAdmin_49.png

Server Audit – Audit Database Version

This page displays the data versions and necessary information for audit purposes.

psaDBAdmin_50.png

Server Audit – Audit Patch Log

This page contains all patch details applied in the system.

psaDBAdmin_51.png

Configuration – Disk Space Alert

Within Configuration, set up thresholds for each available drive. An email will be sent to the stored account to notify when the threshold for the drive is met.

psaDBAdmin_52.png

Image Definitions:

mceclip10.png

Professional Service Accelerators (PSAs) are licensed separately from the SST. For more information, please email NASMT@syniti.com. To download or install a PSA, submit a request to Syniti support.

 

Updated on November 14, 2022

 

Related articles