Redigera

Dela via

Configure Snapshot Properties (Replication Transact-SQL Programming)

  • Artikel

Applies to: SQL Server Azure SQL Managed Instance

Snapshot properties can be defined and modified programmatically using replication stored procedures, where the stored procedures used depend on the type of publication.

To configure snapshot properties when creating a snapshot or transactional publication

  1. At the Publisher, execute sp_addpublication. Specify a publication name for @publication, a value of either snapshot or continuous for @repl_freq, and one or more of the following snapshot-related parameters:

    • @alt_snapshot_folder - specify a path if the snapshot for this publication is accessed from that location instead of or in addition to the snapshot default folder.
    • @compress_snapshot - specify a value of true if the snapshot files in the alternate snapshot folder are compressed in the Microsoft CAB file format.
    • @pre_snapshot_script - specify the file name and full path of a .sql file that will be executed at the Subscriber during initialization before the initial snapshot is applied.
    • @post_snapshot_script - specify the file name and full path of a .sql file that will be executed at the Subscriber during initialization after the initial snapshot is applied.
    • @snapshot_in_defaultfolder - specify a value of false if the snapshot is available only in a non-default location.

    For more information about creating publications, see Create a Publication.

To configure snapshot properties when creating a merge publication

  1. At the Publisher, execute sp_addmergepublication. Specify a publication name for @publication, a value of either snapshot or continuous for @repl_freq, and one or more of the following snapshot-related parameters:

    • alt_snapshot_folder - specify a path if the snapshot for this publication is accessed from that location instead of or in addition to the snapshot default folder.
    • @compress_snapshot - specify a value of true if the snapshot files in the alternate snapshot folder are compressed in the CAB file format.
    • @pre_snapshot_script - specify the file name and full path of a .sql file that will be executed at the Subscriber during initialization before the initial snapshot is applied.
    • @post_snapshot_script - specify the file name and full path of a .sql file that will be executed at the Subscriber during initialization after the initial snapshot is applied.
    • @snapshot_in_defaultfolder - specify a value of false if the snapshot is available only in a non-default location.

  2. For more information about creating publications, see Create a Publication.

To modify snapshot properties of an existing snapshot or transactional publication

  1. At the Publisher on the publication database, execute sp_changepublication. Specify a value of 1 for @force_invalidate_snapshot and one of the following values for @property:

    • alt_snapshot_folder -also specify a new path to the alternate snapshot folder for @value.
    • compress_snapshot - also specify a value of either true or false for @value to indicate whether the snapshot files in the alternate snapshot folder are compressed in the CAB file format.
    • pre_snapshot_script - also for @value specify the file name and full path of a .sql file that will be executed at the Subscriber during initialization before the initial snapshot is applied.
    • post_snapshot_script - also for @value specify the file name and full path of a .sql file that will be executed at the Subscriber during initialization after the initial snapshot is applied.
    • snapshot_in_defaultfolder - also specify a value of either true or false to indicate whether the snapshot is available only in a non-default location.

  2. (Optional) At the Publisher on the publication database, execute sp_changepublication_snapshot. Specify @publication and one or more of the scheduling or security credential parameters being changed.

    Important

    When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.

  3. Run the Replication Snapshot Agent from the command prompt or start the Snapshot Agent job to generate a new snapshot. For more information, see Create and Apply the Initial Snapshot.

To modify snapshot properties of an existing merge publication

  1. At the Publisher on the publication database, execute sp_changemergepublication. Specify a value of 1 for @force_invalidate_snapshot and one of the following values for @property**:

    • alt_snapshot_folder -also specify a new path to the alternate snapshot folder for @value.
    • compress_snapshot - also specify a value of either true or false for @value to indicate whether the snapshot files in the alternate snapshot folder are compressed in the CAB file format.
    • pre_snapshot_script - also for @value specify the file name and full path of a .sql file that will be executed at the Subscriber during initialization before the initial snapshot is applied.
    • post_snapshot_script - also for @value specify the file name and full path of a .sql file that will be executed at the Subscriber during initialization after the initial snapshot is applied.
    • snapshot_in_defaultfolder - also specify a value of either true or false to indicate whether the snapshot is available only in a non-default location.

  2. Run the Replication Snapshot Agent from the command prompt or start the Snapshot Agent job to generate a new snapshot. For more information, see Create and Apply the Initial Snapshot.

Example

This example creates a publication that uses an alternate snapshot folder and a compressed snapshot.

-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables  
-- on the command line and in SQL Server Management Studio, see the 
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".

--Declarations for adding a merge publication
DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @article AS sysname;
DECLARE @owner AS sysname;
DECLARE @snapshot_share AS sysname;
SET @publicationDB = N'AdventureWorks2022'; 
SET @publication = N'AdvWorksSalesOrdersMergeAltSnapshot'; 
SET @article = N'SpecialOffer';
SET @owner = N'Sales';
SET @snapshot_share = '\\' + $(InstanceName) + '\AltSnapshotFolder';

-- Enable merge replication on the publication database, using defaults.
USE master
EXEC sp_replicationdboption 
    @dbname = @publicationDB, 
    @optname=N'merge publish',
    @value = N'true';

-- Create new merge publication with an alternate snapshot location. 
USE [AdventureWorks]
EXEC sp_addmergepublication 
-- required parameters
    @publication = @publication, 
    @snapshot_in_defaultfolder = N'false',
    @alt_snapshot_folder = @snapshot_share,
    @compress_snapshot = N'true';

-- Create the snapshot job for the publication.
EXEC sp_addpublication_snapshot 
    @publication = @publication,
    @job_login = $(Login),
    @job_password = $(Password);

-- Add an article.
EXEC sp_addmergearticle 
    @publication = @publication, 
    @article = @article, 
    @source_object = @article, 
    @type = N'table', 
    @source_owner = @owner, 
    @destination_owner = @owner;

-- Start the snapshot job.
EXEC sp_startpublication_snapshot
    @publication = @publication;
GO

Related content