Replication to a specific IP

If we had two SQL 2000 servers each with 2 NIC's. For each server one network card was pointing to an internal network 172.16.x.xxx we use for production. The other NIC (say its 198.16.x.xxx) we wanted to use just for replication purposes between the two servers in an attempt to lighten the network traffic on the 172 network. My question is, can you set up 2 SQL servers to replicate between themselves on a specific IP or Named Pipe?If both server is in different geographical area or on a WAN, you will face a big threat from anyone in the world who wrote a simple program to guess your user "sa" password or any user account. To post that on a WAN, its preferable to go through a firewall.

There are 2 options:-
1) get a 1gigabit network adapter card and a switch that support 1GB.
2) make use of Client Utility Connection on both SQL server. Configured according to your Server Name with static IP address and specify the port that is use by both server. This will tell the replication agents to call the static ip.

Originally posted by sdonovan
If we had two SQL 2000 servers each with 2 NIC's. For each server one network card was pointing to an internal network 172.16.x.xxx we use for production. The other NIC (say its 198.16.x.xxx) we wanted to use just for replication purposes between the two servers in an attempt to lighten the network traffic on the 172 network. My question is, can you set up 2 SQL servers to replicate between themselves on a specific IP or Named Pipe?

Replication through VPN successful??

In the office network domain I've already setup replication between two SQL
Servers( SQL11Server & SQL22Server). Now I am extending replication with SQL
servers in other non-trusted domains/workgroups (SQL33Server & SQL44Server).
SQL33Server & SQL44Server are in non-trusted domains connecting to the
office via VPN. SQL33Server can ping SQL11Server.mydomain.local fine,
but it cannot ping just SQL11Server. Is this going to be a problem if it
pings the full DNS address?
When I started MAKEPIPE on SQL11Server, from SQL33Server I have to type
READPIPE /Ssql11server.mydomain.local for it to work. If I type READPIPE
/Ssql11Server it doesn't work..
Do I have a NETBIOS resolution problem ' Or what is the problem and how can
I successful established the connections through VPN? What the steps I
must take to established a successful VPN replication?use a host file for this.
For instance your host file should contain this entry
123.345.567.678 SQL11Server
replacing the numbers with your IP address for SQL11Server
If a netbios name has a . in it, the resolution will be using DNS. It should
like your name resolution service (probably WINS) can figure out where
SQL111Server is.
"Joe Mine" <huytuanattpgdotcomdotau> wrote in message
news:utbkWlm%23DHA.2664@.TK2MSFTNGP09.phx.gbl...
> In the office network domain I've already setup replication between two
SQL
> Servers( SQL11Server & SQL22Server). Now I am extending replication with
SQL
> servers in other non-trusted domains/workgroups (SQL33Server &
SQL44Server).
> SQL33Server & SQL44Server are in non-trusted domains connecting to the
> office via VPN. SQL33Server can ping SQL11Server.mydomain.local fine,
> but it cannot ping just SQL11Server. Is this going to be a problem if it
> pings the full DNS address?
> When I started MAKEPIPE on SQL11Server, from SQL33Server I have to type
> READPIPE /Ssql11server.mydomain.local for it to work. If I type
READPIPE
> /Ssql11Server it doesn't work..
> Do I have a NETBIOS resolution problem ' Or what is the problem and how
can
> I successful established the connections through VPN? What the steps I
> must take to established a successful VPN replication?
>
>

Replication scenario question...Merge or Transactional?

The situation I am faced with is we have a web application supported by a SQL
2000(sp4) database that resides on a limited bandwidth network. Our
distributed users are constantly complaining of "slow" response times. Our
local users have no such complaints. Some of our leadership has suggested
sending a SQL server/IIS server to the remote location and using some type of
replication to synchronize the data between these boxes. The requirements
are for minimal latency and concurrent updating of data. The leadership also
want this solution to be completely automated (little or no supervision of
the replication process) and as with everything we do they want it right away
(we're talking days, not weeks). I am very new to replication and have read
through the BOL section and am in the process of reading Hillary Cotter's
book. I am leaning toward an implementation of Merge Replication but I am
unsure if this is the right solution. Any advice or informed opinions would
be greatly appreciated.
There is no concurrent replication option ie each solution will have a degree
of latency. If you use merge then you can select from a variety of conflict
resolvers and easily work offline. This might be your best option. There are
alternatives - queued updating subscribers, immediate updating subscribers
and bidirectional transactional replication. Do you have BLOBS in the table?
Are the subscribers always connected? Should they be able to continue if not
connected? These questions will clarify and narrow down the options a bit.
Whichever option you select, don't rush - you'll need time to configure it in
a test environment to establish a set of protocols (change management, error
handling...) and to simply verify that it all works for your situation.
HTH,
Paul Ibison
"Dave Stokes" wrote:

> The situation I am faced with is we have a web application supported by a SQL
> 2000(sp4) database that resides on a limited bandwidth network. Our
> distributed users are constantly complaining of "slow" response times. Our
> local users have no such complaints. Some of our leadership has suggested
> sending a SQL server/IIS server to the remote location and using some type of
> replication to synchronize the data between these boxes. The requirements
> are for minimal latency and concurrent updating of data. The leadership also
> want this solution to be completely automated (little or no supervision of
> the replication process) and as with everything we do they want it right away
> (we're talking days, not weeks). I am very new to replication and have read
> through the BOL section and am in the process of reading Hillary Cotter's
> book. I am leaning toward an implementation of Merge Replication but I am
> unsure if this is the right solution. Any advice or informed opinions would
> be greatly appreciated.

Replication Problem

High I hope someone can help me.

I'm trying to set-up replication between us and a business client.
We have a VPN into their network on 1 account set against 1 userid.
We then have a SQL SERVER acocunt on their Sql Server 7 machine.

We are running Win2000 and SQL Server 2000 Enterprise Std.

The publication has been set-up and looks okay
so has the pull subscription on our server.

When running the replication though I'm getting the following error.

The process could not connect to Distributor 'NXTIMSVR'.

Any ideas would be appriciated.

CliveWhere is your Distributor reside at? how was the account setup in the Distributor?|||Originally posted by joejcheng
Where is your Distributor reside at? how was the account setup in the Distributor?
The distributer is a the remote client site, I do not know how the distributer was set-up for we have just been asked to look after a section of their data.
Is there something that I could ask them to check.|||The first thing you have to make sure is that your publisher has to be able to access the Distributor. The ID that you setup your publication is the one you need to check. Then your subscirber has to be able to pull in information from the Distirbutor, so it need to be able to access the Distributor also.|||The publisher can see the distributer, its the PULL subscriber that is not connecting to the distributer.

Clive

Replication Problem

Due to network failure (2 hours a day) replication fails between the servers. Normally when network is down for more than an hr, I am not able to restart the replication process by starting the agent. All RED cross marks Appears in the runing services.
Please tell me how do I manage this condition using some frontend deamon that check the replication status, if the replication is down due to network prob or any other reason, daemon tries to restart the replication process.
From http://www.developmentnow.com/g/114_0_0_0_0_0/sql-server-replication.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
Schedule your agents to restart every 5 minutes. If an agent fails it will
fail for a specific reason. The most common reasons are network
interruptions.
I would poll the msrepl_errors table on the distribution database and have
new errors sent to me via a page. I could then evaluate whether the
automatic scheduled restart will clear the error condition.
http://www.zetainteractive.com - Shift Happens!
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
"Madhur" <raj_max@.hotmail.com> wrote in message
news:6a88e5f5-42b0-4969-99cc-c41781493419@.developmentnow.com...
> Due to network failure (2 hours a day) replication fails between the
> servers. Normally when network is down for more than an hr, I am not able
> to restart the replication process by starting the agent. All RED cross
> marks Appears in the runing services.
> Please tell me how do I manage this condition using some frontend deamon
> that check the replication status, if the replication is down due to
> network prob or any other reason, daemon tries to restart the replication
> process.
> From
> http://www.developmentnow.com/g/114_0_0_0_0_0/sql-server-replication.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

Replication performance degrade in unidirectional Direction and lock time out (Update are high t

We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.

1) One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).

2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..

on subscriber database response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.

can any one can also suggest me server level settings for aviding locking time out.

looking for any experieced solution/suggestion.

Thanks in advance.

Hi adrshen,

Need more info. What performance has degraded? You mean user transactions. What was the response time like before and what is it like now.

With merge replication, it would affect the performance as it uses triggers to capture the changes.

regards

Jag

|||plz read my question again .. I just edit it :)|||If it is unidirectional always from publisher to subscriber, then you should look at download_only_articles. This is a special type of setting on an article to indicate that the subscriber will not do DML and it will be more performant. However, note that the performance of the merge agent will increase, but I am not sure if your subscriber itself will start performing better. You can give it a try.

Replication performance degrade in unidirectional Direction (Update are high than inserts)

We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.

1) One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).

2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..

on subscriber database response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.

can any one can also suggest me server level settings for aviding locking time out.

looking for any experieced solution/suggestion.

Thanks in advance.

Hi adrshen,

Need more info. What performance has degraded? You mean user transactions. What was the response time like before and what is it like now.

With merge replication, it would affect the performance as it uses triggers to capture the changes.

regards

Jag

|||plz read my question again .. I just edit it :)|||If it is unidirectional always from publisher to subscriber, then you should look at download_only_articles. This is a special type of setting on an article to indicate that the subscriber will not do DML and it will be more performant. However, note that the performance of the merge agent will increase, but I am not sure if your subscriber itself will start performing better. You can give it a try.

Replication Performance -- Many publishers, 1 subscriber

I have a situation where I need about 50 client machines that are
collecting data to synchronize with a network server on our local 100
Mbps LAN. Am running SQL Server Enterprise 2000 on the back end.
Have experimented with using MSDE on the client machines and using
merge replication with a push subscription to the server. Everything
seems to work good on a single client, but I'm wondering:
- How does this scale? Is it realistic to think I can do this with 50
machines?
- Locking implications. Since this is an automated process, The
ability to have real-time inserts needs to be always available
Typically happens 1 - 2 times per minute. (Updates can be handled
through standard error handling since a human operator is involved.)
- As time goes by and table sizes increase would this slow down, even
if the primary activity on the table is the addition of records?
Thanks in advance.
Mike
Mike,
I would say it is preferable to have a single publisher on the main server
and multiple push subscribers for this topology.
I'm not sure what your separation of inserts and updates is for - could you
explain a little further.
Inserts shouldn't cause any locking contention. For contention of resources,
you could stagger the synchronization, and limit the number of concurrent
merge processes.
Increasing table size for inserts should be no problem. You'll have a
gradual increase in metadata, which will be removed automatically though.
HTH,
Paul Ibison
|||do you need bi-directional replication? From what you describe you have
selected merge, where if you don't need bi-directional replication you can
get away with transactional which will offer better performance.
"Mike Von Stein" <mvonstein@.yahoo.com> wrote in message
news:c92d1754.0405070936.fcab9ad@.posting.google.co m...
> I have a situation where I need about 50 client machines that are
> collecting data to synchronize with a network server on our local 100
> Mbps LAN. Am running SQL Server Enterprise 2000 on the back end.
> Have experimented with using MSDE on the client machines and using
> merge replication with a push subscription to the server. Everything
> seems to work good on a single client, but I'm wondering:
> - How does this scale? Is it realistic to think I can do this with 50
> machines?
> - Locking implications. Since this is an automated process, The
> ability to have real-time inserts needs to be always available
> Typically happens 1 - 2 times per minute. (Updates can be handled
> through standard error handling since a human operator is involved.)
> - As time goes by and table sizes increase would this slow down, even
> if the primary activity on the table is the addition of records?
> Thanks in advance.
> Mike
|||Paul-
Thanks for the info. It was helpful. The reason I was pursuing this
topology is because the clients are the ones collecting the data.
Which need to be warehoused in a central place. (There is also
static setup data that is coming one way from the server, which I'm
doing exactly what you describe.)
The inserts and updates separation issue is because some of the data
comes automatically from the machine while another, different type of
data, data comes from the operator. It's stored in separate tables in
MSDE. The person's data can be corrected by the person to fix
mistakes. The machine data "never makes mistakes" (or at least it
doesn't know it does) so it's only an insert operation.
Thanks again,
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<#vLI3YGNEHA.1616@.TK2MSFTNGP12.phx.gbl>...
> Mike,
> I would say it is preferable to have a single publisher on the main server
> and multiple push subscribers for this topology.
> I'm not sure what your separation of inserts and updates is for - could you
> explain a little further.
> Inserts shouldn't cause any locking contention. For contention of resources,
> you could stagger the synchronization, and limit the number of concurrent
> merge processes.
> Increasing table size for inserts should be no problem. You'll have a
> gradual increase in metadata, which will be removed automatically though.
> HTH,
> Paul Ibison
|||Hilary-
Thanks for the info. Not sure exactly what you mean by "selected
merge"? Like horizontal selection? You're right. Actually, I don't
need bi-directional replication in this case but, to my knowledge,
MSDE (which is running on the client) doesn't support transactional
and I thought snapshot would be inefficient.
Thanks again,
Mike
mvonstein@.yahoo.com (Mike Von Stein) wrote in message news:<c92d1754.0405070936.fcab9ad@.posting.google.c om>...
> I have a situation where I need about 50 client machines that are
> collecting data to synchronize with a network server on our local 100
> Mbps LAN. Am running SQL Server Enterprise 2000 on the back end.
> Have experimented with using MSDE on the client machines and using
> merge replication with a push subscription to the server. Everything
> seems to work good on a single client, but I'm wondering:
> - How does this scale? Is it realistic to think I can do this with 50
> machines?
> - Locking implications. Since this is an automated process, The
> ability to have real-time inserts needs to be always available
> Typically happens 1 - 2 times per minute. (Updates can be handled
> through standard error handling since a human operator is involved.)
> - As time goes by and table sizes increase would this slow down, even
> if the primary activity on the table is the addition of records?
> Thanks in advance.
> Mike
|||I meant "selected" in the sense of "chosen".
MSDE can be a subscriber to a transactional publication, or a publisher for
merge or snapshot publications. On further reflection I guess this is why
you have selected merge.
I have worked on a topology that used over 60 merge subscribers to a single
publisher, so it is highly scalable.
I have heard about people using merge with over 100 subscribers with no real
problems.
Achieving real time replication with merge is a problem. Set your
pollinginterval to something low - perhaps 10s or so.
"Mike Von Stein" <mvonstein@.yahoo.com> wrote in message
news:c92d1754.0405080525.16417da3@.posting.google.c om...
> Hilary-
> Thanks for the info. Not sure exactly what you mean by "selected
> merge"? Like horizontal selection? You're right. Actually, I don't
> need bi-directional replication in this case but, to my knowledge,
> MSDE (which is running on the client) doesn't support transactional
> and I thought snapshot would be inefficient.
> Thanks again,
> Mike
>
> mvonstein@.yahoo.com (Mike Von Stein) wrote in message
news:<c92d1754.0405070936.fcab9ad@.posting.google.c om>...[vbcol=seagreen]
|||Mike,
as you know either way works, but I'd still choose to use use a central
publisher and multiple subscribers, as the metadata and alerts etc are then
in a central place and maintenance is simpler. For merge, the distinction
between publisher and subscriber is not too relevant if you don't have
conflicts.
Cheers,
Paul
|||Paul-
Okay, I guess I was thinking since the clients would be the primary
information generators, they would be the "Publishers", but I guess
the names shouldn't be taken literally in this case. I think I see
the benefits you are suggesting.
One question though. How does this impact future schema changes?
Would you have to drop all subscriptions remotely at the clients and
recreate? (That's a maintenance concern, since there are so many.)
We're kind of in a "formative" phase here and I'm expecting to have to
do some tweaks...
Thanks,
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<#of5O$cNEHA.3328@.TK2MSFTNGP10.phx.gbl>...
> Mike,
> as you know either way works, but I'd still choose to use use a central
> publisher and multiple subscribers, as the metadata and alerts etc are then
> in a central place and maintenance is simpler. For merge, the distinction
> between publisher and subscriber is not too relevant if you don't have
> conflicts.
> Cheers,
> Paul
|||Hilary-
The performance info is interesting. Yes, that is why I was talking
about Merge, but maybe I should look at reversing the terminology like
Paul in this thread suggested. Then maybe I could use transactional
with MSDE?
One concern, though, is the whole reason we are doing this is to
buffer the system to handle things such as planned/unplanned,
server/network downtime, etc. (The clients have to be available 24/7,
but not necessarily the server, if that makes sense...) Read in BOL,
that transactional was for a constant connection. In general, this
would be more like a 99.9% constant connection... So not sure what to
think about that...
Thanks,
Mike
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<O1Ly#gXNEHA.2500@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> I meant "selected" in the sense of "chosen".
> MSDE can be a subscriber to a transactional publication, or a publisher for
> merge or snapshot publications. On further reflection I guess this is why
> you have selected merge.
> I have worked on a topology that used over 60 merge subscribers to a single
> publisher, so it is highly scalable.
> I have heard about people using merge with over 100 subscribers with no real
> problems.
> Achieving real time replication with merge is a problem. Set your
> pollinginterval to something low - perhaps 10s or so.
> "Mike Von Stein" <mvonstein@.yahoo.com> wrote in message
> news:c92d1754.0405080525.16417da3@.posting.google.c om...
> news:<c92d1754.0405070936.fcab9ad@.posting.google.c om>...
|||Transaction does not support bi-directional replication through the wizards. You can configure it for bi-directional replication using the replication stored procedures, or you could use queued replication (but it is tuned for less than 10 subscribers whi
ch rules it out in this case) or immediately updating susbcribers (only if your subscribers/publisher are always well connected).
Transactional does support the subscribers going offline, however you are updating the subscribers, so your best option in this case is merge.
Merge also supports having the publisher and susbcribers going offline.
The caveat in this is the more frequently your publisher/subscribers goes off line the greater the chance of having conflicts.
-- Mike Von Stein wrote: --
Hilary-
The performance info is interesting. Yes, that is why I was talking
about Merge, but maybe I should look at reversing the terminology like
Paul in this thread suggested. Then maybe I could use transactional
with MSDE?
One concern, though, is the whole reason we are doing this is to
buffer the system to handle things such as planned/unplanned,
server/network downtime, etc. (The clients have to be available 24/7,
but not necessarily the server, if that makes sense...) Read in BOL,
that transactional was for a constant connection. In general, this
would be more like a 99.9% constant connection... So not sure what to
think about that...
Thanks,
Mike
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<O1Ly#gXNEHA.2500@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> I meant "selected" in the sense of "chosen".
> merge or snapshot publications. On further reflection I guess this is why
> you have selected merge.
> publisher, so it is highly scalable.
> problems.
> pollinginterval to something low - perhaps 10s or so.
> news:c92d1754.0405080525.16417da3@.posting.google.c om...
> news:<c92d1754.0405070936.fcab9ad@.posting.google.c om>...

Replication outside our network?

OOPS! Forgot the subject line the first time I posted this question....
How do we (transactional) replicate to a database outside our firewall?
We are able to replicate out to that database, but how can we pull back into the network database behind our firewall?
Both servers need access to the Snapshot folder, how is this accomplished, when you don't want outside sources gaining access to your network?
I know that someone out there has to be doing this, I just don't know how it's done.
Any advice will be appreciated.
JUDE
JLS,
we use replication with a VPN between non-trusted domains. There are some
particular configuration settings required - aliases instead of IP
addresses, pass-through authentication etc and this article can help you to
set it up:
http://support.microsoft.com/?id=321822
HTH,
Paul Ibison
|||Thanx so much Paul, I appreciate this info!!!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23IRGA1fLEHA.624@.TK2MSFTNGP11.phx.gbl...
> JLS,
> we use replication with a VPN between non-trusted domains. There are some
> particular configuration settings required - aliases instead of IP
> addresses, pass-through authentication etc and this article can help you
to
> set it up:
> http://support.microsoft.com/?id=321822
> HTH,
> Paul Ibison
>

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)

Replication in 2005

We need to push all the changes across network to a secondary, reporting
server, on a timely manner. We were using replication in SQL200, but it
takes a lot server resources. Any new feathers in SQL2005?
There are a lot of new features in SQL 2005. Transactional replication will
place fewer locks on the publisher by default during snapshot generation.
You could also look at database snapshots or log shipping for this.
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
"awan" <awan@.discussions.microsoft.com> wrote in message
news:5D8A9D21-2AF4-4EB7-AAFA-603FCDA399FA@.microsoft.com...
> We need to push all the changes across network to a secondary, reporting
> server, on a timely manner. We were using replication in SQL200, but it
> takes a lot server resources. Any new feathers in SQL2005?