Ócáid
Mar 31, 11 PM - Apr 2, 11 PM
An ócáid foghlama SQL, Fabric and Power BI is mó. Márta 31 – 2 Aibreán. Bain úsáid as cód FABINSIDER chun $ 400 a shábháil.
Cláraigh inniuNí thacaítear leis an mbrabhsálaí seo a thuilleadh.
Uasghrádú go Microsoft Edge chun leas a bhaint as na gnéithe is déanaí, nuashonruithe slándála, agus tacaíocht theicniúil.
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.
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.
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:
@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.For more information about creating publications, see Create a Publication.
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
:
@value
.@value
to indicate whether the snapshot files in the alternate snapshot folder are compressed in the CAB file format.@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.@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.(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.
Tábhachtach
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.
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.
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**
:
@value
.@value
to indicate whether the snapshot files in the alternate snapshot folder are compressed in the CAB file format.@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.@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.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.
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
Ócáid
Mar 31, 11 PM - Apr 2, 11 PM
An ócáid foghlama SQL, Fabric and Power BI is mó. Márta 31 – 2 Aibreán. Bain úsáid as cód FABINSIDER chun $ 400 a shábháil.
Cláraigh inniuOiliúint
Doiciméadúchán
View and Modify Replication Security Settings - SQL Server
Learn how to view and modify replication security settings in SQL Server by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects.
Configure Publishing and Distribution - SQL Server
Learn how to configure publishing and distribution in SQL Server by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects.
Frequently asked questions for replication administrators - SQL Server
A list of frequently asked questions relevant to replication administrators for SQL Server.