Skip to content

Latest commit

 

History

History
154 lines (94 loc) · 13.2 KB

define-an-article.md

File metadata and controls

154 lines (94 loc) · 13.2 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords dev_langs
Define an Article
Define an Article
MashaMSFT
mathoma
03/14/2017
sql
replication
how-to
updatefrequency5
articles [SQL Server replication], defining
sp_addmergearticle
adding articles
sp_addarticle
articles [SQL Server replication], adding
TSQL

Define an Article

[!INCLUDE SQL Server] This topic describes how to define an article in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull], [!INCLUDEtsql], or Replication Management Objects (RMO).

In This Topic

Before You Begin

Limitations and Restrictions

  • Article names cannot include any of the following characters: % , * , [ , ] , | , : , " , ? , ' , \ , / , < , >. If objects in the database include any of these characters and you want to replicate them, you must specify an article name that is different from the object name.

Security

When possible, prompt users to enter security credentials at runtime. If you must store credentials, use the cryptographic services provided by the [!INCLUDEmsCoName] Windows .NET Framework.

Using SQL Server Management Studio

Create publications and define articles with the New Publication Wizard. After a publication is created, view and modify publication properties in the Publication Properties - <Publication> dialog box. For information about creating a publication from an Oracle database, see Create a Publication from an Oracle Database.

To create a publication and define articles

  1. Connect to the Publisher in [!INCLUDEmsCoName] [!INCLUDEssManStudioFull], and then expand the server node.

  2. Expand the Replication folder, and then right-click the Local Publications folder.

  3. Click New Publication.

  4. Follow the pages in the New Publication Wizard to:

    • Specify a Distributor if distribution has not been configured on the server. For more information about configuring distribution, see Configure Publishing and Distribution.

      If you specify on the Distributor page that the Publisher server will act as its own Distributor (a local Distributor), and the server is not configured as a Distributor, the New Publication Wizard will configure the server. You will specify a default snapshot folder for the Distributor on the Snapshot Folder page. The snapshot folder is simply a directory that you have designated as a share; agents that read from and write to this folder must have sufficient permissions to access it. For more information about securing the folder appropriately, see Secure the Snapshot Folder.

      If you specify that another server should act as the Distributor, you must enter a password on the Administrative Password page for connections made from the Publisher to the Distributor. This password must match the password specified when the Publisher was enabled at the remote Distributor.

      For more information, see Configure Distribution.

    • Choose a publication database.

    • Select a publication type. For more information, see Types of Replication.

    • Specify data and database objects to publish; optionally filter columns from table articles, and set article properties.

    • Optionally filter rows from table articles. For more information, see Filter Published Data.

    • Set the Snapshot Agent schedule.

    • Specify the credentials under which the following replication agents run and make connections:

      - Snapshot Agent for all publications.

      - Log Reader Agent for all transactional publications.

      - Queue Reader Agent for transactional publications that allow updating subscriptions.

      For more information, see Replication Agent Security Model and Replication Security Best Practices.

    • Optionally script the publication. For more information, see Scripting Replication.

    • Specify a name for the publication.

Using Transact-SQL

After a publication has been created, articles can be created programmatically using replication stored procedures. The stored procedures used to create an article will depend on the type of publication for which the article is being defined. For more information, see Create a Publication.

To define an article for a Snapshot or Transactional Publication

  1. At the Publisher on the publication database, execute sp_addarticle. Specify the name of the publication to which the article belongs for @publication, a name for the article for @article, the database object being published for @source_object, and any other optional parameters. Use @source_owner to specify the schema ownership of the object, if not dbo. If the article is not a log-based table article, specify the article type for @type; for more information, see Specify Article Types (Replication Transact-SQL Programming).

  2. To horizontally filter rows in a table or view an article, use sp_articlefilter to define the filter clause. For more information, see Define and Modify a Static Row Filter.

  3. To vertically filter columns in a table or view an article, use sp_articlecolumn. For more information, see Define and Modify a Column Filter.

  4. Execute sp_articleview if the article is filtered.

  5. If the publication has existing subscriptions and sp_helppublication returns a value of 0 in the immediate_sync column, you must call sp_addsubscription to add the article to each existing subscription.

  6. If the publication has existing pull subscriptions, execute sp_refreshsubscriptions at the Publisher to create a new snapshot for existing pull subscriptions that contains just the new article.

    [!NOTE]
    For subscriptions that are not initialized using a snapshot, you do not need to execute sp_refreshsubscriptions as this procedure is executed by sp_addarticle.

To define an article for a Merge Publication

  1. At the Publisher on the publication database, execute sp_addmergearticle. Specify the name of the publication for @publication, a name for the article name for @article, and the object being published for @source_object. To horizontally filter table rows, specify a value for @subset_filterclause. For more information, see Define and Modify a Parameterized Row Filter for a Merge Article and Define and Modify a Static Row Filter. If the article is not a table article, specify the article type for @type. For more information, see Specify Article Types (Replication Transact-SQL Programming).

  2. (Optional) At the Publisher on the publication database, execute sp_addmergefilter to define a join filter between two articles. For more information, see Define and Modify a Join Filter Between Merge Articles.

  3. (Optional) At the Publisher on the publication database, execute sp_mergearticlecolumn to filter table columns. For more information, see Define and Modify a Column Filter.

Examples (Transact-SQL)

This example defines an article based on the Product table for a transactional publication, where the article is filtered both horizontally and vertically.

:::code language="sql" source="../codesnippet/tsql/define-an-article_1.sql":::

This example defines articles for a merge publication, where the SalesOrderHeader article is statically filtered based on SalesPersonID, and the SalesOrderDetail article is join filtered based on SalesOrderHeader.

:::code language="sql" source="../codesnippet/tsql/define-an-article_2.sql":::

Using Replication Management Objects (RMO)

You can define articles programmatically by using Replication Management Objects (RMO). The RMO classes that you use to define an article depend on the type of publication for which the article is defined.

Examples (RMO)

The following example adds an article with row and column filters to a transactional publication.

[!code-csHowTo#rmo_CreateTranArticles]

[!code-vbHowTo#rmo_vb_CreateTranArticles]

The following example adds three articles to a merge publication. The articles have column filters, and two join filters are used to propagate a parameterized row filter to the other articles.

[!code-csHowTo#rmo_CreateMergeArticles]

[!code-vbHowTo#rmo_vb_CreateMergeArticles]

See Also

Create a Publication
Replication System Stored Procedures Concepts
Add Articles to and Drop Articles from Existing Publications
Filter Published Data
Publish Data and Database Objects
Replication System Stored Procedures Concepts