Setup a subscriber in a remote location

Ben Thul

2010-10-29 11:42:06 UTC

Unfortunately, this won't work. That is unless you can have your
publisher database down while you do the transfer. A better approach
(in my opinion) would be the following procedure:

1) Set up distributor. An important note, make sure the minimum
retention at the distributor accommodates the amount of time it'll
take you to take the backup, transfer it, and restore it.
2) Create publication and add the articles to it.
3) Take a backup of the publisher and transfer it to the subscriber
4) Restore the backup, run sp_removedbreplication on it, disable (or
remove) any triggers you don't want to fire on this database.
5) Add the subscription using the 'initialize with backup' option

Once that's done, anything that happened at the publisher since the
backup was taken should now start delivering to the subscriber through
the distribution agent.

As for your question about snapshots, it doesn't seem particularly
relevant here; there are no snapshots involved in the process outlined
above. However, to answer your question, snapshots are delivered
through the distribution agent.

HTH,
Ben

Post by xav
I need to setup an SQL Server 2005 replication with a 20GB database.
That's the first time I setup a replication. The subordinate server
will be hosted in a remote location with a low bandwith internet
access. Therefore I need to make a backup (or stop and zip ldf & mdf
files) before going to this location, restore this backup, setup the
1. stop the main server
2. zip ldf & mdf files (due to different constraints it is faster than
a backup)
3. setup the main server as a distributor (no replication yet)
4. start the main server
5. go to the remote location
6. install an SQL Server, attach the decompressed ldf & mdf files
7. setup the subscription on this subordinate server
- is this process correct ?
- will snapshots be downloaded through MSSQL connection or does it
require a shared folder or FTP ?
- new data inserted in the main database between operation 1 and 7
will be downloaded with the 1st distribution (I mean no data lost) ?
Thanks for your help