Learning the Tricks of the Trade - Microsoft SQL Server 2008 R2 Administration - page 404

Let us classify the task as 'tools of the trade'; to cover all the SQL Server related tools and
utilities it might require a separate book to explain. However, in this chapter, we will go
through the important ingredients that are required to administer and manage the data
platform. These important ingredients are:
F Managing automated administration using a multi-server environment
F Administering the resource health policies
F Administration of multiple analysis services using available tools
F Automating the event capture to troubleshoot problems
F Implementing DBA manageability practices
Automated administration across an
enterprise
The automated administration of multiple SQL Server instances requires a pool of resources
and multiple utilities to manage. Since SQL Server 2005 Service Pack 2 (SP2), the multiple
instance management feature is introduced and using SQL Server agent services for the
central administration of jobs. Managing multiple instances will increase the total cost of
ownership (TCO) and software footprint. In this recipe, we will concentrate on the feature
that will help in achieving automated administration across the data platform enterprise and
manage multiple administrative scheduled jobs using Multiserver administration concepts.
Getting ready
To enable the Multiserver administration environment, we need more than one SQL Server
instance that will stand as master server and target server. The Multiserver administration
requires setup of a master server (MSX) and one or more target servers (TSX).
The Multiserver administration is possible between SQL Server 2008 R2 and lower versions,
the least TSX version can only be until SQL Server 2000 SP3. The security settings for SQL
Server and SQL Server agent services, which require a Windows domain account are an
important requisite in Multiserver administration.
How to do it...
The implementation is a two-fold task that involves the setup of the master server and target
servers. The following steps are essential to implement automated administration across an
enterprise using the Multiserver administration concept:
1. UsingSSMS in Object Explorer, connect to the SQL Server instance to expand and
navigate to the SQL Server Agent folder.