Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    3

    Exclamation Unanswered: Pull subscription initialisation

    Hi there all

    I have a problem creating a pull merge subscription on a server that's outside of our firewall. All standard ports are blocked so MS UNC connections are not possible which SQL 2000 uses by default when creating a subscription. Equally FTP is out = insecure.

    I found a procedure to that said create the snapshot of the publication, create a backup of the published database, restore that backup to the remote server as a subscription db and then sync using merge. Didn't work, failed (several times).

    Can anyone enlighten me as to what I'm doing wrong or indeed if I'm doing anything correctly!

    I can create a remote desktop (Term Server) connection to the remote and another back to the publisher (from the subscriber's desktop) and both connections are using SSH tunnels. SQL Server uses a non standard port to communicate over the firewall. The remote server sits behind another firewall/router with port redirection to it's private address. Each server has the other registered and there are no comms problems and indeed there are other replicated dbs between them.

    Both servers are win 2003 and the remote (subscriber) is R2 version, both with SQL 2000 Server Std patched up to SP3. (if this has any bearing on the solution which I presume there is)

    Many thanks for listening and I hope a few of you can answer as well

    Craig

  2. #2
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    I have a problem creating a pull merge subscription on a server that's outside of our firewall. All standard ports are blocked
    Default SQL Server port is 1433. You need to ask your System Administrator to set appropriate access for MS SQL Server at both end.

    Check your server is enabled for subscription on publisher / distributor server.

    These are basic check system, could be other reasons but you need to start from bottom.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  3. #3
    Join Date
    Sep 2006
    Posts
    3
    Hi there

    Yep, I'm the SysAdmin, that's why I'm fielding the question. We purposefully changed the default port as a security measure. Duplication and general SQL functions work perfectly on the chosen port. Ports are blocked and changed because communication is over the internet. The problem is that when you launch an initialisation of a subscriber SQL uses the windows default port 445 for mapped drives, etc to copy down snapshot.pre and the other scripts. This port is is blocked, again this is a security measure.

    What I need to do is find a way to apply the snapshot (after copying it from the publisher) to the Subscriber without using a network or internetwork connection and then the subscriber can sync with the Publisher

    I hope this clarifies the problem

  4. #4
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    The problem is that when you launch an initialization of a subscriber SQL uses the windows default port 445 for mapped drives, etc to copy down snapshot.pre and the other scripts. This port is is blocked, again this is a security measure.
    As far as I knew, regarding firewall you can set particular port access for particular application in your firewall. Even you can configure particular IP access for port, but this all depend on your firewall configuration options.

    Without providing adequate access privilege, you could not expect any transaction on network.

    What I need to do is find a way to apply the snapshot (after copying it from the publisher) to the Subscriber without using a network or internetwork connection and then the subscriber can sync with the Publisher
    Get the copy of snapshot, dump it & set snapshot file location from subscription properties.

    But I feel it's not the correct way, manual setups could be disturb frequently.
    Last edited by rajeshpatel; 09-16-06 at 01:57.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  5. #5
    Join Date
    Sep 2006
    Posts
    3
    Hi Rajesh

    I've managed to do it and syncronisation is working. The process as I managed to do it was

    Create a subscription in the publisher
    create a backup of the subscription
    copy this backup to the subscriber
    restore the subscription over a previously created database making the DB larger enough that SQL doesn't have to grow the DB during initialisation
    copy the snapshot to a folder on the subscriber ensuring that the complete path below \\%publisher%\%replfolder%\ was as it exists on the publisher (because this appears to be encoded into the snapshot)
    Create the subscription on the subscriber
    Change the snapshot location on the subscriber to point to the local snapshot
    Launch sincronisation
    Change the subscriber snapshot location back to the publisher default (v. important)


    and it worked

    to test we added some data to the publication and sincronised from the subscriber because it's a pull sub.

    and then cleaned up ready to populate the database with it's full content

    We've create the subscription as a data-empty database because there's some 11 Gb of data to prepopulate the db for use and we'll sincronise later using a modified agent to cope with the longer sincronisation times (and not get timeout errors)

    The normally impossible was acheived

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •