Scenario
You’ve set up replication initially by using the GUI and accepting all defaults and now due to growth you need to change your default replication snapshot folder location to another drive since the current location is a local disk and cannot be expanded to accommodate the growth.
This concerns you as you cannot afford downtime to perform a re-initialization of all your existing subscribers as they are needed to run the business.
Solution
You can change the snapshot location without having to perform a re-initialization. The only caveat to this is you do need to generate a new snapshot. This snapshot will not be applied to your subscribers, but has to go through the generation process. I recommend doing this at night or weekends to minimize the impact of the snapshot agent locking tables and contention on the publisher database.
Current Snapshot Location for a Publication
You can see the snapshot folder is currently pointed at a share called “\Some_Server_Name\Share”. This is your snapshot location that will change later in the tip. To get to this screen right click on your publication and select Properties and click on Snapshot. You could change the location of the snapshot here, but this would need to be done for all publications.
Current Snapshot Filesystem Folder
This shows the snapshot files that are located in “\Some_Server_Name\Share” meaning it was used to perform the initialization of this subscription.
Changing Default Snapshot Location
The script below changes the default snapshot location to “\Some_Server_Name\Share2” from “\Some_Server_Name\Share” for all publications..
In your environment this could be a different mount point that has expandable storage. This is accomplished by calling sp_changedistpublisher and updating the working directory. This procedure takes a “publisher”, “property” and “value” parameters and needs to be run on the distributor. You can learn more about this here sp_changedistpublisher.
USE distribution
exec sp_changedistpublisher
@publisher = 'Some_Server_Name',
@property = 'working_directory',
@value = '\\Some_Server_Name\Share2'
This can also be changed from SSMS by right clicking on Replication, selecting Properties, select Publishers and click on “…” to bring up this window.
Synchronization After Changing Snapshot Location
As you can see the sync completed without error after changing the snapshot location. I inserted some rows after changing the snapshot location and performed a sync manually to ensure there were no errors in replication.
New Snapshot Location
When looking at the publication properties a second time, you can see it is now showing “\Some_Server_Name\Share2” instead of the original snapshot location of “\Some_Server_Name\Share”.
As you can see changing the snapshot location was a painless process and the next time the snapshot agent runs it will write to the new location. Normal synchronization occurs without interruption.
Thanks for reading this article,
Next steps :
- Share this with your colleagues because Sharingis Learning
- Comment below if you need any assistance
Powered by CodeReview – Let’s make it Better!