SQL Health Dashboard

The SQL Health Dashboard provides SYSPRO Administrators and SQL Database Administrators a one-stop-shop for viewing and managing the system for high availability.

The dashboard displays information on both the system and company databases to assist in identifying potential problems, as well as enabling the repair of certain issues found within the databases.

PDF version

Exploring

This program provides the following functionality:

  • Single view of all your SYSPRO databases
  • Repair capability on certain issues found (e.g. creation of missing tables/columns)
  • Insights providing at-a-glance values and drill-down capabilities

Information displayed includes:

  • Configuration information for the SQL instance and each SYSPRO database
  • Information on the database and log files, including the size, free space and growth options
  • User access and permissions
  • Non-SYSPRO objects that interact with and which are dependent on SYSPRO data (i.e. stored procedures, views, triggers. etc.)
  • Fragmentation of SYSPRO indexes
  • SYSPRO table, column, index and foreign key information

  • This program is accessed from the Program List pane of the SYSPRO menu:

    Program List > Utilities > SQL Server Utilities

This refers to a set of rules determining how data is compared and sorted. Besides determining the alphabet, the collation order also determines whether accents, case and other alphabet properties are considered in the sort order.

For example:

If the collation is case-sensitive, the uppercase letters are sorted first.

Binary sort order is case-sensitive (i.e. lowercase precedes uppercase) and accent-sensitive. This is the fastest sorting order.

A compatibility level sets certain database behaviors to be compatible with the specified version of SQL Server. Performance issues may arise if your compatibility level is set to an older version of SQL.

SQL Server fragmentation occurs when data is sorted in a non-contiguous way. Index fragmentation is an expected and unavoidable characteristic of any OLTP environment.

Fragmentation is defined as any condition which causes more than the optimal amount of disk I/O to be performed in accessing a table, or causes the disk I/O's that are performed to take longer than optimal.

SQL Server backup and restore operations occur within the context of the recovery model of the database. Recovery models are designed to control transaction log maintenance.

A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available.

Three recovery models exist:

  • Simple
  • Full
  • Bulk-logged

Typically, a database uses the full recovery model or simple recovery model. You can switch a database to another recovery model at any time.

Topics

SYSPRO Security

Programs

Diagnostic Extract Utility

PDF and Video Resources

Feature Guides

Starting

  • SYSPRO 8 2018 R2 onwards

You can restrict operator access to activities within a program (configured using the Operator Maintenance program).

You can restrict operator access to the fields within a program (configured using the Operator Maintenance program).

You can restrict access to the eSignature transactions within a program at operator, group, role or company level (configured using the eSignature Setup program). Electronic Signatures provide security access, transaction logging and event triggering that gives you greater control over your system changes.

You can restrict operator access to programs by assigning them to groups and applying access control against the group (configured using the Operator Groups program).

You can restrict operator access to functions within a program using passwords (configured using the Password Definition program). When defined, the password must be entered before you can access the function.

  • The following database information is not available through the SQL Health Dashboard:
    • Point of Sale (PoS)
    • Manufacturing Operations Management (MOM)
  • The following is not verified on any databases that are not on the same version as the system database (which is assumed to be current):
    • Tables
    • Columns
    • Indexes
    • Foreign keys
  • Certain issues found and displayed in the dashboard (e.g. index fragmentation) cannot be corrected in the dashboard, as these must be addressed as part of your SQL maintenance plan

Solving

This typically indicates that the administrator login doesn't have sufficient SQL permissions to run certain functions.

The program will close.

Ensure that the administrator account has sufficient permission to run the required functions.

This typically indicates that the administrator login doesn't have sufficient permission to run certain functions.

The program will skip any further processing of that particular database and continue to the next database in the list (if there is one).

Ensure that the administrator account has sufficient permission to run the required functions.

The program will attempt to repair the following issues when you select the Repair Issues function:

  • Missing tables
  • Missing columns
  • Columns with a definition mismatch
  • User defined columns which are not nullable
  • Missing indexes
  • Indexes with a definition mismatch
  • Indexes defined against incorrect tables
  • Missing foreign keys
  • Foreign keys with a definition mismatch

The SQL Instance Information pane is automatically hidden and can be accessed from the right-hand side of the screen.

We advise that you use the latest version of SQL in order to leverage the latest improvements.

We advise that you use the highest compatibility level available for your version of SQL, in order to leverage the latest improvements.

Unless memory is capped, SQL will use all available memory on a server. This can result in performance issues, especially when SQL exists on the same server as the applications that use it.

If the database version against a company is not the same as the database version on the system database, then no validation is performed on tables, columns, indexes or foreign keys as these will need to be upgraded.

This can be remedied by logging into the company linked to the database which will then process any minor database upgrades required.

Fragmentation of indexes in SQL can lead to performance degradation. We therefore recommend that you add a task to REBUILD or REORGANISE the indexes in the database to your SQL Maintenance Plan.

Your account login should either be a member of the sysadmin fixed server role, or a member of the db_owner role on each of the SYSPRO databases.

Using

  • Unless memory is capped, SQL will use all available memory on a server.

    This can result in performance issues, especially when SQL exists on the same server as the applications that use it.

  • The collation used against any SYSPRO database must be case-sensitive.

  • Columns in a list view are sometimes hidden by default. You can reinstate them using the Field Chooser option from the context-sensitive menu (displayed by right-clicking a header column header in the list view). Select and drag the required column to a position in the list view header.

  • Fields on a pane are sometimes removed by default. You can reinstate them using the Field Selector option from the context-sensitive menu (displayed by right-clicking any field in the pane). Select and drag the required fields onto the pane.

    The captions for fields are also sometimes hidden. You can select the Show Captions option from the context-sensitive menu to see a list of them. Click on the relevant caption to reinstate it to the form.

    If the Show Captions option is grayed out, it means no captions are hidden for that form.

  • Press Ctrl+F1 within a list view or form to view a complete list of functions available.

Referencing

This pane lists all databases identified in the system database itself (including the system database).

An icon in the Company column identifies whether a database contains any issues.

This pane displays information regarding the SYSPRO database selected in the SYSPRO Database pane, as well as physical files for the selected SYSPRO database.

Column

Detail

Repair Issues

This starts the repair process for issues found against tables, columns, indexes and foreign keys.

Company

This indicates the company identification code.

Name

This indicates the company name.

Database

This indicates the name of the SYSPRO database.

Version

This indicates the version of the SYSPRO database.

No validation on tables, columns, indexes or foreign keys is performed if the versions of the company and system databases don't match. These will need to be upgraded (this can be done by logging into the company linked to the database).

Collation

The collation used against any SYSPRO database must be case-sensitive.

A critical icon is displayed if the collation is case-insensitive.

Compatibility level

The compatibility level plays an important role in determining what mechanisms SQL uses in processing.

A warning icon is displayed if the compatibility level is less than the highest level available for your current version of SQL, as this can lead to performance issues.

Recovery model

This indicates the recovery model used to control transactional log maintenance.

Auto close

This indicates whether the option is enabled against the database.

A critical icon is displayed if this option is set (i.e. this option mustn't be enabled for SYSPRO databases).

Auto shrink

This indicates whether the option is enabled against the database.

A critical icon is displayed if this option is set, as it can cause performance issues.

Auto create statistics

This indicates whether the option is enabled against the database.

Auto update statistics

This indicates whether the option is enabled against the database.

Status

This reflects the status of the database at the time the query was loaded (e.g. ONLINE). If the status is anything other than ONLINE, then the checks on tables, columns, indexes, etc., are not performed.

Possible database states include:

  • ONLINE
  • RESTORING
  • RECOVERING
  • RECOVERY_PENDING
  • SUSPECT
  • EMERGENCY
  • OFFLINE

Read only

This indicates whether the option is enabled against the database.

A critical icon is displayed if this option is set (i.e. SYSPRO databases should never be set to read only).

Last full backup

This indicates the date and time of last full backup.

Last log backup

This indicates the date and time of last log backup.

The following information regarding the physical files for the selected SYSPRO database is included in the Database Details pane:

  • Logical name

  • Type (e.g. Data/Log)

  • Location

  • Size (MB)

  • Growth (e.g. 10% or 1MB)

    It is important that the growth setting on these files is set correctly as this has an impact on performance.

  • Maximum size allowed

  • Space Used (MB)

  • Free space in file (MB)

  • Free space %

    Monitoring free space within tables is important as it has a bearing on both the growth and maximum size of the table.

  • Disk Space (MB)

    This indicates the physical file information for the database selected.

The bottom pane of the dashboard displays information found during the inspection and repair process, and relates to the following:

  • Tables
  • Columns
  • Indexes
  • Foreign Keys
  • Object Dependencies
  • Index Fragmentation
  • SQL Users

The following checks are performed against the company databases' tables.

Results are displayed in the Tables pane:

Checks on custom form tables are included.
Check/Message Definition Repairable?

Standard table missing

A table defined in the data dictionary does not exist in the database.

Yes

Standard custom form table missing

A definition for custom form information exists, however the table is not in the database.

Yes

User defined table

A table exists in the database that is not defined in the data dictionary.

No

Informational only

User defined custom form table

A table ending in “+“ exists and this is not a standard custom form table.

No

Informational only

The following checks are performed against the company databases' columns.

Results are displayed in the Columns pane:

Checks on columns against custom forms are included.
Check/Message Definition Repairable?

Standard column missing

A column defined in the data dictionary is missing from the SYSPRO table.

Yes

Standard custom form column missing

A custom form column is missing from the table.

Yes

User defined column

A user defined column exists in a standard SYSPRO table.

No

Informational only

User defined custom form column

A user defined column exists in the custom form table.

No

Informational only

Column definition does not match SYSPRO definition

A column's definition (type, size, collation, default, nullable) does not match what is expected.

Yes

Custom form column definition does not match SYSPRO definition

A custom form column's definition (type, size, collation, default, nullable) does not match what is expected.

Yes

User defined column not nullable and has no default constraint

A user defined column in a standard table does not have a default and is not nullable.

This will cause issues when trying to insert a row into the table.

Yes

User defined custom form column not nullable and has no default constraint

A user defined column in a custom form table does not have a default and is not nullable.

This will cause issues when trying to insert a row into the table.

Yes

The following checks are performed against the company databases' indexes.

Results are displayed in the Indexes pane:

Checks on custom form table indexes are included.
Check/Message Definition Repairable?

Standard index missing

A missing index can lead to performance issues.

If the missing index is the primary key, then this can lead to loss of data integrity.

Yes

Standard custom form index missing

A missing index can lead to performance issues.

If the missing index is the primary key, then this can lead to loss of data integrity.

Yes

User defined index

User defined index exists on SYSPRO table.

No

Informational only

User defined custom form index

User defined index exists on custom form table.

No

Informational only

Index column mismatch

The index does not contain the columns SYSPRO expects and can lead to performance issues.

Yes

Custom form index column mismatch

The index does not contain the columns SYSPRO expects and can lead to performance issues.

Yes

Index defined against incorrect table

This occurs when an existing table is renamed without deleting the indexes, etc. and will prevent the creation of the index against the correct table.

Yes

Custom form index defined against incorrect table

This occurs when an existing table is renamed without deleting the indexes, etc. and will prevent the creation of the index against the correct table.

Yes

User defined primary key

A standard table has a primary key defined that is not what is expected by SYSPRO.

This can and will lead to data integrity issues.

Yes

User defined custom form primary key

A standard table has a primary key defined that is not what is expected by SYSPRO.

This can and will lead to data integrity issues.

Yes

The following checks on foreign keys are performed against the company databases.

Results are displayed in the Foreign Keys pane:

Check/Message Definition Repairable?

Standard foreign key missing

A foreign key linking standard SYSPRO tables is missing.

Yes

User defined foreign key

A user defined foreign key has been defined against a SYSPRO table and has been created with the NOCHECK directive.

No

Informational only

Standard foreign key is enabled

A standard foreign key has been enabled. SYSPRO uses foreign keys created with the NOCHECK directive, which means that the foreign key is not used for data integrity purposes as SYSPRO performs its own data integrity verification.

Yes

User defined foreign key is enabled

A user defined foreign key has been defined against a SYSPRO table.

We strongly discourage you from adding your own Foreign Keys to a SYSPRO database (especially without the NOCHECK directive) as this could cause SYSPRO applications to fail unexpectedly.

No

Foreign key defined against incorrect table

A named foreign key exists against the incorrect table.

This will prevent the creation of the same key against the correct tables.

Yes

Foreign key column mismatch

A foreign key's definition does not match what is expected by SYSPRO.

Yes

After the program completes its checks on the databases, the following list is returned containing all user defined objects (e.g. views, stored procedures, etc.) that are dependent on both standard SYSPRO and custom form tables and columns:

This is for informational purposes and no repair functionality is available or applied.
Column Detail

Table

This indicates the SYSPRO table.

Column

This indicates the SYSPRO column.

Schema

This indicates the schema in which the object is defined.

Name

This indicates the name of the object.

Object Type

This indicates the type of object (e.g. View, Stored procedure, Trigger).

Selected

This indicates whether the column is selected (e.g. SELECT StockCode).

Selected all

This indicates whether the column is selected using a wildcard (e.g. SELECT *).

Updated

This indicates that the column is updated by the object and is flagged as a warning.

We strongly advise against updating SYSPRO data outside of the software, as any change may not follow the business rules associated with the data.
Trigger Enabled

If triggers are defined against SYSPRO objects then this column indicates if the trigger is active.

Because index fragmentation and out-of-date statistics can have a significant effect on performance, this pane displays the following:

  • Indexes in SYSPRO that are fragmented by more than 5 percent
  • Indexes' statistics that have not been updated in the last 30 days
Information on user defined indexes is not displayed.

No repair function is available, as we recommend that the rebuilding or reorganization of an index (as well as updating statistics) is performed when the database is not in use.

Column Detail

Table

This iIndicates the SYSPRO table.

Index

This indicates the SYSPRO index.

Fragmentation %

This indicates the percentage of fragmentation.

Page Count

This indicates the number of pages occupied by the index.

If this is a low count then it is possible that SQL will not rebuild or reorganize the index.

Rows in Table

This indicates the number of rows in the table.

If this is a low count then it is possible that SQL will not rebuild or reorganize the index.

Statistics updated

This indicates the date and time that the statistics for this index were last updated.

SQL automatically updates these depending on usage. If the date and time is set as 1900-01-01 0:00:00.00 then the statistics have never been updated.

Suggested function

This indicates the function to run, as suggested by Microsoft.

Rebuilding the index is suggested when the fragmentation is more than 30%.

Reorganizing the index is suggested when the fragmentation is between 5% and 30%.

This action is suggested when the fragmentation is less than 5%.

This also indicates that the statistics are older than 30 days and you should consider updating them.

After the program completes its checks on the databases, the following list is returned detailing all users that have access to the database, as well as their permission levels:

Column

Detail

User

This indicates the user defined against the database.

Any sysadmin logins will be returned as blank due to them having full access to the database and not requiring a link to a specific user.

Login

This indicates the login to which the user is linked.

Type

This indicates the type of login (e.g. WINDOWS_LOGIN or SQL_LOGIN)
Disabled  

Permissions

This indicates the user's permissions as a comma-separated list (e.g. db_datareader, db_datawriter, sysadmin)

This pane is automatically hidden and can be accessed from the right-hand side of the screen.

The dashboard displays the following information regarding the SQL instance used by SYSPRO 8:

  • SQL instance name

  • SQL version

  • Default collation for the SQL instance

  • Minimum memory allocated to SQL

  • Maximum memory allocated to SQL

    A warning icon is displayed against this field if the SQL instance is set with no memory cap (i.e. unlimited maximum memory).

  • Number of logical processors (CPU)

  • Amount of physical memory on the server

  • Date and time SQL started

  • Connection time (for the SQL administrator login to connect)

    A warning icon is displayed against this field if the connection takes more than 0.05 seconds