To identify the location of the MDW enter a server name, and database. Use the “New” button to create a new MDW database. Note that if you click on the new button and specify a new database to be created and then cancel the wizard the database will still be created.
There are two modes that in which a Data Collection might process. There are those collections that collect data based on a snapshot in time, and then those that constantly collect data. The Data Collections that are constantly collecting data use the “Cache directory” to store collected data between uploads to the MDW. To specify a cache location, either type in the name of the directory, or browse for it by using the ellipse (…) button. If you browse for the cache directory it must be present. If you type it in it you must make sure you identify an existing directory. If the directory doesn’t exist the system Data Collection processes that require a cache will fail until you create the cache folder specified. I’d suggest you create the cache folder in advance to avoid having any Data Collections failures issues to deal with.
Once you select a server, a database and identify a cache directory location the “Next >” button will become available. When you click on the next button the following screen will be displayed:
On this screen you identify the different rights each login will have in the MDW. There are three different roles identified: mdw_admin, mdw_reader, and , mdw_writer. The mdw_admin role will be allowed to read, write and update data, as well as run purge and cleanup jobs against the MDW. The mdw_reader role only has access to read data in the MDW, where as the mdw_writer role can write and upload data to the MDW. If you are a DBA and you want to completely manage the MDW then make sure you give yourself access to the “mdw_admin” role. You don’t have to add all the rights when using the wizard, you can go directly into the MDW and place people in these roles later if you desire. Once you are done mapping logins you can either click on the “Next>” or “Finish>>|” button, they both take you to a summary window. On the summary window you can verify what you have entered in the wizard. If while you are reviewing the summary information if you find something you want to change you can click on the “<Back” button to go back and change your MDW configuration options and then click onto the “Finish>>|” button to jump you back to the summary window.
After you click finish the wizard will take a few minutes to create the MDW environment. When the wizard is done setting up the MDW the following screen is displayed:
Once you have completed the wizard the MDW database will have been created, as well as three system Data Collections, and a number of SQL Server agent jobs and SSIS packages.
If you expand the “Data Collection” item in Object Explore within SSMS you will be able to see the three different system Data Collections under the “System Data Collection Set” item.
In addition to the three different system Data Collections a number of SQL Agent jobs and SSIS packages are also created. These SQL Agent jobs and SSIS packages are used to automate the extraction and load process of the MDW for each Data Collection.
System Data Collections
The three different Data Collection items created are: Disk Usage, Query Statistics, and the System Statistics. The Disk Usage Data Collection collects disk space usage information related to Data and Log files associated with each database. This collected information can be used to track the growth rate of your databases over time. This data is useful for performing capacity management.
The Query Statistics Data Collection collects information about any queries that are run against the SQL Server instance. Since Query Statistics Data Collection is disabled by default you will need to enable it if you want to collect query statistics. The System Statistics Data Collection gathers performance counter information, like CPU, Memory, etc. This Data Collection allows you to monitor the resource usages of various counters over time, so you can identify resource bottlenecks and trends over time.
SQL Server 2008 Performance Monitoring
The MDW and the Data Collection containers provide DBAs with an easy method to gather performance monitoring data for SQL Server 2008. Having the performance data collection process built into SQL Server 2008 reduces the need to build your own data collection routines. By using the MDW and the Data Collection process DBAs now have the tools necessary to provide them with data that they can use to track the performance of their SQL Server environment over time and perform capacity planning when new hardware needs to be acquired.
]]>