MS SQL Server Replication - Setting up a subscription over a low bandwidth connection

Author

Setting up a subscription over a low bandwidth connection

Russell

2005-07-22, 7:23 am

I am in the process of drawing up the plan to reinstate a number
subscriptions which where lost when a windows update disrupted our wan. The
publications are still in place on the overseas servers, but I need to be
able to create the subscription and to then be able to generate the snapshot,
and instead of the distribution agent sending it over the wan I want to zip
it up and copy it back and have the subscribing server take it from a local
directory.

I determined so far that I need to add the subscriber before I generate the
snapshot. But I am a little unsure after that.

Hilary Cotter

2005-07-22, 9:23 am

For named subscriptions you do, for unnamed (anonymous) you do not need to
create the subscriptions in advance.

Just to clarify what this means - if you have named subscriptions you can
only generate the snapshot after creating the subscription. For anonymous
you can use an old snapshot and then deploy the subscription.

I situations like what you have what I do is zip up the snapshot, get it to
the subscriber via email, ftp, courier, pigeon, whatever, and then in the
pull subscription wizard you can use the Use the Snapshot Files from the
following folder in the Snapshot Delivery dialog box.

You can also deploy your subscription manually from a backup, but this
involves considerable work for transactional replication.

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Russell" <Russell@discussions.microsoft.com> wrote in message
news:9B9FD86B-A41E-475A-9800- 0D735B52C56C@microso
ft.com...
> I am in the process of drawing up the plan to reinstate a number
> subscriptions which where lost when a windows update disrupted our wan.
The
> publications are still in place on the overseas servers, but I need to be
> able to create the subscription and to then be able to generate the
snapshot,
> and instead of the distribution agent sending it over the wan I want to
zip
> it up and copy it back and have the subscribing server take it from a
local

> directory.
>
> I determined so far that I need to add the subscriber before I generate
the
> snapshot. But I am a little unsure after that.

Russell

2005-07-22, 9:23 am

I believe it will be using named subscribers. So do I add the subscriber and
generate the snapshot, then disable the schedule for the subscription. Zip
the snapshot, copy it back and unpack it. Then update the subscriber with the
location of the snapshot and enable and run the distribution job for the
subscriber. Also when setting up the subscriber I take it I choose to
intialize the schema but not to run the snapshot immeadealty.

"Hilary Cotter" wrote:

> For named subscriptions you do, for unnamed (anonymous) you do not need to
> create the subscriptions in advance.
>
> Just to clarify what this means - if you have named subscriptions you can
> only generate the snapshot after creating the subscription. For anonymous
> you can use an old snapshot and then deploy the subscription.
>
> I situations like what you have what I do is zip up the snapshot, get it to
> the subscriber via email, ftp, courier, pigeon, whatever, and then in the
> pull subscription wizard you can use the Use the Snapshot Files from the
> following folder in the Snapshot Delivery dialog box.
>
> You can also deploy your subscription manually from a backup, but this
> involves considerable work for transactional replication.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Russell" <Russell@discussions.microsoft.com> wrote in message
> news:9B9FD86B-A41E-475A-9800- 0D735B52C56C@microso
ft.com...
> The
> snapshot,
> zip
> local
> the
>
>
>

Hilary Cotter

2005-07-22, 11:23 am

This is one approach, but its not for the timid. What you have to do is make
sure your distribution agent is stopped and disabled, and then run the
snapshot. Its your choice on whether you want to run the snapshot
immediately - as it will lock the tables on your publisher which can cause
problems for your users.

After the commands have been posted to the distribution database run
sp_browsereplcmds in the distribution database and note the synchronization
commands. Note the order they are run in. Run these scripts on your
subscription database in the same order. Then you will have to delete these
commands from your distribution database. You might want to back up the
tables when you do this.

Your other option is to build your subscription database to be a clone of
your publisher making all triggers, constraints, and identity columns not
for replication. and do a no-sync.

Hilary

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Russell" <Russell@discussions.microsoft.com> wrote in message
news:5A0390A2-5074-4744-9171- 7BE02C168FAF@microso
ft.com...
> I believe it will be using named subscribers. So do I add the subscriber
and
> generate the snapshot, then disable the schedule for the subscription. Zip
> the snapshot, copy it back and unpack it. Then update the subscriber with
the[color=darkred]
> location of the snapshot and enable and run the distribution job for the
> subscriber. Also when setting up the subscriber I take it I choose to
> intialize the schema but not to run the snapshot immeadealty.
>
> "Hilary Cotter" wrote:
>
to[color=darkred]
can[color=darkred]
anonymous[color=dark
red]
to[color=darkred]
the[color=darkred]
wan.[color=darkred]
be[color=darkred]
to[color=darkred]
generate[color=darkr
ed]

Sponsored Links