Deploying User Defined Performance Counters

Performance Collector Application Box

It is recommended that all user defined performance counter collections for SQL instances [physical or virtual] occur from dedicated application box(s).  Based on your security policy [data center or production vs. test/demo], there maybe 1 or more of these application box(s) setup for this purpose.  The performance collector SQL database, which would store the user defined performance counters, can also be segregated based on security policy.

The dedicated application box(s) will need firewall access opened to the SQL instances they are collecting from.  The firewall rules required for remote performance collection are the same as those required for SMB access.  The same application will need to have firewall access opened to log the data to the performance collector SQL instance.  You may need to disable any virus checking application as it may interfere with remote performance collection.

Here is an example of rules that you may need to set  for access.  Please consult with the network/security team for rules and questions.

Performance Collector Application Box à Monitored SQL Instances

Source Host:                 DBAWPRPL1P ß Sample hostname of the application collection box(s)

Destination Host:           <IP Network Subnet>, <IP Network Subnet>, …, <IP Network Subnet>

Ports:                           TCP – 135-139,445

                                    UDP – 135-139, 445

Performance Collector Application Box à Performance Collector SQL Instances

Source Host:                 DBAWPRPL1P ß Sample hostname of the application collection box(s)

Destination Host:           DBAMONVSQL1P ß Sample performance collector SQL instance

Ports:                           TCP – 1433

                                    UDP – 1434

Setting up System DSN for Performance Collector SQL Instance

Now you must configure a System DSN [System Data Source] so the performance collectors can write to the backend performance collector SQL instance.

  1. Go to Start –> Control Panel –> Administrative Tools.
  2. Open Data Sources (ODBC) tool.
  3. Go to System DSN –> Add.
  4. Choose SQL Server Native Client and click Finish.
  5. Specify the name DSN name, description [if needed] and name of the SQL instance [hostname or hostname\instance].  Click Next.
  6. Choose either an integrated Windows authentication or SQL login.  Click Next.
  7. Specify the default database where the performance collector tables exist.  Click Next.
  8. Specify any additional options you wish, as needed.  Click Finish when complete.

 

Manually Creating User Defined Performance Counters

You now need to create a user defined performance counter collection.  This is where you specify all the counters you wish to collect from the monitored SQL instance.  This procedure assumes you are using Windows 2008 Server for your application collector box.

  1. Go to Start –> Run and type in perfmon.
  2. Open Data Collector Sets –> User Defined à right click New –> Data Collector Set.
  3. Specify a collector name.  It is recommended that you come up with a standard for all monitored SQL instance collector names.  For example:  SMPWPROVVSQL1P_Benchmark_DSN, where the _Benchmark_DSN is also the suffix used for a monitored SQL instance.
  4. Choose Create manually (Advanced) and click Next.
  5. Choose Create data logs –> Performance counter and click Next.
  6. Click Add and specify the name of the remote computer you wish to collect from.  You need to specify \\<HOSTNAME> to a remote SQL instance.  For example:  \\SMPWPROVVSQL1P.  Click Browse.
  7. Once connected, scroll the list of available counters and specify those you wish to collect and click Add.  After you have chosen all counters you wish to collect, click OK.
  8. Change the Sample interval, if you wish, at this time.  Click Finish to complete.
  9. Right click on the newly created user defined counter name and choose Properties.  Change the Run As from SYSTEM to a secure service account.  This service account should have BULK ADMIN and write permission on the performance collector SQL database.  Click OK.

10.  Click on the newly created user defined counter name and double click on DataCollector01.  Under Log format choose SQL.  Under Data Source Name choose the System DSN which was created above.  You may also specify the sample internal time, if needed.

11.  Click Add to add any additional performance counters.  Follow steps above.  Click OK when complete.

12.  Right click on the data collector name and choose Start.  After a few moments you should see the icon for the counter change to a running state.

13.  Login to the performance collector SQL database and verify data is being logged from the SQL instance being monitored.

14.  Repeat as needed.

User Defined Performance Counter Using a Template File

You may also create a user defined performance counter collection from a template file.  This allows you to simply modify a template instead of re-entering all performance counters by hand.  This procedure assumes you are using Windows 2008 Server for your application collector box.

  1. Go to Start –> Run and type in perfmon.
  2. Open Data Collector Sets –> User Defined.  Right click on a user defined counter you wish copy and choose Save Template.  Specify the template name and location of the file.
  3. Edit the XML template file and change values such as the name of the SQL instance you are monitoring, directory folders, databases to monitor, etc.  Save the file when complete.
  4. Open Data Collector Sets –> User Defined –> right click New –> Data Collector Set.
  5. Specify a collector name.  It is recommended that you come up with a standard for all monitored SQL instance collector names.  For example:  SMPWPRSVSQL1P_Benchmark_DSN, where the _Benchmark_DSN is also the suffix used for a monitored SQL instance.
  6. Choose Create from a template (Recommended) and click Next.
  7. Choose Basic and click Browse.  Choose the XML template file you have modified.  Click Next 2 times.
  8. Change the Run As from SYSTEM to a secure service account.  This service account should have BULK ADMIN and write permission on the performance collector SQL database.  Choose Open properties for this data collector set and click Finish.  Verify everything then click OK.
  9. Click on the newly created user defined counter name and double click on DataCollector01.  Under Log format verify it is set to SQL and the correct Data source name is set.  Verify the sample internal time, if needed.

10.  Click Add to add any additional performance counters.  Follow steps above.  Click OK when complete.  You may need to provide credentials for the secure service account again, if needed.

11.  Right click on the data collector name and choose Start.  After a few moments you should see the icon for the counter change to a running state.

12.  Login to the performance collector SQL database and verify data is being logged from the SQL instance being monitored.

13.  Repeat as needed.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s