The primary purpose of this document is to instruct the SQL Admin on how to run the HDB maintenance tasks without needing advanced privileges granted by SMC and how to replace the HDB service. In both cases the maintenance stored procedures will continue to run. Only thing is that without advanced privileges some of the maintenance tasks couldn’t be performed automatically and has to be manually administered by an experienced SQL Admin and these steps are mentioned in the document.
In SMC the administration model can be configured to automatic or manual.
1. Automatic (strongly recommended):
In this case the "Siemens GMS HDB Service" executes the following tasks on the HDB:
- Updating Statistics
- Reorganization of indexes when needed
- Handling of the database size: The functionality takes care that the database never expands the size of the history database
- Handling of the transaction log size: The functionality takes care that the transaction log never expands double the size of the history database (needs advanced privileges)
- Archive Database Creation for Long Term Storage (needs advanced privileges)
2. Manual (not recommended):
This is set only in specific cases wherein the customer doesn’t want the "Siemens GMS HDB Service" to run with advanced privileges or even doesn’t want the service itself. The maintenance stored procedures is able to execute all the above tasks on the HDB except the Archive Database Creation task which has to be explicitly executed manually by a SQL admin (read section “Steps to remove the advanced privileges” below for more details).
In the case of automatic administration model, Desigo CC historic database (HDB) requires a Microsoft Windows service called "Siemens GMS HDB Service" with a login on Microsoft SQL Server and some advanced privileges higher than "dbo". "Siemens GMS HDB Service" internally calls maintenance stored procedures.
Customers may want to remove these advanced privileges or even the service itself.
The first part of the document describes how the "Siemens GMS HDB Service" can be replaced by the Microsoft SQL Server Agent (SQL SA).
Please note that to remove some of the advanced privileges and to replace the HDB service, the prerequisite is to have SQL SA, which is not included in the SQL Server Express edition.
Once the HDB service is replaced, SQL SA requires the same advanced privileges that the HDB Service used to have. Most likely the SQL SA account will have these privileges but, customers may not want to execute HDB procedures with this account.
For this reason, the second part of this document describes how and what to do in order to run the HDB service or SQL SA without advanced privileges.
These additional privileges are needed only in the case of following configurations:
- HDB with Long Term Storage activated. In this case “CREATE ANY DATABASE” advance privilege is required to create and mount the archive databases.
- HDB with recovery model "Full". In this case “VIEW SERVER STATE” advance privilege is required to check the fill level of transaction log file.
If the HDB doesn’t have Long Term Storage activated and have recovery model "Simple" then the advanced privileges can be just removed without having to do any further steps (read section “Steps to remove the advanced privileges” below for more details).