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:- Store SQL Server system information
- Manage SQL Server from one location
- 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:- Storage (for system metadata like configuration, state and performance)
- Execution (gathering the data or acting on it)
- 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.- Install SQL Server 2008, at least Standard Edition. Note - SQL Server Enterprise recommended since this will become a single point of failure.
- Install and configure Reporting Services.
- Install Report Builder 2.0
- Optional: Set the SQLCMS as a Master Server for Agent.
- 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.
- Register the servers discovered in the previous step in the Central Management Servers feature on the SQLCMS.
- Install and configure the EPM Framework.
- 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:- Set up your PBM policies the way you want them.
- Run the policies as stated in the EPM Framework.
- Determine the refresh rate for your system discovery.
- Refresh the data for your systems.
- 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. |