HOW TO: Automate Replication over a Dial-Up Connection (241149)

MORE INFORMATION

To configure automatic replication over a dial-up connection, you must first install replication on your SQL Server as you normally would. You can use the Configure Publishing and Distribution Wizard to set up replication, and then use the Create Publication Wizard on the publishing server to create the publication. Next, use the Pull Subscription Wizard on the subscribing server to create the pull subscription. You should use a pull subscription rather than a push subscription because you want the subscribing server to initiate the subscription.

NOTE: When you set up replication, choose a transactional publication on the publisher and a pull subscription on the subscriber. This creates the Distribution Agent job on the subscriber and the Log Reader Agent job at the publisher.

After you install replication and create the publication and pull subscription, create a new multi-step scheduled job on the subscriber. This job should have the following steps:

Step	Command type	Command	Comments
Step 1	Operating System Command (CmdExec)	RASDIAL -entryname-	This command starts the dial-up process.
Step 2	Transact-SQL Script (TSQL)	WAITFOR DELAY '00:02:00'	This step initiates a delay to ensure that the connection is stable.
Step 3	Transact-SQL Script (TSQL)	EXEC sp_start_job @job_name = 'Distribution job name'	This step starts the distribution job.
Step 4	Transact-SQL Script (TSQL)	WAITFOR DELAY '00:20:00'	This delay allows the distribution task to complete.
Step 5	Operating System Command (CmdExec)	RASDIAL -entryname- / DISCONNECT	This step closes the connection and disconnects.

After this multi-step scheduled job has been created, it should run at your predetermined schedule to perform the replication over the dial-up connection.

NOTE concerning WAITFOR: The values for the WAITFOR statement are examples only; you should replace them with appropriate values for your own situation.

NOTE concerning the Distribution Job: The Distribution Job should be scheduled to run "One Time". The Distribution Job completes after transferring the accumulated published transactions.

REFERENCES

For more information about replication, see the "Replication" topic in the SQL Server Books Online.

For additional information about the RASDIAL command, please see the following articles in the Microsoft Knowledge Base: