The Management Data Warehouse (MDW) is a component in SQL Server that allows users to collect information from different servers for further analysis. While the MDW solution is widely used and easy to set up, removing it from your SQL Server environment can be a challenge.
Structure of the MDW
After creating the MDW, several jobs are created to collect and upload data. These jobs include:
- mdw_purge_data_[MDW]
- collection_set_1_noncached_collect_and_upload
- collection_set_2_collection
- collection_set_2_upload
- collection_set_3_collection
- collection_set_3_upload
In SQL Server 2012, three additional jobs were added:
- sysutility_get_cache_tables_data_into_aggregate_tables_daily
- sysutility_get_cache_tables_data_into_aggregate_tables_hourly
- sysutility_get_views_data_into_cache_tables
Removing the MDW
Removing the MDW jobs intuitively by stopping the collection sets and disabling the data collector may not be enough. The MSDB table system has constraints that prevent the deletion of these jobs.
In SQL Server 2012, Microsoft introduced a stored procedure called “sp_syscollector_cleanup_collector” to assist in removing the MDW components. This stored procedure disables constraints, deletes the data collector jobs, and enables the constraints back. However, it does not remove all components created by the MDW, and the “mdw_purge_data” job and the “sysutility” jobs need to be manually excluded.
It is important to note that existing schedules in SQL Server can also be excluded, but it is not recommended as they are necessary for the future operation of the MDW if you decide to re-enable it.
Conclusion
Removing the Management Data Warehouse in SQL Server is not a straightforward process. While there is a script available to assist in removing some components, there are still issues that can be overlooked, such as logins and users. It is important to carefully review and plan the removal process to ensure all components are properly removed.