Home / server / Questions / 77708
Accepted
Refracted Paladin
Asked: 2009-10-24 09:18:17 +0800 CST

SQL Merge Replication - Compress Snapshot

  • 772

I have Merge Replication set up at our company with a central SQL 2005 as the Publisher/Distributor and the clients are all SQL 2005 Express.

In our remote branches we are having difficulty with the initial sync time. The database is ~2GB and is taking well over an hour for that first run. In troubleshooting this I noticed that I can specify an alternate Snapshot location and then optionally compress that.

I tried this(compressing) and it compresses it significantly but I am wondering what I should be aware of when turning this on. I notice it is a single .cab file in compressed form versus all the individual script files of before.

  • What are the catches?

  • Why wouldn't you always use the compressed format?

  • If both are left checked, default and alternate compressed, how do I tell it to use one or the other?

sql-server

3 Answers

  1. Best Answer

    The biggest catch is that a CAB file can only be so large. If it tried to make a CAB file larger than is supported the snapshot agent will crash. I think the limit is 2 Gigs.

    It takes a lot of CPU power to compress the snapshot. Some people may not have the CPU power to spare. And over a LAN there really wouldn't be any point as the time to create the snapshot would increase with no real decrease in transfer time.

    If both are checked, then is uses both settings. You can't tell it to use one or the other if both are enabled.

    • 2

  2. In your case (snapshot ~ 2GB) this is absolutely not the correct way of initializing a replication (by applying a snapshot). Infact you should never initialize a subscription from the publisher side. Rather use a backup-restore strategy. http://technet.microsoft.com/en-us/library/ms152488.aspx

    1. Backup Publication db. Rar(compress) the backup file and ship it at subscriber by any means.
    2. Restore it on subscription db. Make sure to uncheck Preserve Replication Settings (WITH KEEP_REPLICAITON) on the Options page. I usually prefer to check Overwrite existing database. and ofcourse remember to verify the filenames as they will be of path as present at publisher.
    3. Create the subscription and make sure you uncheck Initialize subscription in the wizard as you have already initialized it manually.

    I wonder why they have marked this solution as deprecated since 2005, if MS cannot come up with its alternative.

    • 1

  3. "Infact you should never initialize a subscription from the publisher side" - Don't know where that comes from, but I definitely don't think it's true. You're supposed to create the snapshot on the publication, optionally publish it to the distribution server, and initialize from there. The backup-restore strategy is a soon-to-be deprecated alternative that is not the recommended method.

    mrdenny's correct, the trade-off with the compressed snapshot folder is the processing power required to compress for the publisher, and decompress for the subscriber. For smaller snapshots, you will get better performance out of a non-compressed snapshot, but for larger (like yours) you will likely get better performance using compression, trials will help determine the better method.

    • 1