View and Modify Distributor and Publisher Properties

This topic describes how to view and modify Distributor and Publisher properties in SQL Server 2012 by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects (RMO).

In This Topic

• Before you begin:

  Recommendations

  Security

• To view and modify Distributor and Publisher properties, using:

  SQL Server Management Studio

  Transact-SQL

  Replication Management Objects (RMO)

Before You Begin

Recommendations

• For Publishers running versions prior to Microsoft SQL Server 2005, a user in the sysadmin fixed server role can register Subscribers on the Subscribers page. Beginning with SQL Server 2005, it is no longer necessary to explicitly register Subscribers for replication.

Security

When possible, prompt users to enter security credentials at runtime.

[Top]

Using SQL Server Management Studio

To view and modify Distributor properties

1. Connect to the Distributor in SQL Server Management Studio, and then expand the server node.

2. Right-click the Replication folder, and then click Distributor Properties.

3. View and modify properties in the Distributor Properties - <Distributor> dialog box.

   • To view and modify properties for a distribution database, click the properties button (...) for the database on the General page of thedialog box.

   • To view and modify Publisher properties associated with the Distributor, click the properties button (...) for the Publisher on the Publishers page of the dialog box.

   • To access profiles for replication agents, click the Profile Defaults button on the General page of the dialog box. For more information, see Replication Agent Profiles.

   • To change the password for the account used when administrative stored procedures execute at the Publisher and update information at the Distributor, enter a new password in the Password and Confirm password boxes on the Publishers page of the dialog box. For more information, see Secure the Distributor.

4. Modify any properties if necessary, and then click OK.

To view and modify Publisher properties

1. Connect to the Publisher in SQL Server Management Studio, and then expand the server node.

2. Right-click the Replication folder, and then click Publisher Properties.

3. View and modify properties in the Publisher Properties - < Publisher > dialog box.

   • A user in the sysadmin fixed server role can enable databases for replication on the Publication Databases page. Enabling a database does not publish that database; rather, it allows any user in the db_owner fixed database role for that database to create one or more publications in the database.

4. Modify any properties if necessary, and then click OK.

[Top]

Using Transact-SQL

Publisher and Distributor properties can be viewed programmatically using replication stored procedures.

To view Distributor and distribution database properties

1. Execute sp_helpdistributor to return information about the Distributor, distribution database, and working directory.

2. Execute sp_helpdistributiondb to return properties of a specified distribution database.

To change Distributor and distribution database properties

1. At the Distributor, execute sp_changedistributor_property to modify Distributor properties.

2. At the Distributor, execute sp_changedistributiondb to modify distribution database properties.

3. At the Distributor, execute sp_changedistributor_password to change the Distributor password.

   Security Note
   When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, secure the file to prevent unauthorized access.

4. At the Distributor, execute sp_changedistpublisher to change the properties of a Publisher using the Distributor.

Examples (Transact-SQL)

The following example Transact-SQL script returns information about the Distributor and distribution database.

-- View information about the Distributor, distribution database, 
-- working directory, and SQL Server Agent user account. 
USE master
EXEC sp_helpdistributor;
GO

-- View information about the specified distribution database. 
USE distribution
EXEC sp_helpdistributiondb;
GO

This example changes retention periods for the Distributor, the password used when connecting to the Distributor, and the interval at which the Distributor checks the status of various replication agents (also known as the heartbeat interval).

Security Note
When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, secure the file to prevent unauthorized access.

-- Change the heartbeat interval at the Distributor to 5 minutes. 
USE master 
exec sp_changedistributor_property 
    @property = N'heartbeat_interval', 
    @value = 5;
GO

DECLARE @distributionDB AS sysname;
SET @distributionDB = N'distribution';

-- Change the history retention period to 24 hours and the
-- maximum retention period to 48 hours.  
USE distribution
EXEC sp_changedistributiondb @distributionDB, N'history_retention', 24
EXEC sp_changedistributiondb @distributionDB, N'max_distretention', 48
GO 

-- Change the password on the Distributor. 
-- To avoid storing the password in the script file, the value is passed 
-- into SQLCMD as a scripting variable. For information about how to use 
-- scripting variables on the command line and in SQL Server Management
-- Studio, see the "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".
USE master
EXEC sp_changedistributor_password $(Password)
GO

[Top]

Using Replication Management Objects (RMO)

To view and modify Distributor properties

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

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

3. (Optional) Check the IsDistributor property to verify that the currently connected server is a Distributor.

4. Call the Load method to get the properties from the server.

5. (Optional) To change properties, set a new value for one or more of the Distributor properties that can be set on the ReplicationServer object.

6. (Optional) If the CachePropertyChanges property on the ReplicationServer object is set to true, call the CommitPropertyChanges method to commit the changes to the server.

To view and modify distribution database properties

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

2. Create an instance of the DistributionDatabase class. Specify the name property and pass the ServerConnection object from step 1.

3. Call the LoadProperties method to get the properties from the server. If this method returns false, the database with the specified name does not exist on the server.

4. (Optional) To change properties, set a new value for one of the DistributionDatabase properties that can be set.

5. (Optional) If the CachePropertyChanges property on the DistributionDatabase object is set to true, call the CommitPropertyChanges method to commit the changes to the server.

To view and modify Publisher properties

1. Create a connection to the Publisher by using the ServerConnection class.

2. Create an instance of the DistributionPublisher class. Specify the Name property and pass the ServerConnection object from step 1.

3. (Optional) To change properties, set a new value for one of the DistributionPublisher properties that can be set.

4. (Optional) If the CachePropertyChanges property on the DistributionPublisher object is set to true, call the CommitPropertyChanges method to commit the changes to the server.

To change the password for the administrative connection from the Publisher to the Distributor

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

2. Create an instance of the ReplicationServer class.

3. Set the ConnectionContext property to the connection created in step 1.

4. Call the Load method to get the properties of the object.

5. Call the ChangeDistributorPassword method. Pass the new password value for the password parameter.

   Security Note
   When possible, prompt users to enter security credentials at runtime. If you must store credentials, use the cryptographic services provided by the Microsoft Windows .NET Framework.

6. (Optional) Perform the following steps to change the password at each remote Publisher that uses this Distributor:

   1. Create a connection to the Publisher by using the ServerConnection class.

   2. Create an instance of the ReplicationServer class.

   3. Set the ConnectionContext property to the connection created in step 6a.

   4. Call the Load method to get the properties of the object.

   5. Call the ChangeDistributorPassword method. Pass the new password value from Step 5 for the password parameter.

Example (RMO)

This example shows how to change Distribution and distribution database properties.

Security Note
To avoid storing credentials in the code, the new Distributor password is supplied at runtime.

          // Set the Distributor and distribution database names.
            string distributionDbName = "distribution";
            string distributorName = publisherInstance;

            ReplicationServer distributor;
            DistributionDatabase distributionDb;

            // Create a connection to the Distributor using Windows Authentication.
            ServerConnection conn = new ServerConnection(distributorName);

            try
            {
                // Open the connection. 
                conn.Connect();

                distributor = new ReplicationServer(conn);

                // Load Distributor properties, if it is installed.
                if (distributor.LoadProperties())
                {
                    // Password supplied at runtime.
                    distributor.ChangeDistributorPassword(password);
                    distributor.AgentCheckupInterval = 5;

                    // Save changes to the Distributor properties.
                    distributor.CommitPropertyChanges();
                }
                else
                {
                    throw new ApplicationException(
                        String.Format("{0} is not a Distributor.", publisherInstance));
                }

                // Create an object for the distribution database 
                // using the open Distributor connection.
                distributionDb = new DistributionDatabase(distributionDbName, conn);

                // Change distribution database properties.
                if (distributionDb.LoadProperties())
                {
                    // Change maximum retention period to 48 hours and history retention 
                    // period to 24 hours.
                    distributionDb.MaxDistributionRetention = 48;
                    distributionDb.HistoryRetention = 24;

                    // Save changes to the distribution database properties.
                    distributionDb.CommitPropertyChanges();
                }
                else
                {
                    // Do something here if the distribution database does not exist.
                }
            }
            catch (Exception ex)
            {
                // Implement the appropriate error handling here. 
                throw new ApplicationException("An error occured when changing Distributor " +
                    " or distribution database properties.", ex);
            }
            finally
            {
                conn.Disconnect();
            }

' Set the Distributor and distribution database names.
Dim distributionDbName As String = "distribution"
Dim distributorName As String = publisherInstance

Dim distributor As ReplicationServer
Dim distributionDb As DistributionDatabase

' Create a connection to the Distributor using Windows Authentication.
Dim conn As ServerConnection = New ServerConnection(distributorName)

Try
    ' Open the connection. 
    conn.Connect()

    distributor = New ReplicationServer(conn)

    ' Load Distributor properties, if it is installed.
    If distributor.LoadProperties() Then
        ' Password supplied at runtime.
        distributor.ChangeDistributorPassword(password)
        distributor.AgentCheckupInterval = 5

        ' Save changes to the Distributor properties.
        distributor.CommitPropertyChanges()
    Else
        Throw New ApplicationException( _
            String.Format("{0} is not a Distributor.", publisherInstance))
    End If

    ' Create an object for the distribution database 
    ' using the open Distributor connection.
    distributionDb = New DistributionDatabase(distributionDbName, conn)

    ' Change distribution database properties.
    If distributionDb.LoadProperties() Then
        ' Change maximum retention period to 48 hours and history retention 
        ' period to 24 hours.
        distributionDb.MaxDistributionRetention = 48
        distributionDb.HistoryRetention = 24

        ' Save changes to the distribution database properties.
        distributionDb.CommitPropertyChanges()
    Else
        ' Do something here if the distribution database does not exist.
    End If
Catch ex As Exception
    ' Implement the appropriate error handling here. 
    Throw New ApplicationException("An error occured when changing Distributor " + _
        " or distribution database properties.", ex)
Finally
    conn.Disconnect()
End Try

[Top]

See Also

Tasks

View Information and Perform Tasks for a Publisher (Replication Monitor)

Concepts

Replication Management Objects Concepts

Disable Publishing and Distribution

Configure Distribution

Replication Management Objects Concepts

Distributor and Publisher Information Script

Replication System Stored Procedures Concepts