Microsoft’s Management Data Warehouse
Management Data Warehouse Terms are here check them out first as the article will use that terminology.
Managed Instance Requirements:
- SQL Server must be version 10.50 or higher.
- The SQL Server instance type must be Database Engine.
- The SQL Server Utility must operate within a single Windows domain, or across domains with two-way trust relationships.
- The SQL Server service accounts on the UCP and all managed instances of SQL Server must have read permission to Users in Active Directory.
- The SQL Server edition must be Datacenter, Enterprise, Developer, or Enterprise Evaluation.
- We recommend that the UCP is hosted by a case-sensitive instance of SQL Server.
Capacity Planning on the UPC computer or computers if you have more than one production environment:
* Important note is that one UPC computer is limited to 25 installations of SQL server to monitor (may change in later releases)
- In a typical scenario, disk space used by the sysutility_mdw database on the UCP is approximately 2 GB per managed instance of SQL Server per year. This estimate can vary depending on the number of database and system objects collected by the managed instance. The sysutility_mdw disk space growth rate is highest during the first two days.
- In a typical scenario, disk space used by msdb on the UCP is approximately 20 MB per managed instance of SQL Server. Note that this estimate can vary depending on the resource utilization policies and the number of database and system objects collected by the managed instance. In general, disk space usage increases as the number of policy violations increases and the duration of the moving time window for volatile resources increases.
- Note that removing a managed instance from the UCP will not reduce the disk space used by UCP databases until expiration of data retention periods for the managed instance.
After you have created the MDW, follow this article to setup the default collection sets and also using MDW to manage data collection in your SQL Server farm.
Collection of data from multiple nodes is handled through what Microsoft named “Application and Multi-server Management Utility” and will be discussed later in the article (section 2)
*This article assumes you have already created the Management Data Warehouse and need to configure it.
*You must turn off/on data collection when making updates or changes to their definition
Security: Brief Note on Security – Standard Protocol Encryption can be used to secure transmission.
The data collector uses the role-based security model implemented by SQL Server Agent and is used for operations involving internal tables. No permissions are granted to internal tables. More on security here
Storage:Important to note the MDW collects data in MSDB and the MDW.
Architecture: to read more about architecture and processing go here
1. Configure “Data Collection” & Select Configure “Management Data Warehouse”
2. On the next screen choose “Set up data collection” ( what we are doing in this example) & click “Next”
3. On the next screen select your collection server and the cache directory.
4. Review your settings and click “Finish”:
5. Fix errors if any & once you are done click “Close”
Data Collector Upload Modes:
- Non-cached mode. Data collection and upload are on the same schedule. The packages start, collect and upload data at their configured frequency, and run until they are finished. After the packages finish, they are unloaded from memory.
- Cached mode. Data collection and upload are on different schedules. The packages collect and cache data until they receive a signal to exit from a loop control-flow task. This ensures that the data flow can be executed repeatedly, which enables continuous data collection.*In most cases you will choose “cached mode”
- The collection package gathers data from a data provider and keeps it in temporary storage. If configured to do so, this package also appends additional information to the data it collects, such as the snapshot time or information about the source of the data.
- The upload package reads the data in temporary storage, processes the data as required (for example, removing unnecessary data points, normalizing the data, and data aggregation), and then uploads the data to the management data warehouse. The upload is done as a bulk insert to minimize the impact on server performance.
Overview of the farm management tools.
Background Information: Once you have the basic components of the MDW installed & have completed “Section 1” of this article, the remaining piece is the setup of the SQL Server Utility with Multi-server management enabled.
This tool models an organizations SQL Server related entities in a unified view and provides a holistic view of the environment. This is enabled via what is called a “UCP”; you must define a UCP or “Utility Control Point”
The images above shows the workflow designed for the Enterprise Management Data Warehouse.
The combination of summary and detailed data presented in the UCP for both underutilization and overutilization for a variety of key metrics & enables resource consolidation opportunities and resource overutilization to be identified with graphically & with ease. All managed centrally in the SQL Server Utility.
Step 1 To get started with Central Management, the first step is to create a Utility Control Point or UCP
First go to view > Utility Explorer
If you are launching for the first time, this will take you to this screen:
Let’s select “Create a Utility Control Point” – UCP as we have not defined one yet.
*note: you should be doing this on the node you want to serve as your UCP.