Publishing Data and Database Objects

SQL Replication


Publishing Data and Database Objects

When creating a publication, you can choose the tables, filtered partitions of data, and database objects that you want to publish.

A table used in a snapshot or transactional publication can have a maximum of 255 columns and a maximum row size of 8,000 bytes. A table used in a merge publication can have a maximum of 246 columns and a maximum row size of 6,000 bytes.

Horizontal, vertical, dynamic, and join filters enable you to create partitions of data to be published. By filtering published data, you can:

  • Minimize the amount of data sent over the network.

  • Reduce the amount of storage space required at the Subscriber.

  • Achieve better security; the Subscriber sees only data that that they need to see.

  • Customize publications and applications based on individual Subscriber requirements.

  • Avoid conflicts because the different data partitions can be sent to different Subscribers (limiting the number of Subscribers likely to be updating the same data values).

  • Restrict visibility of sensitive data to Subscribers. For example, the Employees table might be vertically filtered to exclude the employee salary or review information because that is sensitive and might be information that is not necessary at the Subscriber.

Horizontal (row) filters and vertical (column) filters are available for snapshot replication, transactional replication, and merge replication. Dynamic and join filters are available for merge replication. However, by using transformable subscriptions, you can create custom partitions for snapshot replication and transactional replication that are similar to dynamic partitions. For information about creating filtered partitions of data, see Filtering Published Data. For information about dynamic partitions in snapshot or transactional replication, see Using Transformable Subscriptions to Create Custom Data Partitions.

For information about the specific data types, see Data Needs and Characteristics and Planning for Each Type of Replication.

Note  When you create a publication using an existing publication as the template, the Publication Access List (PAL) of the original publication will not be copied to the second publication. You must re-create any PAL settings manually using publication properties and the Publication Access List tab after the publication is created.

Publishing Database Objects

The following database objects can be published with Microsoft® SQL Server™ 2000 replication.

Database Object
Snapshot Replication or Transactional Replication
Merge Replication
Tables X X
Stored Procedures – Definition X X
Stored Procedures – Execution X  
Views X X
Indexed Views X X
Indexed Views as Tables X  
User-Defined Functions X X

When you publish these objects, their definitions are copied to Subscribers. When you add or drop columns to a publication database, those changes to the definitions of the objects will be propagated to Subscribers. Changes to the definition of other types of objects may not be copied to Subscribers automatically.

When you change data in a published table, or run a stored procedure published for execution, the data changes that are made will be propagated to Subscribers.

If you are publishing a database object that references other database objects, you must publish all objects referenced by the object. For example, the Products Above Average Price view on the Northwind database retrieves data from the PRODUCTS tables. If you publish this view, you must also publish the PRODUCTS tables as part of the publication.

A publication containing a stored procedure definition might be replicated even if you do not publish the database objects that the stored procedure references; however, when trying to execute that stored procedure at the Subscriber, you will get an error. This occurs because of deferred name resolution, where object dependencies are checked when the stored procedure is executed rather than when the stored procedure is created.

Publishing Views, User-Defined Functions, Stored Procedure Definitions, and Triggers

After you create views, user-defined functions, and stored procedure definitions in a database, they will appear as objects in the Create Publication Wizard in the Specify Articles dialog box.

When you replicate these objects, the definitions are replicated as part of the initial snapshot applied at the Subscriber. Subsequent changes to the definition of these objects are not copied automatically to Subscribers. However, replicating the definition of these objects can provide a convenient mechanism for deploying these components of your application to Subscribers.

When publishing indexed views that are not schema-only articles for snapshot replication or transactional replication, you do not have to replicate the view as a table. When the view is published to the Subscriber, a table is created on the Subscriber that contains the data the view is based upon. Indexing a view as a table at a Subscriber can be a convenient way of replicating the contents of a view without requiring that each of the tables that comprise the view definition are replicated as well. An indexed view published as a table article cannot be partitioned vertically using column filters.

Triggers are defined as part of a table and are published as a schema option when that table is replicated as part of a publication. To publish triggers for a table article that is being published:

  1. Right click a publication, and then click Properties.

  2. On the Articles tab, click the properties button (...) for a specific table article.

  3. In the Table Articles Properties dialog box, on the Snapshot tab, select the User triggers check box under Copy objects to destination.

Note  If you are publishing to a Subscriber running an earlier version of SQL Server, you are limited to the functionality of that version. For example, you will not be able to publish views, user-defined functions, triggers and schema objects to Subscribers running SQL Server 7.0.

Schema Objects

In addition to the database objects listed in the table earlier, you can also specify if you want schema objects to be copied, such as declared referential integrity (primary key constraints, reference constraints, unique constraints), clustered indexes, nonclustered indexes, user triggers, extended properties, and collation.

Encrypted Database Objects

Stored procedures, views, triggers, and user-defined functions that are marked with ENCRYPTION or WITH ENCRYPTION cannot be published as part of SQL Server replication.

To create publications and define articles