Managing multiple instances of SQL Servers and databases is a challenging task, having a third party tool is not a problem however it is always BEST to manage the show using SQL Server Management Studio (SSMS). SQL Server tools are easy to use and help to manage multi-server management through automated multi-server jobs, event forwarding and the ability to manage multiple instances from a single-machine console.
Since SQL Server 2008 R2 version (and SQL Server 2008 SP2) onwards the SSMS tool has been given a good overview of managing the multiple instances using the Utility Control Point (UCP). In short, UCP is the model that will represent the SQL Server instances/databases as entitites with an unified view. There are view points that will to identify the bottlenecks on the system, the entities are differentiated as follows:
- Instance names of SQL Server (multiple)
- Data-tier applications
- Database files
- Resource utilization dimension
- CPU utilization
- Storage space utilization
- Storage volume information
The architecture behind UCP provides the capabilities of dashboard, view points and resource utilization policies which are couped under Utility Administration. As of now using SQL Server 2008 R2 Enterprise Edition UCP tool, we can manage 25 instances.Similar to other process the UCP wizard helps you to setup the UCP to register the required instances and Data-tier applications (DACPACK) are registered automatically. The policies behind UCP are driven by the Policy Based Framework and all the health policies can be defined globally for all data-tier applications and managed instances of SQL Server in the SQL Server Utility, or they can be defined individually for each data-tier application and for each managed instance of SQL Server in the SQL Server Utility.
You can also restore default values or discard changes using buttons at the bottom of the display. The summary and detailed data is presented in Management Studio for each instance of SQL Server and data-tier application, SQL Server Utility dashboard in SSMS presents an at-aglance summary of performance and configuration data for managed instance and data-tier application CPU utilization, database file utilization, storage volume utilization, and computer
CPU utilization. Data displays provide separation of over-utilized and under-utilized resources, as well as graphs of CPU utilization and storage utilization over time. Each instance of SQL Server and data-tier application managed by the SQL Server Utility can be monitored based on global policy definitions, or based on individual policy definitions.
More information on the best practices approach and taking help of policies read on Microsoft SQL Server 2008 R2 Administration cookbook.