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 Microsoft SQL Server 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

  • Connection string configuration and encryption 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.

(also known as Data Encryption at Rest)

Transparent Data Encryption (TDE) encrypts Microsoft SQL Server, Azure SQL Database, and Azure Synapse Analytics (SQL DW) data files.

Data Encryption at Rest describes the technique of configuring Microsoft SQL Server so that the physical database files stored on the Windows file system are encrypted.

This ensures that, in the event of a network or other security breach, information remains secure even if someone is able to access the physical database data or log files (or a backup of these files).

(also known as Data Encryption in Motion)

This is a cryptographic protocol designed to provide communications security over a computer network.

Data Encryption in Motion describes the technique of configuring SYSPRO and Microsoft SQL Server so that all communication between the two is encrypted. Data encryption can therefore be enabled from the client, instead of only controlling encryption within the Microsoft SQL Server instance.

The following data is encrypted when using this type of configuration:

  • Initial connection information
  • SQL statements issued
  • Actual data passed to and from Microsoft SQL Server

Data Encryption in Motion ensures that eavesdroppers and hackers can't see what's transmitted - particularly useful for private and sensitive information, but also for all information sent between SYSPRO and Microsoft SQL Server.

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 programs by assigning them to groups and applying access control against the group (configured using the Operator Groups program).

  • The SQL Health Dashboard only verifies system and company databases.

  • 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.

SYSPRO works effectively with all supported versions of Microsoft SQL Server, however you should consider using the latest SQL version in order to leverage the latest optimizations.

View the SYSPRO 8 - Supported Platforms topic for a list of all Microsoft SQL Server versions supported.

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

View the following technical article for more in-depth information about database architecture and how to configure Microsoft SQL Server to work effectively with SYSPRO:

Database Architecture

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

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.

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.

The following SQL Server users must exist with the appropriate permissions enabled:

User type Rules

Administration SQL user

The user entered here must have the following SQL permissions assigned to it:

  • db_creator

    This is used to create and drop entities, such as databases, tables, columns, etc.

  • bulkadmin

    This is used to issue BULK INSERT statements.

  • securityadmin

    This is used to give database-level permissions as and when needed.

  • processadmin

    This is used to end SQL processes running on the server using the View Users program.

  • db_owner

    This needs to be set for both system-wide and company databases.

The user cannot be the same account as the Standard SQL user and the User name cannot contain any spaces.

Standard SQL user

The user entered here must have the following SQL permissions enabled for both system-wide and company databases:

  • db_datareader
  • db_datawriter

The user cannot be the same account as the Administrative SQL user and the User name cannot contain any spaces.

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.

The following checks are performed against the company databases' tables and the 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 and the 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 and the 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 and the 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

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.

Field

Description

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.

TDE encryption status

This indicates if Transparent Data Encryption (TDE) is in use and the current status of the encryption.

Change data capture enabled

This indicates if change data capture (CDC) is enabled for the database.

CDC records activity (i.e. insert, update, and delete) that is applied to a Microsoft SQL Server table.

Tracking database changes using Change Data Capture (CDC) can have a significant overhead as any changes against tables marked to be included in CDC are output to tracking tables whenever there is free time.

Change tracking enabled

This indicates if Change Tracking is enabled for the database.

Change Tracking provides an efficient change tracking mechanism for applications. The main difference between Change Tracking and CDC is that CDC tracks each and every change, whereas Change Tracking only tracks the net change in a row.

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

  • 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 (MB)

    This indicates the maximum size allowed.

  • Used (MB)

    This indicates the space currently being used.

  • Free (MB)

    This indicates the free space available in the file.

  • Free %

    This indicates free space available in the file as a percentage.

    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

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 indicates 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 Microsoft SQL Server instance used by SYSPRO 8:

  • Server name

  • OS Version (Operating System)

  • Instance name

  • SQL version

  • Default collation for the Microsoft SQL Server instance

  • Minimum memory allocated to Microsoft SQL Server

  • Maximum memory allocated to Microsoft SQL Server

    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

  • Connection driver

    This indicates the ODBC driver used when connecting to Microsoft SQL Server.

  • Connection encrypted

    This indicates whether the connection to Microsoft SQL Server is encrypted.

  • Connection protocol

    This indicates the protocol used when connecting to Microsoft SQL Server (i.e. TCP, Named pipes or Shared memory).

  • Instant file initialization

    In Microsoft SQL Server, instant file initialization allows for fast execution of file operations and reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files.

    We recommend that you enable this option to ensure optimal performance for the growth of SQL tables on disk. A warning icon is displayed if it is not selected.

    This option is only applicable when using Microsoft SQL Server 2016 and above.