Dela via

  • Artikel

How to: Work with Replication Agent Profiles (Replication Transact-SQL Programming)

Agent profiles are used to define the default values when an agent job is created. Each agent has a default profile, and some have additional predefined profiles. You can create user-defined profiles using replication stored procedures. For information about agent profiles, see Replication Agent Profiles.

Note

You should not modify or delete predefined profiles. Agent parameter changes take effect the next time the agent is started. If the agent runs continuously, you must stop and restart the agent.

To create a new agent profile

  1. At the Distributor, execute sp_add_agent_profile (Transact-SQL). Specify @name, a value of 1 for @profile_type, and one of the following values for @agent_type:

    If this profile will become the new default profile for its type of replication agent, specify a value of 1 for @default. The identifier for the new profile is returned using the @profile_id output parameter. This creates a new profile with a set of profile parameters based on the default profile for the given agent type.

  2. After the new profile has been created, add, remove, or modify the default parameters to customize the profile.

To modify an existing agent profile

  1. At the Distributor, execute sp_help_agent_profile (Transact-SQL). Specify one of the following values for @agent_type:

    This returns all profiles for the specified type of agent. Note the value of profile_id in the result set for the profile to change.

  2. At the Distributor, execute sp_help_agent_parameter (Transact-SQL). Specify the profile identifier from step 1 for @profile_id. This returns all parameters for the profile. Note the name of any parameters to modify or remove from the profile.

  3. To change the value of a parameter in a profile, execute sp_change_agent_profile (Transact-SQL). Specify the profile identifier from step 1 for @profile_id, the name of the parameter to change for @property, and a new value for the parameter for @value.

    Note

    You cannot change an existing agent profile to become the default profile for an agent. You must instead create a new profile as the default profile, as shown in the previous procedure.

  4. To remove a parameter from a profile, execute sp_drop_agent_parameter (Transact-SQL). Specify the profile identifier from step 1 for @profile_id and the name of the parameter to remove for @parameter_name.

  5. To add a new parameter to a profile, you must do the following:

    • Query the MSagentparameterlist (Transact-SQL) table at the Distributor to determine which profile parameters can be set for each agent type.

    • At the Distributor, execute sp_add_agent_parameter (Transact-SQL). Specify the profile identifier from step 1 for @profile_id, the name of a valid parameter to add for @parameter_name, and the value of the parameter for @parameter_value.

To remove an agent profile

  1. At the Distributor, execute sp_help_agent_profile (Transact-SQL). Specify one of the following values for @agent_type:

    This returns all profiles for the specified type of agent. Note the value of profile_id in the result set for the profile to remove.

  2. At the Distributor, execute sp_drop_agent_profile (Transact-SQL). Specify the profile identifier from step 1 for @profile_id.

To use agent profiles during synchronization

  1. At the Distributor, execute sp_help_agent_profile (Transact-SQL). Specify one of the following values for @agent_type:

    This returns all profiles for the specified type of agent. Note the value of profile_name in the result set for the profile to use.

  2. If the agent is started from an agent job, edit the job step that starts the agent to specify the value of profile_name obtained in step 1 after the -ProfileName command-line parameter. For more information, see How to: View and Modify Replication Agent Command Prompt Parameters (SQL Server Management Studio).

  3. When starting the agent from the command prompt, specify the value of profile_name obtained in step 1 after the -ProfileName command-line parameter.

Example

This example creates a custom profile for the Merge Agent named custom_merge, changes the value of the -UploadReadChangesPerBatch parameter, adds a new -ExchangeType parameter, and returns information on the profile that is created.

DECLARE @profilename AS sysname;
DECLARE @profileid AS int;
SET @profilename = N'custom_merge';

-- Create a temporary table to hold the returned 
-- Merge Agent profiles.
CREATE TABLE #profiles (
    profile_id int, 
    profile_name sysname,
    agent_type int,
    [type] int,
    description varchar(3000),
    def_profile bit)

INSERT INTO #profiles (profile_id, profile_name, 
    agent_type, [type],description, def_profile)
    EXEC sp_help_agent_profile @agent_type = 4;

SET @profileid = (SELECT profile_id FROM #profiles 
    WHERE profile_name = @profilename);

IF (@profileid IS NOT NULL)
BEGIN
    EXEC sp_drop_agent_profile @profileid;
END
DROP TABLE #profiles

-- Add a new merge agent profile. 
EXEC sp_add_agent_profile @profile_id = @profileid OUTPUT, 
@profile_name = @profilename, @agent_type = 4, 
@description = N'custom merge profile';

-- Change the value of uploadreadchangesperbatch in the profile.
EXEC sp_change_agent_parameter @profile_id = @profileid, 
    @parameter_name = N'-UploadReadChangesPerBatch', @parameter_value = 50;

-- Add a new parameter ExchangeType the profile. 
EXEC sp_add_agent_parameter @profile_id = @profileid, 
    @parameter_name = N'-ExchangeType', @parameter_value = 1;

-- Verify the new profile. 
EXEC sp_help_agent_parameter @profileid;
GO