Initializing a Transactional Subscription Without a Snapshot
By default, a subscription to a transactional publication is initialized with a snapshot, which is generated by the Snapshot Agent and applied by the Distribution Agent. In some scenarios, such as those involving large initial datasets, it is preferable to initialize a subscription using another method. Other methods of initializing a Subscriber include:
- Specifying a backup. Restore the backup on the Subscriber, and then the Distribution Agent copies any required replication metadata and system procedures. Initializing with a backup is the fastest way to deliver data to the Subscriber and is convenient, because any recent backup can be used if it was taken after the publication was enabled for initialization with a backup.
- Copying an initial dataset to the Subscriber through another mechanism, such as attaching a database. You must ensure the correct data and schema are at the Subscriber, and then the Distribution Agent copies any required metadata and system procedures.
Initializing a subscription with a backup
A backup contains an entire database; therefore each subscription database will contain a complete copy of the publication database when it is initialized:
- The backup includes tables not specified as articles for the publication.
- The backup includes all data, even if row or column filters are specified on a table.
It is the responsibility of the administrator or application to remove any unwanted objects or data after the backup has been restored. In subsequent synchronizations, data changes are only replicated if they apply to tables specified as articles, and the changes meet any filtering criteria you specified.
Note
When restoring a backup, you must ensure that the backup came from the Publisher if you want the Subscriber to automatically synchronize. The log sequence number (LSN) values in the backup (which are used to set the point at which to start synchronizing) are specific to the Publisher.
To initialize a subscription with a backup
To initialize a subscription with a backup, you first must enable the option when you create a publication, and then specify values for a number of options when you create a subscription. Publications can be enabled through the New Publication Wizard or programmatically. However, the values required for the subscription options can only be specified programmatically.
- SQL Server Management Studio: How to: Enable Initialization with a Backup for Transactional Publications (SQL Server Management Studio)
- Replication Transact-SQL programming: How to: Initialize a Transactional Subscriber from a Backup (Replication Transact-SQL Programming)
Note
If a subscription is initialized without using a snapshot, the account under which the SQL Server service runs at the Publisher must have write permissions on the snapshot folder at the Distributor. For more information about permissions, see Replication Agent Security Model.
Ensuring the suitability of a backup
A backup is suitable for initializing a Subscriber if all transactions that occur after the backup was taken are stored at the Distributor. Replication will display an error message if the backup is not suitable.
To help ensure that a backup is suitable for use, follow these guidelines:
- Use the latest backup available, and if the latest backup is older than the maximum distribution retention period, create a new backup before attempting to initialize a subscription with a backup. For more information about retention period, see Subscription Expiration and Deactivation.
- By default, the distribution cleanup job clears transactions older than 72 hours from the distribution database. Cleanup is based on the retention period set for the publication. When synchronizing with older backups, consider temporarily disabling the job before the backup you would like to restore and re-enabling it after the subscription is successfully created. This prevents removal of transactions from the distribution database that might be needed to synchronize successfully from the backup. For information about running cleanup jobs, see How to: Run Replication Maintenance Jobs (SQL Server Management Studio).
In some cases you must manually perform customizations in the restored Subscriber database after setting up subscriptions that are initialized with a backup. In general, manual modifications at the restored Subscriber database are required if the Publication is defined in such a way that the Subscriber database content is expected to be different from the Publisher database content.
- Indexed-views at the restored database have to be converted to tables if they are published as log-based indexed-view-to-table articles
- Subscribed timestamp columns at the restored database must be converted to binary(8) columns: copy the content of the tables containing timestamp columns to new tables with matching schemas except having binary(8) columns in place of the timestamp columns, drop the original tables, and rename the new tables with the same names as the original tables.
Initializing a subscription with an alternative method
It is possible to initialize a subscription using any method that allows you to copy the publication database schema and data to the Subscriber, such as Integration Services. When you use an alternative method to initialize the Subscriber, replication support objects are copied to the Subscriber.
Unlike initializing with a backup, you or your application must ensure the data and schema are properly synchronized at the time you add the subscription. If, for example, there is activity on the Publisher between the time data and schema are copied to the Subscriber and the time at which the subscription is added, changes resulting from this activity might not be replicated to the Subscriber.
To initialize a subscription with an alternative method
- SQL Server Management Studio: How to: Initialize a Subscription Manually (SQL Server Management Studio)
- Replication Transact-SQL programming: How to: Initialize a Subscription Manually (Replication Transact-SQL Programming)