By: Chad Churchwell | Updated: 2012-10-15 | Comments (5) | Related: > Replication
Problem
We set up replication initially by using the GUI and accepting all defaults and now due to growth we need to change our default replication snapshot folder location to another drive since the current location is a local disk and cannot be expanded to accommodate our growth. This concerns us as we cannot afford downtime to perform a re-initialization of all our 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 "\\FL-WS-CON-CC03\Share". This is my 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 "\\FL-WS-CON-CC03\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 "\\FL-WS-CON-CC03\Share2" from "\\FL-WS-CON-CC03\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 = 'FL-WS-CON-CC03',
@property = 'working_directory',
@value = '\\FL-WS-CON-CC03\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 "\\FL-WS-CON-CC03\Share2" instead of the original snapshot location of "\\FL-WS-CON-CC03\Share".
Here is a screen shot of TSQL to verify the new location
This query shows where this default location is stored and can be used to verify your snapshot locations in your environment. This lives on the distributor and has entries for each remote publisher. More documentation around MSdistpublishers can be found at MSdistpublishers
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.
Next Steps
- Check your environment to see if your snapshot location is running low on space or is not on expandable disk
- Default Snapshot Folder Documentation
- Make sure you test and verify your expected results as all replication scenarios were not tested in this tip
About the author
Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2012-10-15
