Policies in RockSolid is a hierarchical structure in which operational management settings relating to your database environment are defined. The collective set of relevant settings applied to each instance and/or database form the "policy" which affects how entities are monitored, analysed, managed and maintained.
The two key policy hierarchies are applied depending on the object type, and are as follows:
- The RockSolid Instance Policy Hierarchy. This is applied at the level of the SQL Server installation, and affects general configuration, patching, security, monitoring, analytics and default database settings. This policy hierarchy applies to all instance types including SQL Server database engine, analysis services, reporting services and integration services.
- The RockSolid Database Policy Hierarchy. This is applied at the level of the SQL Server database and affects database configuration, security, monitoring, analytics and specific database settings. This policy hierarchy applies to both SQL Server database engine and SQL Server Analysis Services databases types.
An instance or database inherits the "flattened" combined policy of all the relevant level in the policy tree. In all cases a more specific setting (defined at a lower level in the tree) overrides a definition for the same setting higher in the tree. If a setting is "Not Set" at the instance or database level, it inherits the value from the lowest level in the relevant hierarchy where it is defined as either ON or OFF.
A policy control value can be in one of three possible states:
- ON - The policy control value is turned on.
- OFF - The policy control value is turned off.
- NOT SET/INHERIT - The policy control value is not set at this level in the hierarchy and will inherit from the lowest level where this is defined. If that policy control value is not defined anywhere in the policy tree then the policy setting is ignored by RockSolid and no action is taken by RockSolid as a result.
Conflicting Policies at the Different Hierarchy Level
If a policy is defined as either ON or OFF are a higher level in the policy and defined as the inverse at the lower level in the policy then the lower level definition always takes precedent.
A database administrator defines the database AUTO UPDATE STATISTICS option to be ON in their "Production" database bucket. However for a specific database they wish this to be disabled, so within the database policy for this database they set AUTO UPDATE STATISTICS to OFF. As the database level is lower level in the policy hierarchy than the database bucket level, the database level policy value is used and AUTO UPDATE STATISTICS is set to OFF.
Conflicting Policies at the Same Hierarchy Level
Instance Group Policy Hierarchy
An instance can only be in a single Instance Group. This must be manually set in the instance definition by a RockSolid user. As there is only a single instance group allowed, not possibility of conflict occurs.
Database Bucket Policy Hierarchy
A database can only be in a single database bucket. This can be achieved by specifically placing the database in a specific bucket, or routing a database to the database bucket using a routing criteria.
If the database is placed in the database bucket specifically this policy will be used and all routing rules ignored. However if routing rules are used then the order of the routing rules in RockSolid is important. The first database bucket, in order specified by the user, that matches successfully for a given database will be used and no further routing will occur.
Database Overlay Policy Hierarchy
While a database can only be in one bucket, multiple Database Bucket Overlays can be applied. These allow users to modify settings defined in the database bucket based on a setting of grouping rules. When multiple overlays are applied, the following logic is used to resolve conflicts.
If multiple overlays are applied which do not modify the same policy control value, then no conflict occurs. Each overlay will apply each defined policy control value to the policy tree.
If multiple overlays are applied which modify the same policy control value, then a conflict occurs. In this situation the order of the overlays as defined within the RockSolid console occurs. The first matching overlay is used in this situation and all other overlay modifications for that policy control value are ignored.
A database administrator defines a "SIMPLE RECOVERY MODEL" overlay which changes the Recovery Model setting to SIMPLE for affected databases. In addition they define a second overlay, "NO BACKUPS" which disables all checks for backups within their environment. They create an overlay rule which matches databases named "ReportServerTempDB" to the "SIMPLE RECOVERY MODEL" overlay and a second overlay rule which matches "ReportServerTempDB" to the "NO BACKUPS".
In this situation RockSolid starts with the Database Bucket configuration and applies the "SIMPLE RECOVERY MODEL" overlay which modifies the Recovery Model policy control value to SIMPLE. Then RockSolid applies the "NO BACKUPS" overlay which disables checks for backups. In this situation there is no conflict and both overlays are applied to the resulting policy.
A database administrator defines a "SIMPLE RECOVERY MODEL" overlay which changes the Recovery Model setting to SIMPLE for affected databases. In addition they define a second overlay called, "FULL BACKUPS with TLOG" which sets the recovery model to FULL and implements full and transaction log backups. They create an overlay rule which matches databases named "MyProd" to the "SIMPLE RECOVERY MODEL" overlay and a second overlay rule which matches "MyProd" to the "FULL BACKUPS with TLOG".
In this situation RockSolid starts with the Database Bucket configuration and applies the "SIMPLE RECOVERY MODEL" overlay which modifies the Recovery Model policy control value to SIMPLE. Then RockSolid attempts to apply the "FULL BACKUPS with TLOG". As the recovery model option has already been applied by a prior overlay, this option is ignored and not applied. However other policy control options specified in the "FULL BACKUPS with TLOG" overlay are applied. The resulting policy is a combination of the full application of the "SIMPLE RECOVERY MODEL" overlay and a partial application of the "FULL BACKUPS with TLOG" overlay.
In order to keep structured management simple it is advised to minimise the possibility of conflicts at the overlay level. To do this it is advised to keep overlays functionally separate e.g. "SIMPLE RECOVERY MODEL" and "NO BACKUPS", and apply multiple overlays to a given database which require both overlay settings.