Problem : As a DBA, one of your primary job description is to handle management and administration of 1 or 100+ SQL Server databases. One of the top skill required for this is being proactive in your managing task and being able to fix something even before it even happens. For this you will need a tool to help in accomplishing this. Good thing in SQL Server it is already available for you to use. Ease to setup and aside from its default reporting you can add your own custom data collection set.
Solution : There is a built in tool for this in SQL Server called Data Collection Set. The idea for this you have data warehouse repository that collects data from all your SQL Servers and generates a report on the current state of your SQL Servers.
If you are Formula 1 Grand Prix fanatic like me, imagine your engineering team collecting all kinds of data from your car and presents it in a dashboard format of reporting so that they will know how to fix the car so you, the driver can come out on top of the race. It is the similar principle here for SQL Server.
Setting up your Data Collector
For configuring your data collector server, you need to configure a server that will act as the data collector and also generate the management data warehouse report. Also you will need a service account for data collection and grant this account data collection roles.
Configure the Management Data Warehouse
In your SQL Server that will act as the data collector, you need to setup the Management Data Warehouse database.
From your SSMS, go to Management menu and right click on Data Collection.
Select the Configure Data Management Warehouse. This will launch the Configure Data Management Warehouse wizard. Click on Create or upgrade a management data warehouse option. Click Next.
Enter the server name, that will act as your data collector or the SQL Server on which you are running this wizard. Enter the database name that will act as the repository database from which your report will gather data. For the cache directory, this is optional and for cases when you want your data collector server to collect data but not yet upload to the MDW server.
That’s all that is needed and you can click Finish. Click Close.
Refresh your Data Collection Menu. You should be able to see the following default data collection sets configured.
Each data collection set will automatically create a job in your SQL Agent. You may see this starting with job name ‘collection_setxxxxxxx’.
At this point, you have completed the setup of your Data Collection in SQL Server. Now to configure the properties of your data collection sets, you right click on each data collection set and select Properties.
From the Properties dialog window, you can configure the schedule of data collection, the retention period of the data collected
Data Collector Permissions and Roles
There are 3 database roles within the MDW database. The service accounts from the database server that have client data collector configured must be added to the logins and mapped to one of the following database role.
- mdw_admin – members of this role have rights to manage the data collection set properties. Members of this role also have read and write access to the MDW database.
- mdw_reader – members of this role have read access the MDW database
- mdw_writer – members of this role have write access the MDW database
From your client database server that is sending the data for data collection set, the least privilege must be granted. The service account of the client database server must be added to the MDW server and must be added to the database role mdw_writer.
Data Collector SQL Server Clients
For all your SQL Server that you want to collect data from, you will need to run the same configuration wizard. And you need to add the SQL Server service account from this client server to your MDW server and make it a member of mdw_writer in the MDW database.
From your client SQL Server, select the option ‘Set up Data Collection’. Click Next.
In the below wizard dialog box, enter the name of your data collector server and the name of your repository database.
Click Finish and you are set. You will need to this for all the SQL Servers that you want to monitor.
Built in Reports and Dashboard
You may have a hard time finding the built-in reports for MDW because they must be used once before they automatically appear in the SSMS menu.
To get started:
- Open SSMS and go to one of the MDW databases.
- Right-click and select the Reports menu item.
- Select Management Data Warehouse menu item.
- Select Management Data Warehouse Overview menu item.
You should be able to see main reporting dashboard below. The below dashboard contains the server or instance name for all your client SQL Servers and the last data collection date for the 3 default data collector sets
Click on each column to view the different dashboard for your default data collection set.
I have been using the Data Collection only recently but i find it useful for SQL Server monitoring especially the Disk Usage report and dashboard. It really helps on forecast and disk usage trending of all the servers that I am monitoring. I hope you find it useful in your case too.
Aside from the default data collection set that SQL Server automatically creates, you can create your own custom data collection set. There maybe a future requirement for all failed SQL Agent jobs report in your environment or something like an auditing report for all your logins across all your SQL Server Production environment. This can be created too and I will tackle that on my next post!
For more information on data collection, you may visit this link.