Writing Perfmon data to SQL Server

Q: I’m trying to get Perfmon to write performance counter data to SQL Server. In Perfmon I’ve created a Data Collector and I set the Log format to SQL. I have also created a data source using the SQL Server Native driver but when I try to start Perfmon I get the error Call to SQLAllocConnect failed with %1.

A: I’ve found that you cannot use the SQL Server Native Client to log data to SQL Server from Perfmon. I have only had success when the data source that I created used the ODBC driver like you can see in Figure 1.

Figure 1 - Selecting the ODBC Driver for a Perfmon data source

Next, you need to realize that Perfmon runs under the SYSTEM account which doesn’t have a SQL Server login so you’ll need to change the account that the Data Collector set uses.  To change the account that the Data Collector runs under open Perfmon and expand the Data Collector Sets node followed by the User Defined node. Then right click on the Data Collector that you created and select Properties from the context menu. This will display the Properties dialog like the one you can see in Figure 2.

Figure 2 - Changing the Data Collector’s Run As property

Initially the Run AS box will show SYSTEM. Click the Change button then enter a domain account that has the appropriate rights to login to SQL Server and update that database that you specified for your Perfmon data collection. It’s important to note you need to use an account with Windows authentication. SQL Server authentication won’t work.

After you’ve made these two changes you will be able to start your user defined data collector and it will begin logging data to your database. When Perfmon logs data to SQL Server it uses three tables: CounterData, CounterDetails and DisplayToID.