Редагувати

Поділитися через

Work with Replication Agent Profiles

  • Стаття

Applies to: SQL Server Azure SQL Managed Instance

This topic describes how to work with Replication Agent Profiles in SQL Server by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects (RMO). The behavior of each replication agent is controlled by a set of parameters that can be set through agent profiles. Each agent has a default profile, and some have additional predefined profiles; at a given time, only one profile is active for an agent.

In This Topic

Using SQL Server Management Studio

To access the Agent Profiles dialog box from SQL Server Management Studio

  1. On the General page of the Distributor Properties - <Distributor> dialog box, click Profile Defaults.

To access the Agent Profiles dialog box from Replication Monitor

  • To open the dialog box for all agents, right-click a Publisher, and then click Agent Profiles.

  • To open the dialog box for a single agent:

    1. Expand a Publisher group in the left pane of Replication Monitor, expand a Publisher, and then click a publication.

    2. For Distribution Agent and Merge Agent profiles, right-click a subscription on the All Subscriptions tab, and then click Agent Profile. For other agents, right-click the agent on the Agents tab, and then click Agent Profile.

To specify a profile for an agent

  1. If the Agent Profiles dialog box displays profiles for more than one agent, select an agent.

  2. Select a profile in the Default for new column of the Agent profiles grid. By default, the profile is only applied to agents for new publications and subscriptions.

  3. To specify that all agents of the selected type for existing publications or subscriptions should use this profile, click Change existing agents.

To view and edit the parameters associated with a profile

  1. If the Agent Profiles dialog box displays profiles for more than one agent, select an agent.

  2. Click the properties button (...) next to a profile.

  3. View the parameters and values in the <ProfileName> Profile Properties dialog box.

    • Parameters in user-defined profiles can be edited; parameters in predefined system profiles cannot.

    • To view all parameters for an agent, clear the Show only parameters used in this profile check box. For information about agent parameters, see the links at the end of this topic.

  4. Click Close.

To create a user-defined profile

  1. If the Agent Profiles dialog box displays profiles for more than one agent, select an agent.

  2. Click New.

  3. In the New Agent Profile initialization dialog box, select an existing profile on which to base the new profile.

  4. In the New Agent Profile dialog box, enter values in the Name and Description text boxes.

  5. Modify parameters to tailor the profile. To view all parameters for an agent, clear the Show only parameters used in this profile check box. For information about agent parameters, see the links at the end of this topic.

  6. Select OK.

To delete a user-defined profile

  1. If the Agent Profiles dialog box displays profiles for more than one agent, select an agent.

  2. If a profile is associated with one or more agents, change the profile for those agents:

    1. Select a different profile in the Agent profiles grid.

    2. Click Change existing agents.

      Note

      This will change the profile for all agents of the selected type for existing publications or subscriptions, not only the ones using the profile you want to delete.

  3. Select the profile you want to delete, and then click Delete.

  4. Select OK.

Using Transact-SQL

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_parameter (Transact-SQL). Specify the profile identifier from step 1 for @profile_id, the name of the parameter to change for @parameter_name, and a new value for the parameter for @parameter_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 delete 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 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 (Transact-SQL)

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

Using RMO

To create a new agent profile

  1. Create a connection to the Distributor by using an instance of the ServerConnection class.

  2. Create an instance of the AgentProfile class.

  3. Set the following properties on the object:

  4. Call the Create method to create the profile on the server.

  5. Once the profile exists on the server, you can customize it by adding, removing, or changing the values of replication agent parameters.

  6. To assign the profile to an existing replication agent job, call the AssignToAgent method. Pass the name of the distribution database for distributionDBName and the ID of the job for agentID.

To modify an existing agent profile

  1. Create a connection to the Distributor by using an instance of the ServerConnection class.

  2. Create an instance of the ReplicationServer class. Pass the ServerConnection object created in step 1.

  3. Call the LoadProperties method. If this method returns false, verify that the Distributor exists.

  4. Call the EnumAgentProfiles method. Pass an AgentType value to narrow down the returned profiles to a specific type of replication agent.

  5. Get the desired AgentProfile object from the returned ArrayList, where the Name property of the object matches the profile name.

  6. Call one of the following methods of AgentProfile to change the profile:

    • AddParameter - adds a supported parameter to the profile, where name is the name of the replication agent parameter and value is the specified value. To enumerate all supported agent parameters for a given agent type, call the EnumParameterInfo method. This method returns an ArrayList of AgentProfileParameterInfo objects that represent all supported parameters.

    • RemoveParameter - removes an existing parameter from the profile, where name is the name of the replication agent parameter. To enumerate all current agent parameters defined for the profile, call the EnumParameters method. This method returns an ArrayList of AgentProfileParameter objects that represent the existing parameter for this profile.

    • ChangeParameter - changes the setting of an existing parameter in the profile, where name is the name of the agent parameter and newValue is the value to which the parameter is being changed. To enumerate all current agent parameters defined for the profile, call the EnumParameters method. This method returns an ArrayList of AgentProfileParameter objects that represent the existing parameter for this profile. To enumerate all supported agent parameter settings, call the EnumParameterInfo method. This method returns an ArrayList of AgentProfileParameterInfo objects that represent the supported values for all parameters.

To delete an agent profile

  1. Create a connection to the Distributor by using an instance of the ServerConnection class.

  2. Create an instance of the AgentProfile class. Set the name of the profile for Name and the ServerConnection from step 1 for ConnectionContext.

  3. Call the LoadProperties method. If this method returns false, either the specified name was incorrect or the profile does not exist on the server.

  4. Verify that the Type property is set to User, which indicates a customer profile. You should not remove a profile that has a value of System for Type.

  5. Call the Remove method to remove the user-defined profile represented by this object from the server.

Follow Up: After Changing Agent Parameters

Agent parameter changes take effect the next time the agent is started. If the agent runs continuously, you must stop and restart the agent. Starting with SQL Server 2017 CU3, some agent parameter changes take effect without having to restart the Agents.

Related content