What is the syntax for the snapshop location setup referring to the "Client
path to this folder from the FTP root"? When my pull connects, it doesn't
seem to issue any change directory commands and does not find the snapshot
files which are there.
The client path is the path from the FTP root directory, so if your snapshot
directory is called TRSnapshot and is in the ftp root directory, the path
would be \TRSnapshot\ftp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks, worked like a charm but my main issue turned out to be the space in
the order details table in the Northwind db. I am doing a proof of concept
with that DB and it tried to run that script first and bombed.
"Paul Ibison" wrote:
> The client path is the path from the FTP root directory, so if your snapshot
> directory is called TRSnapshot and is in the ftp root directory, the path
> would be \TRSnapshot\ftp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Jeffrey,
this isn't such a large table - at what point does it fail?
Might be worth using the compression if you have a slow link?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Showing posts with label ftp. Show all posts
Showing posts with label ftp. Show all posts
Monday, March 12, 2012
replication over internet
Hi
I want to set up a replication between servers over the internet. I know
there are 3 ways to do this, using VPN,proxy server or TCP/IP (FTP). but I
don't know the advantages of each one. could you tell me what are the
differences between these ways in performance and security?
and could you tell me what will happen if the servers disconnect from each
other during the process of replication?
Thanks
Tajik,
connectivity is really a choice between VPN or non-trusted connectivity. The
way I see these breaking down are...
using a VPN is secure and you'll be able to use fileshares ie there's no
need to enable FTP. So, although you're operating over the internet things
are much the same as per usual.
The proxy server is an element of the connectivity to a network by acting as
a firewall and blocking traffic on non-essential ports, and possibly
allowing traffic from only assigned IP addresses.
TCP/IP & FTP is the method used if you are replicating across non-trusted
domains - ie in the absence of a VPN. You can have it operate securely by
using a certificate and encrypting the TCP/IP traffic.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul, I am getting the following error when I attempt to download the
snapshot files from the Replication FTP Site:
Date11/30/2005 1:20:22 PM
LogJob History
(28053-DB-inventory_master-inventory_master_a-S-HUNLEY-inventory_master-5007D5E3-0C82-47A1-A26A-5E8F52E68F83)
Step ID1
ServerS-HUNLEY
Job
Name28053-DB-inventory_master-inventory_master_a-S-HUNLEY-inventory_master-5007D5E3-0C82-47A1-A26A-5E8F52E68F83
Step NameRun agent.
Duration00:20:24
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
-XSERVER S-HUNLEY
-XCMDLINE 0
-XCancelEventHandle 00000828
2005-11-30 18:40:37.661 Startup Delay: 4093 (msecs)
2005-11-30 18:40:41.757 Connecting to Subscriber 'S-HUNLEY'
2005-11-30 18:40:42.008 Connecting to Distributor '28053-DB'
2005-11-30 18:40:42.729 Initializing
2005-11-30 18:40:42.919 Parameter values obtained from agent profile:
-bcpbatchsize 100000
-commitbatchsize 100
-commitbatchthreshold 1000
-historyverboselevel 1
-keepalivemessageinterval 300
-logintimeout 15
-maxbcpthreads 1
-maxdeliveredtransactions 0
-pollinginterval 10000
-querytimeout 300
-skiperrors
-transactionsperhistory 100
2005-11-30 18:40:44.121 Snapshot files will be downloaded via ftp
2005-11-30 18:40:44.301 Connecting to ftp site '28053-DB'
2005-11-30 18:40:46.334 Agent message code 20033. The process could not
retrieve file
'28053-DB_inventory_master_inventory_master_articles\2005 1130120007\Purchase_Order_Line_Items_1.sch' from the FTP site '28053-DB'.
The Publisher is a SQL Server 2000 Database (SP3A) running on Windows 2000
Advanced Server and the Subscriber is a SQL Server 2005 database running on
Windows XP Pro SP2. These databases on in diferent domains. I can ping the
machines from one another and I can see the FTP folder from the subscriber as
well as on the publisher.
Any ideas?
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"Paul Ibison" wrote:
> Tajik,
> connectivity is really a choice between VPN or non-trusted connectivity. The
> way I see these breaking down are...
> using a VPN is secure and you'll be able to use fileshares ie there's no
> need to enable FTP. So, although you're operating over the internet things
> are much the same as per usual.
> The proxy server is an element of the connectivity to a network by acting as
> a firewall and blocking traffic on non-essential ports, and possibly
> allowing traffic from only assigned IP addresses.
> TCP/IP & FTP is the method used if you are replicating across non-trusted
> domains - ie in the absence of a VPN. You can have it operate securely by
> using a certificate and encrypting the TCP/IP traffic.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Scott,
perhaps it is the path you are using to the ftp folder.
When I set this up I used the path
'c:\inetpub\ftproot\trsnapshot'
as the alternative snapshot location on the publisher (snapshot location
tab) - and
\trsnapshot\ftp
as the 'client path to this folder'.
Pls can you tell us what are the corresponding settings on your publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I have the following settings in my Publisher:
Alternate Path (Default Path unchecked) -
'\\28053-DB\D$\inetpub\ftproot\replication\inventory_master \'
Ftp Client path -
'\replication\inventory_master\'
The Server that I remote into, automatically assigns the \\28053-DB\D$\
qualifier. I didn't include the ftp subdirectory that you did, it was
created by the process however.
Is this explanation concise enough? If more details are needed please let
me know and I will provide them ASAP.
Thanks in advance for any help or advice you might have.
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"Paul Ibison" wrote:
> Scott,
> perhaps it is the path you are using to the ftp folder.
> When I set this up I used the path
> 'c:\inetpub\ftproot\trsnapshot'
> as the alternative snapshot location on the publisher (snapshot location
> tab) - and
> \trsnapshot\ftp
> as the 'client path to this folder'.
> Pls can you tell us what are the corresponding settings on your publisher.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Paul, by the time I was finished writing the first post/response.. I realized
what i had done wrong. I had forgot to put the subdirectory /ftp/ in my
Client Ftp path. Once i did, I reran the process and it worked. Now, I'm
not sure how well replication of anything other than the tables will work,
going from 2000 to 2005, but at least the Data Warehouse is populating
correctly, and transactionally.
I greatly appreciate your time and look forward to discussing other items in
the future.
Respectfully,
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"Paul Ibison" wrote:
> Scott,
> perhaps it is the path you are using to the ftp folder.
> When I set this up I used the path
> 'c:\inetpub\ftproot\trsnapshot'
> as the alternative snapshot location on the publisher (snapshot location
> tab) - and
> \trsnapshot\ftp
> as the 'client path to this folder'.
> Pls can you tell us what are the corresponding settings on your publisher.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
I want to set up a replication between servers over the internet. I know
there are 3 ways to do this, using VPN,proxy server or TCP/IP (FTP). but I
don't know the advantages of each one. could you tell me what are the
differences between these ways in performance and security?
and could you tell me what will happen if the servers disconnect from each
other during the process of replication?
Thanks
Tajik,
connectivity is really a choice between VPN or non-trusted connectivity. The
way I see these breaking down are...
using a VPN is secure and you'll be able to use fileshares ie there's no
need to enable FTP. So, although you're operating over the internet things
are much the same as per usual.
The proxy server is an element of the connectivity to a network by acting as
a firewall and blocking traffic on non-essential ports, and possibly
allowing traffic from only assigned IP addresses.
TCP/IP & FTP is the method used if you are replicating across non-trusted
domains - ie in the absence of a VPN. You can have it operate securely by
using a certificate and encrypting the TCP/IP traffic.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul, I am getting the following error when I attempt to download the
snapshot files from the Replication FTP Site:
Date11/30/2005 1:20:22 PM
LogJob History
(28053-DB-inventory_master-inventory_master_a-S-HUNLEY-inventory_master-5007D5E3-0C82-47A1-A26A-5E8F52E68F83)
Step ID1
ServerS-HUNLEY
Job
Name28053-DB-inventory_master-inventory_master_a-S-HUNLEY-inventory_master-5007D5E3-0C82-47A1-A26A-5E8F52E68F83
Step NameRun agent.
Duration00:20:24
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
-XSERVER S-HUNLEY
-XCMDLINE 0
-XCancelEventHandle 00000828
2005-11-30 18:40:37.661 Startup Delay: 4093 (msecs)
2005-11-30 18:40:41.757 Connecting to Subscriber 'S-HUNLEY'
2005-11-30 18:40:42.008 Connecting to Distributor '28053-DB'
2005-11-30 18:40:42.729 Initializing
2005-11-30 18:40:42.919 Parameter values obtained from agent profile:
-bcpbatchsize 100000
-commitbatchsize 100
-commitbatchthreshold 1000
-historyverboselevel 1
-keepalivemessageinterval 300
-logintimeout 15
-maxbcpthreads 1
-maxdeliveredtransactions 0
-pollinginterval 10000
-querytimeout 300
-skiperrors
-transactionsperhistory 100
2005-11-30 18:40:44.121 Snapshot files will be downloaded via ftp
2005-11-30 18:40:44.301 Connecting to ftp site '28053-DB'
2005-11-30 18:40:46.334 Agent message code 20033. The process could not
retrieve file
'28053-DB_inventory_master_inventory_master_articles\2005 1130120007\Purchase_Order_Line_Items_1.sch' from the FTP site '28053-DB'.
The Publisher is a SQL Server 2000 Database (SP3A) running on Windows 2000
Advanced Server and the Subscriber is a SQL Server 2005 database running on
Windows XP Pro SP2. These databases on in diferent domains. I can ping the
machines from one another and I can see the FTP folder from the subscriber as
well as on the publisher.
Any ideas?
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"Paul Ibison" wrote:
> Tajik,
> connectivity is really a choice between VPN or non-trusted connectivity. The
> way I see these breaking down are...
> using a VPN is secure and you'll be able to use fileshares ie there's no
> need to enable FTP. So, although you're operating over the internet things
> are much the same as per usual.
> The proxy server is an element of the connectivity to a network by acting as
> a firewall and blocking traffic on non-essential ports, and possibly
> allowing traffic from only assigned IP addresses.
> TCP/IP & FTP is the method used if you are replicating across non-trusted
> domains - ie in the absence of a VPN. You can have it operate securely by
> using a certificate and encrypting the TCP/IP traffic.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Scott,
perhaps it is the path you are using to the ftp folder.
When I set this up I used the path
'c:\inetpub\ftproot\trsnapshot'
as the alternative snapshot location on the publisher (snapshot location
tab) - and
\trsnapshot\ftp
as the 'client path to this folder'.
Pls can you tell us what are the corresponding settings on your publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I have the following settings in my Publisher:
Alternate Path (Default Path unchecked) -
'\\28053-DB\D$\inetpub\ftproot\replication\inventory_master \'
Ftp Client path -
'\replication\inventory_master\'
The Server that I remote into, automatically assigns the \\28053-DB\D$\
qualifier. I didn't include the ftp subdirectory that you did, it was
created by the process however.
Is this explanation concise enough? If more details are needed please let
me know and I will provide them ASAP.
Thanks in advance for any help or advice you might have.
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"Paul Ibison" wrote:
> Scott,
> perhaps it is the path you are using to the ftp folder.
> When I set this up I used the path
> 'c:\inetpub\ftproot\trsnapshot'
> as the alternative snapshot location on the publisher (snapshot location
> tab) - and
> \trsnapshot\ftp
> as the 'client path to this folder'.
> Pls can you tell us what are the corresponding settings on your publisher.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Paul, by the time I was finished writing the first post/response.. I realized
what i had done wrong. I had forgot to put the subdirectory /ftp/ in my
Client Ftp path. Once i did, I reran the process and it worked. Now, I'm
not sure how well replication of anything other than the tables will work,
going from 2000 to 2005, but at least the Data Warehouse is populating
correctly, and transactionally.
I greatly appreciate your time and look forward to discussing other items in
the future.
Respectfully,
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"Paul Ibison" wrote:
> Scott,
> perhaps it is the path you are using to the ftp folder.
> When I set this up I used the path
> 'c:\inetpub\ftproot\trsnapshot'
> as the alternative snapshot location on the publisher (snapshot location
> tab) - and
> \trsnapshot\ftp
> as the 'client path to this folder'.
> Pls can you tell us what are the corresponding settings on your publisher.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
Replication Over FTP, Initial Snapshot Timeout
Hello,
I am trying to replicate over FTP (merge replication) and I'm having
trouble with subscribing.
After 60-70 minutes I get a timeout error and the initial snapshot fails
to work.
The exact error is:
"Category:OS
Source:
Number: 12002
Message: The operation timed out"
Then it says that it failed to propagate the file it was working on.
The entire snapshot directory is less than 500 meg. I tried the
compressed snapshot first (which makes it about 130 meg) and that gets
the same timeout error.
Is there a way to increase that timeout somewhere? I don't quite
understand why it's timing out anyway, it should have plenty of time to
transfer that size of a file. Both machines are on high speed connections.
Thanks,
Jeff
right click on your pull subscription agent. Select agent properties, then
select run steps, and give focus to run agent. click on the commands button
and press the end key. Then press space, and type -QueryTimeout 600.
Then restart your pull subscription agent.
You may want to right click on replicaiton monitor and select refresh rate
and settings and set your inactivity threshold to something high.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jeff Hedlund" <jeff.hedlund_NOSPAM_@._NOSPAM_elsym.com> wrote in message
news:oIA3d.1761$DY.272@.chiapp18.algx.net...
> Hello,
> I am trying to replicate over FTP (merge replication) and I'm having
> trouble with subscribing.
> After 60-70 minutes I get a timeout error and the initial snapshot fails
> to work.
> The exact error is:
> "Category:OS
> Source:
> Number: 12002
> Message: The operation timed out"
> Then it says that it failed to propagate the file it was working on.
> The entire snapshot directory is less than 500 meg. I tried the
> compressed snapshot first (which makes it about 130 meg) and that gets
> the same timeout error.
> Is there a way to increase that timeout somewhere? I don't quite
> understand why it's timing out anyway, it should have plenty of time to
> transfer that size of a file. Both machines are on high speed
connections.
> Thanks,
> Jeff
|||Hilary Cotter wrote:
> right click on your pull subscription agent. Select agent properties, then
> select run steps, and give focus to run agent. click on the commands button
> and press the end key. Then press space, and type -QueryTimeout 600.
Ok, I had previously tried this with no success but ...
> Then restart your pull subscription agent.
> You may want to right click on replicaiton monitor and select refresh rate
> and settings and set your inactivity threshold to something high.
This appeared to do the trick. I noticed that after 10 minutes (the
default timeout) is when my transfer would stop taking place.
I successfully transferred a few large bcp files (133 meg and 220 meg),
but ran into another problem when it applied one of those files before
continuing to download. So by the time it finished applying one of those
files, it tried to download the next file and the connection was no
longer active.
So I decided to go back to the compressed snapshot and try again since
it looked like my timeout issue disappeared with the inactivity
threshold setting.
Now I'm not getting a timeout, but for some reason the compressed
snapshot (cab) fails to complete it's download (it's about 120 meg).
First time I got to ~58 meg. Second time it got to ~9 meg. Third time
it got to ~105 meg.
I don't see any problem with the network between the two machines
(though they are separated by the Atlantic Ocean), and only moments
before it had no problem downloading the two large bcp files.
Is there some other setting I can look for? Is there something else it
does while downloading a compressed snapshot?
Thanks,
Jeff
|||Jeff Hedlund wrote:
> Now I'm not getting a timeout, but for some reason the compressed
> snapshot (cab) fails to complete it's download (it's about 120 meg).
> First time I got to ~58 meg. Second time it got to ~9 meg. Third time
> it got to ~105 meg.
> I don't see any problem with the network between the two machines
> (though they are separated by the Atlantic Ocean), and only moments
> before it had no problem downloading the two large bcp files.
Looking at the FTP server logs, I see errors about 120 second inactivity
timeouts. So maybe I am having some network congestion/problems.
Jeff
|||I would try to zip up the snapshot files and then have the subscriber pull
them through ftp. Once they arrive at the subscriber, expand them, create a
pull subscription and then when you get to the specify snapshot location
select specify directory and browse to the location of the snapshot files.
The path will have to be of the form repldata\unc\pubname_subname\date
(IIRC).
"Jeff Hedlund" <jeff.hedlund_NOSPAM_@._NOSPAM_elsym.com> wrote in message
news:hGG3d.1861$DY.760@.chiapp18.algx.net...
> Jeff Hedlund wrote:
> Looking at the FTP server logs, I see errors about 120 second inactivity
> timeouts. So maybe I am having some network congestion/problems.
> Jeff
|||I can use this procedure to manually apply the snapshot But using PUSH
subscriptions? what will be the procedure?
Thanks.
"Hilary Cotter" wrote:
> I would try to zip up the snapshot files and then have the subscriber pull
> them through ftp. Once they arrive at the subscriber, expand them, create a
> pull subscription and then when you get to the specify snapshot location
> select specify directory and browse to the location of the snapshot files.
> The path will have to be of the form repldata\unc\pubname_subname\date
> (IIRC).
>
> "Jeff Hedlund" <jeff.hedlund_NOSPAM_@._NOSPAM_elsym.com> wrote in message
> news:hGG3d.1861$DY.760@.chiapp18.algx.net...
>
>
|||the original poster was using FTP. If you are doing a push you can still
compress your snapshot. If your snapshot is very large you should think
about doing no sync subscriptions, and manually get the data over to the
subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Adrian320" <Adrian320@.discussions.microsoft.com> wrote in message
news:641144BB-058C-4202-B790-ABCE3BC2D18B@.microsoft.com...[vbcol=seagreen]
> I can use this procedure to manually apply the snapshot But using PUSH
> subscriptions? what will be the procedure?
> Thanks.
> "Hilary Cotter" wrote:
pull[vbcol=seagreen]
create a[vbcol=seagreen]
files.[vbcol=seagreen]
time[vbcol=seagreen]
(though[vbcol=seagreen]
had[vbcol=seagreen]
inactivity[vbcol=seagreen]
|||Ok, so when you do it with FTP you can specify the location, ok.
One more question I have the .bcp files on the subscriber computer. But I
don't know how to run a script to restore that table ('article') to the
subscriber database to then enable the nosync subscription.
Is there a link that you could point me to learn about this? Or can I use
the Restore Database command?
Thanks.
"Hilary Cotter" wrote:
> the original poster was using FTP. If you are doing a push you can still
> compress your snapshot. If your snapshot is very large you should think
> about doing no sync subscriptions, and manually get the data over to the
> subscriber.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Adrian320" <Adrian320@.discussions.microsoft.com> wrote in message
> news:641144BB-058C-4202-B790-ABCE3BC2D18B@.microsoft.com...
> pull
> create a
> files.
> time
> (though
> had
> inactivity
>
>
I am trying to replicate over FTP (merge replication) and I'm having
trouble with subscribing.
After 60-70 minutes I get a timeout error and the initial snapshot fails
to work.
The exact error is:
"Category:OS
Source:
Number: 12002
Message: The operation timed out"
Then it says that it failed to propagate the file it was working on.
The entire snapshot directory is less than 500 meg. I tried the
compressed snapshot first (which makes it about 130 meg) and that gets
the same timeout error.
Is there a way to increase that timeout somewhere? I don't quite
understand why it's timing out anyway, it should have plenty of time to
transfer that size of a file. Both machines are on high speed connections.
Thanks,
Jeff
right click on your pull subscription agent. Select agent properties, then
select run steps, and give focus to run agent. click on the commands button
and press the end key. Then press space, and type -QueryTimeout 600.
Then restart your pull subscription agent.
You may want to right click on replicaiton monitor and select refresh rate
and settings and set your inactivity threshold to something high.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jeff Hedlund" <jeff.hedlund_NOSPAM_@._NOSPAM_elsym.com> wrote in message
news:oIA3d.1761$DY.272@.chiapp18.algx.net...
> Hello,
> I am trying to replicate over FTP (merge replication) and I'm having
> trouble with subscribing.
> After 60-70 minutes I get a timeout error and the initial snapshot fails
> to work.
> The exact error is:
> "Category:OS
> Source:
> Number: 12002
> Message: The operation timed out"
> Then it says that it failed to propagate the file it was working on.
> The entire snapshot directory is less than 500 meg. I tried the
> compressed snapshot first (which makes it about 130 meg) and that gets
> the same timeout error.
> Is there a way to increase that timeout somewhere? I don't quite
> understand why it's timing out anyway, it should have plenty of time to
> transfer that size of a file. Both machines are on high speed
connections.
> Thanks,
> Jeff
|||Hilary Cotter wrote:
> right click on your pull subscription agent. Select agent properties, then
> select run steps, and give focus to run agent. click on the commands button
> and press the end key. Then press space, and type -QueryTimeout 600.
Ok, I had previously tried this with no success but ...
> Then restart your pull subscription agent.
> You may want to right click on replicaiton monitor and select refresh rate
> and settings and set your inactivity threshold to something high.
This appeared to do the trick. I noticed that after 10 minutes (the
default timeout) is when my transfer would stop taking place.
I successfully transferred a few large bcp files (133 meg and 220 meg),
but ran into another problem when it applied one of those files before
continuing to download. So by the time it finished applying one of those
files, it tried to download the next file and the connection was no
longer active.
So I decided to go back to the compressed snapshot and try again since
it looked like my timeout issue disappeared with the inactivity
threshold setting.
Now I'm not getting a timeout, but for some reason the compressed
snapshot (cab) fails to complete it's download (it's about 120 meg).
First time I got to ~58 meg. Second time it got to ~9 meg. Third time
it got to ~105 meg.
I don't see any problem with the network between the two machines
(though they are separated by the Atlantic Ocean), and only moments
before it had no problem downloading the two large bcp files.
Is there some other setting I can look for? Is there something else it
does while downloading a compressed snapshot?
Thanks,
Jeff
|||Jeff Hedlund wrote:
> Now I'm not getting a timeout, but for some reason the compressed
> snapshot (cab) fails to complete it's download (it's about 120 meg).
> First time I got to ~58 meg. Second time it got to ~9 meg. Third time
> it got to ~105 meg.
> I don't see any problem with the network between the two machines
> (though they are separated by the Atlantic Ocean), and only moments
> before it had no problem downloading the two large bcp files.
Looking at the FTP server logs, I see errors about 120 second inactivity
timeouts. So maybe I am having some network congestion/problems.
Jeff
|||I would try to zip up the snapshot files and then have the subscriber pull
them through ftp. Once they arrive at the subscriber, expand them, create a
pull subscription and then when you get to the specify snapshot location
select specify directory and browse to the location of the snapshot files.
The path will have to be of the form repldata\unc\pubname_subname\date
(IIRC).
"Jeff Hedlund" <jeff.hedlund_NOSPAM_@._NOSPAM_elsym.com> wrote in message
news:hGG3d.1861$DY.760@.chiapp18.algx.net...
> Jeff Hedlund wrote:
> Looking at the FTP server logs, I see errors about 120 second inactivity
> timeouts. So maybe I am having some network congestion/problems.
> Jeff
|||I can use this procedure to manually apply the snapshot But using PUSH
subscriptions? what will be the procedure?
Thanks.
"Hilary Cotter" wrote:
> I would try to zip up the snapshot files and then have the subscriber pull
> them through ftp. Once they arrive at the subscriber, expand them, create a
> pull subscription and then when you get to the specify snapshot location
> select specify directory and browse to the location of the snapshot files.
> The path will have to be of the form repldata\unc\pubname_subname\date
> (IIRC).
>
> "Jeff Hedlund" <jeff.hedlund_NOSPAM_@._NOSPAM_elsym.com> wrote in message
> news:hGG3d.1861$DY.760@.chiapp18.algx.net...
>
>
|||the original poster was using FTP. If you are doing a push you can still
compress your snapshot. If your snapshot is very large you should think
about doing no sync subscriptions, and manually get the data over to the
subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Adrian320" <Adrian320@.discussions.microsoft.com> wrote in message
news:641144BB-058C-4202-B790-ABCE3BC2D18B@.microsoft.com...[vbcol=seagreen]
> I can use this procedure to manually apply the snapshot But using PUSH
> subscriptions? what will be the procedure?
> Thanks.
> "Hilary Cotter" wrote:
pull[vbcol=seagreen]
create a[vbcol=seagreen]
files.[vbcol=seagreen]
time[vbcol=seagreen]
(though[vbcol=seagreen]
had[vbcol=seagreen]
inactivity[vbcol=seagreen]
|||Ok, so when you do it with FTP you can specify the location, ok.
One more question I have the .bcp files on the subscriber computer. But I
don't know how to run a script to restore that table ('article') to the
subscriber database to then enable the nosync subscription.
Is there a link that you could point me to learn about this? Or can I use
the Restore Database command?
Thanks.
"Hilary Cotter" wrote:
> the original poster was using FTP. If you are doing a push you can still
> compress your snapshot. If your snapshot is very large you should think
> about doing no sync subscriptions, and manually get the data over to the
> subscriber.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Adrian320" <Adrian320@.discussions.microsoft.com> wrote in message
> news:641144BB-058C-4202-B790-ABCE3BC2D18B@.microsoft.com...
> pull
> create a
> files.
> time
> (though
> had
> inactivity
>
>
Replication over FTP
Hey guys.
Really stuck in a jam on this one.
SQL subscriber is behind a firewall, so FTP snapshots don't work. From
behind the firewall you can only connect to an external FTP server and
transfer data via FTP Passive mode. I can't find anywhere a setting for SQL
2000 to use passive mode.
Does anyone at all know if SQL 2000 does not support passive mode in any way?
Does anyone know a solution for this problem. We don't want to do VPN
because then we have to give each location direct access to our network. Is
there an option I'm perhaps missing here?
Thanks in advance.
Take care.
You could write a script that connects with an FTP server download the
required snapshot files, and then have SQL Server apply the snapshot from
an alternate location. You will see this option when you pull your
subscription.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
|||Could I get an explination for what the snapshots are for?
I believe their only for initialization correct? Not the actual replication
merging, that happens on the 1433 port connection?
If so..
Can I, when setting up the database, export all objects to the server. Then
create the subscription, and I've tried to take the initialization step off
in the wizard, saying "subscriber already has schema" etc.
Then when I try and start the pull subscription I get this history in the
job history:
Bulk copying data into table 'sysmergesubsetfilters'
Message: The process could not deliver the snapshot to the Subscriber.
Message: The process could not bulk copy into table
'"dbo"."MSmerge_contents"'.
Message: Unable to open BCP host data-file
The process could not deliver the snapshot to the Subscriber. The step
failed.
Is my idea possible? Not use any snapshots if the server can already be
populated with the data via export?
Thanks for all the help!
"Hilary Cotter" wrote:
> You could write a script that connects with an FTP server download the
> required snapshot files, and then have SQL Server apply the snapshot from
> an alternate location. You will see this option when you pull your
> subscription.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
|||the snapshots are to create the schema on the subscriber and push the data
there. It also contains replication metadata which is normally used for
tracking. So, yes, it is for initialization. All replication activity takes
place over port 1433 except for the application of the snapshot which is
pulled or pushed through a unc or ftp port.
Unfortunately with merge replication even a no-sync snapshot requires a mini
snapshot to be deployed and applied on the subscriber. Transactional
replication uses pure sql statements to create the metadata tables and
populate them with the required data.
"Synced" <Synced@.discussions.microsoft.com> wrote in message
news:1C666DCB-2FEF-4A5E-B142-0DC52A3AE75B@.microsoft.com...[vbcol=seagreen]
> Could I get an explination for what the snapshots are for?
> I believe their only for initialization correct? Not the actual
> replication
> merging, that happens on the 1433 port connection?
> If so..
> Can I, when setting up the database, export all objects to the server.
> Then
> create the subscription, and I've tried to take the initialization step
> off
> in the wizard, saying "subscriber already has schema" etc.
> Then when I try and start the pull subscription I get this history in the
> job history:
> Bulk copying data into table 'sysmergesubsetfilters'
> Message: The process could not deliver the snapshot to the Subscriber.
> Message: The process could not bulk copy into table
> '"dbo"."MSmerge_contents"'.
> Message: Unable to open BCP host data-file
> The process could not deliver the snapshot to the Subscriber. The step
> failed.
> Is my idea possible? Not use any snapshots if the server can already be
> populated with the data via export?
> Thanks for all the help!
> "Hilary Cotter" wrote:
|||Hey Hilary,
Thanks a ton for the reply. So again I hit another bind.
Let me explain my situation. My publisher, when setting up the replication,
will have no data. The subscriber, a pull merge subscription, has all the
data/schema.
Reason for this is, subscribers can't open listening ports on firewalls, so
I'm making them the "puller".
This is all working now with your guidance, the only problem is, when the
subscriber applies snapshots in a manual location just to get the merge
going, because you said its required, it wipes out all the data on the
subscriber applying the snapshots.
What I really want is for the subscriber to push all the data to the
publisher, and from then on they merge continiously.
Is this possible? Or do I have to export all data to the publisher, make
snapshots, and then the subscriber basically because of the snapshots wipes
out its own data, and re-applies it through the snapshot?
Thanks for all the help.
"Hilary Cotter" wrote:
> the snapshots are to create the schema on the subscriber and push the data
> there. It also contains replication metadata which is normally used for
> tracking. So, yes, it is for initialization. All replication activity takes
> place over port 1433 except for the application of the snapshot which is
> pulled or pushed through a unc or ftp port.
> Unfortunately with merge replication even a no-sync snapshot requires a mini
> snapshot to be deployed and applied on the subscriber. Transactional
> replication uses pure sql statements to create the metadata tables and
> populate them with the required data.
> "Synced" <Synced@.discussions.microsoft.com> wrote in message
> news:1C666DCB-2FEF-4A5E-B142-0DC52A3AE75B@.microsoft.com...
>
>
Really stuck in a jam on this one.
SQL subscriber is behind a firewall, so FTP snapshots don't work. From
behind the firewall you can only connect to an external FTP server and
transfer data via FTP Passive mode. I can't find anywhere a setting for SQL
2000 to use passive mode.
Does anyone at all know if SQL 2000 does not support passive mode in any way?
Does anyone know a solution for this problem. We don't want to do VPN
because then we have to give each location direct access to our network. Is
there an option I'm perhaps missing here?
Thanks in advance.
Take care.
You could write a script that connects with an FTP server download the
required snapshot files, and then have SQL Server apply the snapshot from
an alternate location. You will see this option when you pull your
subscription.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
|||Could I get an explination for what the snapshots are for?
I believe their only for initialization correct? Not the actual replication
merging, that happens on the 1433 port connection?
If so..
Can I, when setting up the database, export all objects to the server. Then
create the subscription, and I've tried to take the initialization step off
in the wizard, saying "subscriber already has schema" etc.
Then when I try and start the pull subscription I get this history in the
job history:
Bulk copying data into table 'sysmergesubsetfilters'
Message: The process could not deliver the snapshot to the Subscriber.
Message: The process could not bulk copy into table
'"dbo"."MSmerge_contents"'.
Message: Unable to open BCP host data-file
The process could not deliver the snapshot to the Subscriber. The step
failed.
Is my idea possible? Not use any snapshots if the server can already be
populated with the data via export?
Thanks for all the help!
"Hilary Cotter" wrote:
> You could write a script that connects with an FTP server download the
> required snapshot files, and then have SQL Server apply the snapshot from
> an alternate location. You will see this option when you pull your
> subscription.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
|||the snapshots are to create the schema on the subscriber and push the data
there. It also contains replication metadata which is normally used for
tracking. So, yes, it is for initialization. All replication activity takes
place over port 1433 except for the application of the snapshot which is
pulled or pushed through a unc or ftp port.
Unfortunately with merge replication even a no-sync snapshot requires a mini
snapshot to be deployed and applied on the subscriber. Transactional
replication uses pure sql statements to create the metadata tables and
populate them with the required data.
"Synced" <Synced@.discussions.microsoft.com> wrote in message
news:1C666DCB-2FEF-4A5E-B142-0DC52A3AE75B@.microsoft.com...[vbcol=seagreen]
> Could I get an explination for what the snapshots are for?
> I believe their only for initialization correct? Not the actual
> replication
> merging, that happens on the 1433 port connection?
> If so..
> Can I, when setting up the database, export all objects to the server.
> Then
> create the subscription, and I've tried to take the initialization step
> off
> in the wizard, saying "subscriber already has schema" etc.
> Then when I try and start the pull subscription I get this history in the
> job history:
> Bulk copying data into table 'sysmergesubsetfilters'
> Message: The process could not deliver the snapshot to the Subscriber.
> Message: The process could not bulk copy into table
> '"dbo"."MSmerge_contents"'.
> Message: Unable to open BCP host data-file
> The process could not deliver the snapshot to the Subscriber. The step
> failed.
> Is my idea possible? Not use any snapshots if the server can already be
> populated with the data via export?
> Thanks for all the help!
> "Hilary Cotter" wrote:
|||Hey Hilary,
Thanks a ton for the reply. So again I hit another bind.
Let me explain my situation. My publisher, when setting up the replication,
will have no data. The subscriber, a pull merge subscription, has all the
data/schema.
Reason for this is, subscribers can't open listening ports on firewalls, so
I'm making them the "puller".
This is all working now with your guidance, the only problem is, when the
subscriber applies snapshots in a manual location just to get the merge
going, because you said its required, it wipes out all the data on the
subscriber applying the snapshots.
What I really want is for the subscriber to push all the data to the
publisher, and from then on they merge continiously.
Is this possible? Or do I have to export all data to the publisher, make
snapshots, and then the subscriber basically because of the snapshots wipes
out its own data, and re-applies it through the snapshot?
Thanks for all the help.
"Hilary Cotter" wrote:
> the snapshots are to create the schema on the subscriber and push the data
> there. It also contains replication metadata which is normally used for
> tracking. So, yes, it is for initialization. All replication activity takes
> place over port 1433 except for the application of the snapshot which is
> pulled or pushed through a unc or ftp port.
> Unfortunately with merge replication even a no-sync snapshot requires a mini
> snapshot to be deployed and applied on the subscriber. Transactional
> replication uses pure sql statements to create the metadata tables and
> populate them with the required data.
> "Synced" <Synced@.discussions.microsoft.com> wrote in message
> news:1C666DCB-2FEF-4A5E-B142-0DC52A3AE75B@.microsoft.com...
>
>
Labels:
database,
firewall,
frombehind,
ftp,
guys,
jam,
microsoft,
mysql,
oracle,
replication,
server,
snapshots,
sql,
stuck,
subscriber
Subscribe to:
Posts (Atom)