Freigeben über

Merge Agent fails with The Merge failed to retrieve the snapshot schema script - Merge Replication with Web Synchronization using FTP Over Internet

  • Artikel

When you configure a merge replication with web synchronization using FTP over internet, you might run into an issue when you run the merge agent.

 

Assume that we have configured the merge replication with the above specifications and have run the snapshot agent to take the snapshot of the database. The next step is to run the merge agent to start the synchronization. During this phase you will find that merge agent fails.

 

ENABLING VERBOSE LOGGING:

The first step to start with the troubleshooting is to enable the verbose logging for the merge agent.

 

1.       In the replication monitor view the Merge agent under “All Subscriptions”

2.       Go to the Merge Agent View Details

3.       Click on Action | Merge Agent Job Properties | Step 2 (Run Agent)  

Add the verbose parameters at the end of the TSQL Statement present there

Parameters :  -OutputVerboseLevel (Set this to 3) –Output (Give the location for the output file)

 

When we run the merge agent after enabling the verbose logging, we will get the following in the output file.

 

ERROR MESSAGE:

2009-05-20 21:42:40.880 The Merge Agent failed to retrieve the snapshot schema script file '\\<server-name>\ftproot\ftp\<publication>\20090520144367\City_2.sch'. Run the Snapshot Agent to regenerate the snapshot files for this publication. If delivering the snapshot using FTP, ensure that the account under which the agent runs has access to the FTP directory.

2009-05-20 21:42:40.896 Category:NULL

Source: Merge Replication Provider

Number: -2147199390

Message: Agent The Merge failed to retrieve the snapshot schema script file '\\<server>\ftproot\ftp\<publication>\20090520144367\City_2.sch'. Run the Snapshot Agent to regenerate the snapshot files for this publication. If delivering the snapshot using FTP, ensure that the account under which the agent runs has access to the FTP directory.

2009-05-20 21:42:40.896 Category:AGENT

Source: stridelapdf\sqlexpress

Number: 20033

Message: The process could not retrieve file '<publication-name>/20090520144367/City_2.sch' from the FTP site '<server>'.

2009-05-20 21:42:40.896 Category:OS

Source:

Number: 12003

Message: 200 Type set to I.

200 PORT command successful.

550 <publication-name>/20090520144367/City_2.sch: The system cannot find the path specified.

550 <publication-name>/20090520144367/City_2.sch: The system c

2009-05-20 21:42:40.896 Category:NULL

Source: Merge Replication Provider

Number: -2147201001

Message: The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.

From the above we clearly understand that there is some issue with accessing the FTP location to get the snapshot files.

1. Check the permissions for the agent startup account in the FTP Folder

2. Check if the folder structure is present and accessible from the subscriber as well

3. Check if the FTP port (which is port 21 by default ) is open and accessible using netstat -aon command

As the above steps succeeded, we went ahead and started checking if we are able to open the folder through FTP manually. We were able to do that. Then we looked into the error message where we were able to get a catch.

The error message says that,

<publication-name>/20090520144367/City_2.sch: The system cannot find the path specified.

So the above means merge agent is not able to access or find the above folder under its default location (Default location of the user with which the agent is run). So we logged into the FTP manually and checked the default location for the user and identified the same to be the FTPRoot folder. In our case the FTPRoot was \\<server>\ftproot\ftp

CAUSE:

There are two important things to be noted in this configuration.

1. The location where the snapshot of the database is taken.

2. The FTP location from where the files are picked by the subscriber/Merge Agent

1. Location of Snapshot

This is mentioned in the publisher properties. If you have specified a the FTP Root folder in the “Put Files in the default folder” option in Snapshot tab in Publisher properties, the snapshot agent will create a predefined folder structure inside the FTP Root when taking the snapshot of the database. The same would look like the below,

\\<FTP Root>\ftp\<Publisher Name>\<Snapshot Time Stamp>\actual files

In the above the important thing to be noted is the “ftp” folder that is created by the snapshot agent.

2. FTP Location that Merge Agent would know

When the merge agent is being executed the default location for the user would be the FTP Root directory. And the agent will look for the following folder structure inside the FTP Root folder.

\<Publisher Name>\<Snapshot Time Stamp>\actual files

So the full path of the FTP location where the merge agent will look for the files would be,

\\<FTP Root> \<Publisher Name>\<Snapshot Time Stamp>\actual files

So in our case the default location/FTP Root is \\brevellsvr\ftproot\ and the FTP location searched by the merge agent is <publication>/20090520144367/City_2.sch. So the actual folder structure that is accessed by the merge agent does not have the “ftp” folder in it.

The above caused the error message. The system cannot find the path specified.

SOLUTION:

The solution for this issue would be to add the value \ftp to the parameter “Path from the FTP root folder” in the “FTP Snapshot and Internet” tab in the publisher properties as mentioned in the following MSDN articles

https://msdn.microsoft.com/en-us/library/ms146956(SQL.90).aspx

1. At the Publisher on the publication database, execute sp_addpublication. Specify @publication, a value of true for @enabled_for_internet, and appropriate values for the following parameters:

· @ftp_address - the address of the FTP server used to deliver the snapshot.

· (Optional) @ftp_port - the port used by the FTP server.

· (Optional) @ftp_subdirectory - the subdirectory of the default FTP directory assigned to an FTP login. For example, if the FTP server root is \\ftpserver\home and you want snapshots to be stored at \\ftpserver\home\snapshots, specify \snapshots\ftp for @ftp_subdirectory (replication appends 'ftp' to the snapshot folder path when it creates snapshot files).

· (Optional) @ftp_login - a login account used when connecting to the FTP server.

· (Optional) @ftp_password - the password for the FTP login.

OR

https://msdn.microsoft.com/en-us/library/ms151844(SQL.90).aspx

  1. In the Publication Properties - <Publication> dialog box, select Allow Subscribers to download snapshot files using FTP from one of the following pages:
    • The FTP Snapshot page, for snapshot and transactional publications, and merge publications for Publishers running versions prior to Microsoft SQL Server 2005.
    • The FTP Snapshot and Internet page, for merge publications from Publishers running SQL Server 2005 or later.
  2. Specify values for FTP server name, Port number, Path from the FTP root folder, Login, and Password.

For example, if the FTP server root is \\ftpserver\home and you want snapshots to be stored at \\ftpserver\home\snapshots, specify \snapshots\ftp for the property Path from the FTP root folder (replication appends 'ftp' to the snapshot folder path when it creates snapshot files).

The highlighted portion clearly states that we need to append \ftp in the @ftp_subdirectory parameter.

ADDITIONAL INFORMATION:

This article speaks about the “How to: Deliver a Snapshot through FTP (SQL Server Management Studio)”

https://msdn.microsoft.com/en-us/ms151844.aspx

 

Sivasubramanian C

SE, Microsoft SQL Server

 

Reviewed By

 

Akbar Farishta
Technical lead, Microsoft Sql Server

 

&

 

Sudarshan Narasimhan
Technical lead, Microsoft Sql Server