Replication over the Internet - Resolving server name

We are using anonymouse pull subscriptions over the internet. We are not
using VPN.
The physical SQL Server is called SQL22Server. We have a Domain assigned to
it called something like company.com.
However, I found that on the client machine, I had to put in a hosts entry
to route SQL22Server to the IP address. I was hoping I could just use the
domain name.
This creates an administrative overhead because we have to do add a host
entry during the client install instead of having just assigning value to the
merge object.
If I try the domain, the error I get is: The remote server does not exist or
has not been designated as a valid Publisher.
I created the replication using the SQL22Server name.
Thanks,
Buzz
use the activeX scripts to deploy your subscriptions. In it there are
PublisherNetwork, DistributorNetwork, PublisherAddress, and
DistributorAddress properties. These map to the entries you would otherwise
put in your CNU or hosts file to make this work.
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
"Buzz" <buzz@.online.nospam> wrote in message
news:2C0E041F-5DA1-4DAB-B747-E52E2196395D@.microsoft.com...
> We are using anonymouse pull subscriptions over the internet. We are not
> using VPN.
> The physical SQL Server is called SQL22Server. We have a Domain assigned
to
> it called something like company.com.
> However, I found that on the client machine, I had to put in a hosts entry
> to route SQL22Server to the IP address. I was hoping I could just use the
> domain name.
> This creates an administrative overhead because we have to do add a host
> entry during the client install instead of having just assigning value to
the
> merge object.
> If I try the domain, the error I get is: The remote server does not exist
or
> has not been designated as a valid Publisher.
> I created the replication using the SQL22Server name.
> Thanks,
> Buzz
|||Thanks for the quick reply.
I am publishing what I found for the newsgroup.
For us, the publisher and the distributer are the same machine. We are using
FTP for the initial snapshot. We are using SQL Server 2000 SP4 on the server
and MSDE SP4 on the laptop subscribers.
I found I did not need the distributer values because my distributer and
publisher where the same machine. I will most likely set them anyway so that
someday in production I can have a different distributer.
Here are the subscriber properties of the Merge object:
.Distributor = "SQL22Server"
.DistributorSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.DistributorAddress = "company.com"
.DistributorNetwork = NETWORK_TYPE.TCPIP_SOCKETS
.DistributorLogin = "login"
.DistributorPassword = "password"
.Publisher = "SQL22Server"
.PublisherAddress = "company.com"
.PublisherDatabase = "data"
.PublisherNetwork = NETWORK_TYPE.TCPIP_SOCKETS
.Publication = "data"
.PublisherSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.PublisherLogin = "login"
.PublisherPassword = "password"
The one last thing to make sure is that when setting up the FTP on the
server, use the PublisherAddress value. So "company.com" so that the
subscriber hits ftp://company.com/.
Also if you do this in production be careful the change to the FTP address
causes the publication to force a recreation of the initial snapshot.
Thanks,
Buzz
"Hilary Cotter" wrote:

> use the activeX scripts to deploy your subscriptions. In it there are
> PublisherNetwork, DistributorNetwork, PublisherAddress, and
> DistributorAddress properties. These map to the entries you would otherwise
> put in your CNU or hosts file to make this work.
> --
> 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
> "Buzz" <buzz@.online.nospam> wrote in message
> news:2C0E041F-5DA1-4DAB-B747-E52E2196395D@.microsoft.com...
> to
> the
> or
>
>
|||Hi Buzz,
It looks more than a network issue.
- Could you PING SQL22Server from client and get the response?
- Could you telnet SQL22Server from client use the command like below
telnet <SQL22Server Domain Name>: 1433
- Do you have firewall between server and client? Have you open the TCP
port for them?
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Replication monitor

Is there anyway to clear replication monitor from subscriptions with
error status?
Tomek
The replication monitor gets its info from
tempdb.dbo.MSreplication_agent_status and running
sp_MSload_replication_status refreshes this table. As restarting the SQL
Server Service causes the tempdb to be recreated from the model database,
this also removes the problem.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Replication Job Cleanup

Over the past couple of years, I have added & removed publications &
subscriptions, usung the wizards the come with SQL 2000. Recently we
had a problem with the dBs getting backed up and when I peered into the
SQL Server Agent->Jobs folder, there were many many more jobs than I
had publications & subscriptions, in fact some for tables that no
longer existed.
So my question is this. Is there a SP somewhere that will clean up all
the 'old' stuff without messing up all the stuff that is currently
working.
AHIA,
Larry...
No. For the most part these objects consume very little space and
replication won't reuse them or get confused by their existence.
This being said you can query sysmergearticles and get an idea of what is
currently in use on your publisher/subscriber. Again I would be adverse to
touching a production system in case I whack something - unless of course I
am a consultant as this might mean considerable overtime for me when things
go south.
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
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1130949038.398739.282220@.g44g2000cwa.googlegr oups.com...
> Over the past couple of years, I have added & removed publications &
> subscriptions, usung the wizards the come with SQL 2000. Recently we
> had a problem with the dBs getting backed up and when I peered into the
> SQL Server Agent->Jobs folder, there were many many more jobs than I
> had publications & subscriptions, in fact some for tables that no
> longer existed.
> So my question is this. Is there a SP somewhere that will clean up all
> the 'old' stuff without messing up all the stuff that is currently
> working.
> AHIA,
> Larry...
>
|||Hillary,
What are you saying? What are you implying? Would a consultant
really do that? Who me? Never!!!!
As always, thanks for your impartial advice!!!
Larry...

Replication is failing due to use of FTS on my subscriber, what can I do?

Hi Daniel,
If you are using push subscriptions from a SQL2000 distributor, you are
essentially using the SQL2000 replication components which, I am sorry to
say, are ill-equipped to handle things like full-text indexes during
snapshot processing. This also means that upgrading the subscriber to
SQL2005 SP2 will probably not do you any good. Alternatively, you can use a
SQL2005 SP2 instance as the distributor of your SQL2000 publisher so you can
enable the FulltextIndex (0x0000000001000000) article schema option via
sp_add|changearticle (both SQL2000 and SQL2005 GUI will not allow you to
specify that for a SQL2000 publisher) and let the SQL2005 replication
components handle the full-text index dependencies for you (ideally using
"drop" as the article pre-creation commands). That said, I can imagine that
moving the distributor can involve a substantial amount of work.
Hope that helps,
-Raymond
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:uAOtEPAjHHA.1272@.TK2MSFTNGP04.phx.gbl...
> I'm in the middle of setting up a non-updating transactional replication
> from SQL Server 2000 SP3 (can't put SP4 on as the main application we use
> hasn't been certified for SP4 yet) to SQL Server 2005 SP1 (I could put SP2
> on if it will fix the issue, but it would mean taking our e-commerce sites
> down while doing so and I'd need to schedule a maintenance window for
> this). At first it was working fine, but snapshots were set to delete the
> tables and so we'd lose the FTS setups. I've now changed the snapshots to
> not do this, and now the replications fails with the error:
> Cannot drop index 'PK_Product' because it enforces the full-text key for
> table or indexed view 'Product'.
> This occurs with both "Delete data. If article has a row filter, delete
> only data that matches the filter.", "Truncate all data in the existing
> object" (which is the preferred option), and "Keep existing object
> unchanged". It also doesn't matter if I have the clustered and/or
> nonclustered index copy set to true or false (the PK is the clustered
> index on this table).
> This is a push subscription, with the distribution agent running on the
> publisher.
> Any ideas how I can retain the table structure (so keeping the FTS index
> and not having to rebuild it each time the snapshot is applied) and have
> transaction replication working? Or is it not possible?
> Dan
>
Hi Daniel,
I probably don't understand all the details about your scenario but given
that applying a snapshot typically replaces most of the data at the
subscriber, I would think that a FTS rebuild is likely required (or at least
a good idea) after the snapshot has been applied. That said, we no longer
drop the primary key\unique constraints (which is an arguably bad fix for
some other issue) in a "mostly" SQL2005 environment (SQL2005 snapshot and
distribution agent) if the article pre-creation is 'delete'. However,
leaving any unique constraints\indexes (especially non-clustered) while the
snapshot is being applied runs the risk of slowing down the snapshot
delivery process significantly (no minimal logging) and may prevent a
concurrent snapshot from being delivered successfully since bcp data
generated for a concurrent snapshot can violate uniqueness constraints
(compensated later on during the reconciliation phase).
-Raymond
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:eLx1McKjHHA.4904@.TK2MSFTNGP05.phx.gbl...
> Raymond wrote on Tue, 1 May 2007 09:54:35 -0700:
>
> It seems strange that all options require the PK to be dropped - I would
> have thought that the options to delete or truncate data in the
> destination table would leave the PK untouched (after all, it shouldn't be
> touching the schema). I don't have an FTS index on the SQL Server 2000
> source tables - the FTS is handled solely on the subscriber, so I'm not
> trying to replicate the FTS catalog, just the data in the tables so that
> the change tracking on the subscriber handles all the FTS work.
> Dan
>
|||Hi Daniel,
I probably don't understand all the details about your scenario but given
that applying a snapshot typically replaces most of the data at the
subscriber, I would think that a FTS rebuild is likely required (or at least
a good idea) after the snapshot has been applied. That said, we no longer
drop the primary key\unique constraints (which is an arguably bad fix for
some other issue) in a "mostly" SQL2005 environment (SQL2005 snapshot and
distribution agent) if the article pre-creation is 'delete'. However,
leaving any unique constraints\indexes (especially non-clustered) while the
snapshot is being applied runs the risk of slowing down the snapshot
delivery process significantly (no minimal logging) and may prevent a
concurrent snapshot from being delivered successfully since bcp data
generated for a concurrent snapshot can violate uniqueness constraints
(compensated later on during the reconciliation phase).
-Raymond
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:eLx1McKjHHA.4904@.TK2MSFTNGP05.phx.gbl...
> Raymond wrote on Tue, 1 May 2007 09:54:35 -0700:
>
> It seems strange that all options require the PK to be dropped - I would
> have thought that the options to delete or truncate data in the
> destination table would leave the PK untouched (after all, it shouldn't be
> touching the schema). I don't have an FTS index on the SQL Server 2000
> source tables - the FTS is handled solely on the subscriber, so I'm not
> trying to replicate the FTS catalog, just the data in the tables so that
> the change tracking on the subscriber handles all the FTS work.
> Dan
>