INF: How to Automate Replication over a Dial-Up Connection

• Microsoft SQL Server version 7.0

SUMMARY

This article describes the process for automatically initiating replication over a dial-up connection. The process involves using a scheduled job to establish the dial-up connection and then perform the synchronization. This procedure is described in more detail in the MORE INFORMATION section of this article.

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

Q105194 How to Enable Automatic Logon to a RAS Server

Q147711 Scheduled RASDIAL Fails To Connect to Windows NT RAS Server

Additional query words: repl sync synch synced synched synchronize synchronized dialup

Keywords : SSrvRep kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbhowto kbinfo