In SQL Server 2008 the ability to check and configure multiple SQL Server systems with a functionality called Policy Based Management got introduced. The purpose of those Policies is to define basic checks and configurations which are common to all the SQL Server systems or groups of instances. This Policies then can be applied against the group of SQL Server systems by manually executing the Policies or scheduling the execution of the Policies. Policies are not only covering the push out of configurations, but in a first step detecting the compliancy with the Policies set. Hence systems can be checked on a regular basis on whether they still are compliant with the Configuration Policies set for those systems. For more details, see the links at the end of this article. As of SAP Basis release 720 you even can monitor the defined policies out of the SAP DBACockpit (Configuration->Non-SAP Features) :
As seen in the screenshot above, I created a set of Policies, which you can use to monitor multiple SAP systems. They reflect some of the Best Practices for SAP Systems, based on 10+ years of SAP experience and the SAP notes 879941 and 1237682. The set is not a complete set of all possible system or database setting, they should only show the way how to set up Policies for an SAP system. You can add more Policies or change the provided ones to reflect your system requirements. You can download the Policies as XML files in the attached ZIP file.
How to use the policies:
Installation
Unpack the zip file and store the XML files in a directory of your choice. Then open the SQL Server Management Studio and connect to the SQL Server, that you want to use as the managing server. This server will store the policies and will run the check and evaluations on the other servers.
Expand the server, the Management branch and the Policy Management. Right click on Policies and choose Import Policies.
Open the directory where you stored the XML files of the Policies and add all Policies to the selection (Ctrl-A).
Check the option ‘Replace duplicates with items imported’ and then click OK. This step will import all the Conditions and Policies. You now have the Policies on the managing server, in the next step we will set up the environment to control and evaluate multiple SAP systems.
Open the Registered Servers pane by choosing View –> Registered Servers and expand the ‘Database Engine’ branch. You should see something similar to
Right click on ‘Central Management Servers’ and on ‘Register Central Management Server’. Register the Server from the first step as you would do in the ‘Registered Server’ Panel:
Save the Configuration. Now right click on the newly added Management Server and register your SAP Server underneath this server (New Server Registration). I choose the SAP System ID for the identification of the system. You can set the display name in the registration screen in the ‘Registered server name’ text field. When you have finished adding SAP server, the pane should look like:
The setup of the environment is now finished.
Evaluation of Policies
To evaluate the Policies on all server underneath your managing server, right click on the Management server and choose ‘Evaluate policies’. As source choose the server, where you have imported the Policies.
After you clicked OK, the existing policies will show up in the list:
Select all and start the evaluation with ‘Evaluate’ (the option will be enabled as soon as you choose one or more Policies). Now the evaluation starts on all systems that are assigned to the managing server.
Checking Results
After the evaluation on the system has ended, you will get a list of the Policies and their status after the check. If the icon shows a green sign, the Policy was fulfilled on all servers. If it showing a red sign, one or more server do not fulfill this Policy:
You will get more information what exactly went wrong, when you click the View link on the Details column of the target details list.
When you mark the failed results on the evaluation result report and choose apply, the option will be corrected on the target systems. You can choose multiple failed Policies (for all systems) or only single systems to get this fixed. In this case, the option “Auto Create Statistics Async” will be set to the recommended value (1) on the systems.
After you applied the change, the Policies get evaluated again against the changed target(s) and they then should show up green.
With this functionality it is very easy to monitor multiple SAP systems and to keep their configuration in sync with the recommended settings.
Bonus
With a configured Management Server you can run queries on multiple servers simultaneously. For this choose the option ‘New Query’, when you right click on the managing server. A new query window opens, which is connected to all (!!) of the registered systems. The query you run in this window will run on all server and the result set will be a combination of the single result sets of the server. Lets show this with an example. We want to see the memory setting of the servers, so we run
