DBAs are typically cautious of what they allow to run on their production environment. It goes without saying that this is the best practice that enforces tighter security. After all, it is the DBA’s responsibility that the database or SQL Server instance is not compromised nor its efficiency reduced.
Nonetheless, a total lockdown of the environment is not in anyone’s best interest since there are tools out there that can increase SQL Server instance efficiency and overall management. Knowing what components are being installed with the tool under the hood makes a world of difference in terms of deliberation whether to install the tool or not.
General key points when deciding to install a tool on the production environment are:
- Don’t install tools from unknown sources
- Make informed decisions based on valid information from official sources
In short, apart from installing a CRD on SQL Server, ApexSQL Manage doesn’t install additional software nor tamper with the SQL Server instance except when the user decides to manually run operations from the tool. The tool is mainly focused at the server level, e.g. SQL Server health, configuration, and security without diving deeper into the contents of databases and tables.
ApexSQL Manage comes in two parts, the GUI and the Agent which relays tasks from the GUI. As such a Central Repository Database (CRD) is required to be installed on the SQL Server itself. This installation is fully customizable in terms of letting the user decide on which SQL instance to install the CRD.
The purpose of this article is to dive deeper into ApexSQL Manage CRD and address the tables which are created with the database.
ApexSQL Manage repository database
The tool uses the Central Repository Database to store information exchanged between the user interface and the ApexSQL Manage Agent. Data stored in the database is used for scheduled or manual operations. Only one Central Repository Database is required for storing all information regardless of the number of added SQL instances.
Tables in the CRD are divided to store information specific to the operation they belong to:
- dbo.agentSQLs – stores data from the ApexSQL Manage Agent
- dbo.alertSQLs – stores information from the configured alerts
- dbo.application_dataSQL – stores information of previously executed operations in the application
- dbo.consoleSQLs – currently dbo.consoleSQLs doesn’t store any information
- dbo.emailconfigurationSQLs – stores email configuration
- dbo.health_checkSQL – stores information of latest health check for all added SQL instances
- dbo.job_infoSQL – stores information regarding created schedules and their operations
- dbo.scheduleSQLs – currently, this table doesn’t store any information
- dbo.serverSQLs – stores general information of the added SQL instances e.g. databases and server roles (doesn’t store data from the databases nor login credentials)
- dbo.versionSQLs – stores data regarding the tool and CRD version
It is important to note that all information stored in ApexSQL Manage Central Repository Database is encrypted to prevent security issues.
For more information on ApexSQL Manage permissions and requirements please consult the ApexSQL Manage – Permissions and requirements article.