Export (0) Print
Expand All

How to: Create a Publication (RMO Programming)

You can create publications programmatically by using Replication Management Objects (RMO). The RMO classes that you use to create a publication depend on the type of publication you create.

ms146941.security(en-US,SQL.90).gifSecurity 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.

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

  2. Create an instance of the ReplicationDatabase class for the publication database, set the ConnectionContext property to the instance of ServerConnection from step 1, and call the LoadProperties method. If LoadProperties returns false, verify that the database exists.

  3. If the EnabledTransPublishing property is false, set it to true.

  4. For a transactional publication, check the value of the LogReaderAgentExists property. If this property is true, a Log Read Agent job already exists for this database. If this property is false, do the following:

  5. Create an instance of the TransPublication class, and set the following properties for this object:

    • The ServerConnection from step 1 for ConnectionContext.
    • The name of the published database for DatabaseName.
    • A name for the publication for Name.
    • A PublicationType of either Transactional or Snapshot.
    • The Login and Password fields of SnapshotGenerationAgentProcessSecurity to provide the credentials for the Windows account under which the Snapshot Agent runs. This account is also used when the Snapshot Agent makes connections to the local Distributor and for any remote connections when using Windows Authentication.
      ms146941.note(en-US,SQL.90).gifNote:
      Setting SnapshotGenerationAgentProcessSecurity is not required when the publication is created by a member of the sysadmin fixed server role. In this case, the agent will impersonate the SQL Server Agent account. For more information, see Replication Agent Security Model.

    • (Optional) The SqlStandardLogin and SqlStandardPassword or SecureSqlStandardPassword fields of SnapshotGenerationAgentPublisherSecurity when using SQL Server Authentication to connect to the Publisher.
    • (Optional) Use the inclusive logical OR operator (| in Visual C# and Or in Visual Basic) and the exclusive logical OR operator (^ in Visual C# and Xor in Visual Basic) to set the PublicationAttributes values for the Attributes property.
    • (Optional) The name of the Publisher for PublisherName when the Publisher is a non-SQL Server Publisher.
  6. Call the Create method to create the publication.

    ms146941.security(en-US,SQL.90).gifSecurity Note:
     When configuring a Publisher with a remote Distributor, the values supplied for all properties, including SnapshotGenerationAgentProcessSecurity, are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before calling the Create method. For more information, see Encrypting Connections to SQL Server.

  7. Call the CreateSnapshotAgent method to create the Snapshot Agent job for the publication.

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

  2. Create an instance of the ReplicationDatabase class for the publication database, set the ConnectionContext property to the instance of ServerConnection from step 1, and call the LoadProperties method. If LoadProperties returns false, verify that the database exists.

  3. If EnabledMergePublishing Property is false, set it to true, and call CommitPropertyChanges.

  4. Create an instance of the MergePublication class, and set the following properties for this object:

    • The ServerConnection from step 1 for ConnectionContext.
    • The name of the published database for DatabaseName.
    • A name for the publication for Name.
    • The Login and Password fields of SnapshotGenerationAgentProcessSecurity to provide the credentials for the Windows account under which the Snapshot Agent runs. This account is also used when the Snapshot Agent makes connections to the local Distributor and for any remote connections when using Windows Authentication.
      ms146941.note(en-US,SQL.90).gifNote:
      Setting SnapshotGenerationAgentProcessSecurity is not required when the publication is created by a member of the sysadmin fixed server role. For more information, see Replication Agent Security Model.

    • (Optional) Use the inclusive logical OR operator (| in Visual C# and Or in Visual Basic) and the exclusive logical OR operator (^ in Visual C# and Xor in Visual Basic) to set the PublicationAttributes values for the Attributes property.
  5. Call the Create method to create the publication.

    ms146941.security(en-US,SQL.90).gifSecurity Note:
     When configuring a Publisher with a remote Distributor, the values supplied for all properties, including SnapshotGenerationAgentProcessSecurity, are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before calling the Create method. For more information, see Encrypting Connections to SQL Server.

  6. Call the CreateSnapshotAgent method to create the Snapshot Agent job for the publication.

This example enables the AdventureWorks database for transactional publishing, defines a Log Reader Agent job, and creates the AdvWorksProductTran publication. An article must be defined for this publication. The Windows account credentials that are needed to create the Log Reader Agent job and the Snapshot Agent job are passed at runtime. To learn how to use RMO to define snapshot and transactional articles, see How to: Define an Article (RMO Programming).

// Set the Publisher, publication database, and publication names.
string publicationName = "AdvWorksProductTran";
string publicationDbName = "AdventureWorks";
string publisherName = publisherInstance;

ReplicationDatabase publicationDb;
TransPublication publication;

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


try
{
    // Connect to the Publisher.
    conn.Connect();

    // Enable the AdventureWorks database for transactional publishing.
    publicationDb = new ReplicationDatabase(publicationDbName, conn);

    // If the database exists and is not already enabled, 
    // enable it for transactional publishing.
    if (publicationDb.LoadProperties())
    {
        if (!publicationDb.EnabledTransPublishing)
        {
            publicationDb.EnabledTransPublishing = true;
        }

        // If the Log Reader Agent does not exist, create it.
        if (!publicationDb.LogReaderAgentExists)
        {
            // Specify the Windows account under which the agent job runs.
            // This account will be used for the local connection to the 
            // Distributor and all agent connections that use Windows Authentication.
            publicationDb.LogReaderAgentProcessSecurity.Login = winLogin;
            publicationDb.LogReaderAgentProcessSecurity.Password = winPassword;

            // Explicitly set authentication mode for the Publisher connection
            // to the default value of Windows Authentication.
            publicationDb.LogReaderAgentPublisherSecurity.WindowsAuthentication = true;

            // Create the Log Reader Agent job.
            publicationDb.CreateLogReaderAgent();
        }
    }
    else
    {
        throw new ApplicationException(String.Format(
            "The {0} database does not exist at {1}.",
            publicationDb, publisherName));
    }

    // Set the required properties for the transactional publication.
    publication = new TransPublication();
    publication.ConnectionContext = conn;
    publication.Name = publicationName;
    publication.DatabaseName = publicationDbName;

    // Specify a transactional publication (the default).
    publication.Type = PublicationType.Transactional;

    // Activate the publication so that we can add subscriptions.
    publication.Status = State.Active;

    // Enable push and pull subscriptions and independent Distribition Agents.
    publication.Attributes |= PublicationAttributes.AllowPull;
    publication.Attributes |= PublicationAttributes.AllowPush;
    publication.Attributes |= PublicationAttributes.IndependentAgent;

    // Specify the Windows account under which the Snapshot Agent job runs.
    // This account will be used for the local connection to the 
    // Distributor and all agent connections that use Windows Authentication.
    publication.SnapshotGenerationAgentProcessSecurity.Login = winLogin;
    publication.SnapshotGenerationAgentProcessSecurity.Password = winPassword;

    // Explicitly set the security mode for the Publisher connection
    // Windows Authentication (the default).
    publication.SnapshotGenerationAgentPublisherSecurity.WindowsAuthentication = true;

    if (!publication.IsExistingObject)
    {
        // Create the transactional publication.
        publication.Create();

        // Create a Snapshot Agent job for the publication.
        publication.CreateSnapshotAgent();
    }
    else
    {
        throw new ApplicationException(String.Format(
            "The {0} publication already exists.", publicationName));
    }
}

catch (Exception ex)
{
    // Implement custom application error handling here.
    throw new ApplicationException(String.Format(
        "The publication {0} could not be created.", publicationName), ex);
}
finally
{
    conn.Disconnect();
}

This example enables the AdventureWorks database for merge publishing and creates the AdvWorksSalesOrdersMerge publication. Articles must still be defined for this publication. The Windows account credentials that are needed to create the Snapshot Agent job are passed at runtime. To learn how to use RMO to define merge articles, see How to: Define an Article (RMO Programming).

// Set the Publisher, publication database, and publication names.
string publisherName = publisherInstance;
string publicationName = "AdvWorksSalesOrdersMerge";
string publicationDbName = "AdventureWorks";

ReplicationDatabase publicationDb;
MergePublication publication;

// Create a connection to the Publisher.
ServerConnection conn = new ServerConnection(publisherName);

try
{
    // Connect to the Publisher.
    conn.Connect();

    // Enable the database for merge publication.                
    publicationDb = new ReplicationDatabase(publicationDbName, conn);
    if (publicationDb.LoadProperties())
    {
        if (!publicationDb.EnabledMergePublishing)
        {
            publicationDb.EnabledMergePublishing = true;
        }
    }
    else
    {
        // Do something here if the database does not exist. 
        throw new ApplicationException(String.Format(
            "The {0} database does not exist on {1}.",
            publicationDb, publisherName));
    }

    // Set the required properties for the merge publication.
    publication = new MergePublication();
    publication.ConnectionContext = conn;
    publication.Name = publicationName;
    publication.DatabaseName = publicationDbName;

    // Enable precomputed partitions.
    publication.PartitionGroupsOption = PartitionGroupsOption.True;

    // Specify the Windows account under which the Snapshot Agent job runs.
    // This account will be used for the local connection to the 
    // Distributor and all agent connections that use Windows Authentication.
    publication.SnapshotGenerationAgentProcessSecurity.Login = winLogin;
    publication.SnapshotGenerationAgentProcessSecurity.Password = winPassword;

    // Explicitly set the security mode for the Publisher connection
    // Windows Authentication (the default).
    publication.SnapshotGenerationAgentPublisherSecurity.WindowsAuthentication = true;

    // Enable Subscribers to request snapshot generation and filtering.
    publication.Attributes |= PublicationAttributes.AllowSubscriberInitiatedSnapshot;
    publication.Attributes |= PublicationAttributes.DynamicFilters;

    // Enable pull and push subscriptions.
    publication.Attributes |= PublicationAttributes.AllowPull;
    publication.Attributes |= PublicationAttributes.AllowPush;

    if (!publication.IsExistingObject)
    {
        // Create the merge publication.
        publication.Create();
        
        // Create a Snapshot Agent job for the publication.
        publication.CreateSnapshotAgent();
    }
    else
    {
        throw new ApplicationException(String.Format(
            "The {0} publication already exists.", publicationName));
    }
}

catch (Exception ex)
{
    // Implement custom application error handling here.
    throw new ApplicationException(String.Format(
        "The publication {0} could not be created.", publicationName), ex);
}
finally
{
    conn.Disconnect();
}

Community Additions

ADD
Show:
© 2014 Microsoft