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

CraigI 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.|||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|||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.|||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

public role problem

Hi all
I had a database that was copied over from one server to another say SERVER1
to SERVER2. As always it happens that the permissions too get carried over
with the database. I was able to remove the carried over logins from all the
roles but not from public. So now I am not able to create the same login on
SERVER2 as on SERVER1 since it tells me that the login is already existing
in the database as a role.
I do understand that no members can be removed from public.. but is there
someway I can bypass this to create the same logins on SERVER2 as on SERVER1
' Or is there something wrong that I am doing while copying the files..
should I be taking some other precautions when copying over.. '
Any help is appreciated.. Thank you
NEVILLEDropping the login on server level or the user on db level will remove the
membership of the login/user in public.
Have a look at http://support.microsoft.com/view/tn.asp?kb=246133
hth
Quentin
"Neville" <george@.synygy.com> wrote in message
news:uD5lkGT2DHA.2528@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi all
> I had a database that was copied over from one server to another say

---

SERVER1

quote:

---

> to SERVER2. As always it happens that the permissions too get carried over
> with the database. I was able to remove the carried over logins from all

---

the

quote:

---

> roles but not from public. So now I am not able to create the same login

---

on

quote:

---

> SERVER2 as on SERVER1 since it tells me that the login is already existing
> in the database as a role.
> I do understand that no members can be removed from public.. but is there
> someway I can bypass this to create the same logins on SERVER2 as on

---

SERVER1

quote:

---

> ' Or is there something wrong that I am doing while copying the files..
> should I be taking some other precautions when copying over.. '
> Any help is appreciated.. Thank you
> NEVILLE
>

---

|||Sorry for not specifying something that I just noticed.. I do not see the
carried over logins in the users list of the database through Enterprise
Manager.. However, querying the database shows that the users exist.. I
tried refreshing the screen, reconnecting to the server and a lot of other
things but it still does not show me the users through Enterprise manager.
Funny though, one of my colleagues connected to the server from his desktop
with the same login and was able to see all the carried over logins.. which
rounds off to one thing that the problem should be with something on my
desktop.. However, I did not find any settings different from that on his
desktop..
And btw thanks for the help with the link..
NEVILLE
"Quentin Ran" <ab@.who.com> wrote in message
news:eOVpnlT2DHA.3436@.tk2msftngp13.phx.gbl...

quote:

---

> Dropping the login on server level or the user on db level will remove the
> membership of the login/user in public.
> Have a look at http://support.microsoft.com/view/tn.asp?kb=246133
> hth
> Quentin
>
> "Neville" <george@.synygy.com> wrote in message
> news:uD5lkGT2DHA.2528@.TK2MSFTNGP10.phx.gbl...
> SERVER1
over[QUOTE]
> the
> on
existing[QUOTE]
there[QUOTE]
> SERVER1
>

---