Replication to server with different Port

I have a SQL server running on a remote location that has a different port
enabled to connect other than the default one, which means that when I want
to register I have to specify the port number as well eg.
Server1\InstanceName,4001 .
I am trying to carry out merge replication between the server and the MSDE
on my box. I have set up the pubisher, registered the SQL Server from
Enterprise Manager on my machine and on setting up the subscriber I am able
to view the publication in the list of registered servers. Creating the
subscription works fine, but when I try to sync , I get the error 'The
process could not connect to Distributor 'Server1\InstanceName' . I feel that
this is because of the port since it is not mentioned in the message and SQL
is trying to use the default port. Is there some workaround for this?
Jax,
for a named instance, there is no default port - it is assigned at creation
from the list of available ports, and your port number won't necessarily be
the same as mine, for the first named instance. Usually the port number is
not specified in the replication definition, and it is dynamically picked up
(hence the slammer virus on UDP, port 1434 ). Do you have 1434 blocked? If
not, then please try designing without the port number and see if this
works.
Also, The SQL Server Agent service (SQLServerAgent) at the client should not
use the LocalSystem account. It needs to use a standard domain account.
Finally, please check that the agents use impersonation.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication Problems

We are thinking to replicate a 'READ ONLY' database to an
off-site location by using a third party utility (We will
try to replicate from the warm backup server where we
restore transaction logs every hour from the original prod
server). Here are the two facts:
1- We don't want any performance decrease from the
original production server to replicate directly from the
prod server.
2- The third party utility is a file replication utility
which can replicate the whole database, differences only
e.t.c.
The problem seems to be, when the transaction log restore
starts, it kicks out all other users (SINGLE_USER,
RESTRICTED_USER).
Is there a way to restore the Transaction log without
cutting all the connections (Especially replication) to
the read-only database '
Thanks for any feedback....> Is there a way to restore the Transaction log without
> cutting all the connections (Especially replication) to
> the read-only database '
I'm afraid not.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:b48d01c3ecfc$63946850$a101280a@.phx.gbl...
> We are thinking to replicate a 'READ ONLY' database to an
> off-site location by using a third party utility (We will
> try to replicate from the warm backup server where we
> restore transaction logs every hour from the original prod
> server). Here are the two facts:
> 1- We don't want any performance decrease from the
> original production server to replicate directly from the
> prod server.
> 2- The third party utility is a file replication utility
> which can replicate the whole database, differences only
> e.t.c.
> The problem seems to be, when the transaction log restore
> starts, it kicks out all other users (SINGLE_USER,
> RESTRICTED_USER).
> Is there a way to restore the Transaction log without
> cutting all the connections (Especially replication) to
> the read-only database '
> Thanks for any feedback....|||Change the schedule for the restore portion of transaction logshipping so th
at it doesn't conflict with the replication. Why are you using a third-part
y replication tool?sql

Replication Problems

We are thinking to replicate a 'READ ONLY' database to an
off-site location by using a third party utility (We will
try to replicate from the warm backup server where we
restore transaction logs every hour from the original prod
server). Here are the two facts:
1- We don't want any performance decrease from the
original production server to replicate directly from the
prod server.
2- The third party utility is a file replication utility
which can replicate the whole database, differences only
e.t.c.
The problem seems to be, when the transaction log restore
starts, it kicks out all other users (SINGLE_USER,
RESTRICTED_USER).
Is there a way to restore the Transaction log without
cutting all the connections (Especially replication) to
the read-only database '
Thanks for any feedback....> Is there a way to restore the Transaction log without
> cutting all the connections (Especially replication) to
> the read-only database '
I'm afraid not.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:b48d01c3ecfc$63946850$a101280a@.phx.gbl...
> We are thinking to replicate a 'READ ONLY' database to an
> off-site location by using a third party utility (We will
> try to replicate from the warm backup server where we
> restore transaction logs every hour from the original prod
> server). Here are the two facts:
> 1- We don't want any performance decrease from the
> original production server to replicate directly from the
> prod server.
> 2- The third party utility is a file replication utility
> which can replicate the whole database, differences only
> e.t.c.
> The problem seems to be, when the transaction log restore
> starts, it kicks out all other users (SINGLE_USER,
> RESTRICTED_USER).
> Is there a way to restore the Transaction log without
> cutting all the connections (Especially replication) to
> the read-only database '
> Thanks for any feedback....|||Change the schedule for the restore portion of transaction logshipping so that it doesn't conflict with the replication. Why are you using a third-party replication tool?

Replication Problem

Hi

I have two location and 1 centralised server.
while doing transactional replication first the data from the 1st server got replicated in the central server , while doing with the second location , the data in the central server got overwrited and now the replication from the first location is not happening.Only the replication from the second location is happening.

Please tell me how to do the replication with two location and a centralised server.

Regards
Aneesh

If you want central server always download changes to two clients, you can just set transactional replication on central server and choose two clients as subscribers. But if you have changes on client server, which you want to upload onto central servers as well, you can set merge replication on central server and two clients as subscribers. As for merge replication, if there are conflicts from central server and clients, you need to set conflicts resolver to make sure the data won as what you expected.

Hope it will help.

Thanks

Yunjing

|||

Hi Yunjing,

thanks for your reply.

I dont want central server always download changes to two clients, instead i have to upload the date from the location1 and location2 to the central server. I mean publishers in location1 and location2 and i have a subsciber in central server.

I want to replicate the data from the location1 and location2 servers to the central server.

Replication Over Internet via FTP

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)

replication over a wan

Howdy kids. We are moving our servers to a new location here shortly. One of
the servers is a replicated reporting box. There is an internal ongoing
debate on if we should move this box or not. I have a 25 gig database. The
two buildings are about 40 miles apart from each other. The replication
configuration is as follows:
Transactional.
Remote Distributor. (on the Subscriber)
NOT immediate updating.
Are there any issues with this type of configuration over a WAN? Any gotchas
that wouldn't be had on a local network that could happen here?
SQL2K SP3
TIA, ChrisR
If your subscriber (and consequently your distributor) goes down your tlog
on your publisher will balloon like a balloon. This ballooning tlog will
cause performance problems on the publisher, and can eat up all your space
on the publisher. I would advise you to use a local distributor - preferably
a distributor on the same box as the publisher.
Also you have to check your effective bandwidth of your WAN. Low bandwidth
can cause log reader performance problems leading to ballooning tlogs.
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
"ChrisR" <bla@.noemail.com> wrote in message
news:uwfPwhiCFHA.328@.tk2msftngp13.phx.gbl...
> Howdy kids. We are moving our servers to a new location here shortly. One
of
> the servers is a replicated reporting box. There is an internal ongoing
> debate on if we should move this box or not. I have a 25 gig database. The
> two buildings are about 40 miles apart from each other. The replication
> configuration is as follows:
> Transactional.
> Remote Distributor. (on the Subscriber)
> NOT immediate updating.
> Are there any issues with this type of configuration over a WAN? Any
gotchas
> that wouldn't be had on a local network that could happen here?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||Thanks Hillary. If I switch to a Local Distributor, should I switch from a
Pull to Push Subscription as well? It would make sense to me, what do you
think?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e#XCZQjCFHA.3936@.TK2MSFTNGP09.phx.gbl...
> If your subscriber (and consequently your distributor) goes down your tlog
> on your publisher will balloon like a balloon. This ballooning tlog will
> cause performance problems on the publisher, and can eat up all your space
> on the publisher. I would advise you to use a local distributor -
preferably[vbcol=seagreen]
> a distributor on the same box as the publisher.
> Also you have to check your effective bandwidth of your WAN. Low bandwidth
> can cause log reader performance problems leading to ballooning tlogs.
> --
> 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
> "ChrisR" <bla@.noemail.com> wrote in message
> news:uwfPwhiCFHA.328@.tk2msftngp13.phx.gbl...
One[vbcol=seagreen]
> of
The
> gotchas
>
|||Your choice of push or pull is irrelevant in this case.
Use push when you have small numbers of subscribers, you want a central
point of administration on your publisher, and your subscribers are normally
online and well connected.
Use pull when you have large numbers of subscribers, you don't care about a
central point of administration because you are using replication monitor
group, and your subscribers are not always connected.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <bla@.noemail.com> wrote in message
news:%23PqR59jCFHA.4052@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Thanks Hillary. If I switch to a Local Distributor, should I switch from a
> Pull to Push Subscription as well? It would make sense to me, what do you
> think?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e#XCZQjCFHA.3936@.TK2MSFTNGP09.phx.gbl...
tlog[vbcol=seagreen]
space[vbcol=seagreen]
> preferably
bandwidth[vbcol=seagreen]
> One
ongoing[vbcol=seagreen]
> The
replication
>

replication is completed

We have a server that is replicating information back and forth to 10
different location. My question is how do i know if a location is done
replicating to us. my scenario is that we have 10 stores, each stores as it
makes a sales, the information is replicated back to corporate. Corporate
in turn replicate that information back to the other nine stores. How can i
tell if a store is done downloading information to corporate verse corporate
still trying to push data to the store.
tx in advance.
That depends. Is there ever a point when no transactions are occuring on the
stores? If so you can schedule the agents and after the final job has
completed the central location will have all the data.
Another option is to run sp_browsereplcmds on the publisher and subscriber
until no rows are returned.
You could also query select delivered_transactions from
MSdistribution_history in the distribution database and wait for a value of
delivered_transactions to be 0.
For merge you might want to query select delivery_rate from
MSmerge_history
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"tlee" <tlee@.lang.com> wrote in message
news:OVTyEXXsEHA.904@.TK2MSFTNGP11.phx.gbl...
> We have a server that is replicating information back and forth to 10
> different location. My question is how do i know if a location is done
> replicating to us. my scenario is that we have 10 stores, each stores as
it
> makes a sales, the information is replicated back to corporate. Corporate
> in turn replicate that information back to the other nine stores. How can
i
> tell if a store is done downloading information to corporate verse
corporate
> still trying to push data to the store.
> tx in advance.
>
|||Thanks, i will give it a try.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23C$IATesEHA.3320@.TK2MSFTNGP15.phx.gbl...
> That depends. Is there ever a point when no transactions are occuring on
> the
> stores? If so you can schedule the agents and after the final job has
> completed the central location will have all the data.
> Another option is to run sp_browsereplcmds on the publisher and subscriber
> until no rows are returned.
> You could also query select delivered_transactions from
> MSdistribution_history in the distribution database and wait for a value
> of
> delivered_transactions to be 0.
> For merge you might want to query select delivery_rate from
> MSmerge_history
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "tlee" <tlee@.lang.com> wrote in message
> news:OVTyEXXsEHA.904@.TK2MSFTNGP11.phx.gbl...
> it
> i
> corporate
>

Replication in LAN

Can Replication works in share network, I mean if i make a client as
publisher and server as distributer and subscriber willbe on remote location,
then will it work? if yes then how will i define the subscriber?
Manish,
if the networks are trusted then it is very much as per usual. if not, then
this article should help:
http://www.replicationanswers.com/InternetArticle.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)