Mittwoch, 13. Februar 2013 20:23
We have a customer that is using merge replication between 1 distributor and 2 subscribers. They want to add another subscriber. i need to get the data to that new subscriber. If i make a backup of the data from the publisher/distributor, copy that to the new subscriber, restore the data, and then on the publisher setup the merge replication to that new subscriber, will that automatically replicate changes that occurred between the time the backup was made and "now"? Anytime i setup merge replication i say not to initialize the database because the subscriber already has the data.
i am just trying to figure out the fastest way to get the data to the new subscriber since it is about 60GB worth.
Mittwoch, 13. Februar 2013 21:34Moderator
One option is to compress the snapshot with your favorite program, like 7-Zip, copy the compressed snapshot to the subscriber using file-copy or FTP, and then apply it locally using the -AltSnapshotFolder Merge Agent parameter.The way this works is open the SQL Agent job for the Subscriber you wish to deploy the snapshot to and get the Run Agent. command. It looks something like this:
-Publisher [PACIFIC] -PublisherDB [TestDB2] -Publication [TestMergePub1] -Subscriber [PACIFIC] -SubscriberDB [TestSubDB2] -Distributor [PACIFIC] -DistributorSecurityMode 1
Then, grab the snapshot files from the publisher and compress it. For example, on my server I compress the snapshot folder found in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repldata\unc\. I then copy the snapshot folder to the subscriber using file-copy or FTP and decompress it in a folder called C:\TEMP\unc.
So looking at this on the Subscriber I would see this for the snapshot folder:
Then from the command line on the Subscriber, run this:
c:\Program Files\Microsoft sql server\90\com\replmerg.exe -Publisher [PACIFIC] -PublisherDB [TestDB2] -Publication [TestMergePub1] -Subscriber [PACIFIC] -SubscriberDB [TestSubDB2] -Distributor [PACIFIC] -DistributorSecurityMode 1 -AltSnapshotFolder C:\TEMP
This will apply the snapshot locally and will be significantly faster then applying it over the wire.
- Als Antwort markiert Allen Li - MSFTModerator Mittwoch, 20. Februar 2013 08:31
Mittwoch, 13. Februar 2013 22:37
Got this from Microsoft Support, worked for me. http://support.microsoft.com/kb/320499
- Als Antwort markiert Allen Li - MSFTModerator Mittwoch, 20. Februar 2013 08:32
Mittwoch, 13. Februar 2013 22:53Moderator
Mittwoch, 13. Februar 2013 23:07
will that automatically replicate changes that occurred between the time the backup was made and "now"?
I rely on a process very similar to what Brandon outlined. When you use the -AltSnapshotFolder it will apply the whole snapshot and then check for & apply any changes since that snapshot was created.
Also I'll add if you let the DB compress the snapshot into a .cab file you can apply from the .cab file in the same way Brandon mentioned, however it takes each file from the zip one at a time so it seems to add a lot of overhead. It takes about 30 minutes to apply my 140mb cab file that contains ~240 tables that add up to about 1.5 gb of data after extracted.