SQL Server Performance

SQL Server 2005 - Merge Replication

Discussion in 'SQL Server 2005 Replication' started by ravivarman, Jan 1, 2009.

  1. ravivarman New Member

    we are shifting our data-setup from SQL Server 2000 to SQL Server 2005. With regard to this, I am facing
    an issue with replication of the databases.
    We have a database with over 600 articles that are published.
    The procedure that we followed with SQL Server 2000 in order to have a smooth and quick replication
    scenario was as below:
    On the publisher:
    1. Create the database snapshot.
    2. Back up the published database.
    3. Create a temporary database and restore the published database on the temporary database.
    4. Create a new 'no-sync' pull subscription from the temporary database to the published database
    5. Run the merge agent to synchronize the databases.
    6. Copy the subscription databases (XYZDatasbe.msf file)
    7. Register the subscriber computer
    8. Register the subscribing database using sp_addmergesubscription
    On the subscriber:
    1. Attach the subscription database copy (XYZDatasbe.msf)
    2. Create a pull subscription using sp_addmergepullsubscription
    3. Connect to the publisher using a dial up connection
    4. Run the merge agent to synchronize the data.
    In such a case, the initial synchronization would take less than a minute as no schema was transferred.
    In the case of a scenario with SQL Server 2005:
    a. The attachable subscription databases (.msf files) have been deprecated. So we cannot create .msf files
    to transfer the snapshot to the subscribers.
    b. Instead of .msf files, we can backup and restore the publication database at the subscriber and make
    the pull subscription with the no-sync option. But, for the sp_addmergepullsubscription, the parameter
    sync_type with value 'none' has been deprecated. i.e sync_type='none' is deprecated, however
    sync_type='automatic' is valid.
    Hence when we create a pull subscription by restoring a backup of the published database and indicate 'do
    not initialize', the database schema alone (not data) once again gets transferred from the publisher to
    the subscriber on the first synchronization. This takes a very long time over a dial up connection.
    There is an option to 'initialize with backup' or 'replication support only'. But these options are
    available only for transactional replication and not merge replication.
    Please guide me on overcoming this issue.
  2. satya Moderator

    Without no doubt I can see the main problem is due to the dialup connection between the instances, and also the number of articles that are published are very high.
    Are you looking replication to provide high availability or for reporting?

Share This Page