Publish Data and Database Objects
Applies to: 
SQL Server
Azure SQL Managed Instance
When creating a publication, you choose the tables and other database objects that you want to publish. You can publish the following database objects using replication.
| Database object | Snapshot replication and transactional replication | Merge replication |
|---|---|---|
| Tables | X | X |
| Partitioned Tables | X | X |
| Stored Procedures – Definition (Transact-SQL and CLR) | X | X |
| Stored Procedures – Execution (Transact-SQL and CLR) | X | no |
| Views | X | X |
| Indexed Views | X | X |
| Indexed Views as Tables | X | no |
| User-Defined Types (CLR) | X | X |
| User-Defined Functions (Transact-SQL and CLR) | X | X |
| Alias Data Types | X | X |
| Full text indexes | X | X |
| Schema Objects (constraints, indexes, user DML triggers, extended properties, and collation) | X | X |
Creating Publications
To create a publication, you supply the following information:
- The Distributor.
- The location of the snapshot files.
- The publication database.
- The type of publication to create (snapshot, transactional, transactional with updatable subscriptions, or merge).
- The data and database objects (articles) to include in the publication.
- Static row filters and column filters for all types of publications, and parameterized row filters and join filters for merge publications.
- The Snapshot Agent schedule.
- Accounts under which the following agents will run: the Snapshot Agent for all publications; the Log Reader Agent for all transactional publications; the Queue Reader Agent for transactional publications that allow updating subscriptions.
- A name and description for the publication.
For information about how to work with publications, see the following topics:
- Create a Publication
- Define an Article
- View and Modify Publication Properties
- View and Modify Article Properties
- Delete a Publication
- Delete an Article
Note
Deleting an article or publication does not remove objects from the Subscriber.
Publishing Tables
The most commonly published object is a table. The following links provide additional information about areas related to publishing tables:
- Filter Published Data
- Article Options for Transactional Replication
- Article Options for Merge Replication
- Replicate Identity Columns
When publishing a table for replication, you can specify which schema objects should be copied to the Subscriber, such as declared referential integrity (primary key constraints, reference constraints, unique constraints), indexes, user DML triggers (DDL triggers cannot be replicated), extended properties, and collation. Extended properties are replicated only in the initial synchronization between the Publisher and the Subscriber. If you add or modify an extended property after the initial synchronization, the change is not replicated.
To specify schema options, see Specify Schema Options or SchemaOption.
Partitioned Tables and Indexes
Replication supports the publishing of partitioned tables and indexes. The level of support depends on the type of replication that is used, and the options that you specify for the publication and the articles associated with partitioned tables. For more information, see Replicate Partitioned Tables and Indexes.
Publishing Stored Procedures
All types of replication allow you to replicate stored procedure definitions: the CREATE PROCEDURE is copied to each Subscriber. In the case of common language runtime (CLR) stored procedures, the associated assembly is also copied. Changes to procedures are replicated to Subscribers; changes to associated assemblies are not.
In addition to replicating the definition of a stored procedure, transactional replication allows you to replicate the execution of stored procedures. This is useful in replicating the results of maintenance-oriented stored procedures that affect large amounts of data. For more information, see Publishing Stored Procedure Execution in Transactional Replication.
Publishing Views
All types of replication allow you to replicate views. The view (and its accompanying index, if it is an indexed view) can be copied to the Subscriber, but the base table must also be replicated.
For indexed views, transactional replication also allows you to replicate the indexed view as a table rather than a view, eliminating the need to also replicate the base table. To do this, specify one of the "indexed view logbased" options for the @type parameter of sp_addarticle (Transact-SQL). For more information about using sp_addarticle, see Define an Article.
Publishing User-Defined Functions
The CREATE FUNCTION statements for CLR functions and Transact-SQL functions are copied to each Subscriber. In the case of CLR functions, the associated assembly is also copied. Changes to functions are replicated to Subscribers; changes to associated assemblies are not.
Publishing User-Defined Types and Alias Data Types
Columns that use user-defined types or alias data types are replicated to Subscribers like other columns. The CREATE TYPEstatement for each replicated type is executed at the Subscriber before the table is created. In the case of user-defined types, the associated assembly is also copied to each Subscriber. Changes to user-defined types and alias data types are not replicated to Subscribers.
If a type is defined in a database, but it is not referenced in any columns when a publication is created, the type is not copied to Subscribers. If you subsequently create a column of that type in the database and want to replicate it, you must first manually copy the type (and the associated assembly for a user-defined type) to each Subscriber.
Publishing Full Text Indexes
The CREATE FULLTEXT INDEX statement is copied to each Subscriber, and the full text index is created at the Subscriber. Changes made to full text indexes using ALTER FULLTEXT INDEX are not replicated.
Making Schema Changes to Published Objects
Replication supports a wide range of schema changes to published objects. When you make any of the following schema changes on the appropriate published object at a SQL Server Publisher, that change is propagated by default to all SQL Server Subscribers:
ALTER TABLE
ALTER VIEW
ALTER PROCEDURE
ALTER FUNCTION
ALTER TRIGGER
For more information, see Make Schema Changes on Publication Databases.
Considerations for Publishing
Keep the following issues in mind when publishing database objects:
The database is accessible to users during the creation of the publication and the initial snapshot, but it is advisable to create publications during times of lower activity on the Publisher.
A database cannot be renamed after a publication is created in it. To rename it, you must first remove replication from the database.
If you are publishing a database object that depends on one or more other database objects, you must publish all referenced objects. For example, if you publish a view that depends on a table, you must publish the table also.
Note
If you add an article to a merge publication and an existing article depends on the new article, you must specify a processing order for both articles using the @processing_order parameter of sp_addmergearticle and sp_changemergearticle. Consider the following scenario: you publish a table but you do not publish a function that the table references. If you do not publish the function, the table cannot be created at the Subscriber. When you add the function to the publication: specify a value of 1 for the @processing_order parameter of sp_addmergearticle; and specify a value of 2 for the @processing_order parameter of sp_changemergearticle, specifying the table name for the parameter @article. This processing order ensures that you create the function at the Subscriber before the table that depends on it. You can use different numbers for each article as long as the number for the function is lower than the number for the table.
Publication names cannot include the following characters: % * [ ] | : " ? \ / < >.
Limitations on Publishing Objects
The maximum number of articles and columns that can be published differs by publication type. For more information, see the "Replication Objects" section of Maximum Capacity Specifications for SQL Server.
Stored procedures, views, triggers, and user-defined functions that are defined as WITH ENCRYPTION cannot be published as part of SQL Server replication.
XML schema collections can be replicated but changes are not replicated after the initial snapshot.
Tables published for transactional replication must have a primary key. If a table is in a transactional replication publication, you cannot disable any indexes that are associated with primary key columns. These indexes are required by replication. To disable an index, you must first drop the table from the publication.
Bound defaults created with sp_bindefault (Transact-SQL) are not replicated (bound defaults are deprecated in favor of defaults created with the DEFAULT keyword of ALTER TABLE or CREATE TABLE).
Functions containing the NOEXPAND hint on indexed views cannot be published in the same publication as the referenced tables and indexed views, due to the order in which the distribution agent delivers them. To work around this problem, place the table and indexed view creation in a first publication, and add functions containing the NOEXPAND hint on the indexed views to a second publication which you publish after the first publication completes. Or, create scripts for these functions and deliver the script by using the @post_snapshot_script parameter of sp_addpublication.
Schemas and Object Ownership
Replication has the following default behavior in the New Publication Wizard with respect to schemas and object ownership:
For articles in merge publications with a compatibility level of 90 or higher, snapshot publications, and transactional publications: by default, the object owner at the Subscriber is the same as the owner of the corresponding object at the Publisher. If the schemas that own objects do not exist at the Subscriber, they are created automatically.
For articles in merge publications with a compatibility level lower than 90: by default, the owner is left blank and is specified as dbo during the creation of the object on the Subscriber.
For articles in Oracle publications: by default, the owner is specified as dbo.
For articles in publications that use character mode snapshots (which are used for non-SQL Server Subscribers and SQL Server Compact Subscribers): by default, the owner is left blank. The owner defaults to the owner associated with the account used by the Distribution Agent or Merge Agent to connect to the Subscriber.
The object owner can be changed through the Article Properties - <Article> dialog box and through the following stored procedures: sp_addarticle, sp_addmergearticle, sp_changearticle, and sp_changemergearticle. For more information, see View and Modify Publication Properties, Define an Article, and View and Modify Article Properties.
Publishing Data to Subscribers Running Previous Versions of SQL Server
If you are publishing to a Subscriber running a previous version of SQL Server, you are limited to the functionality of that version, both in terms of replication-specific functionality and the functionality of the product as a whole.
Merge publications use a compatibility level, which determines what features can be used in a publication and allows you to support Subscribers running previous versions of SQL Server.
Publishing Tables in More Than One Publication
Replication supports publishing articles in multiple publications (including republishing data) with the following restrictions:
If an article is published in a transactional publication and a merge publication, ensure that the @published_in_tran_pub property is set to TRUE for the merge article. For more information about setting properties, see View and Modify Publication Properties and View and Modify Article Properties.
You should also set the @published_in_tran_pub property if an article is part of a transactional subscription and is included in a merge publication. If this is the case, be aware that by default transactional replication expects tables at the Subscriber to be treated as read-only; if merge replication makes data changes to a table in a transactional subscription, non-convergence of data can occur. To avoid this possibility, we recommend that any such table be specified as download-only in the merge publication. This prevents a merge Subscriber from uploading data changes to the table. For more information, see Optimize Merge Replication Performance with Download-Only Articles.
An article cannot be published in both a merge publication and a transactional publication with queued updating subscriptions.
Articles included in transactional publications that support updating subscriptions cannot be republished.
If an article is published in more than one transactional publication that supports queued updating subscriptions, the following properties must have the same value for the article across all publications:
Property Parameter in sp_addarticle Identity range management @auto_identity_range (deprecated) and @identityrangemangementoption Publisher identity range @pub_identity_range Identity range @identity_range Identity range threshold @threshold For more information about these parameters, see sp_addarticle (Transact-SQL).
If an article is published in more than one merge publication, the following properties must have the same value for the article across all publications:
Property Parameter in sp_addmergearticle Column tracking @column_tracking Schema options @schema_option Column filtering @vertical_partition Subscriber upload options @subscriber_upload_options Conditional delete tracking @delete_tracking Error compensation @compensate_for_errors Identity range management @auto_identity_range (deprecated) and @identityrangemangementoption Publisher identity range @pub_identity_range Identity range @identity_range Identity range threshold @threshold Partition options @partition_options Blob column streaming @stream_blob_columns Filter type @filter_type (parameter in sp_addmergefilter) For more information about these parameters, see sp_addmergearticle (Transact-SQL) and sp_addmergefilter (Transact-SQL).
Transactional replication and unfiltered merge replication support publishing a table in multiple publications and then subscribing within a single table in the subscription database (commonly referred to as a roll up scenario). Roll up is often used for aggregating subsets of data from multiple locations in one table at a central Subscriber. Filtered merge publications do not support the central Subscriber scenario. For merge replication, roll up is typically implemented through a single publication with parameterized row filters. For more information, see Parameterized Row Filters.