SQL server 2008 (Katmai) Declarative Management Framework (DMgF) - enforce in multi-server environment
SQL Server 2008 brought new feature that is Declarative Management Framework (DMgF, we cannot name it as DMF as it stands Dynamic Management Functions from SQL 2005). This is a policy based system to manage a SQL Server instance, using SSMS you can create policies to manage entities on that SQL instance. This is more useful or power to DBA for an effective management of server instance, databases and objects too. The 3 main components for this DM framework are Policy Management that is helpful to create policies by setting a condition and a target set that can be a server or database or database object. Explicit administration to select one or more managed targets,to check whether that comply with a specific policy or set of policies. Lastly, Automated Administration for execution by using modes:
- Enforce. This uses DDL triggers to prevent policy violations.
- Check on Changes. This uses event notification to evaluate a policy when a relevant change occurs.
- Check on Schedule. This uses a SQL Server Agent job to periodically evaluate a policy.
Dan Jones explained more about DMgF in this case to explain how it works.
To finish up this blog I would ike to highlight enforcing similar DMgF within a multi-server envrionment.
To enforce between database instances, you need to extract the state of DMgF and store it to a file. BOL documented that SQL Server includes several predefined facets. For example, the Surface Area facet that defines, as properties, the features that are off by default. In this scenario, when you manage many similar database instances or databases, you can configure a facet on one instance or database, copy the state of the facet to a file, and then import that file into the same or another database instance as a policy. When the state has been converted to a policy, the policy can be applied to the database instance or to database objects. To enforce, on the target instance of the Database Engine, select the policy file, and then by using the Check execution mode, determine whether the Database Engine meets the policy. To do this open up SSMS on target instance choose object explorer to right-click a server instance, a database, or a database object, point to Policies, and then click View, finally choose Click to run that hyperlink.
Similarly you can also export the policy from the instance of the Database Engine as an XML file, simply then import the policy from that saved XML file.
As SQL 2008 is still in CTP3 (first public), we can expect more changes to these policies for more effect & power to DBA in order to control the unwanted or unwarranted objects within the database including server instances.