SQL Server 2012 : Measuring SQL Server Performance (part 4) - Setting Up the Data Collector

2/20/2014 8:55:19 PM

5. Setting Up the Data Collector

To begin using the data collector, you need to first establish security, a login, that will be used on all your servers for collecting data. You can approach this in two basic ways. First, you can have a single login across all your servers that have sysadmin privileges, which allows the login to do anything. Second, you can use the built-in data collector roles that are stored in the msdb system database. Detailing all the variations for setting up security for the data collector is beyond the scope of this book. For details, refer to “Data Collector Security” in Books Online.

Once the security is set, you’ll need to establish a server as the host to the management data warehouse, where the performance data gathered through the data collector will be stored. When you have the server ready, open SQL Server Management Studio (SSMS), and connect to the server. Scroll down the folders available in the Object Explorer window to the Management folder. Expand this. It should look something like Figure 6, although if you haven’t configured it, you may see a small red arrow like the one visible on the Resource Governor icon.


Figure 6. The Data Collector tool inside the Management folder

Once you have navigated to the Data Collector icon, as shown in Figure 6, you can begin to establish the management data warehouse. Right-click the Data Collector icon, and select Configure Management Data Warehouse from the context menu. This will open the welcome screen to the Configure Management Data Warehouse Wizard. Click the Next button to get past that screen, as shown in Figure 7.


Figure 7. Management Data Warehouse Wizard’s “Select configuration task” page

The default behavior is to create or upgrade a management data warehouse, which is exactly what needs to happen. The other option is to set up data collection on the server that you run this on. This is how you would set up the data collector, and there will be more on that later in this section. Click the Next button. This will open the next page of the wizard, shown in Figure 8.


Figure 8. Management Data Warehouse Wizard’s Configure Management Data Warehouse Storage page

This process is very simple. Either you select an existing database to act as the management data warehouse, or you click the New button to create one when the standard Create Database window opens. Which you do depends on your system. We strongly advise against placing the data collected for the management data warehouse into one of your online transactional systems. You could place it into an existing reporting or management system. If you choose to select a new database, another window, called Map Logins and Users, will open for setting up security. Adjust this as needed for the security within your system and finish the wizard. That’s the entire process. Behind the scenes, more occurred than you can immediately see. Inside the database you selected or created, several tables were created that are used by the process to manage the collection and store the data. Stored procedures, views, user-defined functions, and other objects were also added to the database. All these objects are used to gather and present the data as it comes in from the various servers where you’ve enabled the data collector.

To configure the servers that will send their information to the management data warehouse, connect to those servers through SSMS. Navigate to the Management folder so that you can see the data collector, just like in Figure 6. Right-click and select Configure Management Data Warehouse from the context menu. This will again open the wizard’s welcome screen. Click Next. This will open the Select Configuration Task page like in Figure 7. Click the radio button “Set up data collection,” and click Next. This will open the Configure Management Data Warehouse Storage page, shown in Figure 9.


Figure 9. Configure Management Data Warehouse Storage page

From the server where you want to collect data, you must define the server where the management data warehouse is stored. Select the server by clicking the ellipsis button, which will enable the “Database name” drop-down. Make sure you select the database that you created previously. Finally, you need to define a directory for data to be cached while it waits for the process to pick it up for storage. Choose an appropriate location on your system. The default is to place it on the system drive (usually C:\). Depending on your environment, this is probably a poor choice. Instead, a storage collection that you can manage that won’t affect other processes is a more appropriate location. The data collector is now configured and running.

  •  SQL Server 2012 Security : How Hackers Attack SQL Server
  •  SQL Server 2012 Security : Partially Contained Databases
  •  SQL Server 2012 : SQL Server Audit (part 3) - Viewing Audited Events,Querying Audit Catalog Views
  •  SQL Server 2012 : SQL Server Audit (part 2) - Auditing Server Events, Auditing Database Events
  •  SQL Server 2012 : SQL Server Audit (part 1) - Creating an Audit Object, Recording Audits to the File System
  •  SQL Server 2012 : Encryption Support (part 4) - Transparent Data Encryption - Enabling TDE, Backing Up the Certificate
  •  SQL Server 2012 : Encryption Support (part 3) - Transparent Data Encryption - Creating Keys and Certificates for TDE
  •  SQL Server 2012 : Encryption Support (part 2) - Encrypting Data at Rest
  •  SQL Server 2012 : Encryption Support (part 1) - Encrypting Data on the Move
  •  SQL Server 2012 : Authentication and Authorization (part 2) - User-Schema Separation,Execution Context
    Video tutorials
    - How To Install Windows 8 On VMware Workstation 9

    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Disable Windows 8 Metro UI

    - How To Change Account Picture In Windows 8

    - How To Unlock Administrator Account in Windows 8

    - How To Restart, Log Off And Shutdown Windows 8

    - How To Login To Skype Using A Microsoft Account

    - How To Enable Aero Glass Effect In Windows 8

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen
    programming4us programming4us
    Top 10
    Free Mobile And Desktop Apps For Accessing Restricted Websites
    MASERATI QUATTROPORTE; DIESEL : Lure of Italian limos
    TOYOTA CAMRY 2; 2.5 : Camry now more comely
    KIA SORENTO 2.2CRDi : Fuel-sipping slugger
    How To Setup, Password Protect & Encrypt Wireless Internet Connection
    Emulate And Run iPad Apps On Windows, Mac OS X & Linux With iPadian
    Backup & Restore Game Progress From Any Game With SaveGameProgress
    Generate A Facebook Timeline Cover Using A Free App
    New App for Women ‘Remix’ Offers Fashion Advice & Style Tips
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th