SQL Server Performance

How to copy the initial replication snapshot?

Discussion in 'Performance Tuning for SQL Server Replication' started by irvingwashington, May 11, 2006.

  1. irvingwashington New Member

    All-
    I need to replicate a 36GB database between two datacenters, and I'm curious if there is a way to transport the initial snapshot of the db some other way than letting SQL Server sync it. The problem is that the VPN connection between the two datacenters is rather slow, so the initial 36GB snapshot is going to take at least 40hrs to copy. If I can get move the snapshot db separately outside of SQL Server (via FTP, let's say), I can cut that time in half. The VPN connection is fast enough to support the replication once the snapshot gets there, but the initial snapshot is going to take forever. Any ideas?

    Thanks,
    Z
  2. satya Moderator

    Why not take copy of database on a HDD and restore on the secondary site, then let replication take the action.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. irvingwashington New Member

    Satya-
    Actually that's what I've already done (copy the db file via an alternate route), but the DBA I'm working with on this project said that wouldn't work, that the replication engine needed to be one to make the initial snapshot of the database. So you are saying that if I setup replication, the publisher and subscriber will be able to work out what data needs to be synced?

    Thanks again!
    Zack
  4. Twan New Member

    Hi Zack,

    When you create the subscription you can specify that you don't want the synchronisation to take place. You have to be careful though to do the steps in the right order, what I've done before to help me is

    create the publication
    create a subscriber on the same server and allow it to fully synchronise

    once the subscriber is up to date, then stop all replication jobs

    create a subscriber on the remote server telling it not synchronise
    restore a backup of the subscriber from the source server to the destination server
    start all of the jobs

    That has worked for me in the past

    Cheers
    Twan

Share This Page