CodePlex Project Functional Specification - Central Management System

Original Author Buck Woody

Overview

The SQL Server Central Management System (SQLCMS) uses multiple features from SQL Server 2008 and other products for three purposes:
  1. Store SQL Server system information
  2. Manage SQL Server from one location
  3. Report system state, configuration and peformance

This project will help you design this system for yourself. It has a few scripts, some reports and most of all a set of processes you can follow to set this system up for yourself. You can choose to use the processes defined here, or alternate software, processes or methods to suit your needs. Use the "Discussions" area to suggest other ways of performing these steps.

*_Note - the example project is in the downloads section. It is not an installation as much as a guide. For a full exploration of this sample project, see the following articles at InformIT.com:

http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=316_*
http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=317_*
http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=318_*
http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=319_*
http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=321_*
http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=320_*
http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=322_*

The Components

There are three components in the system:
  1. Storage (for system metadata like configuration, state and performance)
  2. Execution (gathering the data or acting on it)
  3. Reporting (showing the data in a central location)

The following software is used in this example:

Component Software, Feature or Process Used Description Reference
Storage SQL Server 2008 Management Data Warehouse The performance gathering tool in SQL Server 2008. Created with a Wizard, it is a special SQL Server database that stores performance data from the Data Collector Feature. Books Online reference. InformIT guide on the feature.
System Discovery Microsoft Assessment and Planning Solution Accelerator (MAPS) Free software from Microsoft that can evaluate a series of systems and record the discoveries in a database. The MAPS tools official site. InformIT reference on the feature.
Execution PowerShell for SQL Server 2008 Included with SQL Server 2008, this subsystem runs operating system and database commands in a shell. Books Online reference. InformIT reference.
Execution SQL Server Master Job Server and Alert Forwarding Optional. These two features allow you to create and distribute SQL Server Jobs to multiple servers from one location, and store Alerts from SQL Server in one location. Books Online reference for Agent Master Servers. Books Online reference for Alert forwarding.
Execution SQL Server 2008 Policy Based Management SQL Server 2008 feature that compares system state to desired state. Books Online reference. InformIT guide on the feature.
Execution Enterprise Policy Management Framework CodePlex Project to automate Policy Based Management on SQL Server 2008, 2005 and 2000 systems. EPM Codeplex project.
Reporting Reporting Services and Report Builder 2.0 Reporting Services in SQL Server 2008 collects data from storage, and Report Builder 2.0 is a simple (free) tool that creates dashboard reports. Download for Report Builder 2.0. Books Online reference for Reporting Services. Books Online reference for Report Builder 2.0 Reporting Services Install and Configuration Steps.

System Setup and Configuration

The steps for setting up the system are in two parts: setup of the various components, and configuration so that they work together.
  1. Install SQL Server 2008, at least Standard Edition. Note - SQL Server Enterprise recommended since this will become a single point of failure.
  2. Install and configure Reporting Services.
  3. Install Report Builder 2.0
  4. Optional: Set the SQLCMS as a Master Server for Agent.
  5. Set up the MAPS tool, either on the same system or another. Run the evaluation per the instructions, and either copy the MAPS database or transfer the tables to the MDW database on the SQLCMS.
  6. Register the servers discovered in the previous step in the Central Management Servers feature on the SQLCMS.
  7. Install and configure the EPM Framework.
  8. Create dashboard reports to show the data. In time, various examples will be stored in the "Downloads" section of this site.

Using the System

to use the system, follow these steps:
  1. Set up your PBM policies the way you want them.
  2. Run the policies as stated in the EPM Framework.
  3. Determine the refresh rate for your system discovery.
  4. Refresh the data for your systems.
  5. Review the logs and reports daily.

Date Author Description (include reviews with reviewer lists)
04/13/2009 Buck Woody Initial Draft
04/24/2009 Buck Woody Second Draft - Added verbiage on database schemas.
04/25/2009 Buck Woody Completely rewrote document.

Screen Shots

Dashboard Example:

SQLCMS-Dashboard.jpg

server Information from MAPS Example:

SQLCMS-ServerInfo.jpg

Database Growth Example:

SQLCMS-DatabseGrowth.jpg

System Details Example:

SQLCMS-Details.jpg

Policy Details Example:

SQLCMS-Policy.jpg

Last edited Jul 28, 2009 at 8:46 PM by BuckWoody, version 19