You are the SQL administrator of your company. The network contains 10 SQL Server 2008 computers. Allservers are members of a group named Corporate SQL Servers . You are in the process of creating an operator for a master SQL Server Agent. You run the sp_add_operator system stored procedure to configure the master SQL Server Agent operator on a new server named SQL1 . Now,you want to add SQL1 to the Corporate SQL Servers group. Which SQL Server Agent system stored procedure should you use?
A.
sp_add_targetservergroup
B.
sp_add_jobserver
C.
sp_msx_enlist
D.
sp_add_targetsvrgrp_member
Explanation:
You should use the sp_add_targetsvrgrp_member system stored procedure. You can use SQL Server Agentstored procedures to create an operator for local and master server jobs. Operators are aliases for users orgroups that can receive notifications. You can create operators by using SQL Server Management Studio or usingthe sp_add_operator
system stored procedure. To create an operator for a local job, you should run the sp_add_operator system stored procedure. To create an operator for a master server jobs, you should performthe following steps:1. Specify the master SQL Server Agent operator by using the sp_add_operator system stored procedure.2. Add the specified target server to the target server group by using the sp_add_targetsvrgrp_member system stored procedure.3. Enlist the target server in the job by using the sp_msx_enlist system stored procedure.You should not use the sp_add_targetservergroup system stored procedure because this stored procedurecannot be used to add a target server to a target server group. The sp_add_targetservergroup system storedprocedure is used to create a target server group. In this scenario, the target server group, Corporate SQL Servers
, is already created. You should not use the sp_add_jobserver system stored procedure because this stored procedure cannot beused to add a target server to a target server group. The sp_add_jobserver system stored procedure is used toadd a specified job to the specified server. You should not use the sp_msx_enlist system stored procedure because this stored procedure cannot be usedto add a target server to a target server group. The sp_msx_enlist system stored procedure is used to add thecurrent server to the list of available servers on the master server.Objective:
Maintaining SQL Server InstancesSub-Objective:
Manage SQL Server Agent operators.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration >Administration: How-to Topics (Transact-SQL) > Automated Administration How-to Topics (Transact-SQL) > Howto: Create Operators (Transact-SQL) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > AutomatingAdministrative Tasks > (SQL Server Agent) > Monitoring and Responding to Events > Defining Operators TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > System Stored Procedures (Transact-SQL) > SQL Server Agent Stored Procedures (Transact-SQL)> sp_add_operator (Transact-SQL)