SQL Merge Replication

Question

Part of the SQL Merge Replication configuration is that a snapshot is taken. When the subscriber is joined/connected to the Distributor the merge agent applies the snapshot changes. When the publisher is first set up the Snapshot is set up on a schedule and the default appears to be 2 weeks. Based on my understanding the data changes are managed via the rowguid column. So I would assume that the scheduled snapshot would be used to manage changes to stored procedures and data conflicts. Based on our deployment scenario these issues should not occur.

Q. After the initial snapshot and merge the changes are managed via the rowguid column. So is the snapshot (i.e. files contained in snapshot folder) required anymore?

Q. What is the best way to delete the snapshot files?

Q. So do you need to schedule the snapshot or can this be managed manually when changes occur?

Answer 1

Hi @Gavin  

Q1: After the initial snapshot and merge the changes are managed via the rowguid column. So is the snapshot (i.e. files contained in snapshot folder) required anymore?

Yes. Once the initial snapshot is applied and the merge replication is set up, the changes are indeed managed via the rowguid column. The snapshot files are primarily used for the initial synchronization of the subscriber with the publisher. After this initial synchronization, the snapshot files are not required for the ongoing replication process. However, it’s important to keep the snapshot folder accessible because it might be needed for:

1. Adding new subscribers: If you need to add new subscribers, they will require the snapshot to initialize.
2. Reinitializing existing subscribers: In case of issues or if a subscriber needs to be reinitialized, the snapshot will be necessary.

So, while the snapshot files are not needed for the day-to-day replication after the initial setup, they are still important for managing and maintaining the replication environment.

Q2: What is the best way to delete the snapshot files? 

Suggest to regular cleanup the old snapshot files to maintain disk space. We can use SQL server agent jobs to create a cleanup schedule to delete old snapshot files that are no longer needed in merge replication.

 Here’s a step-by-step guide to automate this process using SQL Server Agent:

1.Create a SQL Server Agent Job:

• Open SQL Server Management Studio (SSMS).
• Connect to your SQL Server instance.
• Expand the SQL Server Agent node.
• Right-click on Jobs and select New Job.

2Define Job Properties:

• In the New Job window, give your job a name, such as “Cleanup Old Snapshot Files”.
• Optionally, add a description.

3.Add a Job Step:

• Go to the Steps page and click New.
• Name the step, e.g., “Delete Old Snapshot Files”.
• Set the Type to Operating system (CmdExec).
• In the Command box, enter a script to delete files older than a certain number of days. For example, you can use a PowerShell script:

$snapshotFolder = "C:\Replication\Snapshots"
$daysOld = 30
Get-ChildItem $snapshotFolder -Recurse | Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-$daysOld) } | Remove-Item -Force

4.Schedule the Job:

• Go to the Schedules page and click New.
• Name the schedule, e.g., “Weekly Cleanup”.
• Set the frequency (e.g., weekly) and choose the day and time for the job to run.

5.Set Notifications (Optional):

• Go to the Notifications page to set up alerts if the job fails or succeeds.

6.Save and Enable the Job:

• Click OK to save the job.
• Ensure the job is enabled.

Q3: So do you need to schedule the snapshot or can this be managed manually when changes occur?

        No.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.