distributing subscriber database on multiple machine - install

Hi,

Is it possible to create a 'master' subscriber, back-up the database with the replication triggers and subscription intact, then restore this subscriber database on multiple subscribers during an install? The subscription is for an anonymous web-synced publication, up to 40 subscribers, and I am trying to avoid the initial snapshot download.

Thanks,

Darrell Young
Hi Darrell,

Yes, it is possible to avoid the initial snapshot downlad on your subscribers. An alternative maybe, to initialize snapshot from backup. More information can be found in book online.

Initializing a Merge Subscription Without a Snapshot
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/ee16af24-d7e2-4b65-a25f-dc89caba2ea2.htm

Initializing a Transactional Subscription Without a Snapshot
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/75c8c1f8-60bc-44a8-944b-d18d1f6bda11.htm

Regards,

Gary Chen|||Gary,

Thanks for the reply. The issue is have is that the subscribers will only have a subset of the data and the schema of the published database, so wholly backing up and restoring the published database is not practical. I had tried taking a subscriber database, then removing the subscription and creating a backup from that - the issue is that the rowguids are removed from the tables. I suppose the choice I have left is to create a backup of the published database, restore on a subscriber, manually remove the data and schema I am not including in the publication, then create a backup of that.
Thanks,

Darrell Young
|||

Hi Darrell,

Try the following,

- Create a snapshot at the publisher

- Download the initial snapshot to one subscriber S1

- Back up the subscriber database at S1

- Before you restore the back up database to other subscriber S2, drop the subscription that you have created before at subscriber S2

- Restore the S1 backup to S2 with KEEP_REPLICATION off (I believe by default it is off)

- Re-create the subcription with no-sync option.

You probably want to make sure that no data get updated to your publisher or subscriber database while you perform this backup-restore.

Try it on one or two machines to see if this work before you restore to all 40 machines.

Regards,

Gary Chen