Frequently Asked Questions for Replication Administrators

No. Activity can continue on a database while a publication is being created. Be aware that producing a snapshot can be resource-intensive, so it is best to generate snapshots during periods of lower activity on the database (by default a snapshot is generated when you complete the New Publication Wizard).

The length of time that the locks are taken depends on the type of replication used:

• For merge publications, the Snapshot Agent does not take any locks.

• For transactional publications, by default the Snapshot Agent takes locks only during the initial phase of snapshot generation.

• For snapshot publications the Snapshot Agent takes locks during the entire snapshot generation process.

Because locks prevent other users from updating the tables, the Snapshot Agent should be scheduled to execute during periods of lower activity on the database, especially for snapshot publications.

A subscription is available after the snapshot has been applied to the subscription database. Even though the subscription database is accessible prior to this, the database should not be used until after the snapshot has been applied. Use Replication Monitor to check the status of snapshot generation and application:

• The snapshot is generated by the Snapshot Agent. View the status of snapshot generation on the Agents tab for a publication in Replication Monitor. For more information, see View information and perform tasks using Replication Monitor.

• The snapshot is applied by the Distribution Agent or Merge Agent. View the status of snapshot application in the Distribution Agent or Merge Agent page of Replication Monitor. For more information, see View information and perform tasks using Replication Monitor.

It will not cause an error if the Distribution Agent or Merge Agent runs at the same time as the Snapshot Agent. However, you must be aware of the following:

• If the Distribution Agent or Merge Agent is configured to run continuously, the agent applies the snapshot automatically after the Snapshot Agent completes.

• If the Distribution Agent or Merge Agent is configured to run on a schedule or on-demand, and there is no snapshot available when the agent runs, the agent will shut down with a message stating that a snapshot is not yet available. You must run the agent again to apply the snapshot after the Snapshot Agent has completed. For more information on running agents, see Synchronize a Push Subscription, Synchronize a Pull Subscription, and Replication Agent Executables Concepts.

Yes. Scripting the replication configuration is a key part of any disaster recovery plan for a replication topology. For more information on scripting, see Scripting Replication.

Replication functions properly using any of the recovery models: simple, bulk-logged, or full. Merge replication tracks change by storing information in metadata tables. Transactional replication tracks changes by marking the transaction log, but this marking process is not affected by the recovery model.

To track changes, merge replication and transactional replication with queued updating subscriptions must be able to uniquely identify every row in every published table. To accomplish this:

• Merge replication adds the column rowguid to every table, unless the table already has a column of data type uniqueidentifier with the ROWGUIDCOL property set (in which case this column is used). If the table is dropped from the publication, the rowguid column is removed; if an existing column was used for tracking, the column is not removed.

• If a transactional publication supports queued updating subscriptions, replication adds the column msrepl_tran_version to every table. If the table is dropped from the publication, the msrepl_tran_version column is not removed.

• A filter must not include the rowguidcol used by replication to identify rows. By default this is the column added at the time you set up merge replication and is named rowguid.

There are a number of issues to consider regarding constraints on published tables:

• Transactional replication requires a primary key constraint on each published table. Merge replication does not require a primary key, but if one is present, it must be replicated. Snapshot replication does not require a primary key.

• By default, primary key constraints, indexes, and check constraints are replicated to Subscribers.

• The NOT FOR REPLICATION option is specified by default for foreign key constraints and check constraints; the constraints are enforced for user operations but not agent operations.

For information on setting the schema options that control whether constraints are replicated, see Specify Schema Options.

Replication provides automatic identity range management for replication topologies that include updates at the Subscriber. For more information, see Replicate Identity Columns.

Yes, but with some restrictions. For more information, see the section "Publishing Tables in More Than One Publication" in the topic Publish Data and Database Objects.

Yes. There are no restrictions on the number or types of publications that can use the same distribution database. All publications from a given Publisher must use the same Distributor and distribution database.

If you have multiple publications, you can configure multiple distribution databases at the Distributor to ensure that the data flowing through each distribution database is from a single publication. Use the Distributor Properties dialog box or sp_adddistributiondb (Transact-SQL) to add a distribution database. For more information about accessing the dialog box, see View and Modify Distributor and Publisher Properties.

This information is available through SQL Server Management Studio, and a number of replication stored procedures. For more information, see Distributor and Publisher Information Script.

No. Replication does not encrypt data that is stored in the database or transferred over the network. For more information, see the "Encryption" section of the topic View and modify replication security settings.

Replicate data over the Internet using:

• A Virtual Private Network (VPN). For more information, see Publish Data over the Internet Using VPN.

• The Web synchronization option for merge replication. For more information, see Web Synchronization for Merge Replication.

All types of Microsoft SQL Server replication can replicate data over a VPN, but you should consider Web synchronization if you are using merge replication.

Yes. Replication processing resumes at the point at which it left off if a connection is dropped. If you are using merge replication over an unreliable network, consider using logical records, which ensures related changes are processed as a unit. For more information, see Group Changes to Related Rows with Logical Records.

Yes, replication does work over low bandwidth connections. For connections over TCP/IP, it uses the compression provided by the protocol but does not provide additional compression. For Web synchronization connections over HTTPS, it uses the compression provided by the protocol and also additional compression of the XML files used to replicate changes.

No, replication works only when you use the actual server name. Beginning in SQL Server Management Studio (SSMS) 18.0 (and later versions), replication can be configured by using the actual server name and port number.

No. You could create an SSIS package to transfer logins and passwords from a Publisher to one or more Subscribers.

Beginning with Microsoft SQL Server 2005 (9.x), schema has two meanings:

• The definition of an object, such as a CREATE TABLE statement. By default, replication copies the definitions of all replicated objects to the Subscriber.

• The namespace within which an object is created: <Database>.<Schema>.<Object>. Schemas are defined using the CREATE SCHEMA statement.

• 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.

By default, replication does not execute GRANT statements on the subscription database. If you want the permissions on the subscription database to match those on the publication database, use one of the following methods:

• Execute GRANT statements at the subscription database directly.

• Use a post-snapshot script to execute the statements. For more information, see Execute Scripts Before and After the Snapshot Is Applied.

• Use the stored procedure sp_addscriptexec to execute the statements.

By default, objects at the Subscriber are dropped and recreated when a subscription is reinitialized, which causes all granted permissions for those objects to be dropped. There are two ways to handle this:

• Reapply the grants after the reinitialization using the techniques described in the previous section.

• Specify that objects should not be dropped when the subscription is reinitialized. Prior to reinitialization, either:

  • Execute sp_changearticle or sp_changemergearticle. Specify a value of 'pre_creation_cmd' (sp_changearticle) or 'pre_creation_command' (sp_changemergearticle) for the parameter @property and a value of 'none', 'delete' or 'truncate' for the parameter @value.

  • In the Article Properties - <Article> dialog box in the Destination Object section, select a value of Keep existing object unchanged, Delete data. If article has a row filter, delete only data that matches the filter. or Truncate all data in the existing object for the option Action if name is in use. For more information on accessing this dialog box, see View and Modify Publication Properties.

TRUNCATE TABLE is a DDL statement that does not log individual row deletions and does not fire DML triggers. It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log; merge replication tracks changes through DML triggers on published tables.

For transactional replication, bulk inserts are tracked and replicated like other inserts. For merge replication, you must ensure that change tracking metadata is updated properly.

Yes. There are a number of special considerations for databases that are involved in replication. For more information, see Back Up and Restore Replicated Databases.

Merge replication and snapshot replication do not affect transaction log size, but transactional replication can. If a database includes one or more transactional publications, the log is not truncated until all transactions relevant to the publications have been delivered to the distribution database. If the transaction log is growing too large, and the Log Reader Agent is running on a scheduled basis, consider shortening the interval between runs. Or, set it to run in continuous mode. If it is set to run in continuous mode (the default), ensure that it is running. For more information on checking Log Reader Agent status, see View information and perform tasks using Replication Monitor.

Additionally, if you have set the option 'sync with backup' on the publication database or distribution database, the transaction log is not truncated until all transactions have been backed up. If the transaction log is growing too large, and you have this option set, consider shortening the interval between transaction log backups. For more information on backing up and restoring databases involved in transactional replication, see Strategies for Backing Up and Restoring Snapshot and Transactional Replication.

There are a variety of mechanisms for rebuilding indexes. They can all be used with no special considerations for replication, with the following exception: primary keys are required on tables in transactional publications, so you cannot drop and recreate primary keys on these tables.

Indexes can be added at the Publisher or Subscribers with no special considerations for replication (be aware that indexes can affect performance). CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.

In versions of SQL Server prior to SQL Server 2005 (9.x), moving or renaming database files required detaching and reattaching the database. Because a replicated database cannot be detached, replication had to be removed from these databases first. Beginning with SQL Server 2005 (9.x), you can move or rename files without detaching and re-attaching the database, with no effect on replication. For more information about moving and renaming files, see ALTER DATABASE (Transact-SQL).

First drop the article from the publication using sp_droparticle, sp_dropmergearticle, or the Publication Properties - <Publication> dialog box, and then drop it from the database using DROP <Object>. You cannot drop articles from snapshot or transactional publications after subscriptions have been added; you must drop the subscriptions first. For more information, see Add Articles to and Drop Articles from Existing Publications.

SQL Server supports a wide variety of schema changes on published objects, including adding and dropping columns. For example, execute ALTER TABLE … DROP COLUMN at the Publisher, and the statement is replicated to Subscribers and then executed to drop the column. Subscribers running versions of SQL Server prior to SQL Server 2005 (9.x) support adding and dropping columns through the stored procedures sp_repladdcolumn and sp_repldropcolumn. For more information, see Make Schema Changes on Publication Databases.

Use validation. Validation reports on whether a given Subscriber is synchronized with the Publisher. For more information, see Validate Replicated Data. Validation does not provide information on which rows if any are not synchronized correctly, but the tablediff utility does.

It is not necessary to stop activity on the publication or subscription databases in order to add a table (or another object). Add a table to a publication through the Publication Properties - <Publication> dialog box or the stored procedures sp_addarticle and sp_addmergearticle. For more information, see Add Articles to and Drop Articles from Existing Publications.

Remove a table from the publication using sp_droparticle, sp_dropmergearticle, or the Publication Properties - <Publication> dialog box. You cannot drop articles from snapshot or transactional publications after subscriptions have been added; you must drop the subscriptions first. For more information, see Add Articles to and Drop Articles from Existing Publications.

There are a number of article and publication changes that require subscriptions to be reinitialized. For more information, see Change Publication and Article Properties.

There are a number of article and publication changes that invalidate snapshots and require a new snapshot to be generated. For more information, see Change Publication and Article Properties.

The actions required to remove replication from a database depend on whether the database served as a publication database, subscription database, or both.

For transactional replication, use stored procedures or the Undistributed Commands tab in Replication Monitor. For more information, see View Replicated Commands and Other Information in the Distribution Database (Replication Transact-SQL Programming) and View information and perform tasks using Replication Monitor.

For merge replication, use the stored procedure sp_showpendingchanges. For more information, see sp_showpendingchanges (Transact-SQL).

Use the sp_replmonitorsubscriptionpendingcmds stored procedure or the Undistributed Commands tab in Replication Monitor. The stored procedure and tab display:

• The number of commands in the distribution database that have not been delivered to the selected Subscriber. A command consists of one Transact-SQL data manipulation language (DML) statement or one data definition language (DDL) statement.

• The estimated amount of time to deliver commands to the Subscriber. If this value is greater than the amount of time required to generate and apply a snapshot to the Subscriber, consider reinitializing the Subscriber. For more information, see Reinitialize Subscriptions.

For more information, see sp_replmonitorsubscriptionpendingcmds (Transact-SQL) and View information and perform tasks using Replication Monitor.

Yes. For more information, see Log Shipping and Replication (SQL Server) and Database Mirroring and Replication (SQL Server).

Yes. No special considerations are required because all data is stored on one set of disks on the cluster.

We recommend contacting the third party vendor for any issues to receive support. Generally if the issue is isolated by the vendor to be a core replication issue that is shipped with SQL Server, then Microsoft Support is involved to assist further.

See Also

Replication Administration FAQ
Best Practices for Replication Administration