Write for Us
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.
Try Idera’s new SQL admin toolset
Try Idera’s new SQL admin toolset
Try Idera’s new SQL admin toolset