CodePlex Project Functional Specification - Central Management System
| Original Author || Buck Woody |
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
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:
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. |
server Information from MAPS Example:
Database Growth Example:
System Details Example:
Policy Details Example: