define subscription

Description

Adds a subscription to the Replication Server system tables, but does not materialize or activate the subscription. The subscription may be for a database replication definition, a table replication definition, a function replication definition, or for a publication. This command begins the process of bulk subscription materialization, or the process of refreshing a publication subscription.

Syntax

define subscription sub_name
for {table_rep_def | function_rep_def |
     publication pub_name | database replication definition db_repdef  
     with primary at data_server.database} |
with replicate at data_server.database
     [where {column_name | @param_name}
     {< | > | >= | <= | = | &} value
     [and {column_name | @param_name}
     {< | > | >= | <= | = | &} value]...]
[subscribe to truncate table]
[for new articles]
[use dump marker]

Parameters

Examples

Example 1

Creates a subscription named titles_sub. It specifies that rows from the titles table with columns of the type “business” are to be replicated in the titles table in the pubs2 database of the data server named SYDNEY_DS:

define subscription titles_sub
  for titles_rep
    with replicate at SYDNEY_DS.pubs2
    where type = 'business'

Example 2

Creates a subscription named titles_sub that includes rows from the titles table with prices that are greater than or equal to $10.00:

define subscription titles_sub
  for titles_rep
    with replicate at SYDNEY_DS.pubs2
    where price >= $10.00

Example 3

Creates a subscription named myproc_sub for the function replication definition myproc_rep:

define subscription myproc_sub
  for myproc_rep
    with replicate at SYDNEY_DS.pubs2

Example 4

Creates a subscription named pubs2_sub for the publication pubs2_pub:

define subscription pubs2_sub
  for publication pubs2_pub
    with primary at TOKYO_DS.pubs2
    with replicate at SYDNEY_DS.pubs2

Example 5

Creates a subscription pubs2_sub for the database replication definition pubs2_rep:

define subscription pubs2_sub
  for database replication definition pubs2_rep 
    with primary at NEWYORK_DS.pubs2
    with replicate at TOKYO_DS.pubs2
    subscribe to truncate table
    use dump marker

Refer to the Replication Server Design Guide for examples of creating subscriptions for a complete replication system.

Usage

• Use define subscription to create a subscription manually using bulk materialization. With bulk materialization, subscription creation and materialization is performed in discrete steps so that you can load the initial data from media rather than sending it from the primary database through the WAN.

• If you have added any new articles to a publication with an existing subscription, you must refresh the publication subscription in order to create new subscriptions for these articles.

• Activate the subscription using activate subscription and validate the subscription using validate subscription.

• Although you can create multiple replication definitions for the same primary table, you cannot subscribe to more than one replication definition for the same replicate table. However, you can subscribe to the same replication definition more than once.

• You can create a subscription for a valid publication to begin replication to a replicate database. All forms of subscription materialization are supported.

• Use define subscription to create new article subscriptions in the publication subscription. Then use activate subscription, manually load the subscription data for the new article subscriptions, and use validate subscription to validate the publication subscription.

• When you create a publication subscription, Replication Server creates a separate underlying subscription for each article that the publication contains. Each article subscription uses the name of the parent publication subscription.

• When you activate and validate a publication subscription, all of its article subscriptions are activated and validated at the same time.

• A subscription to a publication cannot include a where clause. Instead, you can customize replication to replicate sites by including one or more where clauses in each article the publication contains.

• When you create a database subscription, you cannot use the where clause to limit data subscription. All data is subscribed.

• With database subscriptions, you can use only the no materialization or bulk materialization methods. Use define subscription to use dump and load or other bulk materialization method. Use create subscription to use the no materialization method.

• You cannot subscribe to more than one database replication definition from the same origin.

• When you create the first subscription for a table, you can either include or not include the subscribe to truncate table option. Each subsequent subscription that copies information into the same table must follow the example of the first subscription. Otherwise, it will be rejected when you try to create it.

• You can view or change the current “subscribe to truncate table” status of a particular replicate table by executing sysadmin apply_truncate_status.

See create subscription for information about working with columns or parameters that use the rs_address datatype.

In addition to the permissions listed below, make sure these requirements are met before you execute this command.

• For a subscription to a table replication definition:

  • A replication definition exists for the primary table you are replicating, and the table is marked for replication with sp_setreptable.

  • Tables referenced in the replication definition exist in both the primary and the replicate database. Each table has the columns and datatypes defined in the replication definition.

    This table is also visible to the user creating the subscription and the user maintaining it. The easiest way to achieve this is to have the Database Owner create the table.

  For a subscription to a function replication definition:

  • A replication definition exists for the stored procedure you are replicating, and the stored procedure is marked for replication with sp_setrepproc.

  • Stored procedures referenced in the function replication definition exist in both the primary and replicate database. Each table has the parameters and datatypes defined in the function replication definition.

  For a subscription to a publication:

  • A publication exists that contains articles for the primary tables or stored procedure you are replicating. The articles specify replication definitions that meet the requirements described above.

  • The publication is valid.

• You can use define subscription to subscribe to a table replication definition, a function replication definition, or a publication.

  • For a subscription to a table replication definition, enter define subscription at the Replication Server that manages the database where the replicate data is to be stored.

  • For a subscription to a function replication definition, enter define subscription at the Replication Server that manages the database where the destination stored procedure is to be executed via applied function delivery.

  • For a subscription to a publication, enter define subscription at the Replication Server that manages the database where the replicate data is to be stored or where destination stored procedures are to be executed.

• A table subscription maintains a replicate copy of a table, or selected rows from a table, in a database. Changes made to the primary version are also applied to the copy.

• A function subscription replicates user-defined function invocations associated with a function replication definition. A replicated function typically includes parameters and modifies data, but it need not involve replicated data.

• A publication subscription involves underlying subscriptions for the articles the publication contains, which replicate table or user-defined function invocations depending on the replication definitions in the article.

• See the Replication Server Administration Guide Volume 1 for more information about subscriptions and the role they play in replication.

• Use create subscription to create, materialize, activate, and validate, in a single step, a subscription for a table replication definition, function definition replication, or publication.

Permissions

To execute define subscription, you must have the following login names and permissions:

• The same login name and password at the replicate Replication Server, primary Replication Server, and primary database.

• “create object” or “sa” permission at the replicate Replication Server where you enter this command.”

• “create object”, “primary subscribe”, or “sa” permission at the primary Replication Server.

See also