Welcome to dbForumz.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Replication over slow connections

 
   Database Forums (Home) -> Replication RSS
Next:  Berichte werden sequentiell beim Drucker ausgegeb..  
Author Message
Rohland

External


Since: Dec 02, 2008
Posts: 4



(Msg. 1) Posted: Tue Dec 02, 2008 8:49 am
Post subject: Replication over slow connections
Archived from groups: microsoft>public>sqlserver>replication (more info?)

Hi

I was wondering if I could get some assistance regarding the setup of a
replication environment across a dedicated 64KB satellite link with an
average latency of about 800ms (occasional time outs).

To give you a bit of background, I have setup Transactional Replication with
one subscriber using the pull mechanism (with continuous synchronization).
The subscriber and publishing database servers are running SQL Server 2005. I
set the merge agent's default profile to "slow link" at the distributor.

I setup the snapshot agent to create a compressed snapshot to reduce the
size of the initial transfer. Once everything was setup I confirmed the
tables were all created at the subscriber once I started synchronization. I
left work and the next morning I expected to see the tables populated.
However they weren't.

I right clicked on the subscription and selected "View Synchronization
status" and found that the job had stopped with "Could not connect to
distributor". I thought that perhaps a few timeouts had caused this, so I
clicked "Start Job" at which point I got an error stating that the job had
already started. This did not make sense to me, if the job was running surely
it should be retrying? I checked the connection by pinging the remote server
and logging in via remote desktop. I left it for a few hours as I thought I
caught it at a point where the error had just occurred. Several hours later
the error was still there.

I took the following action, I stopped the subscription synchronization job
manually and then restarted it by right clicking "View synchronization
status" and then clicking "Start Job". It seems to be up and running again as
the status is still "Synchronization ins progress". I noted now that that
database is locked, I assume the data is now being transferred and bulk
imported? At the distributor I see log records on the "Distributor to
Subscriber History" screen through the replication monitor like the following:

Extracted file 'DBNAME_BV6e3b4288_4#24.bcp'
Extracting snapshot file 'DBNAME_BV6e3b4288_4#25.bcp' from cabinet file ...

Does this mean everything is in order? If it is, how do I prevent the issue
where I have to manually stop and start the synchronization job at the
subscriber?

Any help here would be appreciated.

Thanks
Rohland

 >> Stay informed about: Replication over slow connections 
Back to top
Login to vote
Paul Ibison

External


Since: Oct 03, 2008
Posts: 35



(Msg. 2) Posted: Wed Dec 03, 2008 3:00 am
Post subject: RE: Replication over slow connections [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

The current history messages look good. The only change I'd make is to run
the distribution agent on a schedule (ay every minute) so any dropped
connections won't prevent things going through.
HTH,
Paul Ibison

 >> Stay informed about: Replication over slow connections 
Back to top
Login to vote
Rohland

External


Since: Dec 02, 2008
Posts: 4



(Msg. 3) Posted: Wed Dec 03, 2008 11:23 pm
Post subject: RE: Replication over slow connections [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hi Paul

I noticed at the distributor that after a while each session ends with the
following message:

Error messages:
The file 'snapshot.cab' in folder
'\\192.168.4.4\repldata\unc\SERVERNAME_PUBNAME\20081202095976\' is not a
valid cabinet file (Source: MSSQL_REPL, Error number: MSSQL_REPL20149)
Get help: http://help/MSSQL_REPL20149

The file 'snapshot.cab' in folder
'\\192.168.4.4\repldata\unc\SERVERNAME_PUBNAME\20081202095976\20081202095976\'
is not a valid cabinet file (Source: MSSQL_REPL, Error number: MSSQL_REPL1779)
Get help: http://help/MSSQL_REPL1779

After this error a new session starts up. This new session dies with the
same error and everything restarts again. I dont understand how the
snapshot.cab can be an invalid file when you messages like the following
preceeding it:

Extracting snapshot file 'TABLENAME_6.bcp' from cabinet file
Extracted file 'TABLENAME_EQUIP_6.bcp'
Extracting snapshot file 'TABLENAME_HAU1b03a159_7#1.bcp' from cabinet file

Then...

The file 'snapshot.cab' in folder
'\\192.168.4.4\repldata\unc\SERVERNAME_PUBNAME\20081202095976\' is not a
valid cabinet file

I am pretty sure this is the same error I got a few days ago before
re-configuring everything. Any thoughts here?
 >> Stay informed about: Replication over slow connections 
Back to top
Login to vote
Paul Ibison

External


Since: Oct 03, 2008
Posts: 35



(Msg. 4) Posted: Thu Dec 04, 2008 1:01 pm
Post subject: RE: Replication over slow connections [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

My guess is that the error message is misleading, and that the CAB file is
being partially read and then the connection dropped mid flow. If I were you,
I'd consider getting in touch with PSS and have them go over it. I've found a
few references to this issue on the web but no definitive solutions.
Rgds,
Paul Ibison
 >> Stay informed about: Replication over slow connections 
Back to top
Login to vote
Rohland

External


Since: Dec 02, 2008
Posts: 4



(Msg. 5) Posted: Fri Dec 05, 2008 12:46 am
Post subject: RE: Replication over slow connections [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

I think you may be right.

In the meantime, I have generated a snapshot without compression. I
compressed the snapshot myself and am busy uploading it to the subscriber
using BITS. Once there I hope to setup the pull subscription with it pointing
to a local network location to retrieve the snapshot. Hopefully this works
out for me.

Thanks for your input.
Rohland
 >> Stay informed about: Replication over slow connections 
Back to top
Login to vote
Rohland

External


Since: Dec 02, 2008
Posts: 4



(Msg. 6) Posted: Fri Dec 05, 2008 12:49 am
Post subject: RE: Replication over slow connections [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

I think you may be right.

I am attempting an alternative method of delivering the snapshot.

1. I generated the snapshot without compression
2. I compressed the snapshot manually
3. I am busy uploading the compressed snapshot to the subscriber using BITS
4. Once uploaded I hope to initialise the pull subscription from a local
network location containing the snapshot I uploaded.

Do you see any issues with this approach?

Thanks for your input.
Regards
Rohland
 >> Stay informed about: Replication over slow connections 
Back to top
Login to vote
Paul Ibison

External


Since: Oct 03, 2008
Posts: 35



(Msg. 7) Posted: Fri Dec 05, 2008 12:42 pm
Post subject: RE: Replication over slow connections [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

No issues - I have used this myself with the alternative snapshot location
and it was all fine. In fact if the CAB file is > 2GB then you have to do it
this way.
Rgds,
Paul Ibison
 >> Stay informed about: Replication over slow connections 
Back to top
Login to vote
Paul Ibison

External


Since: Oct 03, 2008
Posts: 35



(Msg. 8) Posted: Fri Dec 05, 2008 1:01 pm
Post subject: RE: Replication over slow connections [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

OK - no doubt you've worked this out but if not it's the
"@alt_snapshot_folder" parameter in sp_addpullsubscription_agent that needs
changing.
Cheers,
Paul Ibison
 >> Stay informed about: Replication over slow connections 
Back to top
Login to vote
Display posts from previous:   
   Database Forums (Home) -> Replication All times are: Pacific Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]