Hi,
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.