Replication through firewall

Hi,
Can anyone tell me whether replication can help me solve a problem
when my publisher has a very tight firewall? Specifically, they
absolutely will not enable ports, MS Proxy Server, or VPN. They are
willing to periodically forward a file through FTP, but there is no
way they will allow logins to their SQL Server. The total data set we
want to replicate is too large for snapshot replication, so I'm
thinking of using transactional replication. Could I set the protected
server up as a publisher with a local distributor but no
subscribers... I'm thinking I could at least capture the
logs/transactions from the distributor to a data file that they would
then ftp to me on their regular schedule. Is there a better way? I'm
hoping to use as much of the replication technology as reuse resources
rather than build from scratch.
Thanks for any advice,
Melissa
What about a Pull Subscription where they pull the data from you? I could be
reading your post wrong but I think that would work.
"Melissa" <shoesmith@.cox.net> wrote in message
news:8e0521f3.0404230709.515a5447@.posting.google.c om...
> Hi,
> Can anyone tell me whether replication can help me solve a problem
> when my publisher has a very tight firewall? Specifically, they
> absolutely will not enable ports, MS Proxy Server, or VPN. They are
> willing to periodically forward a file through FTP, but there is no
> way they will allow logins to their SQL Server. The total data set we
> want to replicate is too large for snapshot replication, so I'm
> thinking of using transactional replication. Could I set the protected
> server up as a publisher with a local distributor but no
> subscribers... I'm thinking I could at least capture the
> logs/transactions from the distributor to a data file that they would
> then ftp to me on their regular schedule. Is there a better way? I'm
> hoping to use as much of the replication technology as reuse resources
> rather than build from scratch.
> Thanks for any advice,
> Melissa
|||you can configure SQL Server to listen on another port other than 1433 by
modifying the ServerPortNumber. If your firewall admins won't allow this
there is little you can do.
"Melissa" <shoesmith@.cox.net> wrote in message
news:8e0521f3.0404230709.515a5447@.posting.google.c om...
> Hi,
> Can anyone tell me whether replication can help me solve a problem
> when my publisher has a very tight firewall? Specifically, they
> absolutely will not enable ports, MS Proxy Server, or VPN. They are
> willing to periodically forward a file through FTP, but there is no
> way they will allow logins to their SQL Server. The total data set we
> want to replicate is too large for snapshot replication, so I'm
> thinking of using transactional replication. Could I set the protected
> server up as a publisher with a local distributor but no
> subscribers... I'm thinking I could at least capture the
> logs/transactions from the distributor to a data file that they would
> then ftp to me on their regular schedule. Is there a better way? I'm
> hoping to use as much of the replication technology as reuse resources
> rather than build from scratch.
> Thanks for any advice,
> Melissa
|||Hi,
The administrator is not willing to open any port including 1433. They
are only willing to send data out periodically over ftp.
Answering the other reply, since they produce the data, I'm not sure
how a pull subscription from them would help solve the problem of
getting the data to me.
The fundamental problem is that I can't log into that SQL Server
database from outside their firewall. I'm trying to move transactions
and their application to a subscriber to outside the database. If I
set up a publisher and distributor inside that firewall, they would
produce some sort of log or dataset of the transactions. If so, where
is that? Could I have a script access that transaction history and
write that out to a file? I could then script sending it over ftp as
the administrator is willing to do. When I get it to my side, could I
insert it into a distributor-like database and subscribe from that? Or
maybe just apply the data directly from the datafile to the
"subscriber"? Is any or all of this possible?
Thanks,
Melissa
|||you can export the msrepl_commands table to the subscriber via some process,
perhaps ado.
Make sure you don't store your parameters for your publication in binary
format. To do this right click on your publication, go to the articles tab,
highlight each article, click on the three dots to the right of each
article, and then commands section uncheck the send parameters in binary
format.
Then make sure you set your distribution cleanup agent to run once a day.
You can convert the msrepl_commands table to meaningful information by doing
this
select convert(nchar(300),command ) from msrepl_commands
"Melissa" <shoesmith@.cox.net> wrote in message
news:8e0521f3.0404260551.4ce1ddbb@.posting.google.c om...
> Hi,
> The administrator is not willing to open any port including 1433. They
> are only willing to send data out periodically over ftp.
> Answering the other reply, since they produce the data, I'm not sure
> how a pull subscription from them would help solve the problem of
> getting the data to me.
> The fundamental problem is that I can't log into that SQL Server
> database from outside their firewall. I'm trying to move transactions
> and their application to a subscriber to outside the database. If I
> set up a publisher and distributor inside that firewall, they would
> produce some sort of log or dataset of the transactions. If so, where
> is that? Could I have a script access that transaction history and
> write that out to a file? I could then script sending it over ftp as
> the administrator is willing to do. When I get it to my side, could I
> insert it into a distributor-like database and subscribe from that? Or
> maybe just apply the data directly from the datafile to the
> "subscriber"? Is any or all of this possible?
> Thanks,
> Melissa
sql