SQL Express 2005 Snapshot Replication on Disconnected/Closed Networks

Question

I am having trouble setting up my Pull Subscription and I am new to replication. 

 

I have several servers hosting a databased website that will be the same, except for user input and traffic.  Quite simply, I need to copy most tables, SPs and data from network to network.  I can't use FTP/Web synch ... as I mentioned the networks do not touch eachother or the internet.

 

On server Web1, it was easy to create a Publication called Pub via the wizard for my database:  TheDB.  Then on Web1, again, I added a Subscription to the Publication, indicating my second server, Web2, and the same database name:  TheDB (I have already backed up and restored TheDB to all my servers).  Here's one of the sp's I ran on Web1:

use [TheDB]

exec sp_addsubscription @publication = N'Pub', @subscriber = N'Web2'', @destination_db = N'TheDB', @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'read only'

GO

 

This is where I feel stuck.  Using the wizard on Web2 doesn't allow me to see Web1.  So I tried the following on Web2:

 

use [TheDB]

exec sp_addpullsubscription @publisher = N'Web1', @publication = N'Pub', @publisher_db = N'TheDB', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 1

 

exec sp_addpullsubscription_agent @publisher = N'Web1', @publisher_db = N'TheDB', @publication = N'Pub', @distributor = N'Web1', @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 0, @active_start_date = 0, @active_end_date = 19950101, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'True', @job_login = null, @job_password = null, @publication_type = 0

GO

 

I copied the snapshot folder, ie. 20070709134423, onto CD and moved it into Web2's default replication folder, but I always receive:  cannot connect to Distibutor.  I've tried using an Alias, as well, but don't understand exactly how I should point that either.  I checked the publication's PAL and my Web2 user has rights and is an owner of the Web2 TheDB database.

 

Any help is appreciated.

 

Nate

Answer 1

what error message are you getting?

Answer 2

Thanks for your reply.  Here is what repeats in the log:

 

2007-07-09 23:25:31.281 Connecting to Subscriber 'Web2'<nl/>

2007-07-09 23:25:31.406 Connecting to Distributor 'Web1'<nl/>

2007-07-09 23:25:47.218 Agent message code 20084. The process could not connect to Distributor 'Web1'.<nl/>

2007-07-09 23:25:47.218 The agent failed with a 'Retry' status. Try to run the agent at a later time.,00:03:01,0,0,,,,0

 

Thanks,

 

Nate

Answer 3

Guess I stumped the whole technical community.  Thanks!

Answer 4

If these servers are on different networks and those networks to not touch each other or the internet how can you expect them to communicate?

 

You cannot replicate between servers which cannot communicate with each other. That communication has to be either over the internet, over a vpn or direct connection on a network.

 

Perhaps I am missing something here.

 

Martin

Answer 5

You are correct, they do not connect - ever.  (fyi:  this has to do with classified networks.)

 

As a computer person, however, I simply cannot believe that you can't copy the folder filled with replication data onto portable media and move it to another server.  If FTP works, which is not database servers connecting, then why can't this. 

 

I am convinced it's possible, I just can't figure out the exact setup or how to tell the Subscriber, "Just look in C:\Data and stop looking for the other server!"

 

Appreciate your input.

 

Nate

Answer 6

What kind of replication are you using? I guess it should work with snapshot replication. However that is only unidirectional.

 

Martin

Answer 7

Yes, snapshot.  I only need to replicate in one direction.

 

Nate

Answer 8

So, it just can't be done, huh?  Interesting ....

 

Answer 9

have a look at bcp. Bcp the files to a cd, tape drive or something, carry them over to the new server and then bcp them in.

 

Answer 10

I am not sure why do we need BCP.

SQL Server has built-in replication using FTP. Where SQL A publishes files on FTP and SQL B downloads the files.

I am not why SQL A must have direct connection to SQL B, when all synchronization data is published on FTP?

It defeats the whole purpose of FTP publishing. If SQL servers can connect directly to each other then they can transfer all the data directly, why need FTP?

In the same manner if there is some custom process which moves data from one network to another (from one publish folder to another), why this can't be accomplished. How is it different than using FTP?

I am not sure I follow the design decisions made for FTP publishing? Can someone please explain?