15.6. Best practice considerations: Data Collector and MDW

The data collection platform is a significant leap forward in deriving maximum value from the vast amount of information contained within a SQL Server instance, opening up a whole range of new reporting options unavailable in previous versions of SQL Server without customized solutions or third-party products.

• When using the data collection platform, creating a centralized MDW database (ideally on a dedicated server) is preferable to creating an MDW on each instance configured with data collection. The benefits of a centralized MDW include a single administration and reporting point and offloading the impact of the data collection load process to the dedicated MDW server.

• When configuring multiple server instances to upload to a central MDW, the uploads should ideally be staggered to reduce the impact of multiple servers uploading data at the same time.

• After creating the MDW database, consider setting its recovery model to full, and ensure the database is adequately sized to avoid frequent autogrow operations.

• For large data collection sets, or sets configured with a high collection frequency, use the cached mode to reduce the impact on the network and server.

• For maximum performance, the cache directory should ideally be set to a dedicated disk that is separate from the database data and transaction log disks. Further, the SQL Server Agent service account will need read/write access to this directory.

• Before changing the collection mode and/or collection and upload schedules, you need to understand the performance impacts of doing so, particularly in a production environment with a large number of servers uploading to a central MDW database.

• Ensure the MDW database and transaction log are backed up as per any other production database. Further, since the MSDB database contains the data collection configuration for each uploading instance, ensure this database is backed up on each participating instance.

Additional links and information on the best practices covered in this chapter can be found online at http://www.sqlcrunch.com/datacollector.

The data collection platform is one of many new features introduced in SQL Server 2008. In the next chapter, we'll look at another one: Resource Governor.