Microsoft SQL Server 2008 R2 Administration Cookbook

Over 70 practical recipes for administering a high-performance SQL Server 2008 R2 system with this book and eBook        

Implementing Central Management feature enhancements

Central Management Server (CMS) is a feature that enables the DBA to administer multiple servers by designating a set of server groups, which maintain the connection information for one or more instances of SQL Server. In this recipe, we will go through the implementation strategy for the CMS feature in your SQL Server data platform. Before we continue into the recipe, it is essential for you to understand the core functionality of this feature, which uses the multiple features from the SQL Server core database engine:

• SQL Server system repository
• Manage the SQL Server farm from one location
• Easy reporting about system state, configuration, and performance

Getting ready

As per the feature characteristics described previously, there are three important components in the system. These components are:

• Storage
• Execution
• Reporting

Storage is used to store the system repository, Execution manages the SQL Server instances, and Reporting shows the statistical information.

The CMS feature implementation is a dual-process (setup and configuration) making use of SQL Server components. The prerequisite of services is to install SQL Server 2008 R2 and Reporting Services. To support the extensive features of the core database engine Enterprise Edition, it is recommended that the CMS be a single point of resource to manage the multiple instances of SQL Server. However, for cost purposes, you can still make use of the Standard Edition.

CMS is introduced in SQL Server 2008, as earlier versions of SQL Server cannot be designated as a Central Management Server. The main characteristic of CMS is to execute TSQL and Policy-based management policies at the same time against these designated server groups.

How to do it...

To get started, we will need to complete the following steps to implement the central management server feature to the existing SQL Server data platform:

1. SQL Server Management Studio is the main tool to designate the CMS feature. Open SQL Server Management Studio, on the View menu, click Registered Servers.
2. In Registered Servers, expand Database Engine, right-click Central Management Servers, point to New, and then click Central Management Servers.
3. In the New Server Registration dialog box, register the instance of SQL Server that will be the central management server.
4. In Registered Servers, right-click the central management server, point to New, and then click New Server Group. Type a group name and description, and then click OK.
5. In Registered Servers, right-click the central management server group, and then click New Server Registration.
6. In the New Server Registration dialog box, register one or more instances of SQL Server that will be members of this server group, see the following screenshot:

   

7. After you have registered a server, the Central Management Server (CMS) will be able to execute queries against all servers in the group at the same time.

   TSQL statements and Policy-based management policies can be executed at the same time against the server groups; CMS maintains the connection information for one of more instances of SQL Server. It is essential to consider the effective permissions on the servers in the server groups, which might vary; hence, it is best to use Windows Authentication in this case.

8. To enable the single-point of resource to execute a single query on multiple CMS servers, it is easier to access the registration properties of a group of servers. Using that specific server group, there are multiple options of new query, object explorer, evaluate policies, and import policies.

   The CMS feature is a powerful manageability feature and is easy to set up. However, it is potentially dangerous to implement a policy that may not be suitable for a set of server groups.

9. Therefore, it is best to represent the CMS query editor with a specific color that can easily catch our eye to avoid any 'unintentional execution of queries or policies'. To enable such a representation follow these steps:
   1. Open SQL Server Management Studio, on the Tools menu, click on Options.
   2. In Options, select Editor Tab and Status Bar.
   3. Select the Status Bar Layout and Colors section from the right-hand side of the options window.
   4. In the following screenshot, we can see that we have selected the Pink color, which highlights that extra care is needed before executing any queries, as shown in the next screenshot:

Now, let us evaluate how to create a CMS server group, evaluate policies, and execute queries on multiple servers.

1. Open SQL Server Management Studio; on the View menu, click on Registered Servers, expand Database Engine, right-click Central Management Servers, point to New, and then click Central Management Servers.
2. In the New Server Registration dialog box, register the instance of SQL Server that will be the Central Management Server.
3. In Registered Servers, right-click on Central Management Server. Point to New and click on New Server Group. Now type a group name and description, for instance, UAT or Production and click OK. See the following screenshot:

   

Unlock access to the largest independent learning library in Tech for FREE!
Get unlimited access to 7500+ expert-authored eBooks and video courses covering every tech area you can think of.
Renews at €18.99/month. Cancel anytime
START FREE TRIAL
4. To register the servers to these individual server groups, in Registered Servers right-click on Central Management Server Group and then click New Server Registration.
5. In the New Server Registration dialog box, register one or multiple instances that are applicable to the relevant server groups, such as Development or Production.

As a best practice, it is ideal to create individual groups within CMS to enable a single-query execution against multiple servers as in the following screenshot:

SQL Server 2008 R2 provides a 'Policy Based Management framework' that consists of multiple sets of policy files that you can import as best practice policies. You can then evaluate the policies against a target set that includes instances, instance objects, databases, or database objects. You can evaluate policies manually, set policies to evaluate a target set according to a schedule, or set policies to evaluate a target set according to an event.

To evaluate a policy complete the following steps:

1. To evaluate the policy against multiple configuration targets in the SQL Server Management Studio, click on the View menu to select Registered Servers and expand the Central Management Server.
2. Right-click on the Production server group and click on Evaluate Policies.
3. The Evaluate Policies screen will be presented as shown in the next screenshot.
4. Click on Source to Choose Source.
5. The relevant best practices policies are stored as XML files under %:Program FilesMicrosoft SQL Server100ToolsPoliciesDatabaseEngine1033 directory, which is shown in the next screenshot:

   For this recipe, we have chosen to select the policy from SQL Server 2008 R2 Best Practices Analyzer that is downloaded from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591 and installed on the server.

   

Let us evaluate the policy of Backup and Data File Location against the production instances. This policy will perform the steps to:

• Check if the database and the backups are on separate backup devices. If they are on the same backup device, and the device that contains the database fails, your backups will be unavailable.
• Putting the data and backups on separate devices optimizes the I/O performance for both the production use of the database and writing the backups, as shown in the following screenshot. (This is one of the Best Practices of Maintenance policies from Microsoft SQL Server 2008 R2 Best Practices Analyzer.)

  

The ease of executing a single query against multiple-servers at the same time is one of my favorite manageability features. The results that are returned with this query can be combined into a single results pane, or in a separate results pane, which can include additional columns for the server name and the login that is used by the query on each server. To accomplish this task, let us execute a query to obtain the Product Version, Product Level, Edition, and EngineEdition that are registered against the Production server group.

1. In SQL Server Management Studio, on the View menu, click Registered Servers.
2. Expand a Central Management Server, right-click a server group, point to Connect, and then click New Query.
3. In Query Editor, execute the following TSQL statement:

   SELECT
   SERVERPROPERTY('ProductVersion') AS ProductVersion,
   SERVERPROPERTY('ProductLevel') AS ProductLevel,
   SERVERPROPERTY('Edition') AS Edition,
   SERVERPROPERTY('EngineEdition') AS EngineEdition;
   GO

   
   

   
   The results are displayed as follows:

   

4. From the preceding screenshot, the results with a pink bar at the bottom state that this is a Production group. To change the multi-server results options, in the Management Studio, on the Tools menu, click Options.
5. Expand the Query Results, expand SQL Server, and then click Multi-server Results.
6. On the Multi-server Results page, specify the option settings that you want, and then click OK.

We now should have successfully completed the CMS feature implementation into the SQL Server data platform.

How it works...

The SQL Server instance that is designated as a Central Management Server maintains server groups, which maintain the connection information for one or more instances of SQL Server. CMS and all subsidiary servers on the network are registered using Windows Authentication only; in case local server groups are registered using Windows Authentication and SQL Server Authentication.

The storage for centralized management server groups, connection information, and authentication details are available from themsdb system database in:

• dbo.sysmanagement_shared_registered_servers_internal
• dbo.sysmanagement_shared_server_groups_internal system tables
• dbo.sysmanagement_shared_registered_servers
• dbo.sysmanagement_shared_server_groups system views

The user and permissions information on the registered servers may vary based on the user permission that uses Windows Authentication connection. For instance, in our recipe, on the dbia-ssqaSQL2K8 instance, the login is a member of the SYSADMIN fixed server role and on the DBIA-SSQASSQA instance, the login is a member of the DBCREATOR fixed server role. In this case, we may not be able to run certain SYSADMIN privileged statements on DBIA-SSQASSQA instance.

There's more...

Using SQL Server 2008 R2 Management Studio with CMS, we can manage the lower SQL Server version instances, such as 2005 (90), 2000 (80), and 7.0 (70), which is truly a central management of instances.