I would like to spend some time discussing one of the wonderful multi-server management features built into SQL Server that can help DBAs manage a wide and deep environment typically associated with SAP. This blog post is dedicated to discussing the Central Management Server feature that was introduced in SQL Server 2008 version. The discussion in this topic is applicable to SQL Server 2008, 2008 R2 and 2012 (screenshots included in this post are captured on a server running SQL Server 2012)
As a DBA managing an SAP landscape, one of the biggest challenges you face is keeping all servers in sync with different policies, changes, software updates and patches. Its all too common to find that an sp_configure setting such as max degree of parallelism has been modified inadvertently on one of the servers. Such small changes could lead to confusion, performance issues and ultimately end-user dissatisfaction. Fortunately all versions of SQL Server including and beyond SQL Server 2008 provide several features that simplify the often frustrating task of managing multiple servers through a single SQL Server Management Studio (SSMS) window.
What is a Central Management Server?
Central Management Server (CMS) was introduced in SQL Server 2008 as part of management feature enhancement. This feature simplifies the task of managing and monitoring multiple SQL Server instances installed on different physical servers. CMS is implemented as a set of server groups in SSMS. The biggest benefit of this functionality is that a single TSQL statement (with some restrictions) can be executed on all registered servers within a server group in a single execution. The results of such execution is presented as a single result set, which further simplifies the collection and analysis of this data.
What is a Server Group?
A server group is a group of registered SQL Server instances that typically require similar management parameters such as configuration settings, scheduled tasks etc. A single SQL Server instance can be added to multiple server groups. This provide DBA the opportunity to decide how to categorize and customize the servers groups. Most commonly deployed server groups may be based on the SQL version (SQL 2008 R2, SQL 2012), geographies where the servers may be placed (eg. Local Datacenter, Backup Datacenter), purpose the servers serve (eg. Development, Staging, Production) etc. In a nutshell, server groups should be created to facilitate the management task and should align with any grouping policy used by the enterprise or the DBA.
As mentioned earlier a single SQL Server instance could be a part of multiple server groups. In the examples demonstrated in this blog, I use a single Server Group, however I would encourage you to group your servers into multiple server groups based on the management criteria that best suits your needs.
How do I setup Central Management Server?
CMS is typically installed on a single SQL Server instance that is used to manage the entire SQL Server presence in the enterprise. Ideally a non-production instance of SQL Server should be used as CMS. Hence the first step to setting up a CMS is to decide which server will play this role in your SAP landscape. CMS metadata is stored in MSDB (table names begin with sysmanagement_shared prefix). This means that the information related to CMS can be backed up using the regular backup/recovery process that includes the MSDB database.
Follow the steps in this Books Online topic to create a new CMS.
Step 4 in this topic walks through the creation of a Server Group. Repeat this setup multiple time, once for each of the groups you would like to setup.
Steps 5 and 6 in the process walk through registering servers under the Server Group created in step 4. Repeat steps 5 and 6 multiple times once for each of the servers you would like to register under each of the groups.
At the end of the setup process, you will have a server structure that is grouped by server groups. Server group now becomes the atomic unit of TSQL command execution thereby facilitating server management through a single SSMS window.
Users who will be managing the CMS structure should be member of the ServerGroupAdministratorRole role in the MSDB database. It is highly recommended that Windows Authentication be used to connect to the servers being managed through CMS. This choice is made when setting up servers in the CMS structure in the previous section. If Windows Authentication is used to configure security for registered CMS servers, login credentials for the logged in user are used to connect to each of the CMS targets.
How can I leverage Central Management Server to manage a SAP landscape effectively?
Once the CMS is setup, managing the target servers becomes an easy task. Commands such as the one required to check server collation can be executed on all servers in a group with single execution. This capability allows an administrator to perform auditing and standardization tasks across multiple SQL Server instances, installed on multiple machines with a single command. Following are some example of how to undertake such tasks.
Note: Screenshots in the sections below are captured in a lab environment in order to demonstrate the capability of the Central Management Server functionality and are not representative of a production environment.
Scenario 1 - Checking to ensure Max Degree of Parallelism is set to 1 across all servers in the SAP landscape.
In this scenario, an administrator can quickly check to ensure that the sp_configure setting is set correctly to 1.
Scenario 2 - Monitor the database properties and ensure recovery model and page verify options are set correctly
The following screenshot demonstrates how an administrator can utilize the CMS functionality to monitor the database properties for databases on all servers registered under the CMS.
Scenario 3 - Ensuring all necessary trace flags are enabled on each server.
In this scenario, an administrator ensures that the trace flags recommended for SAP are turned on each of the servers in the SAP landscape.
Scenario 1 - Setting the Max Degree of Parallelism.
In this scenario an administrator can use CMS to set Max Degree of Parallelism for all servers to a specific number. For SAP non-BI systems, in general the recommendation is to keep Max Degree of Parallelism at1. The following code sets the Max Degree setting to 1 for all servers in the Central Management Server group.
Central Management Server provides a launch-pad for an administrator to standardize configurations in environments where there are more SQL Server than can be managed manually. The next step in this standardization journey is to explore SQL Server's Policy Based Management feature. For more information on how to use Policy Based Management in SAP environments, please review the blog post at this location.
Central Management Server presents a quick and easy method to manage multiple instances of SQL Server from a single SQL Server Management Studio console. It provides the capabilities that a DBA would need when faced with the responsibility of maintaining a standard configuration across an environment with many SQL Server instances such as managing an SAP landscape. As illustrated by the content and examples above, this management task can be simplified by planning and implementing Central Management Server. I would highly encourage DBAs managing SAP environments to explore this feature and use it to simplify and streamline their daily tasks.
I would like to thank the following colleagues for their valuable feedback associated with the content presented in this blog.
SQL Server 2012 MSDN Books Online
SAP on SQL Server Blog