Thursday, May 20, 2010

SQL SERVER MANAGEMENT DATA WAREHOUSE

    The Management Data Warehouse is intended as a centralized repository for performance data from servers across an enterprise, providing an out-of-the-box solution to performance management for SQL Server administrators who are responsible for performance and capacity management. The MDW is populated using data collectors. Three data collectors are set up as default by MDW setup wizard and further collectors can be confi gured as required. The MDW offers a scalable, customizable reporting solution that requires a minimal investment of configuration effort before providing value. The data collection solution utilizes SQL Server components such as SQL Server Agent jobs, SQL Server Integration Services to load performance data, and a relational data warehouse; and data is presented using SQL Server Reporting Services.
    The MDW is not a monitoring solution, as it has no capability to configure acceptable performance or operational thresholds and alerts; and it doesn’t provide real-time performance information (Activity Monitor provides this information). Nor does the MDW provide a centralized overview of all servers within an organization. In most medium-size and enterprise environments, it is still necessary for capacity and performance planning across the server estate, rather than on a per-server basis. However, used for the right purpose, MDW provides better insight than what was available before, as much of the valuable data available withinDMVs can be harvested and stored for mid and longterm performance trending and analysis.