Redigera

Dela via

Create and Apply the Initial Snapshot

  • Artikel

Applies to: SQL Server Azure SQL Managed Instance

This topic describes how to create and apply the initial snapshot in SQL Server by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects (RMO). Merge publications that use parameterized filters require a two-part snapshot. For more information, see Create a Snapshot for a Merge Publication with Parameterized Filters.
Snapshots are generated by the Snapshot Agent after a publication is created. They can be generated:

  • Immediately. By default, a snapshot for a merge publication is generated immediately after the publication is created in the New Publication Wizard.
  • At a scheduled time. Specify a schedule on the Snapshot Agent page of the New Publication Wizard or when using stored procedures or Replication Management Objects (RMO).
  • Manually. Run the Snapshot Agent from the command prompt or from SQL Server Management Studio. For more information about running agents, see Replication Agent Executables Concepts and Start and Stop a Replication Agent (SQL Server Management Studio).

For merge replication, a snapshot is generated every time the Snapshot Agent runs. For transactional replication, snapshot generation depends on the setting of the publication property immediate_sync. If the property is set to TRUE (the default when using the New Publication Wizard), a snapshot is generated every time the Snapshot Agent runs, and it can be applied to a Subscriber at any time. If the property is set to FALSE (the default when using sp_addpublication), the snapshot is generated only if a new subscription has been added since the last Snapshot Agent run; Subscribers must wait for the Snapshot Agent to complete before they can synchronize.

By default, when snapshots are generated, they are saved in the default snapshot folder located on the Distributor. You can also save snapshot files on removable media such as removable disks, CD-ROMs, or in locations other than in the default snapshot folder. Additionally, you can compress the files so that they are easier to store and transfer, and execute scripts before or after the snapshot is applied at the Subscriber. For more information about these options, see Snapshot Options.

If the snapshot is for a merge publication that uses parameterized filters, the snapshot is created using a two-part process. First a schema snapshot is created that contains the replication scripts and the schema of the published objects, but not the data. Each subscription is then initialized with a snapshot that includes the scripts and schema copied from the schema snapshot and the data that belongs to the subscription's partition. For more information, see Snapshots for Merge Publications with Parameterized Filters.

After the snapshot is created at the Publisher and stored in a default or alternate snapshot location, the snapshot can be transferred to the Subscriber and applied. The Distribution Agent (for snapshot or transactional replication) or Merge Agent (for merge replication) transfers the snapshot and applies the schema and data files to the subscription database on the Subscriber during the initial synchronization. By default, the initial synchronization occurs immediately after a subscription is created if you use the New Subscription Wizard. This behavior is controlled by the Initialize When option on the Initialize Subscriptions page of the wizard. When snapshots are generated after a subscription is initialized, they are not applied to a Subscriber unless a subscription is marked for reinitialization. For more information, see Reinitialize Subscriptions.

After the Distribution Agent or Merge Agent applies the initial snapshot, the agent propagates subsequent updates and other data modifications. When snapshots are distributed and applied to Subscribers, only those Subscribers waiting for initial or new snapshots are affected. Other Subscribers to that publication (those that are already receiving inserts, updates, deletes, or other modifications to the published data) are not affected.

To view or modify the default snapshot folder location, see

Default snapshot location

Specify the default snapshot location on the Snapshot Folder page of the Configure Distribution Wizard. For more information about using this wizard, see Configure Publishing and Distribution. If you create a publication on a server that is not configured as a Distributor, specify a default snapshot location on the Snapshot Folder page of the New Publication Wizard. For more information about using this wizard, see Create a Publication.

Modify the default snapshot location on the Publishers page of the Distributor Properties - <Distributor> dialog box. For more information, see View and Modify Distributor and Publisher Properties. Set the snapshot folder for each publication in the Publication Properties - <Publication> dialog box. For more information, see View and Modify Publication Properties.

Modify the default snapshot location

  1. On the Publishers page of the Distributor Properties - <Distributor> dialog box, click the properties button (...) for the Publisher for which you want to change the default snapshot location.

  2. In the Publisher Properties - <Publisher> dialog box, enter a value for the Default Snapshot Folder property.

    Note

    The Snapshot Agent must have write permissions for the directory you specify, and the Distribution Agent or Merge Agent must have read permissions. If pull subscriptions are used, you must specify a shared directory as a universal naming convention (UNC) path, such as \\computername\snapshot. For more information, see Secure the Snapshot Folder.

  3. Select OK.

Create snapshot

By default, if SQL Server Agent is running, a snapshot is generated by the Snapshot Agent immediately after a publication is created with the New Publication Wizard. By default, it is then applied by the Distribution Agent (for snapshot and transactional replication) or Merge Agent (for merge subscriptions) for all subscriptions. A snapshot can also be generated using SQL Server Management Studio and Replication Monitor. For information about starting Replication Monitor, see Start the Replication Monitor.

Using SQL Server Management Studio

  1. Connect to the Publisher in Management Studio, and then expand the server node.
  2. Expand the Replication folder, and then expand the Local Publications folder.
  3. Right-click the publication for which you want to create a snapshot, and then click View Snapshot Agent Status.
  4. In the View Snapshot Agent Status - <Publication> dialog box, click Start.
    When the Snapshot Agent finishes generating the snapshot, a message will be displayed, such as "[100%] A snapshot of 17 article(s) was generated."

In Replication Monitor

  1. In Replication Monitor, expand a Publisher group in the left pane, and then expand a Publisher.
  2. Right-click the publication for which you want to generate a snapshot, and then click Generate Snapshot.
  3. To view the status of the Snapshot Agent, click the Agents tab. For more detailed information, right-click the Snapshot Agent in the grid, and then click View Details.

Using Transact-SQL

Initial snapshots can be programmatically created either by creating and running a Snapshot Agent job or by running the Snapshot Agent executable file from a batch file. After an initial snapshot has been generated, it is transferred to and applied at the Subscriber when the subscription is first synchronized. If you run the Snapshot Agent from a command prompt or a batch file, you will need to rerun the agent whenever the existing snapshot becomes invalid.

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.

  1. Create a snapshot, transactional, or merge publication. For more information, see Create a Publication.

  2. Execute sp_addpublication_snapshot (Transact-SQL). Specify @publication and the following parameters:

    • The @job_login, which specifies the Windows Authentication credentials under which the Snapshot Agent runs at the Distributor.

    • The @job_password, which is the password for the supplied Windows credentials.

    • (Optional) A value of 0 for @publisher_security_mode if the agent will use SQL Server Authentication when connecting to the Publisher. In this case, you must also specify the SQL Server Authentication login information for @publisher_login and @publisher_password.

    • (Optional) A synchronization schedule for the Snapshot Agent job. For more information, see Specify Synchronization Schedules.

    Important

    When configuring a Publisher with a remote Distributor, the values supplied for all parameters, including job_login and job_password, are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before executing this stored procedure. For more information, see Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).

  3. Add articles to the publication. For more information, see Define an Article.

  4. At the Publisher on the publication database, execute sp_startpublication_snapshot (Transact-SQL), specifying the value of @publication from step 1.

Apply a snapshot

Using SQL Server Management Studio

  1. After the snapshot is generated, it is applied by synchronizing the subscription with the Distribution Agent or the Merge Agent:

    • If the agent is set to run continuously (the default for transactional replication), the snapshot is automatically applied after it is generated.
    • If the agent is set to run on a schedule, the snapshot is applied the next time the agent is scheduled to run.
    • If the agent is set to run on demand, it is applied the next time you run the agent.

    For more information about synchronizing subscriptions, see Synchronize a Push Subscription and Synchronize a Pull Subscription.

Use Transact-SQL

  1. Create a snapshot, transactional, or merge publication. For more information, see Create a Publication.

  2. Add articles to the publication. For more information, see Define an Article.

  3. From the command prompt or in a batch file, start the Replication Snapshot Agent by running snapshot.exe, specifying the following command-line arguments:

    • -Publication
    • -Publisher
    • -Distributor
    • -PublisherDB
    • -ReplicationType

    If you are using SQL Server Authentication, you must also specify the following arguments:

    • -DistributorLogin
    • -DistributorPassword
    • -DistributorSecurityMode = 0
    • -PublisherLogin
    • -PublisherPassword
    • -PublisherSecurityMode = 0

Examples (Transact-SQL)

This example shows how to create a transactional publication and add a Snapshot Agent job for the new publication (using sqlcmd scripting variables). The example also starts the job.

-- To avoid storing the login and password in the script file, the values 
-- are passed into SQLCMD as scripting variables. 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".

DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publicationDB = N'AdventureWorks2022'; --publication database
SET @publication = N'AdvWorksCustomerTran'; -- transactional publication name
SET @login = $(Login);
SET @password = $(Password);

USE [AdventureWorks]

-- Enable transactional and snapshot replication on the publication database.
EXEC sp_replicationdboption 
  @dbname = @publicationDB, 
  @optname = N'publish',
  @value = N'true';

-- Execute sp_addlogreader_agent to create the agent job. 
EXEC sp_addlogreader_agent 
  @job_login = @login, 
  @job_password = @password,
  -- Explicitly specify the security mode used when connecting to the Publisher.
  @publisher_security_mode = 1;

-- Create new transactional publication, using the defaults. 
USE [AdventureWorks2022]
EXEC sp_addpublication 
  @publication = @publication, 
  @description = N'transactional publication';

-- Create a new snapshot job for the publication, using the defaults.
EXEC sp_addpublication_snapshot 
  @publication = @publication,
  @job_login = @login,
  @job_password = @password;

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

This example creates a merge publication and adds a Snapshot Agent job (using sqlcmd variables) for the publication. This example also starts the job.

-- To avoid storing the login and password in the script file, the value 
-- is passed into SQLCMD as a scripting variable. 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".

DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publicationDB = N'AdventureWorks2022'; 
SET @publication = N'AdvWorksSalesOrdersMerge'; 
SET @login = $(Login);
SET @password = $(Password);

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

-- Create new merge publication, using the defaults. 
USE [AdventureWorks]
EXEC sp_addmergepublication 
  @publication = @publication, 
  @description = N'Merge publication.';

-- Create a new snapshot job for the publication, using the defaults.
EXEC sp_addpublication_snapshot 
  @publication = @publication,
  @job_login = @login,
  @job_password = @password;

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

The following command-line arguments start the Snapshot Agent to generate the snapshot for a merge publication.

Note

Line breaks were added to improve readability. In a batch file, commands must be made in a single line.

  
REM -- Declare variables  
SET Publisher=%InstanceName%  
SET PublicationDB=AdventureWorks2022   
SET Publication=AdvWorksSalesOrdersMerge   
  
REM --Start the Snapshot Agent to generate the snapshot for AdvWorksSalesOrdersMerge.  
"C:\Program Files\Microsoft SQL Server\120\COM\SNAPSHOT.EXE" -Publication %Publication%   
-Publisher %Publisher% -Distributor %Publisher% -PublisherDB %PublicationDB%   
-ReplicationType 2 -OutputVerboseLevel 1 -DistributorSecurityMode 1

Using Replication Management Objects (RMO)

The Snapshot Agent generates snapshots after a publication is created. You can generate these snapshots programmatically by using Replication Management Objects (RMO) and direct managed code access to replication agent functionalities. The objects you use depend on the type of replication. The Snapshot Agent can be started synchronously using the SnapshotGenerationAgent object or asynchronously using the agent job. After the initial snapshot has been generated, it is transferred to and applied at the Subscriber when the subscription is first synchronized. You will need to rerun the agent whenever the existing snapshot no longer contains valid, up-to-date data. For more information, see Maintain Publications.

Important

When possible, prompt users to enter security credentials at runtime. If you must store credentials, use the cryptographic services provided by the Microsoft Windows .NET Framework.

To generate the initial snapshot for a snapshot or transactional publication by starting the Snapshot Agent job (asynchronous)

  1. Create a connection to the Publisher by using the ServerConnection class.

  2. Create an instance of the TransPublication class. Set the Name and DatabaseName properties for the publication, and set the ConnectionContext property to the connection created in step 1.

  3. Call the LoadProperties method to load the remaining properties of the object. If this method returns false, either the publication properties in step 2 were defined incorrectly or the publication does not exist.

  4. If the value of SnapshotAgentExists is false, call CreateSnapshotAgent to create the snapshot agent job for this publication.

  5. Call the StartSnapshotGenerationAgentJob method to start the agent job that generates the snapshot for this publication.

  6. (Optional) When the value of SnapshotAvailable is true, the snapshot is available to Subscribers.

To generate the initial snapshot for a snapshot or transactional publication by running the Snapshot Agent (synchronous)

  1. Create an instance of the SnapshotGenerationAgent class, and set the following required properties:

  2. Set a value of Transactional or Snapshot for ReplicationType.

  3. Call the GenerateSnapshot method.

To generate the initial snapshot for a merge publication by starting the Snapshot Agent job (asynchronous)

  1. Create a connection to the Publisher by using the ServerConnection class.

  2. Create an instance of the MergePublication class. Set the Name and DatabaseName properties for the publication, and set the ConnectionContext property to the connection created in step 1.

  3. Call the LoadProperties method to load the remaining properties of the object. If this method returns false, either the publication properties in step 2 were defined incorrectly or the publication does not exist.

  4. If the value of SnapshotAgentExists is false, call CreateSnapshotAgent to create the snapshot agent job for this publication.

  5. Call the StartSnapshotGenerationAgentJob method to start the agent job that generates the snapshot for this publication.

  6. (Optional) When the value of SnapshotAvailable is true, the snapshot is available to Subscribers.

To generate the initial snapshot for a merge publication by running the Snapshot Agent (synchronous)

  1. Create an instance of the SnapshotGenerationAgent class, and set the following required properties:

  2. Set a value of Merge for ReplicationType.

  3. Call the GenerateSnapshot method.

Examples (RMO)

This example synchronously runs the Snapshot Agent to generate the initial snapshot for a transactional publication.

// Set the Publisher, publication database, and publication names.
string publicationName = "AdvWorksProductTran";
string publicationDbName = "AdventureWorks2022";
string publisherName = publisherInstance;
string distributorName = publisherInstance;

SnapshotGenerationAgent agent;

try
{
    // Set the required properties for Snapshot Agent.
    agent = new SnapshotGenerationAgent();
    agent.Distributor = distributorName;
    agent.DistributorSecurityMode = SecurityMode.Integrated;
    agent.Publisher = publisherName;
    agent.PublisherSecurityMode = SecurityMode.Integrated;
    agent.Publication = publicationName;
    agent.PublisherDatabase = publicationDbName;
    agent.ReplicationType = ReplicationType.Transactional;

    // Start the agent synchronously.
    agent.GenerateSnapshot();

}
catch (Exception ex)
{
    // Implement custom application error handling here.
    throw new ApplicationException(String.Format(
        "A snapshot could not be generated for the {0} publication."
        , publicationName), ex);
}

' Set the Publisher, publication database, and publication names.
Dim publicationName As String = "AdvWorksProductTran"
Dim publicationDbName As String = "AdventureWorks2022"
Dim publisherName As String = publisherInstance
Dim distributorName As String = publisherInstance

Dim agent As SnapshotGenerationAgent

Try
    ' Set the required properties for Snapshot Agent.
    agent = New SnapshotGenerationAgent()
    agent.Distributor = distributorName
    agent.DistributorSecurityMode = SecurityMode.Integrated
    agent.Publisher = publisherName
    agent.PublisherSecurityMode = SecurityMode.Integrated
    agent.Publication = publicationName
    agent.PublisherDatabase = publicationDbName
    agent.ReplicationType = ReplicationType.Transactional

    ' Start the agent synchronously.
    agent.GenerateSnapshot()

Catch ex As Exception
    ' Implement custom application error handling here.
    Throw New ApplicationException(String.Format( _
     "A snapshot could not be generated for the {0} publication." _
     , publicationName), ex)
End Try

This example asynchronously starts the agent job to generate the initial snapshot for a transactional publication.

// Set the Publisher, publication database, and publication names.
string publicationName = "AdvWorksProductTran";
string publicationDbName = "AdventureWorks2022";
string publisherName = publisherInstance;

TransPublication publication;

// Create a connection to the Publisher using Windows Authentication.
ServerConnection conn;
conn = new ServerConnection(publisherName);

try
{
    // Connect to the Publisher.
    conn.Connect();

    // Set the required properties for an existing publication.
    publication = new TransPublication();
    publication.ConnectionContext = conn;
    publication.Name = publicationName;
    publication.DatabaseName = publicationDbName;

    if (publication.LoadProperties())
    {
        // Start the Snapshot Agent job for the publication.
        publication.StartSnapshotGenerationAgentJob();
    }
    else
    {
        throw new ApplicationException(String.Format(
            "The {0} publication does not exist.", publicationName));
    }
}
catch (Exception ex)
{
    // Implement custom application error handling here.
    throw new ApplicationException(String.Format(
        "A snapshot could not be generated for the {0} publication."
        , publicationName), ex);
}
finally
{
    conn.Disconnect();
}

' Set the Publisher, publication database, and publication names.
Dim publicationName As String = "AdvWorksProductTran"
Dim publicationDbName As String = "AdventureWorks2022"
Dim publisherName As String = publisherInstance

Dim publication As TransPublication

' Create a connection to the Publisher using Windows Authentication.
Dim conn As ServerConnection
conn = New ServerConnection(publisherName)

Try
    ' Connect to the Publisher.
    conn.Connect()

    ' Set the required properties for an existing publication.
    publication = New TransPublication()
    publication.ConnectionContext = conn
    publication.Name = publicationName
    publication.DatabaseName = publicationDbName

    If publication.LoadProperties() Then
        ' Start the Snapshot Agent job for the publication.
        publication.StartSnapshotGenerationAgentJob()
    Else
        Throw New ApplicationException(String.Format( _
         "The {0} publication does not exist.", publicationName))
    End If
Catch ex As Exception
    ' Implement custom application error handling here.
    Throw New ApplicationException(String.Format( _
     "A snapshot could not be generated for the {0} publication." _
     , publicationName), ex)
Finally
    conn.Disconnect()
End Try

Blocking when applying initial snapshot

If you have multiple publications, that publish data into one database at the subscriber(s), while applying the initial snapshot(s), you notice that only one publication is able to apply its snapshot at a time.

You may see a wait resource similar to the following when reviewing SQL activity:

APP: 18:16384:[snapshot_delivery_in_progress_Tr]:(9bcdaf92)
APP: 5:16384:[snapshot_delivery_in_progress_Er]:(3c3b7db9)

Querying for locking behavior may show resources similar to the following:

APP 16384:[appname]:(fbe42d68) XAPP 16384:[snapshot_del]:(9bcdaf92) X

This behavior is by design. It occurs because an application lock is used to prevent multiple replication agents from concurrently applying snapshots of different publications to the same subscriber database. Because the application lock contains the name of the subscriber database, any publications that publish into the same subscriber database will be impacted. The result is that only one snapshot can be inserted into the subscriber database at a given time.

Exclusive locks are used in this situation to help avoid the possibility of replication agents becoming deadlocked with each other.

To work around this issue, specify a different subscriber database for each publication.

Related content