Replication solution?

We have Epicor Financials as our accounting system which uses SQL Server as
its backend DBMS and a powerbuilder front-end. For the longest time, we've
had the accounting system only in one office (Vancouver) and had our
accountants able to periodically work from home using remote desktop. Now
we've opened a 2nd office located on the other side of the contenient
(Ottawa) and access from Ottawa is really slow. It has been proposed that an
identical SQL Server in the Ottawa would make it faster for the Ottawa
accountants. And a manual coordination of emails and nightly database
restores on either server will keep information synchronized. However, this
could be very impractical and prone to human error. As well, it would be
very doubtful that requests for database restores would be kept only at
night. We have looked at options of SQL Server active-active clustering but
it is unclear how SQL Server can deal with conflict resolutions due to
network failures over a WAN. We suspect that a cheaper option might be
peer-to-peer replication. I'm not sure if it works the same way as log
shipping for redo-ing operations that are queued up before a network failure.
I don't think log shipping would do us any good since we need two active
servers. Any recommendations?
Clustering is a high-availability solution, not performance or distributed
transaction processing since the nodes are generally sitting right next to
each other.
Log Shipping would allow you read-only access to the destination
server...not write. It is a disaster recovery/poor mans reporting server
option.
Replication is your solution for distributed transaction processing...but
you may be limited by the fact that you are using 3rd party software, as in
some cases replication makes (or forces you to make) changes to the schema.
For example, you cannot replicate a table in SQL 2000 Transactional
replication that doesn't have a PK.
I'm not as familiar with PtP replication (new in 2005 I think?), but you are
most likely going to want to look into Merge (good for slow or inconsistent
connectivity situations) or transactional (good for almost always connected
scenarios), with updating subscribers.
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Alvin" <Alvin@.discussions.microsoft.com> wrote in message
news:5BB4190B-A62B-4800-850E-4DDA5C132341@.microsoft.com...
> We have Epicor Financials as our accounting system which uses SQL Server
> as
> its backend DBMS and a powerbuilder front-end. For the longest time,
> we've
> had the accounting system only in one office (Vancouver) and had our
> accountants able to periodically work from home using remote desktop. Now
> we've opened a 2nd office located on the other side of the contenient
> (Ottawa) and access from Ottawa is really slow. It has been proposed that
> an
> identical SQL Server in the Ottawa would make it faster for the Ottawa
> accountants. And a manual coordination of emails and nightly database
> restores on either server will keep information synchronized. However,
> this
> could be very impractical and prone to human error. As well, it would be
> very doubtful that requests for database restores would be kept only at
> night. We have looked at options of SQL Server active-active clustering
> but
> it is unclear how SQL Server can deal with conflict resolutions due to
> network failures over a WAN. We suspect that a cheaper option might be
> peer-to-peer replication. I'm not sure if it works the same way as log
> shipping for redo-ing operations that are queued up before a network
> failure.
> I don't think log shipping would do us any good since we need two active
> servers. Any recommendations?
|||Hi Kevin,
Interesting thought regarding your idea for merge replication but doesn't
that mean that articles are modified/published uni-directionally? I assume
that merge replication is more suited for when you are combining data from of
a hub and spoke deployment rather than everybody modifying everything
(bi-directional).
I'm not sure the procedure in either a clustered scenario or replication
scenario what is the procedure if connectivity between two offices is
disrupted.
"Kevin3NF" wrote:

> Clustering is a high-availability solution, not performance or distributed
> transaction processing since the nodes are generally sitting right next to
> each other.
> Log Shipping would allow you read-only access to the destination
> server...not write. It is a disaster recovery/poor mans reporting server
> option.
> Replication is your solution for distributed transaction processing...but
> you may be limited by the fact that you are using 3rd party software, as in
> some cases replication makes (or forces you to make) changes to the schema.
> For example, you cannot replicate a table in SQL 2000 Transactional
> replication that doesn't have a PK.
> I'm not as familiar with PtP replication (new in 2005 I think?), but you are
> most likely going to want to look into Merge (good for slow or inconsistent
> connectivity situations) or transactional (good for almost always connected
> scenarios), with updating subscribers.
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Alvin" <Alvin@.discussions.microsoft.com> wrote in message
> news:5BB4190B-A62B-4800-850E-4DDA5C132341@.microsoft.com...
>
>
|||I would use bi-directional transactional replication over peer to peer
as it is faster and you don't need the EE edition of SQL Server 2005
for this.
It is more difficult to implement and you have to tear it down and
bring it up again each time you make a schema change.
As Kevin mentions clustering is a HA solution and does not work over
the distance that you have.
If you architect your solution so that each site has a location
identifier on all tables which might encounter conflicts you will not
have a problem with being offline for any appreciable amount of time.
On Jan 8, 5:52 pm, Alvin <Al...@.discussions.microsoft.com> wrote:
> We have Epicor Financials as our accounting system which uses SQL Server as
> its backend DBMS and a powerbuilder front-end. For the longest time, we've
> had the accounting system only in one office (Vancouver) and had our
> accountants able to periodically work from home using remote desktop. Now
> we've opened a 2nd office located on the other side of the contenient
> (Ottawa) and access from Ottawa is really slow. It has been proposed that an
> identical SQL Server in the Ottawa would make it faster for the Ottawa
> accountants. And a manual coordination of emails and nightly database
> restores on either server will keep information synchronized. However, this
> could be very impractical and prone to human error. As well, it would be
> very doubtful that requests for database restores would be kept only at
> night. We have looked at options of SQL Server active-active clustering but
> it is unclear how SQL Server can deal with conflict resolutions due to
> network failures over a WAN. We suspect that a cheaper option might be
> peer-to-peer replication. I'm not sure if it works the same way as log
> shipping for redo-ing operations that are queued up before a network failure.
> I don't think log shipping would do us any good since we need two active
> servers. Any recommendations?
|||Sorry for conflict resolution inquiry, I meant the transactional replication.
"Kevin3NF" wrote:

> Clustering is a high-availability solution, not performance or distributed
> transaction processing since the nodes are generally sitting right next to
> each other.
> Log Shipping would allow you read-only access to the destination
> server...not write. It is a disaster recovery/poor mans reporting server
> option.
> Replication is your solution for distributed transaction processing...but
> you may be limited by the fact that you are using 3rd party software, as in
> some cases replication makes (or forces you to make) changes to the schema.
> For example, you cannot replicate a table in SQL 2000 Transactional
> replication that doesn't have a PK.
> I'm not as familiar with PtP replication (new in 2005 I think?), but you are
> most likely going to want to look into Merge (good for slow or inconsistent
> connectivity situations) or transactional (good for almost always connected
> scenarios), with updating subscribers.
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Alvin" <Alvin@.discussions.microsoft.com> wrote in message
> news:5BB4190B-A62B-4800-850E-4DDA5C132341@.microsoft.com...
>
>
|||The best example I can give for what merge was intended for is a Central
server with a mobile workforce in the field (like sales force) running SQL
Express/MSDE on their laptops. They are usually disconnected and synch on
the next opportunity.
Transactional is better (as Hilary mentions) for a very stable link between
two offices.
MS Clustering does not come into play in the scenario you describe, as the
nodes must be near each other an only one of them can read-write to the
database
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Alvin" <Alvin@.discussions.microsoft.com> wrote in message
news:7C9F5146-9105-4FC0-9688-9384DB1A802F@.microsoft.com...[vbcol=seagreen]
> Hi Kevin,
> Interesting thought regarding your idea for merge replication but doesn't
> that mean that articles are modified/published uni-directionally? I assume
> that merge replication is more suited for when you are combining data from
> of
> a hub and spoke deployment rather than everybody modifying everything
> (bi-directional).
> I'm not sure the procedure in either a clustered scenario or replication
> scenario what is the procedure if connectivity between two offices is
> disrupted.
>
> "Kevin3NF" wrote:
|||Hi Hilary,
Wow I just checked the Microsoft site for the implementation of
bi-directional transactional replication. It imples that you'd need to
create at least 3 stored procedures (insert/update/delete) per table. For
our case, the current accounting system has 12 databases with identical
schemas. Each database has about 1200 tables. So working out the math, if
takes me about 10 mins per stored proc, it would take me about 7 yrs?
(12 databases) * (1200 tables) * (3 sp) * (2 nodes) * (10mins) *
(1hr/60mins) * (1wk/40hrs) * (1yr/52wks)
Unless I missed something, it looks like I better start young.
"Hilary Cotter" wrote:

> I would use bi-directional transactional replication over peer to peer
> as it is faster and you don't need the EE edition of SQL Server 2005
> for this.
> It is more difficult to implement and you have to tear it down and
> bring it up again each time you make a schema change.
> As Kevin mentions clustering is a HA solution and does not work over
> the distance that you have.
> If you architect your solution so that each site has a location
> identifier on all tables which might encounter conflicts you will not
> have a problem with being offline for any appreciable amount of time.
> On Jan 8, 5:52 pm, Alvin <Al...@.discussions.microsoft.com> wrote:
>

Replication schedule

How can I check if the transactional replication is set up in continuous
mode or on a set time such as replicate once every hour ? Thanks
Have a look at the distribution agent's job schedule.
Rgds,
Paul Ibison

Replication rows getting deleted at publisher

I am using merge replication between two servers.
When i insert data into a perticular table ..the data gets deleted after some time.
Anyone faced this problem?In your publisher table, how many rowguid id in each table?|||The each table has one rowguid...

Replication Performance?

I'm having a tough time finding any good resources on Sql Server Replication Performance. Are there any benchmarks / state of any kind? How well does replication scale out?

In my scenario, I have one central publisher and several large tables, all with hundreds of millions of records. Every day I may insert/update millions of records in the publisher, and then I need to replicate the changes (in a few hours at most) onto a pool of subscribers, while they remain online.

Is the replication story robust enough to handle a situation like this?

Is this merge or transactional replication, and on what version lf sql server? And what is the approximate transaction size (meaning how many commands per txn)? Assuming this is transactional replication, performance will depend on your hardware, network bandwidth, size of changes (in K or MB), # of indexes at the subscriber, type of changes (ins, upd or del), existance of triggers, etc.

Throughput of several thousand commands per second is not out of the question, but as I mentioned above, it depends on the above. Tell us more about your scenario and we can comment further. You can reference this basic performance guide, http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx.

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 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 or bcp or DTS?

Hi,
I have 2 instances of databases in 2 different cities.Data entry /
updations are going on at both the sides.At any point of time data in
both the databases should be same.Currently every alternate day, i am
performing update / insert / deletes on incremental basis on both the
sides.
Can I implement replcation? What r the requisites I have to follow for
replication?

I do not have permenant connectyvity but I need to connect using ISDN
line to a remote location as and when required for doing
synchrinisation of data.

Thanks in advance.

Regards
Rohit

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Dis...pict236236.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=819975Rohit (UseLinkToEmail@.dbForumz.com) writes:
> I have 2 instances of databases in 2 different cities.Data entry /
> updations are going on at both the sides.At any point of time data in
> both the databases should be same.Currently every alternate day, i am
> performing update / insert / deletes on incremental basis on both the
> sides.
> Can I implement replcation? What r the requisites I have to follow for
> replication?
> I do not have permenant connectyvity but I need to connect using ISDN
> line to a remote location as and when required for doing
> synchrinisation of data.

I'm a little confused, since you say that at any point in time, the
contents of the databases should be the same. But then you talk about
not having permanent connectivity. Is that the connectivity between
the databases, or your own connectivity.

The gist of replication is that the I in ACID for transactions is
relaxed. That is, transactions should normally be Atomic, Consistent,
Immediate and D-something. With replication, you say that I does not
matter. A transaction can be replicated later at any time. This is good
if you have disconnected servers. For connected servers, replication
can still be acceptable, since the delay in most cases will not be
noticeable.

Since you need to send data in both directions, it looks as if you are
into merge replication. There are probably a bunch of requirements,
for instance the table must have GUID as keys. And then you need rules
to handle conflicts. (What if the same row is updated at both sites?)

My own knowledge only goes as far about this, so I would recommend you
to ask in microsoft.sqlserver.public.replication.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"" wrote:
> Rohit (UseLinkToEmail@.dbForumz.com) writes:
> > I have 2 instances of databases in 2 different
> cities.Data entry /
> > updations are going on at both the sides.At any point of
> time data in
> > both the databases should be same.Currently every alternate
> day, i am
> > performing update / insert / deletes on incremental basis on
> both the
> > sides.
> > Can I implement replcation? What r the requisites I have to
> follow for
> > replication?
> > I do not have permenant connectyvity but I need to connect
> using ISDN
> > line to a remote location as and when required for doing
> > synchrinisation of data.
> I'm a little confused, since you say that at any point in
> time, the
> contents of the databases should be the same. But then you
> talk about
> not having permanent connectivity. Is that the connectivity
> between
> the databases, or your own connectivity.
> The gist of replication is that the I in ACID for transactions
> is
> relaxed. That is, transactions should normally be Atomic,
> Consistent,
> Immediate and D-something. With replication, you say that I
> does not
> matter. A transaction can be replicated later at any time.
> This is good
> if you have disconnected servers. For connected servers,
> replication
> can still be acceptable, since the delay in most cases will
> not be
> noticeable.
> Since you need to send data in both directions, it looks as if
> you are
> into merge replication. There are probably a bunch of
> requirements,
> for instance the table must have GUID as keys. And then you
> need rules
> to handle conflicts. (What if the same row is updated at both
> sites?)
> My own knowledge only goes as far about this, so I would
> recommend you
> to ask in microsoft.sqlserver.public.replication.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Thanks for your reply. probably I wrote it in a wrong way.
There is no permenant connectivity between the two locations. Whenever
mself / management decides to Synch. the data I connect using ISDN
connection , perform export / Import Using BCP at both the sides and
then run SQL Script to push the incremental data inside the database.
Before going to the database it comes to a buffer database FROM text
files.

regards
Rohit

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Dis...pict236236.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=821317|||Rohit (UseLinkToEmail@.dbForumz.com) writes:
> Thanks for your reply. probably I wrote it in a wrong way.
> There is no permenant connectivity between the two locations. Whenever
> mself / management decides to Synch. the data I connect using ISDN
> connection , perform export / Import Using BCP at both the sides and
> then run SQL Script to push the incremental data inside the database.
> Before going to the database it comes to a buffer database FROM text
> files.

OK. As I said, merge replication could be worth looking into. But I know
too little of merge replication as well as your application to say for
sure that it is a good idea.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

replication merge not put instantaneous time out

replication type merge
suscription two(a-b)
function suscription (a)
not function suscription (b)
What this is the problem !! Help
error: replication merge not put instantaneous time out
thanks!
Christian,
please can you post up the complete error text from the merge agent, and
also list any distinctions between subscriber a and b.
TIA,
Paul Ibison

replication log reader failed

Hi all,
Sql server 7.0
I have replication jobs which replicates the data from one
server to another.
one of our server was down for some duration of time and
the replication job log reader has failed it gives the
error msg log reader agent: "the process could not
execute 'sp_repldone/sp_replcount".
pls help me in solving this problem.
TIA
Haseeb,
I thought these issues had been sorted in SP>=2. If you don't have it
installed it's worth putting on. Either way, you could try increasing the
Query timeout (default: 300 seconds) on the logreader by editing the
QueryTimeout option as this is I think the issue.
HTH,
Paul Ibison
|||I also had this problem and setting the QueryTimeout did not help. I tried everything in every posting I could find and nothing worked. I ended up deleting all my publications to reset the Log Reader Agent, then re-setup the publications and subscriptio
ns. Once the Log Reader was recreated, everything worked fine again.
"Paul Ibison" wrote:

> Haseeb,
> I thought these issues had been sorted in SP>=2. If you don't have it
> installed it's worth putting on. Either way, you could try increasing the
> Query timeout (default: 300 seconds) on the logreader by editing the
> QueryTimeout option as this is I think the issue.
> HTH,
> Paul Ibison
>
>

Replication latency

Hi,
I have set up a transactional replication on two SQL Server
2000 (SP3) machines.
How do I determine the time it passes between the
transaction was placed in the transaction log at the
Publisher and it has finished at the Subscriber?
--
Many thanks,
OskRun performance monitor... YOu should notice that there will now be new
counters for replication, one of which is latency...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:2aa901c509c4$9d8b76d0$a501280a@.phx.gbl...
> Hi,
> I have set up a transactional replication on two SQL Server
> 2000 (SP3) machines.
> How do I determine the time it passes between the
> transaction was placed in the transaction log at the
> Publisher and it has finished at the Subscriber?
> --
> Many thanks,
> Osk

Replication latency

Hi,
I have set up a transactional replication on two SQL Server
2000 (SP3) machines.
How do I determine the time it passes between the
transaction was placed in the transaction log at the
Publisher and it has finished at the Subscriber?
Many thanks,
Osk
Run performance monitor... YOu should notice that there will now be new
counters for replication, one of which is latency...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:2aa901c509c4$9d8b76d0$a501280a@.phx.gbl...
> Hi,
> I have set up a transactional replication on two SQL Server
> 2000 (SP3) machines.
> How do I determine the time it passes between the
> transaction was placed in the transaction log at the
> Publisher and it has finished at the Subscriber?
> --
> Many thanks,
> Osk

Replication latency

Hi,
I have set up a transactional replication on two SQL Server
2000 (SP3) machines.
How do I determine the time it passes between the
transaction was placed in the transaction log at the
Publisher and it has finished at the Subscriber?
Many thanks,
OskRun performance monitor... YOu should notice that there will now be new
counters for replication, one of which is latency...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:2aa901c509c4$9d8b76d0$a501280a@.phx.gbl...
> Hi,
> I have set up a transactional replication on two SQL Server
> 2000 (SP3) machines.
> How do I determine the time it passes between the
> transaction was placed in the transaction log at the
> Publisher and it has finished at the Subscriber?
> --
> Many thanks,
> Osk